This discussion is archived
13 Replies Latest reply: Jul 25, 2013 12:41 AM by Michael dbi RSS

Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column

Michael dbi Newbie
Currently Being Moderated

Hi,

 

Since I wanted to demonstrate new Oracle 12c enhancements on SecureFiles, I tried to use PDML statements on a non partitioned table with LOB column, in both Oracle 11g and Oracle 12c releases. The Oracle 11.2 SecureFiles and Large Objects Developer's Guide of January 2013 clearly says:

 

Parallel execution of the following DML operations on tables with LOB columns is supported. These operations run in parallel execution mode only when performed on a partitioned table. DML statements on non-partitioned tables with LOB columns continue to execute in serial execution mode.

 

  • INSERT AS SELECT
  • CREATE TABLE AS SELECT
  • DELETE
  • UPDATE
  • MERGE (conditional UPDATE and INSERT)
  • Multi-table INSERT

 

So I created and populated a simple table with a BLOB column:

 

SQL> CREATE TABLE T1 (A BLOB);
Table created.

 

Then, I tried to see the execution plan of a parallel DELETE:

 

SQL> EXPLAIN PLAN FOR
  2  delete /*+parallel (t1,8) */ from t1;
Explained.

 

 

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3718066193
-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |  2048 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  DELETE               | T1       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  2048 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  2048 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T1       |  2048 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

 

And I finished by executing the statement.

 

SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
SQL> delete /*+parallel (t1,8) */ from t1;
2048 rows deleted.

 

As we can see, the statement has been run as parallel:

 

SQL> select * from v$pq_sesstat;
STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          5             0
Allocation Height                       5             0
Allocation Width                        1             0
Local Msgs Sent                        55            55
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      55            55
Distr Msgs Recv'd                       0             0
11 rows selected.

 

Is it normal ? It is not supposed to be supported on Oracle 11g with non-partitioned table containing LOB column....

 

Thank you for your help.

 

Michael

  • 1. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    user12011367 Newbie
    Currently Being Moderated

    "These operations run in parallel execution mode only when performed on a partitioned table"

     

    Why do you mean that it is supposed to run in parallel on a non partitioned table?

  • 2. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Michael dbi Newbie
    Currently Being Moderated

    Hi,

     

    I want to demonstrate that it was not possible in 11.2, and that now it works in 12.1. In my post, I said that it is not supposed to work in 11.2, as it is clearly written in the doc. Hhowever as you an see, my test shows that the statement is run in parallel, even if the table is not partitioned... It goes against what is said in the doc, and I wonder what is wrong.

  • 3. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    user12011367 Newbie
    Currently Being Moderated

    Hi,


    The explain plan shows that only the query is executed in parallel but not the delete.

     

    Regards

    Maurice

  • 4. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Lakmal Rajapakse Expert
    Currently Being Moderated

    It's fine - the select bit of the delete is done in parallel but the delete itself is done serially

     

    PX SEND QC (RANDOM)| :TQ10000 |  2048 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  

  • 5. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Michael dbi Newbie
    Currently Being Moderated

    Please can you explain how you can see that ? It is the first time I play with execution plans...

     

    I tried the same delete statement on a 12.1 database, with a non partitioned table and LOB stored as SecureFiles. I got the same execution plan

     

    -------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    -------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT      |          |  2048 |     4   (0)| 00:00:01 |        |      |            |
    |   1 |  DELETE               | T1       |       |            |          |        |      |            |
    |   2 |   PX COORDINATOR      |          |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)| :TQ10000 |  2048 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     PX BLOCK ITERATOR |          |  2048 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |
    |   5 |      TABLE ACCESS FULL| T1       |  2048 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
  • 6. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Lakmal Rajapakse Expert
    Currently Being Moderated

    You can see the results of the full table scan are passed to the query coordinator before the delete operation is executed - the P->S in step 3 is parallel to serial. If it was run in parallel you would most probably see a PCWP (parallel combined with parent) parallel operation next to the delete operation. Something like:

     

    [code]

    ------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT         |          |  5164 |   297K|    14   (0)| 00:00:01 |        |      |            |
    |   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
    |   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  5164 |   297K|    14   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
    |   3 |    INDEX MAINTENANCE     | PS_JOB   |       |       |            |          |  Q1,01 | PCWP |            |
    |   4 |     PX RECEIVE           |          |  5164 |   297K|    14   (0)| 00:00:01 |  Q1,01 | PCWP |            |
    |   5 |      PX SEND RANGE       | :TQ10000 |  5164 |   297K|    14   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
    |   6 |       DELETE             | PS_JOB   |       |       |            |          |  Q1,00 | PCWP |            |
    |   7 |        PX BLOCK ITERATOR |          |  5164 |   297K|    14   (0)| 00:00:01 |  Q1,00 | PCWC |            |

    |   8 |         TABLE ACCESS FULL| PS_JOB   |  5164 |   297K|    14   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------

    [code]

  • 7. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Lakmal Rajapakse Expert
    Currently Being Moderated

    And as for 12c - I believe PDML is only available for securefile lobs (for non-partitioned objects)

     

    so try

     

    create table t1 (c clob) lob(c) store as securefile parallel 8;

  • 8. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Michael dbi Newbie
    Currently Being Moderated

    One of the enhancements of Oracle 12c is that LOBS are stored as SecureFiles per default. I did not specify any clause for LOB storage when creating the table.

     

    From the 12c DB:

     

    SQL> select table_name, securefile from user_lobs;
    TABLE_NAME                                                                                                          SEC
    -------------------------------------------------------------------------------------------------- ---
    T1                                                                                                                              YES
    
    

     

    So, from one side, I have an unsupported situation on Oracle 11g with a non partitionned table and BasicFile LOB column, and on the other side I have a supported situation with 12c with a non partitionned table and  LOB column stored as secure files.

     

    However, in both cases, the DELETE statement appears to not be run in parallel, according to your explanation of the execution plan...

  • 9. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Lakmal Rajapakse Expert
    Currently Being Moderated

    OK I didn't know that.

     

    Did you do this before generating the plan:

     

    alter session enable parallel dml; 

     

    Unfortunately I don't have 12c to test ...

  • 10. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Michael dbi Newbie
    Currently Being Moderated

    Yes I did it. I tried with force parallel dml, and that is the results on my 12c DB, with the non partitionned and SecureFiles LOB column.

     

    SQL> explain plan for delete from t1;
    Explained.
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    -------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT      |          |     4 |     2   (0)| 00:00:01 |        |      |            |
    |   1 |  DELETE               | T1       |       |            |          |        |      |            |
    |   2 |   PX COORDINATOR      |          |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)| :TQ10000 |     4 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     PX BLOCK ITERATOR |          |     4 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
    |   5 |      TABLE ACCESS FULL| T1       |     4 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |

     

    The DELETE is not performed in Parallel.

     

    I tried with another statement :

     

    SQL> explain plan for
    2        insert into t1 select * from t1;

     

    Here are the results:

     

    11g

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

     

    12c

    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |          |     4 |  8008 |     2   (0)| 00:00:01 |        |      |            |
    |   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
    |   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     4 |  8008 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    |   3 |    LOAD AS SELECT                  | T1       |       |       |            |          |  Q1,00 | PCWP |            |
    |   4 |     OPTIMIZER STATISTICS GATHERING |          |     4 |  8008 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    |   5 |      PX BLOCK ITERATOR             |          |     4 |  8008 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |

     

    It seems that the DELETE statement has problems but not the INSERT AS SELECT !

  • 11. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    rp0428 Guru
    Currently Being Moderated

    >

    Since I wanted to demonstrate new Oracle 12c enhancements on SecureFiles, I tried to use PDML statements on a non partitioned table with LOB column, in both Oracle 11g and Oracle 12c releases.

    >

    Unfortunately the 12c docs are not clear on just what 'enhancements' are really supported. The doc makes this statement:

    http://docs.oracle.com/cd/E16655_01/appdev.121/e17605/adlob_data_interface.htm#ADLOB1306

    >

    Parallel DML (PDML) Support for LOBs

    Oracle supports parallel execution of most of the following DML operations when performed on partitioned tables with SecureFiles LOBs or BasicFiles LOBs, and non-partitioned tables with SecureFiles LOBs only:

    •   INSERT
    •   INSERT AS SELECT
    •   CREATE TABLE AS SELECT
    •   DELETE
    •   UPDATE
    •   MERGE (conditional UPDATE and INSERT)
    •   Multi-table INSERT
    •   SQL Loader
    •   Import/Export

    Starting with release 12c, enhanced support for parallel DML includes the following:

    •   LOB columns stored as SecureFiles LOBs in non-partitioned tables. (Previous releases already included partitioned tables)
    •   Direct load support for SecureFiles LOB columns that have context index defined on them.

    >

    It is the word 'most' in the very first sentence that muddies the issue: 'Oracle supports parallel execution of most of the following DML operations'.

     

    Since this is new functionality there is no way to know just what Oracle means by that. Since the 11g doc listed DELETE as being supported for tables with SecureFiles LOBS one would think that it would still be supported.

     

    You will have to open an SOR with Oracle to get the official word as to which of those operations is and is not supported for the various combos of LOB.

  • 12. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Michael dbi Newbie
    Currently Being Moderated

    Indeed I have seen the subtility of "most of the following statements". However, as you said, the DELETE statement was already supported in Oracle 11g with partitioned tables. Here I can only make fork the INSERT AS SELECT in non partitioned tables. With partitioned tables, in both 11g and 12c, I can run no DML in parallel with a RANGE partitioned table, the INSERT AS SELECT does not work too. I wonder it it is because all types of partitioning are not supported, or if I am in an unsupported case because of an obscure reason...

     

    I will try other partitioning type to see..

  • 13. Re: Oracle 11.2 - Perform parallel DML on a non partitioned table with LOB column
    Michael dbi Newbie
    Currently Being Moderated

    I found what's wrong. My partitioned table had only one partition. I recreated it with two partitions and it works fine, so it seems that a minimum partition number is required.

    There are results of some tests I performed this morning:

     

     

     

    11g, SECUREFILE on range partitioned table

     

    - insert as select: OK

    - delete: OK

    - update: OK

    - create table as select: KO

     

     

    12c, BASICFILE or SECUREFILE on range partitioned table

     

    - insert as select: OK

    - delete: OK

    - update: OK

    - create table as select: KO

    - insert values: KO

     

     

    12c, SECUREFILE on non partitioned table

     

    - insert as select: OK

    - delete: KO

    - update: KO

    - create table as select: KO

    - insert values: KO

     

    I confirm that BASICFILE are not supported on non-partitioned tables.

     

    Somebody can try these statements in order to confirm ?

     

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points