16 May, 2015

Parallel Execution -- 5b Parallel INSERT Execution Plan

As noted in my previous post, Oracle does NOT enable Parallel DML by default.  You need to explicitly enable it with ALTER SESSION ENABLE PARALLEL DML.

Can you use the Execution Plan of an INSERT statement to identify if the INSERT was executed in Parallel ?

Here's a brief demonstration :

SQL*Plus: Release 11.2.0.2.0 Production on Sat May 16 22:27:49 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
         1

HEMANT>alter table another_large_table parallel 4;

Table altered.

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
  2  select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |         |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |         |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |         |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |         |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>
HEMANT>select count(*) from another_large_table;

  COUNT(*)
----------
   4802944

HEMANT>

Notice the "LOAD TABLE CONVENTIONAL" ?  Parallel Execution Servers were used for querying the source table LARGE_TABLE but the actual INSERT was executed as a non-parallel INSERT.  Another piece of evidence that the INSERT was not executed using PX Servers is that I was able to query the table without an ORA-12838 error.

Let me try again with the correct ALTER SESSION command.

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
  2  select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |             |       |       |  1314 (100)|          |        |      |         |
|   1 |  PX COORDINATOR       |             |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM) | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT     |             |       |       |            |          |  Q1,00 | PCWP |         |
|   4 |     PX BLOCK ITERATOR |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS FULL| LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |         |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>

Here, the ORA-12838 is evidence that the INSERT was Parallel. But that evidence is only visible from the same session. What if we had to use another session to check the Execution ? We'd then use the DBMS_XPLAN.DISPLAY_CURSOR method.  Here we notice the PX COORDINATOR at ID=1 appearing *above* the LOAD AS SELECT.  There is no LOAD TABLE CONVENTIONAL step.  These are evidence that the LOAD was executed by PX Servers.
Note : Ignore the "P->S" in ID=2.



Question : Why does the first (non-parallel insert) statement have 2 child cursors.  And the 2nd child cursor actually does indicate an Auto DoP.  I have evidence that child cursor 0 is executed and child cursor 1, although parsed, shows as 0 executions in V$SQL :

SYS>select sql_id, child_number, executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4  order by 1,2;

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6            0          1
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh            0          1
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYS>
SYS>l
  1  select sql_id, child_number, executions, px_servers_executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4* order by 1,2
SYS>/

SQL_ID        CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6            0          1                     0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh            0          1                     0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

Interesting, isn't it ? This time, the PX_SERVERS_EXECUTIONS doesn't seem to be correct.  So, either EXECUTIONS is correct or PX_SERVERS_EXECUTIONS is correct.  These need to be re-verified in 11.2.0.4.

UPDATE 18-May-15 :  In 11.2.0.4,  I see 1 child cursor (not 2) with the correct EXECUTIONS and PX_SERVERS_EXECUTIONS being reported.  I will need to rerun my tests in the same 11.2.0.2 environment.


Re-testing in the same 11.2.0.2 environment :
First, the insert that does NOT get executed as a Parallel INSERT :

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Sun May 24 22:58:28 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>truncate table ANOTHER_LARGE_TABLE;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
         4

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
  2  select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>select count(*) from another_large_table;

  COUNT(*)
----------
   4802944

HEMANT>
SYSTEM>select sql_id, child_number, executions, px_servers_executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4  order by 1,2;

SQL_ID        CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
fuuygy5k8nfrh            0          1                     0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

Next, the actual Parallel Insert.

HEMANT>commit;

Commit complete.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
  2  select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |             |       |       |  1314 (100)|          |        |      |            |
|   1 |  PX COORDINATOR       |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT     |             |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>
SYSTEM>l
  1  select sql_id, child_number, executions, px_servers_executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4* order by 1,2
SYSTEM>
SYSTEM>/

SQL_ID        CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6            0          1                     0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh            0          1                     0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>
SYSTEM>l
  1  select sql_id, executions, px_servers_executions, sql_text
  2  from v$sqlstats
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4* order by 1,2
SYSTEM>/

SQL_ID        EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6          1                     0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh          1                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

The behaviour of the V$ views in the 11.2.0.2 environment doesn't seem correct. (Note : V$SQLSTATS doesn't differentiate by CHILD_NUMBER as does V$SQL).
.
.



No comments: