May 22, 2010

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.)

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