Oracle 11g introduced parallel statement queueing as a new feature.
Till 10g, if a running statement was already using all the parallel processes as specified by PARALLEL_SERVERS_TARGET and another statement requiring parallel servers was issued, second statement would fail .
As of 11g, in the same situation as above, second statement would be placed in a queue and would start executing as soon as parallel servers are freed by the first statement.
This feature is influeneced by following parameters:
PARALLEL_DEGREE_POLICY – should be AUTO to enable parallel statement queueing
PARALLEL_MAX_SERVERS – The maximume no. of parallel servers that can be allotted.
PARALEL_SERVERS_TARGET – speciifes the total no. of parallel servers currently available for use.
Upper limit decided by PARALLEL_MAX_SERVERS
Let’s demonstrate…
– Set parallel_servers_target to 4 so that if a statement has used up 4 processes, and another statement
requiring parallel servers is issued, second statement should be queued
SQL>
alter system set parallel_degree_policy=auto;
alter system set parallel_servers_target=4;
alter system set parallel_max_servers=20;
SQL> sho parameter parallel
NAME TYPE VALUE
———————————— ———– ——————————
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 20
parallel_min_percent integer 0
NAME TYPE VALUE
———————————— ———– ——————————
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 4
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
– Let us see the execution plan of the following statement –
– Note that DOP as computed automatically = 2
Since two processes requiring parallel servers are in the query i.e. Table access Full and sort order by, each of the processes runs with DOP = 2 and hence 4 parallel servers will be used by this query
SESS1>explain plan for
select * from sh.sales
order by 1,2,3,4;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————–
Plan hash value: 2055439529
—————————————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
—————————————————————————————————————————————–
| 0 | SELECT STATEMENT | | 918K| 25M| | 297 (11)| 00:00:04 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 918K| 25M| | 297 (11)| 00:00:04 | | | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 918K| 25M| 42M| 297 (11)| 00:00:04 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 918K| 25M| | 274 (3)| 00:00:04 | | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 918K| 25M| | 274 (3)| 00:00:04 | | | Q1,00 | P->P | RANGE |
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————–
| 6 | PX BLOCK ITERATOR | | 918K| 25M| | 274 (3)| 00:00:04 | 1 | 28 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| SALES | 918K| 25M| | 274 (3)| 00:00:04 | 1 | 28 | Q1,00 | PCWP | |
—————————————————————————————————————————————–
Note
—–
- automatic DOP: Computed Degree of Parallelism is 2
– Run the above query in two sessions –
– I have given a hint to identify the sessions from which the statement has been issued
– The query in first session executes
SESS1>select /*+ sess1 */ * from sh.sales
order by 1,2,3,4;
– The query in second session hangs
SESS2>select /*+ sess2 */ * from sh.sales
order by 1,2,3,4;
– Check the status of the queries from a third session
– Note that statement issued from the first session is executing while the statement issued from the second session is queued as all the 4 available parallel servers have been used by the first query
SYS> col sql_text for a50
select status, sql_text
from v$sql_monitor
where sql_text like ‘%sess%';
STATUS SQL_TEXT
——————- ——————————————
EXECUTING select /*+ sess1 */ * from sh.sales
order by 1,2,3,4
QUEUED select /*+ sess2 */ * from sh.sales
order by 1,2,3,4
– Abort the query in session 1 by pressing Ctrl-C –
– The query starts executing in session 2 —
– The status of the first query changes from EXECUTING to DONE and
– The status of the SECOND query changes from QUEUED to executing
SQL> col sql_text for a50
select status, sql_text
from v$sql_monitor
where sql_text like ‘%sess%';
STATUS SQL_TEXT
——————- ————————————————–
DONE (FIRST N ROWS) select /*+ sess1 */ * from sh.sales
order by 1,2,3,4
EXECUTING select /*+ sess2 */ * from sh.sales
order by 1,2,3,4
References:
———————————————————————————————————————
Related links:
——————