New parameters and parallel behaviour are introduced in Oracle 11g that simplifies management of parallel DOP calculations just before execution of a query in database.

New parameters

PARALLEL_MIN_TIME_THRESHOLD - any number > 0, AUTO(default)
PARALLEL_DEGREE_POLICY - MANUAL(default),LIMITED,AUTO
PARALLEL_DEGREE_LIMIT - any number > 1, CPU(default), IO, AUTO
PARALLEL_FORCE_LOCAL - TRUE,FALSE(default)

Changed parameters:

PARALLEL_IO_CAP_ENABLED  - is deprecated and remapped 
to PARALLEL_DEGREE_LIMIT set to IO
PARALLEL_THREADS_PER_CPU - is now hidden parameter
 _PARALLEL_THREADS_PER_CPU
PARALLEL_AUTOMATIC_TUNING - is deprecated and should not 
be used. It means should have default value FALSE.

DOP – degree of parallelism

DOP features:

• DOP is the number of parallel execution servers used by one parallel operation
• DOP applies only to intraoperation parallelism
• SQL statements can use maximum 2 x intraoperation=called interoperation parallelism

Default DOP

DOP can be calculated automatically by Oracle database:

• For a single instance DOP is calculated as

  PARALLEL_THREADS_PER_CPU * CPU_COUNT

• For Rac configuration DOP is calculated as

  PARALLEL_THREADS_PER_CPU * CPU_COUNT*INSTANCE_COUNT

It’s not recommended to use default parallelism in multiuser environments

PARALLEL_MIN_TIME_THRESHOLD

This new parameter limits parallelism to SQL statements which are worth it. If computed elapsed time for query just before execution is below this threshold the query is executed serially. In other case the query can be considered to execute in parallel.

Accepted values:

• greater than 0 – in seconds
• AUTO – the threshold is computed by system. It’s DEFAULT value. Usually computed as 10 seconds.

Old parameter PARALLEL_MIN_PERCENT works as in past releases if the estimated elapsed time exceeds PARALLEL_MIN_TIME_THRESHOLD

Changeable with:

• ALTER SYSTEM
• ALTER SESSION

PARALLEL_DEGREE_POLICY

This parameter specifies whether automatic degree of parallelism, statement queuing, and in-memory parallel execution will be enabled.

Accepted values:

• MANUAL – disables automatic degree of parallelism, statement-queuing and in-memory execution. Reverts behaviour to pre-11.2 release. So statement can allocate up to PARALLEL_MAX_SERVERS. It’s DEFAULT value.
• LIMITED – Enables automatic degree of parallelism but statement queuing and in -memory parallel execution is disabled. Automatic DOP is applied only to statements that access tables or indexes declared explicitly with the PARALLEL clause. Tables and indexes that have a DOP specified use that explicit DOP settings.
• AUTO – Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution

Changeable with:

• ALTER SYSTEM
• ALTER SESSION

PARALLEL_DEGREE_LIMIT

This parameter decides about maximum DOP that can be used when automatic degree of parallelism turned on. Automatic DOP is turned on if PARALLEL_DEGREE_POLICY is LIMITED or AUTO.

Accepted values:

• CPU – maximum degree of parallelism is limited by the number of CPUs in the system. Calculated by PARALLEL_THREADS_PER_CPU*CPU_COUNT*number of available instances. It’s DEFAULT value.
• IO – maximum degree of parallelism is limited by I/O of the system. Value is calculated by divide total throughput by maximum I/O per process. DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure must be started in order to use IO settings .
• integer – defines manually maximum degree of parallelism.

It’s possible to user hint to force greater DOP.

Changeable with:

• ALTER SYSTEM
• ALTER SESSION

PARALLEL_FORCE_LOCAL

This parameters is important in RAC environments. If set to TRUE restricts allocation of parallel servers to node where initial connection was mapped.

Accepted values:

• TRUE
• FALSE – It’s DEFAULT value

Changeable with:

• ALTER SYSTEM
• ALTER SESSION

PARALLEL hint

Let’s first set parallelism on the object level.

alter table emp parallel 4;
alter table dept noparallel;

In old behaviour If PARALLEL_DEGREE_POLICY is set to MANUAL then PARALLEL hint is related to object level. In this case overrides parallelism for table EMP.

select /*+ PARALLEL(emp 2) */ * from dept, emp 
where dept.deptno=emp.deptno;

In new behaviour If PARALLEL_DEGREE_POLICY is set to LIMITED, AUTO then PARALLEL hints is related to statement not object

select /*+ PARALLEL(4) */ * from dept,emp
where dept.deptno=emp.deptno;

Another example how to request AUTO DOP

SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno;

or force the statement to use Oracle Database 11g Release 1 (11.1) behavior

SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno;

Explain plan behaviour

Explain plan is modified to show computed degree of parallelism that optimizer will use.

You can find in explain plan following notes:

• computed degree of parallelism is <DOP>
• computed degree of parallelism is <DOP> derived from scan of <object name>
• computed degree of parallelism is <DOP> because of degree limit
• computed degree of parallelism is 1 because of parallel threshold
• computed degree of parallelism is 1 because of parallel overhead
• degree of parallelism is <DOP> because of hint

EXPLAIN PLAN FOR
SELECT SUM(AMOUNT_SOLD) FROM SH.SALES;

PLAN TABLE OUTPUT

Plan hash value: 672559287
-----------------------------------------------
| Id |          Operation    |   Name |  Rows |
-----------------------------------------------
|  0 | SELECT STATEMENT      |        |    1  |
|  1 | SORT AGGREGATE        |        |    1  |
|  2 |  PX COORDINATOR       |        |    1  |
|  3 |   PX SEND QC(RANDOM)  |:TQ10000|    1  |
|  4 |    SORT AGGREGATE     |        |    1  |
|  5 |     PX BLOCK ITERATOR |        |   960 |
|  6 |     TABLE ACCESS FULL |  SALES |   960 |
-----------------------------------------------

Note
-----
 - Computed Degree of Parallelism is 2
 - Degree of Parallelism of 2 is derived from scan of object SH.SALES

Parallel Statement Queuing

NOTE – this option is automatically available when PARALLEL_DEGREE_POLICY is set AUTO or enforced by new hint STMT_QUEUING.

Automatic DOP calculation can cause quick exhaust of parallel execution process. To avoid running queries in serial mode or exhausting system Oracle introduced parallel statement queuing FIFO – first in first out.

When parallel statement request DOP Oracle checks if parallel servers are available. If not the query is queued.

Parallel statements are queued if running the statements would increase the number of active parallel servers above the value of the PARALLEL_SERVER_TARGET initialization parameter. For example, if PARALLEL_SERVER_TARGET is set to 64, the number of current active servers is 60, and a new parallel statement needs 16 parallel servers, it would be queued because 16 added to 60 is greater than 64, the value of PARALLEL_SERVER_TARGET.  It is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement gets all of the parallel server resources required and to prevent overloading the system with parallel server processes.

If a statement has been queued, it is identified by the resmgr:pq queued wait event.

You can control parallel statement queuing by using new hints:

• NO_STATEMENT_QUEUING – it ignores parallel statement queuing

select /*+ NO_STATEMENT_QUEUING */ from emp;

• STATEMENT_QUEUING – delays statement execution until resources are availbale without having PARALLEL_DEGREE_POLICY is set to AUTO

select /*+ STATEMENT_QUEUING */ from emp;

New status QUEUED value available in V$SQL_MONITOR, V$SQL_PLAN_MONITOR

SELECT s.sql_id, s.sql_text 
FROM v$SQL_MONITOR m, v$SQL s
WHERE m.status='QUEUED'
AND   m.sql_id = s.sql_id;

New wait events

• resmgr:pq queued –  indicates queued queries

SELECT s.sid, s.serial#, s.event, s.sql_id, sq.sql_text 
  FROM v$session s, v$sql sq
 WHERE s.event='resmgr:pq queued'
   AND s.sql_id = sq.sql_id;

sql monitoring tool shows new status – queued queries as “Clock”

 

Details of queued query

In-Memory Parallel Query

This option enables to store output of parallel computation in buffer cache instead of PGA (direct mode). Moreover data stored in buffer cache can be shared between RAC instances but not as cache fusion but parallel process which requires it will read it directly from memory on another node and will return only required blocks. This option was introduced because todays’ database usually has got huge buffer caches.

Oracle database decides by its own if to read data to buffer cache or just directly to PGA to avoid thrashing buffer cache. This option enables to avoid I?O if a given object used in parallel queries is often read.

To turn this option PARALLEL_DEGREE_POLICY must be set to AUTO

Have a fun 🙂

Tomasz