Oracle Performance Storyteller MERGE

us_yunleiwang發表於2014-11-27
OracleORM


Interpreting Parallel MERGE Statement

This post is inspired by one of my colleagues, who had small difficulty while interpreting parallel MERGE execution plan.


This is the step-by-step explanation I gave him.

1. Create tables.

SQL> create table t1
  2  as
  3  select
  4           level as c1,
  5           level as c2,
  6           rpad('x',100) as c3
  7  from
  8           dual
  9  connect by level <= 10000
 10  ;
 
Table created.
 
SQL> create table t2
  2  as
  3  select
  4           level as c1,
  5           level as c2,
  6           rpad('x', 100) as c3
  7  from
  8           dual
  9  connect by level <= 10000
 10  ;
 
Table created.

2. Would this MERGE statement run in parallel?

SQL> explain plan for
  2  merge /*+ parallel */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6           update set t1.c2 = t1.c2
  7  when not matched then
  8           insert(c1, c2) values(t2.c1, t2.c2)
  9  ;
 
Explained.
 
-------------------------------------------------------
| Id  | Operation                  | Name     | Rows  |
-------------------------------------------------------
|   0 | MERGE STATEMENT            |          |  9356 |
|   1 |  MERGE                     | T1       |       |
|   2 |   PX COORDINATOR           |          |       |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |  9356 |
|   4 |     VIEW                   |          |       |
|*  5 |      HASH JOIN OUTER       |          |  9356 |
|   6 |       PX BLOCK ITERATOR    |          |  9356 |
|   7 |        TABLE ACCESS FULL   | T2       |  9356 |
|   8 |       BUFFER SORT          |          |       |
|   9 |        PX RECEIVE          |          | 11234 |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 11234 |
|  11 |          PX BLOCK ITERATOR |          | 11234 |
|  12 |           TABLE ACCESS FULL| T1       | 11234 |
-------------------------------------------------------

The answer is NO. As you notice from the operation #2, the MERGE part itself runs in serial. It is SELECT part that runs in parallel.

3. The reason would be parallel dml not enabled. Let’s see what happens if we have parallel dml enabled.

SQL> alter session enable parallel dml;
 
Session altered.
 
SQL> explain plan for
  2  merge /*+ parallel */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6           update set t1.c2 = t1.c2
  7  when not matched then
  8           insert(c1, c2) values(t2.c1, t2.c2)
  9  ;
 
-------------------------------------------------------
| Id  | Operation                  | Name     | Rows  |
-------------------------------------------------------
|   0 | MERGE STATEMENT            |          |  9356 |
|   1 |  MERGE                     | T1       |       |
|   2 |   PX COORDINATOR           |          |       |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |  9356 |
|   4 |     VIEW                   |          |       |
|*  5 |      HASH JOIN OUTER       |          |  9356 |
|   6 |       PX BLOCK ITERATOR    |          |  9356 |
|   7 |        TABLE ACCESS FULL   | T2       |  9356 |
|   8 |       BUFFER SORT          |          |       |
|   9 |        PX RECEIVE          |          | 11234 |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 11234 |
|  11 |          PX BLOCK ITERATOR |          | 11234 |
|  12 |           TABLE ACCESS FULL| T1       | 11234 |
-------------------------------------------------------

Still, the MERGE part runs serially.

4. Maybe parallel merge needs more specific HINT, which is quite natural because the MERGE statement has two tables involved. Let me make the PARALLE hint more specific.

SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6           update set t1.c2 = t1.c2
  7  when not matched then
  8           insert(c1, c2) values(t2.c1, t2.c2)
  9  ;
 
----------------------------------------------------
| Id  | Operation                       | Name     |
----------------------------------------------------
|   0 | MERGE STATEMENT                 |          |
|   1 |  PX COORDINATOR                 |          |
|   2 |   PX SEND QC (RANDOM)           | :TQ10003 |
|   3 |    MERGE                        | T1       |
|   4 |     PX RECEIVE                  |          |
|   5 |      PX SEND HYBRID (ROWID PKEY)| :TQ10002 |
|   6 |       VIEW                      |          |
|*  7 |        HASH JOIN OUTER BUFFERED |          |
|   8 |         BUFFER SORT             |          |
|   9 |          PX RECEIVE             |          |
|  10 |           PX SEND HASH          | :TQ10000 |
|  11 |            TABLE ACCESS FULL    | T2       |
|  12 |         PX RECEIVE              |          |
|  13 |          PX SEND HASH           | :TQ10001 |
|  14 |           PX BLOCK ITERATOR     |          |
|  15 |            TABLE ACCESS FULL    | T1       |
----------------------------------------------------

Now, the MERGE part really runs in parallel!

5. One more thing you should be careful about. Let me create an index on the target table t1 and see what happens on the plan.

SQL> create index t1_n1 on t1(c1);
 
Index created.
 
SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6           update set t1.c2 = t1.c2
  7  when not matched then
  8           insert(c1, c2) values(t2.c1, t2.c2)
  9  ;
 
---------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |
---------------------------------------------------------------
|   0 | MERGE STATEMENT                    |          |  9356 |
|   1 |  PX COORDINATOR                    |          |       |
|   2 |   PX SEND QC (RANDOM)              | :TQ10004 |  9356 |
|   3 |    INDEX MAINTENANCE               | T1       |       |
|   4 |     PX RECEIVE                     |          |  9356 |
|   5 |      PX SEND RANGE                 | :TQ10003 |  9356 |
|   6 |       MERGE                        | T1       |       |
|   7 |        PX RECEIVE                  |          |  9356 |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10002 |  9356 |
|   9 |          VIEW                      |          |       |
|* 10 |           HASH JOIN OUTER BUFFERED |          |  9356 |
|  11 |            BUFFER SORT             |          |       |
|  12 |             PX RECEIVE             |          |  9356 |
|  13 |              PX SEND HASH          | :TQ10000 |  9356 |
|  14 |               TABLE ACCESS FULL    | T2       |  9356 |
|  15 |            PX RECEIVE              |          | 11234 |
|  16 |             PX SEND HASH           | :TQ10001 | 11234 |
|  17 |              PX BLOCK ITERATOR     |          | 11234 |
|  18 |               TABLE ACCESS FULL    | T1       | 11234 |
---------------------------------------------------------------

Now we have a new operation called INDEX MAINTENANCE and need one more table queue(TQ10004) to parallelize index maintenance itself. But why do we have index maintenance here?

  • Parallel MERGE is composed of parallel update and parallel insert.
  • Parallel insert is direct path insert.
  • Indexes need to be maintained after direct path insert.

Oracle needs to maintain the indexes after direct path insert completes. This is controlled by _idl_conventional_index_maintenance hidden parameter, which you’d never have a chance to modify.

SQL> select * from table(tpack.param('_idl_conventional_index_maintenance'));
 
NAME                                VALUE
----------------------------------- ----------------------------------------
Name #1                             _idl_conventional_index_maintenance
  Value                             TRUE
  Is Default                        TRUE
  Sess Modifiable                   false
  Sys Modifiable                    false
  Description                       enable conventional index maintenance fo
                                    r insert direct load

The INDEX MAINTENANCE operation does not disappear even when the index is disabled, but I believe that Oracle would skip the maintenance for the disabled indexes in runtime.

SQL> alter index t1_n1 unusable;
 
Index altered.
 
SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6           update set t1.c2 = t1.c2
  7  when not matched then
  8           insert(c1, c2) values(t2.c1, t2.c2)
  9  ;
 
---------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |
---------------------------------------------------------------
|   0 | MERGE STATEMENT                    |          |  9356 |
|   1 |  PX COORDINATOR                    |          |       |
|   2 |   PX SEND QC (RANDOM)              | :TQ10004 |  9356 |
|   3 |    INDEX MAINTENANCE               | T1       |       |
|   4 |     PX RECEIVE                     |          |  9356 |
|   5 |      PX SEND RANGE                 | :TQ10003 |  9356 |
|   6 |       MERGE                        | T1       |       |
|   7 |        PX RECEIVE                  |          |  9356 |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10002 |  9356 |
|   9 |          VIEW                      |          |       |
|* 10 |           HASH JOIN OUTER BUFFERED |          |  9356 |
|  11 |            BUFFER SORT             |          |       |
|  12 |             PX RECEIVE             |          |  9356 |
|  13 |              PX SEND HASH          | :TQ10000 |  9356 |
|  14 |               TABLE ACCESS FULL    | T2       |  9356 |
|  15 |            PX RECEIVE              |          | 11234 |
|  16 |             PX SEND HASH           | :TQ10001 | 11234 |
|  17 |              PX BLOCK ITERATOR     |          | 11234 |
|  18 |               TABLE ACCESS FULL    | T1       | 11234 |
---------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1348901/，如需轉載，請註明出處，否則將追究法律責任。

相關文章