Mar-15

Oracle 12c introduces two new parallel query distribution features to communicate between the producer and consumer slaves. They are adaptive broadcast distribution and Hybrid distribution for parallel HASH and MERGE skewed join expressions.

For a parallel execution status – V$PQ_TQSTAT is very interesting and some of the columns are important. The data in view will be kept as long as the session is active. Some of the interesting columns

SERVER_TYPE è Can be consumer or producer or merger.

NUM_ROWS è Number of rows produced or consumed

BYTES è Number of bytes produced / consumed.

Other useful columns are showing, how effectively the processes communicated between the processes during the parallel query execution. Specially to analyze skewed parallel executions.

The skewed distribution is quite oblivious in 11.2 where a slave requests for a data set to work on, the QC hands over a set of granules rather a single granule based on a threshold _px_granule_batch_size. If you are seeing a consistently high skewed distribution, then set the _px_granule_batch_size to 1 (where the default value is 10) to distribute a single granule for each salve request. (See MOC note: 1228903.1)

There are different types of data distribution between producers and consumers.

BROADCAST è is the method of distributing rows from a producer slave to all consumer slave is known as BROADCAST distribution.

PART è is the method when a certain slave set restricted to work on a range partitions is known as PART distribution.

OPTIMIZER_DYNAMIC_SAMPLING=11. Oracle 12c onwards, oracle will gather dynamic statistics while parsing if the available statistics is stale or not usable due to composite column evaluation. This feature is back ported to 11.2.0.4. The default value for this parameter is 4.

For the event ‘latch: cache buffers chains’, P1RAW gives the latch address of the Cache Buffer Chains which is linked to the X$BH using the HLADDR and the DBABLK gives the BLOCK_ID in DBA_EXTENTS which help us to identify the hot blocks and extents.