Oracle Partitioning and Parallel Execution for Performance seminar by Tanel Poder

Tanel was our visitor last week as I mentioned earlier, he did a one day seminar on partitioning and parallel execution. There were lots of interesting discussions for me, I tried to share some of my notes just to give an idea how Tanel’s experience may affect your daily Oracle database administration or development routine.

Other ways for detecting partition pruning rather than Pstart and Pstop info at DBMS_XPLAN:
– Event 10128, semi documented in Note 209070.1, create partition pruning tracing table under your schema KPAP_PRUNING
– Samping ROW_WAIT_OBJ# on V$SESSION with Snapper or ASH, you get the dba_object.data_object_id values
– Taking snapshots of V$SEGMENT_STATISTICS logical IO for each partition segment

Partitioning for OLTP DML Concurrency
– Partitioning can be effectively used for reducing concurrency on right hand index blocks in OLTP(ever increasing sequence or timestamp based columns)
– Hash sub partitioning for meaningless pkey columns, the kgghash() function used for row placement by hash is optimized for power of 2 buckets
– Partitioning for data load concurrency, below option will take TM lock only on the partition and allow multiple load operations on the same table
INSERT /*+ APPEND */ INTO taget_table PARTITION(target_partition)
SELECT * FROM source_table;

Parallel Query Execution change with 10g
– In 9i the QC unparses transformed query text and send a copy for each slave for separate execution. In 10g+ each slave executes the same cursor

Parallel execution more resources
– Especially because of the PX communication overhead
– And additionally PX operation on a table requires a segment level checkpoint.
– With 11.2 in-memory parallel execution if PARALLEL_DEGREE_POLICY is set to AUTO
– So not everything should use paralelism and not every system is a candidate for parallelism, even in DWs(like ETL metadata updates etc.)

For parallel DML always remember to enable parallel dml at session level
– alter session enable parallel DML;
insert /*+ parallel(4) pq_distribute(eo none) */ into etl_orders eo
select * from tab;
– Query PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS columns of V$SESSION to check parallel DML is enabled or not for a session
– if data read is highly skewed, different slave produce way amount of rows, you may want to distribute rows for loading with pq_distribute(eo random) hint

Major Parallel Execution bottlenecks
– Serially executed steps in PQ plans, check for P->S and S->P operations in the middle of the execution plans. “IN-OUT” column is empty for serial operations in DBMS_XPLAN
– Too much communication between slaves, if possible replace a BROADCAST distribution with HASH or PART
– Skewed row distribution between producers and consumers, check V$PQ_TQSTAT if some slaves produced/consumed signaficantly more rows than others in the slave set, swith form HASH to BROADCAST ot PARTITION distribution
– Unbalanced hardware configuration, not enough IO bandwith, the very common reason, query ASH or run snapper of PX slaves to measure time waited for IO

Parallel Execution waith events and sql trace
– Metalink Note 191103.1 for PQ wait events
– After 10g+ before tkprof utulity to aggregate the multiple tracefiles produced for each slave use trcsess utulity

Parallel Execution and RAC
– In 10g set PARALLEL_INSTANCE_GROUPS parameter in each instance to assign the instances to groups
ALTER SYSTEM SET instance_groups = ‘rac01′,’all’,’batch’ ;
ALTER SYSTEM SET instance_groups = ‘rac02′,’all’,’batch’ ;
ALTER SYSTEM SET instance_groups = ‘rac03′,’all’,’oltp’ ;
ALTER SYSTEM SET instance_groups = ‘rac04′,’all’,’oltp’ ;
— Before running the PQ statement SET parallel_instance_groups
ALTER SESSION SET parallel_instance_groups = ‘batch’ ;
— The slaves will run only on the instances associated with the ‘batch’ group
– In 11g again parallel_instance_groups controls the allowed groups where QC can schedule inter instance PQ slaves, but this time slaves can only run on the instances which allow running the same “service” the QC connected through.
— Alternatively
ALTER SESSION set PARALLEL_INSTANCE_GROUP = ‘service1′,’service3’ ;

Parallel Execution Overview
– PX COORDINATOR: Query Coordinator(QC), distributes the work to slaves and returns results back to the client. Additionally performans work if something has to be done serially.
– PX SEND QC: Sends produced rows to QC via a table queue
– PX BLOCK ITERATOR: Operation which picks different ranges ıf PX granules to read different slaves
– Table Queues: In-memory communication channel between PX slaves, if SGA_TARGET is in use or PARALLEL_AUTOMATIC_TUNING is set to TRUE a memory buffer inside large pool
– QC(RAND): The row source is sending its rows back to QC

Things to remember
– Watch out for accidently enabled paralelism especially on OLTP, after parallel index builds and rebuilds remember to ALTER INDEX NOPARALLEL
– The Cost we see in DBMS_XPLAN does not include PX distribution cost, however CBO does choose PC distribution based on cost(10053 CBO tracefile shows that the costing is done)
– Use PARALLEL_MIN_PERCENT parameter to decide the minimum % of the requested Degree of Paralellism(DOP) query should be able to allocate in order to run the query
– PARALLEL_EXECUTION_MESSAGE_SIZE default to 16KB after 11.2
– Use ALTER SESSION FORCE PARALLEL QUERY PARALLEL n; to force session level paralelism

Oracle Partitioning and Parallel Execution for Performance seminar by Tanel Poder

ps: Check out Oracle Sweden User Group(ORCAN) Event presentations of Hüsnü

I may be back :)

It has been a while again since my last blog, so I wanted to give some quick highlights.

– Turkcell bought a full Rac Sun Exadata V2 box, we are still in test phase and we plan to migrate our 100+ TB DWH this August. This installation and migration is being a major experience for both us and Oracle. My team has several customer experience presentation proposals on ODI with Exadata and CEP for OOW and UKOUG, so I hope these proposals are being accepted and we can meet to share these experiences in detail.

– We are working close with Oracle Data Mining product development team for the In-Database mining options on 11.2 and Exadata. Automated SAS model migration is an important development. We will be at Krakow, Poland for Oracle’s DWH Global Leaders event to meet with the product development team.

OracleTurk is now 11 years old, with each year the forum activity is getting much more mature, last year I passed to read only mode after one of my close friend’s warning, he told me to give chance for the new people to answer since I was mad for answering any question on this forum for years. It was very hard for me I must admit, but now I see this warning was very helpful since lots of new faces is now around the forum :)

Tanel and Tom will be at Istanbul this week, I will attend Tanel’s session at my company. If I can, I will try to share my seminar notes like I did in old days.

– My friend, colleague Hüsnü who is the first Turkish Oracle ACE Director and Oracle DBA of year 2009 has quited his job at Turkcell and started a new journey. I am very sad about this decision on one hand since now we are apart but happy for Hüsnü since I am very pretty sure that he will face and win this new challenge.

– Recently I saw that one of my Turkish DBA friend Kamran has been rewarded as an Oracle ACE, I am very happy to see young passionate and hard working people to achieve these kind of success and become another inspiration to the newbies.

It is my 8th month as the team leader for the BI development team at Turkcell group. Leading my team is very exciting, I am also inside lots of non-Oracle projects like developing a Turkish text mining and web crawler product, AbInitio Linux migration, developing a Java based ETL product etc. I have the prvilige to work with very talented young software engineers so leading my team is a great joy and experience until now. Where as being managed as a manager is something I am still being challenged, I couldn’t get used to this feeling until now. Anyway, as once Lance Armstrong said: “Pain is temporary, but quiting lasts forever.” so I continue to fight for what I believe in.

ps: I chosed this title for this post just to make an expression for my last comeback, I hope this blog may be a similar comeback :)