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.

Oracle 12c optimizer changes


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

SQL> SELECT p1,p1raw,sql_id FROM  gv$session_wait
WHERE EVET=’ latch: cache buffers chains';

Then identify the object ID and other details using the HLADDR=P1RAW.

set linesize 200
col object_name for a40
SELECT object_name,object_type,dbablk
FROM dba_objects o, x$BH b
WHERE  o.object_id=b.obj AND
hladdr IN (
SELECT p1raw FROM gv$session
WHERE event='latch: cache buffers chains');
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: