\nFollowing a question<\/a> from Randolf Geist (who can imagine that there is something about parallel query that Randolf didn’t know?), I get back to some notes I’ve taken when 12c was out and I’ve tested them again on 12.1.0.2 – it’s about the ability to enable parallel DML at query level.<\/p>\n
Test case<\/h3>\n
In 12.1.0.2 I create two tables. DEMO1 has 100000 rows and is about 80MB. DEMO2 is empty. DEMO1 is parallel.<\/p>\n
SQL> create table DEMO1 parallel 2 as\n 2 select rownum id , ora_hash(rownum,10) a from xmltable('1 to 1000000');\n\nTable created.\n\nSQL> select table_name,num_rows,blocks from user_tables where table_name='DEMO';\n\nTABLE_NAME NUM_ROWS BLOCKS\n---------- ---------- ----------\nDEMO 100000 10143\n\nSQL>\nSQL> create table DEMO2 as select * from DEMO1 where null is not null;\n\nTable created.\n\nSQL>\nSQL> alter session set statistics_level=all;\n\nSession altered.\n\n<\/code><\/pre>\ninsert … select …<\/h3>\n
Here is a simple insert as select:<\/p>\n
SQL> insert into DEMO2 select * from DEMO1;\n\n1000000 rows created.\n\nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------\nSQL_ID bx27xdnkr7dvw, child number 0\n-------------------------------------\ninsert into DEMO2 select * from DEMO1\n\nPlan hash value: 4271246053\n\n-----------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\n-----------------------------------------------------------------------------------------------\n| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:17.40 | 24311 |\n| 1 | LOAD TABLE CONVENTIONAL | DEMO2 | 1 | | 0 |00:00:17.40 | 24311 |\n| 2 | PX COORDINATOR | | 1 | | 1000K|00:00:04.49 | 5 |\n| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1000K| 0 |00:00:00.01 | 0 |\n| 4 | PX BLOCK ITERATOR | | 0 | 1000K| 0 |00:00:00.01 | 0 |\n|* 5 | TABLE ACCESS FULL | DEMO1 | 0 | 1000K| 0 |00:00:00.01 | 0 |\n-----------------------------------------------------------------------------------------------\n\nNote \n-----\n - Degree of Parallelism is 2 because of table property\n - PDML is disabled in current session\n\n<\/code><\/pre>\nThe select part is done in parallel (it’s below the coordinator) but the insert part (LOAD TABLE) is above the coordinator, which means that it is done in serial by the coordinator. In 12.1.0.2 you have no doubt: dbms_xplan has a note to tell you that PDML was not used and it gives the reason: it’s not enabled in the session.<\/p>\n
When you have tuning pack the parallel queries are monitored by default, so we can get the SQL Monitor Plan. You can get it as text, html or flash but I’ll use Lighty<\/a> here as I find it awesome for that as well:<\/p>\n![\"PDML1.png\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/PDML1.png\" "\\"PDML1.png\\"")
<\/a><\/p>\nLook at the bottom right which details the highlighted plan line: 100% of the load has been done by my session process.<\/p>\n
Enable parallel dml<\/h3>\n
So we need to enable parallel DML for our session. Do you know why? Because inserting in parallel requires to lock the table (or partition) it is inserted into, so the optimizer cannot decide that without our permission. So let’s enable parallel DML:<\/p>\n
SQL> alter session enable parallel dml;\nERROR:\nORA-12841: Cannot alter the session parallel DML state within a transaction\n\n<\/code><\/pre>\nI cannot do that here because I have a transaction in progress. But in 12c you can also enable parallel DML at query level, with the ENABLE_PARALLEL_DML hint. I’ve seen it when 12c came out, but it was undocumented. But I discover today that it is documented in the Enable Parallel DML Mode<\/a> of the Database VLDB and Partitioning Guide.<\/p>\nWith the DISABLE_PARALLEL_DML hint you can disable PDML at query level when you have enabled it in the session. And with the ENABLE_PARALLEL_DML hint you can enable PDML for one query even when it’s not enabled in the session. And you can do that even if you have a transaction in progress:<\/p>\n
SQL> insert \/*+ enable_parallel_dml *\/ into DEMO2 select * from DEMO1;\n\n1000000 rows created.\n\nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------\nSQL_ID 707bk8y125hp4, child number 0\n-------------------------------------\ninsert \/*+ enable_parallel_dml *\/ into DEMO2 select * from DEMO1\n\nPlan hash value: 4271246053\n\n-----------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\n-----------------------------------------------------------------------------------------------\n| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:18.76 | 22343 |\n| 1 | LOAD TABLE CONVENTIONAL | DEMO2 | 1 | | 0 |00:00:18.76 | 22343 |\n| 2 | PX COORDINATOR | | 1 | | 1000K|00:00:04.22 | 5 |\n| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1000K| 0 |00:00:00.01 | 0 |\n| 4 | PX BLOCK ITERATOR | | 0 | 1000K| 0 |00:00:00.01 | 0 |\n|* 5 | TABLE ACCESS FULL | DEMO1 | 0 | 1000K| 0 |00:00:00.01 | 0 |\n-----------------------------------------------------------------------------------------------\n\nNote\n-----\n - Degree of Parallelism is 2 because of table property\n - PDML disabled because object is not decorated with parallel clause\n - Direct Load disabled because no append hint given and not executing in parallel\n\n<\/code><\/pre>\nOk. I’ve enabled PDML but PDML occurs only when in parallel. Here the table has no parallel degree and there is no PARALLEL hint. Once again dbms_xplan gives us the reason. And because it’s not PDML and there is no append hint, then it’s not loaded in direct-path.<\/p>\n
Here is the SQL Monitoring plan. Note that is the same as the previous one except that it’s not the same cost. I don’t know why yet. If you have any idea, please comment.<\/p>\n
![\"PDML2.png\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/PDML2.png\" "\\"PDML2.png\\"")
<\/a><\/p>\n![\"PDML5.png\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/PDML5.png\" "\\"PDML5.png\\"")
<\/a><\/p>\n