By Franck Pachot

.
Following a question 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.

Test case

In 12.1.0.2 I create two tables. DEMO1 has 100000 rows and is about 80MB. DEMO2 is empty. DEMO1 is parallel.

SQL> create table DEMO1 parallel 2 as
  2  select rownum id , ora_hash(rownum,10) a from xmltable('1 to 1000000');

Table created.

SQL> select table_name,num_rows,blocks from user_tables where table_name='DEMO';

TABLE_NAME   NUM_ROWS     BLOCKS
---------- ---------- ----------
DEMO           100000      10143

SQL>
SQL> create table DEMO2 as select * from DEMO1 where null is not null;

Table created.

SQL>
SQL> alter session set statistics_level=all;

Session altered.

insert … select …

Here is a simple insert as select:

SQL> insert into DEMO2 select * from DEMO1;

1000000 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  bx27xdnkr7dvw, child number 0
-------------------------------------
insert into DEMO2 select * from DEMO1

Plan hash value: 4271246053

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |      1 |        |      0 |00:00:17.40 |   24311 |
|   1 |  LOAD TABLE CONVENTIONAL | DEMO2    |      1 |        |      0 |00:00:17.40 |   24311 |
|   2 |   PX COORDINATOR         |          |      1 |        |   1000K|00:00:04.49 |       5 |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |      0 |   1000K|      0 |00:00:00.01 |       0 |
|   4 |     PX BLOCK ITERATOR    |          |      0 |   1000K|      0 |00:00:00.01 |       0 |
|*  5 |      TABLE ACCESS FULL   | DEMO1    |      0 |   1000K|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------

Note                                                                                                                                
-----
   - Degree of Parallelism is 2 because of table property
   - PDML is disabled in current session

The 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.

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 here as I find it awesome for that as well:

PDML1.png

Look at the bottom right which details the highlighted plan line: 100% of the load has been done by my session process.

Enable parallel dml

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:

SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction

I 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 of the Database VLDB and Partitioning Guide.

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

SQL> insert /*+ enable_parallel_dml */ into DEMO2 select * from DEMO1;

1000000 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  707bk8y125hp4, child number 0
-------------------------------------
insert /*+ enable_parallel_dml */ into DEMO2 select * from DEMO1

Plan hash value: 4271246053

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |      1 |        |      0 |00:00:18.76 |   22343 |
|   1 |  LOAD TABLE CONVENTIONAL | DEMO2    |      1 |        |      0 |00:00:18.76 |   22343 |
|   2 |   PX COORDINATOR         |          |      1 |        |   1000K|00:00:04.22 |       5 |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |      0 |   1000K|      0 |00:00:00.01 |       0 |
|   4 |     PX BLOCK ITERATOR    |          |      0 |   1000K|      0 |00:00:00.01 |       0 |
|*  5 |      TABLE ACCESS FULL   | DEMO1    |      0 |   1000K|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 2 because of table property
   - PDML disabled because object is not decorated with parallel clause
   - Direct Load disabled because no append hint given and not executing in parallel

Ok. 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.

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.

PDML2.png

Enable parallel DML while in a transaction

I disable PDML and start a transaction:

SQL> commit;

Commit complete.

SQL> alter session disable parallel dml;

Session altered.

SQL> delete from DEMO1 where rownum
SQL> select status,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=sys_context('userenv','sid'));

STATUS            USED_UREC
---------------- ----------
ACTIVE                 1000

And while I’m within that transaction, Let’s do the parallel insert enabled by hint:

SQL> insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1;

999000 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  2b8q4k902pbdx, child number 1
-------------------------------------
insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1

Plan hash value: 86785878

-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Starts | A-Rows | Buffers | OMem |1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |      |      1 |      4 |     136 |      |     |          |
|   1 |  PX COORDINATOR                    |      |      1 |      4 |     136 |      |     |          |
|   2 |   PX SEND QC (RANDOM)              | :TQ10|      0 |      0 |       0 |      |     |          |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|      |      0 |      0 |       0 |   33M|  33M| 2068K (0)|
|   4 |     OPTIMIZER STATISTICS GATHERING |      |      0 |      0 |       0 |      |     |          |
|   5 |      PX BLOCK ITERATOR             |      |      0 |      0 |       0 |      |     |          |
|*  6 |       TABLE ACCESS FULL            | DEMO1|      0 |      0 |       0 |      |     |          |
-------------------------------------------------------------------------------------------------------

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

Here PDML occurred. We know that because of the load operator under the coordinator (Note to self: HYBRID TSM/HWMB is something to investigate – once again comments welcome). I’ve displayed the plan with ‘allstats last’ which show only the coordinator activity. SQL Monitor can show all:

PDML5.png

Conclusion

Yes you can enable PDML at query level in 12c and it is documented. And you can do it even when within a transaction which is a restriction only for ‘alter session enable parallel dml’ but not for that hint.

Post Views: 1,600
Thumbnail [60x60]
by
Oracle Team