PQ_DISTRIBUTE enhancement – Part 1

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 :)

About these ads

One Comment

  1. Sivakumar
    Posted September 16, 2013 at 06:22 | Permalink | Reply

    awesome research

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 288 other followers