More on the parallel execution queue

In the last post, I looked at how the 11GR2 parallel execution queue worked.  Essentially the holder of the JX enqueue is the next in line for parallel execution and others in the queue are waiting on the JX enqueue.  

I thought it would be useful to write an SQL to list the PQO statements running, and those waiting.  The script is here:  it joins V$SESSION, V$WAIT_CHAINS, V$PX_SESSION and V$SQL to show execution parallel SQLs and the SQLs holding or waiting on the JX enqueue. 

Here's some sample output:

 

The NO_STATEMENT_QUEUING hint

There's a new hint - NO_STATEMENT_QUEUING - that will prevent a SQL from being queued regardless of the setting of PARALLEL_DEGREE_POLICY.  If the NO_STATEMENT_QUEUING hint is included in the SQL, and there are insufficient parallel query processes to run at the requested Degree of Parallelism, then the SQL will be downgraded, serialized or fail with an error, depending on the settings for PARALLEL_MIN_PERCENT.   It will not, of course, be queued for later execution using the JX enqueue mechanism. 

The inverse hint - STATEMENT_QUEUING - causes statements to be queued even if the setting for PARALLEL_DEGREE_POLICY is LIMITED or MANUAL. 

Comments

[Krishna]

Thanks for the script!