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 :
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.
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 :
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 :
Next, the actual Parallel Insert.
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).
.
.
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:
Post a Comment