Oracle 11g release 2 introduced a number of enhancements related to parallel query processing.
One new parameter parallel_degree_policy has been introduced. Setting this parameter to MANUAL results in oracle 11g release1 behavior. Setting this parameter to Auto provides three facilities:
1. Each SQL statement will be considered for parallel execution according to a threshold.
2. in-memory parallel executions as well as
3. parallel statement queuing.
This section deals with parallel statement queuing feature.
With this feature , Oracle will queue parallel statement if required parallel server processes are not available. Only once the necessary resources becomes available, statement will dequeued and allowed to run. Prior to 11g release 2, if there were insufficient parallel servers available, then either the statement would be run with downgraded DOP or would serialize. But, in 11g release2 , statement will go into the queue if number of active parallel server process is greater or equal to the PARALLEL_SERVERS_TARGET parameter.
PARALLEL_SERVERS_TARGET is set lower to the PARALLEL_MAX_SERVER (which controls the maximum number of PX servers allowed on the system) , thus preventing the overloading of the system with PX servers and ensuring each parallel statement will get all of the required Px servers. Statement will be dequeued according to FIFO order. Here is the small demonstration for this feature.
Following are the parameters that needs to consider:
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_max_servers integer 10
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_servers_target integer 6
parallel_threads_per_cpu integer 2
Here parallel_degree_policy is set to Auto. That means each statement will be considered for parallelism.
Now, Lets execute a query.
SQL> set autotrace on
SQL> select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;
//output not shown//
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2696793116
--------------------------------------------------------------------------------
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | | | |
| 1 | PX COORDINATOR | | | | |
| | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | PX RECEIVE | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | P->P | HASH |
| 6 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | PCWP | |
| 7 | MERGE JOIN CARTESIAN | | 50G| 617G| 13M (1)| 4
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5:42:18 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 11 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWP | |
| 12 | BUFFER SORT | | 918K| 4486K| 16M (17)| 5
4:07:28 | | | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | SALES | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
While the query is running, from another terminal, view the number of active px processes. These can be identified by ora_pxxx.
[oracle@host1 dbs]$ ps -ef|grep ora_p
oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11
oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11
oracle 12314 1 0 12:10 ? 00:00:00 ora_pmon_db11
oracle 12326 1 0 12:10 ? 00:00:00 ora_psp0_db11
oracle 12765 1 0 12:18 ? 00:00:00 ora_p000_db11
oracle 12767 1 1 12:18 ? 00:00:00 ora_p001_db11
oracle 12769 1 0 12:18 ? 00:00:00 ora_p002_db11
oracle 12771 1 0 12:18 ? 00:00:00 ora_p003_db11
oracle 12788 11529 0 12:18 pts/2 00:00:00 grep ora_p
SQL>select status,sql_text from v$sql_monitor where sql_text like '%sh%';
STATUS
-------------------
SQL_TEXT
--------------------------------------------------------------------------------
QUEUED
select /*+ q3 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name
EXECUTING
select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name
EXECUTING
select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name
Related links:
1. http://koenigocm.blogspot.in/search/label/AUTOMATIC%20DEGREE%20OF%20PARALLELISM%20%28DOP%29%20IN%20ORACLE%2011G%20R2%20--%20PART%20-%20I
2.
http://oracleinaction.blogspot.in/2012/11/PARALLELSTMTQUEUEING.html
One new parameter parallel_degree_policy has been introduced. Setting this parameter to MANUAL results in oracle 11g release1 behavior. Setting this parameter to Auto provides three facilities:
1. Each SQL statement will be considered for parallel execution according to a threshold.
2. in-memory parallel executions as well as
3. parallel statement queuing.
This section deals with parallel statement queuing feature.
With this feature , Oracle will queue parallel statement if required parallel server processes are not available. Only once the necessary resources becomes available, statement will dequeued and allowed to run. Prior to 11g release 2, if there were insufficient parallel servers available, then either the statement would be run with downgraded DOP or would serialize. But, in 11g release2 , statement will go into the queue if number of active parallel server process is greater or equal to the PARALLEL_SERVERS_TARGET parameter.
PARALLEL_SERVERS_TARGET is set lower to the PARALLEL_MAX_SERVER (which controls the maximum number of PX servers allowed on the system) , thus preventing the overloading of the system with PX servers and ensuring each parallel statement will get all of the required Px servers. Statement will be dequeued according to FIFO order. Here is the small demonstration for this feature.
Following are the parameters that needs to consider:
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_max_servers integer 10
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_servers_target integer 6
parallel_threads_per_cpu integer 2
Here parallel_degree_policy is set to Auto. That means each statement will be considered for parallelism.
Now, Lets execute a query.
SQL> set autotrace on
SQL> select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;
//output not shown//
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2696793116
--------------------------------------------------------------------------------
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | | | |
| 1 | PX COORDINATOR | | | | |
| | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | PX RECEIVE | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | P->P | HASH |
| 6 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | PCWP | |
| 7 | MERGE JOIN CARTESIAN | | 50G| 617G| 13M (1)| 4
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5:42:18 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 11 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWP | |
| 12 | BUFFER SORT | | 918K| 4486K| 16M (17)| 5
4:07:28 | | | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | SALES | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
While the query is running, from another terminal, view the number of active px processes. These can be identified by ora_pxxx.
[oracle@host1 dbs]$ ps -ef|grep ora_p
oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11
oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11
oracle 12314 1 0 12:10 ? 00:00:00 ora_pmon_db11
oracle 12326 1 0 12:10 ? 00:00:00 ora_psp0_db11
oracle 12765 1 0 12:18 ? 00:00:00 ora_p000_db11
oracle 12767 1 1 12:18 ? 00:00:00 ora_p001_db11
oracle 12769 1 0 12:18 ? 00:00:00 ora_p002_db11
oracle 12771 1 0 12:18 ? 00:00:00 ora_p003_db11
oracle 12788 11529 0 12:18 pts/2 00:00:00 grep ora_p
Currently, two sets of parallel server processes are running because DOP is 2, meaning 4 parallel server process are running at a time. Default DOP is 2 because of parallel_degree_limit parameter.
On the same time while the first query is executing, run another query from another terminal.
SQL> set autotrace on
SQL> select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;
SQL> select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;
[oracle@host1 ~]$ ps -ef|grep ora_p
oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11
oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11
oracle 14001 1 0 12:45 ? 00:00:00 ora_pmon_db11
oracle 14013 1 0 12:45 ? 00:00:00 ora_psp0_db11
oracle 14810 1 0 13:00 ? 00:00:00 ora_p000_db11
oracle 14812 1 0 13:00 ? 00:00:00 ora_p001_db11
oracle 14814 1 6 13:00 ? 00:00:27 ora_p002_db11
oracle 14816 1 6 13:00 ? 00:00:26 ora_p003_db11
oracle 15156 1 0 13:06 ? 00:00:00 ora_p004_db11
oracle 15158 1 0 13:06 ? 00:00:00 ora_p005_db11
oracle 15160 1 33 13:06 ? 00:00:20 ora_p006_db11
oracle 15162 1 0 13:06 ? 00:00:00 ora_p007_db11
oracle 15203 14835 0 13:07 pts/4 00:00:00 grep ora_p
See, number of active processes running has increased .
From the third terminal, run the parallel query again at the same time while the other two queries are active.
Now, you will see that your query is queued. You can monitor the status of SQL statement using V$SQL_MONITOR.
From the third terminal, run the parallel query again at the same time while the other two queries are active.
Now, you will see that your query is queued. You can monitor the status of SQL statement using V$SQL_MONITOR.
SQL>select status,sql_text from v$sql_monitor where sql_text like '%sh%';
STATUS
-------------------
SQL_TEXT
--------------------------------------------------------------------------------
QUEUED
select /*+ q3 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name
EXECUTING
select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name
EXECUTING
select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name
Additional Note: Statement is waiting on the event "PX QUEUING: statement queue". It is the first statement in the statement queue. All other statements in the queue will be waiting on "ENQ JX SQL statement queue". Only when a statement gets to the head of the queue will the wait event switch to "PX QUEUING: statement queue".
interesting !
interesting !
Related links:
1. http://koenigocm.blogspot.in/search/label/AUTOMATIC%20DEGREE%20OF%20PARALLELISM%20%28DOP%29%20IN%20ORACLE%2011G%20R2%20--%20PART%20-%20I
2.
http://oracleinaction.blogspot.in/2012/11/PARALLELSTMTQUEUEING.html
No comments:
Post a Comment
Please write your comments here.