Oracle 11g has introduced a hell lot of new features related to Parallel Execution – like statement queuing, in-memory PX and auto-DOP, to name a few. There are also small bits of enhancements here and there – like default value of the PARALLEL_EXECUTION_MESSAGE_SIZE and an introduction of an easy way to stick PX slaves to a current RAC node with PARALLEL_FORCE_LOCAL. As I’ve recently discovered, PQ_DISTRIBUTE hint used only for joins prior to 11gR2, can now be used for load operations.
Here is an example:
drop table t1 cascade constraints purge; drop table t2 cascade constraints purge; drop table t3 cascade constraints purge; drop table t4 cascade constraints purge; drop table t5 cascade constraints purge; create table t1 partition by hash(owner) partitions 8 parallel 4 as select * from all_objects; insert into t1 select * from t1; / / / / commit; insert /*+ append */ into t1 select * from t1; commit; insert /*+ append */ into t1 select * from t1; commit; @seg t1 exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size 1') create /*+ pq_distribute(t2 none) gather_plan_statistics */ table t2 parallel 4 partition by hash(owner, object_type) partitions 8 as select * from t1; @x @tq_stat create /*+ pq_distribute(t3 partition) gather_plan_statistics */ table t3 parallel 4 partition by hash(owner, object_type) partitions 8 as select * from t1; @x @tq_stat create /*+ pq_distribute(t4 random) gather_plan_statistics */ table t4 parallel 4 partition by hash(owner, object_type) partitions 8 as select * from t1; @x @tq_stat create /*+ pq_distribute(t5 random_local) gather_plan_statistics */ table t5 parallel 4 partition by hash(owner, object_type) partitions 8 as select * from t1; @x @tq_stat
So I have a hash-partitioned table T1 based on ALL_OBJECTS which I then re-partition and supply different PQ_DISTRIBUTE options to the corresponding CTAS statements. The gather_plan_statistics hint is included just so the x.sql script doesn’t report a message that query is missing gather_plan_statistics hint. For parallel execution gather_plan_statistics is almost meaningless since only query coordinator statistics is reported. The tq_stat.sql script displays the data from V$PQ_TQSTAT plus rows distribution between slaves in a set. Test output of running it on Oracle 11.2.0.2 @ Linux x86-64 is here:
SEGMENT_NAME MB
--------------------------------------------------------------------------------- --------------------
T1 8
T1 8
T1 48
T1 8
T1 16
T1 16
T1 760
T1 8
8 rows selected.
PL/SQL procedure successfully completed.
Table created.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 1zdhwx6cmfwmr, child number 0
-------------------------------------
create /*+ pq_distribute(t2 none) gather_plan_statistics */ table t2
parallel 4 partition by hash(owner, object_type) partitions 8 as select
* from t1
Plan hash value: 682413595
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | | 32 |00:00:01.62 | 33 |
| 1 | PX COORDINATOR | | 1 | | 32 |00:00:01.62 | 33 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 3 | LOAD AS SELECT | | 0 | | 0 |00:00:00.01 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 7239K| 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL | T1 | 0 | 7239K| 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
24 rows selected.
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS Num rows, % BYTES INSTANCE
---------- ----- ----------- ----------- ------------ ----------- ---------------- --------
1 0 Consumer QC 32 100 3,520 1
0 Producer P000 8 25 880 1
0 Producer P001 8 25 880 1
0 Producer P002 8 25 880 1
0 Producer P003 8 25 880 1
5 rows selected.
Table created.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID bt9f0gvhxt3ga, child number 0
-------------------------------------
create /*+ pq_distribute(t3 partition) gather_plan_statistics */ table
t3 parallel 4 partition by hash(owner, object_type) partitions 8 as
select * from t1
Plan hash value: 2061925674
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | | 8 |00:00:04.09 | 33 |
| 1 | PX COORDINATOR | | 1 | | 8 |00:00:04.09 | 33 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 3 | LOAD AS SELECT | | 0 | | 0 |00:00:00.01 | 0 |
| 4 | PX RECEIVE | | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 5 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 6 | PX BLOCK ITERATOR | | 0 | 7239K| 0 |00:00:00.01 | 0 |
|* 7 | TABLE ACCESS FULL | T1 | 0 | 7239K| 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
26 rows selected.
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS Num rows, % BYTES INSTANCE
---------- ----- ----------- ----------- ------------ ----------- ---------------- --------
1 1 Consumer QC 8 100 952 1
1 Producer P000 2 25 238 1
1 Producer P001 2 25 238 1
1 Producer P002 2 25 238 1
1 Producer P003 2 25 238 1
0 Consumer P000 3,682,816 51 420,782,416 1
0 Consumer P001 609,920 8 63,924,736 1
0 Consumer P002 38,400 1 4,089,600 1
0 Consumer P003 2,908,416 40 337,984,848 1
0 Producer P004 1,813,236 25 207,310,144 1
0 Producer P005 1,819,355 25 207,748,125 1
0 Producer P006 1,807,689 25 206,495,058 1
0 Producer P007 1,799,272 25 205,228,273 1
13 rows selected.
Table created.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID b90j08nqunu1t, child number 0
-------------------------------------
create /*+ pq_distribute(t4 random) gather_plan_statistics */ table t4
parallel 4 partition by hash(owner, object_type) partitions 8 as select
* from t1
Plan hash value: 2166871997
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | | 32 |00:00:02.11 | 33 |
| 1 | PX COORDINATOR | | 1 | | 32 |00:00:02.11 | 33 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 3 | LOAD AS SELECT | | 0 | | 0 |00:00:00.01 | 0 |
| 4 | PX RECEIVE | | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 5 | PX SEND ROUND-ROBIN| :TQ10000 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 6 | PX BLOCK ITERATOR | | 0 | 7239K| 0 |00:00:00.01 | 0 |
|* 7 | TABLE ACCESS FULL| T1 | 0 | 7239K| 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
26 rows selected.
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS Num rows, % BYTES INSTANCE
---------- ----- ----------- ----------- ------------ ----------- ---------------- --------
1 1 Consumer QC 32 100 3,520 1
1 Producer P000 8 25 880 1
1 Producer P001 8 25 880 1
1 Producer P002 8 25 880 1
1 Producer P003 8 25 880 1
0 Consumer P000 1,834,097 25 209,486,729 1
0 Consumer P001 1,869,780 26 213,498,511 1
0 Consumer P002 1,835,567 25 209,651,522 1
0 Consumer P003 1,700,108 23 194,144,838 1
0 Producer P004 1,813,063 25 207,311,628 1
0 Producer P005 1,808,867 25 206,544,025 1
0 Producer P006 1,747,138 24 199,861,174 1
0 Producer P007 1,870,484 26 213,064,773 1
13 rows selected.
Table created.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 4jqhr15czz9r3, child number 0
-------------------------------------
create /*+ pq_distribute(t5 random_local) gather_plan_statistics */
table t5 parallel 4 partition by hash(owner, object_type) partitions 8
as select * from t1
Plan hash value: 1906677292
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | | 32 |00:00:02.10 | 33 |
| 1 | PX COORDINATOR | | 1 | | 32 |00:00:02.10 | 33 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 3 | LOAD AS SELECT | | 0 | | 0 |00:00:00.01 | 0 |
| 4 | PX RECEIVE | | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 5 | PX SEND RANDOM LOCAL| :TQ10000 | 0 | 7239K| 0 |00:00:00.01 | 0 |
| 6 | PX BLOCK ITERATOR | | 0 | 7239K| 0 |00:00:00.01 | 0 |
|* 7 | TABLE ACCESS FULL | T1 | 0 | 7239K| 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
26 rows selected.
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS Num rows, % BYTES INSTANCE
---------- ----- ----------- ----------- ------------ ----------- ---------------- --------
1 1 Consumer QC 32 100 3,520 1
1 Producer P000 8 25 880 1
1 Producer P001 8 25 880 1
1 Producer P002 8 25 880 1
1 Producer P003 8 25 880 1
0 Consumer P000 1,893,945 26 216,291,012 1
0 Consumer P001 1,906,194 26 217,685,982 1
0 Consumer P002 1,554,614 21 177,533,232 1
0 Consumer P003 1,884,799 26 215,271,374 1
0 Producer P004 1,813,063 25 207,311,628 1
0 Producer P005 1,808,867 25 206,544,025 1
0 Producer P006 1,747,138 24 199,861,174 1
0 Producer P007 1,870,484 26 213,064,773 1
13 rows selected.
Here are my observations:
- CTAS #1 uses NONE as a target for PQ_DISTRIBUTE. That means data distribution is eliminated and data load to the new table was done by the same slaves that read original table – the fastest method in this case as reported by A-Time column in the plan. No re-distribution means V$PQ_TQSTAT view is of no help.
- CTAS #2 uses PARTITION, which is not recommended in my case since there’s data skew. And the statistical data from V$PQ_TQSTAT only confirms it – on the load step there’s a big difference in the number of rows processed by Consumers of the table queue #0 – from 1 to 51%. It was also the slowest query, and that is what usually happens when slaves are unevenly loaded with their work.
- CTAS #3 uses RANDOM, which is the best option from the data re-distribution perspective, and it’s also confirmed by the report data.
- CTAS #4 uses RANDOM_LOCAL, which is almost identical to the RANDOM in both performance and data distribution between slaves. Although not stated clearly in the documentation, I’ve a feeling that suffix LOCAL is for RAC world and it means that Oracle will try to use RANDOM distribution confined to slaves on the same node, which will eliminate inter-node PX communication. Since I don’t have 11gR2 RAC at the moment, I can’t confirm my guess.
- SQL Hint definition in the documentation is not valid
Right now I’m not sure if there will be second part, but I’ve put Part 1 to the title just in case. We’ll see
