Oracle Scratchpad

March 9, 2017

Quiz Night

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 10:34 pm BST Mar 9,2017

The following is a straight, continuous, untouched, cut-n-paste from an SQL*Plus session on 12.1.0.2. How come the update doesn’t execute in parallel – noting that parallel DML has been enabled and the tablescan to identify rows to be updated does execute in parallel ?


SQL> desc t1
 Name                                                                            Null?    Type
 ------------------------------------------------------------------------------- -------- ------------------------------------------------------
 OWNER                                                                           NOT NULL VARCHAR2(128)
 OBJECT_NAME                                                                     NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                                                           VARCHAR2(128)
 OBJECT_ID                                                                       NOT NULL NUMBER
 DATA_OBJECT_ID                                                                           NUMBER
 OBJECT_TYPE                                                                              VARCHAR2(23)
 CREATED                                                                         NOT NULL DATE
 LAST_DDL_TIME                                                                   NOT NULL DATE
 TIMESTAMP                                                                                VARCHAR2(19)
 STATUS                                                                                   VARCHAR2(7)
 TEMPORARY                                                                                VARCHAR2(1)
 GENERATED                                                                                VARCHAR2(1)
 SECONDARY                                                                                VARCHAR2(1)
 NAMESPACE                                                                       NOT NULL NUMBER
 EDITION_NAME                                                                             VARCHAR2(128)
 SHARING                                                                                  VARCHAR2(13)
 EDITIONABLE                                                                              VARCHAR2(1)
 ORACLE_MAINTAINED                                                                        VARCHAR2(1)

SQL> select * from t1 minus select * from all_objects;

OWNER           OBJECT_NAME          SUBOBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE             CREATED   LAST_DDL_
--------------- -------------------- ---------------------- ---------- -------------- ----------------------- --------- ---------
TIMESTAMP           STATUS  T G S       NAMESPACE EDITION_NAME         SHARING       E O
------------------- ------- - - - --------------- -------------------- ------------- - -
TEST_USER       T1                                              159331         159331 TABLE                   09-MAR-17 09-MAR-17
2017-03-09:22:16:36 VALID   N N N               1                      NONE            N

1 row selected.

SQL> alter session force parallel dml;

Session altered.

SQL> set serveroutput off
SQL> update t1 set object_name = lower(object_name) where data_object_id is null;

78324 rows updated.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b16abyv8p2790, child number 0
-------------------------------------
update t1 set object_name = lower(object_name) where data_object_id is
null

Plan hash value: 121765358

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

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

   5 - access(:Z>=:Z AND :Z<=:Z)
       filter("DATA_OBJECT_ID" IS NULL)

Note
-----
   - Degree of Parallelism is 8 because of table property
   - PDML disabled because single fragment or non partitioned table used

29 rows selected.

SQL> select * from v$pq_tqstat;

DFO_NUMBER      TQ_ID SERVER_TYPE       NUM_ROWS      BYTES  OPEN_TIME AVG_LATENCY      WAITS   TIMEOUTS PROCESS         INSTANCE     CON_ID
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- --------------- -------- ----------
         1          0 Producer              8997     363737 ##########           0         14          0 P004                   1          0
                                            9721     409075 ##########           0         12          0 P007                   1          0
                                            9774     408591 ##########           0         12          0 P005                   1          0
                                            9844     396816 ##########           0         12          0 P003                   1          0
                                            9965     403926 ##########           0         13          0 P006                   1          0
                                            9727     388829 ##########           0         12          0 P002                   1          0
                                            9951     399162 ##########           0         14          0 P001                   1          0
                                           10345     408987 ##########           0         13          0 P000                   1          0
                      Consumer             78324    3179123 ##########           0          0          0 QC                     1          0

9 rows selected.

If you want to see the fully parallel plan, it would look like this (after running the query above against v$pq_tqstat I executed one statement that I’m not showing before carrying on with the statements below):


SQL> update t1 set object_name = lower(object_name) where data_object_id is null;

78324 rows updated.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b16abyv8p2790, child number 0
-------------------------------------
update t1 set object_name = lower(object_name) where data_object_id is
null

Plan hash value: 3991856572

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

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

   5 - access(:Z>=:Z AND :Z<=:Z)
       filter("DATA_OBJECT_ID" IS NULL)

Note
-----
   - Degree of Parallelism is 8 because of table property

28 rows selected.

SQL> select object_name, object_type from user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
T1                   TABLE

1 row selected.

Answer (late-breaking)

Thanks to a very recent comment, I’ve just discovered (26th May 2017) that I never got around to adding the answer (for my case) to the body of the posting. Franck Pachot came very close with a suggestion about an invisible LOB column, but (in my reply to him) I owned up to having had a LOB column that I had marked as unused. A LOB column is a documented restriction on parallel execution – though the manuals (perfectly reasonably, in my view) leave it to you to realise that it’s still a restriction even if  you’ve marked the column unusable.

 

19 Comments »

  1. Hi Jonathan,
    Did you have a LOB column (invisible as we don’t see it in desc)? PDML UPDATE on SecureFile LOB is possible in 12.1 only if table is partitioned. Then the hidden command is an alter table drop column.
    Regards,
    Franck.

    Comment by @FranckPachot — March 9, 2017 @ 11:24 pm BST Mar 9,2017 | Reply

    • As it seems there are many solution, I’ve build the test case for mine (invisible BLOB) hoping that code formatting is ok

      SQLcl: Release 4.2.0 Production on Fri Mar 10 11:50:30 2017
       
      Copyright (c) 1982, 2017, Oracle.  All rights reserved.
       
      SQL> set sqlformat ansiconsole
      SQL> connect demo/demo@//localhost/PDB1;
      Connected.
       
      SQL> create table T1 as select all_objects.* from all_objects;
      Table T1 created.
       
      SQL> alter table T1 add x clob invisible;
      Table T1 altered.
       
      SQL> select * from T1 minus select * from all_objects;
      no rows selected
       
      SQL> select * from T1 minus select * from all_objects;
      no rows selected
       
      SQL> alter session force parallel dml;
      Session altered.
       
      SQL> update T1 set object_name = lower(object_name) where data_object_id is null;
      63,751 rows updated.
       
      SQL> select * from table(dbms_xplan.display_cursor);
       
      PLAN_TABLE_OUTPUT
      -----------------
      SQL_ID  dw670mt80tdvx, child number 0
      -------------------------------------
      update T1 set object_name = lower(object_name) where data_object_id is null
       
      Plan hash value: 121765358
       
      ---------------------------------------------------------------------------------------------------------------
      | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT      |          |       |       |   207 (100)|          |        |      |            |
      |   1 |  UPDATE               | T1       |       |       |            |          |        |      |            |
      |   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |
      |   3 |    PX SEND QC (RANDOM)| :TQ10000 | 63751 |  2365K|   207   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
      |   4 |     PX BLOCK ITERATOR |          | 63751 |  2365K|   207   (0)| 00:00:01 |  Q1,00 | PCWC |            |
      |*  5 |      TABLE ACCESS FULL| T1       | 63751 |  2365K|   207   (0)| 00:00:01 |  Q1,00 | PCWP |            |
      ---------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         5 - access(:Z>=:Z AND :Z<=:Z)
             filter("DATA_OBJECT_ID" IS NULL)
       
      Note
      -----
         - Degree of Parallelism is 2 because of table property
         - PDML disabled because single fragment or non partitioned table used
       
      29 rows selected.
       
      SQL> select * from v$pq_tqstat;
       
      DFO_NUMBER  TQ_ID  SERVER_TYPE  NUM_ROWS  BYTES    OPEN_TIME  AVG_LATENCY  WAITS  TIMEOUTS  PROCESS  INSTANCE  CON_ID
      ----------  -----  -----------  --------  -----    ---------  -----------  -----  --------  -------  --------  ------
      1           0      Producer     32844     1659762  0          0            16     3         P001     1         3
      1           0      Producer     30907     1655999  0          0            14     1         P000     1         3
      1           0      Consumer     63751     3315761  0          0            64     0         QC       1         3
       
       
      SQL> alter table T1 drop column x;
      Table T1 altered.
       
      SQL> update T1 set object_name = lower(object_name) where data_object_id is null;
      63,751 rows updated.
       
      SQL> select * from table(dbms_xplan.display_cursor);
       
      PLAN_TABLE_OUTPUT
      -----------------
      SQL_ID  dw670mt80tdvx, child number 0
      -------------------------------------
      update T1 set object_name = lower(object_name) where data_object_id is null
       
      Plan hash value: 3991856572
       
      ---------------------------------------------------------------------------------------------------------------
      | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT      |          |       |       |   207 (100)|          |        |      |            |
      |   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
      |   2 |   PX SEND QC (RANDOM) | :TQ10000 | 63751 |  2365K|   207   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
      |   3 |    UPDATE             | T1       |       |       |            |          |  Q1,00 | PCWP |            |
      |   4 |     PX BLOCK ITERATOR |          | 63751 |  2365K|   207   (0)| 00:00:01 |  Q1,00 | PCWC |            |
      |*  5 |      TABLE ACCESS FULL| T1       | 63751 |  2365K|   207   (0)| 00:00:01 |  Q1,00 | PCWP |            |
      ---------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         5 - access(:Z>=:Z AND :Z<=:Z)
             filter("DATA_OBJECT_ID" IS NULL)
       
      Note
      -----
         - Degree of Parallelism is 2 because of table property
       
      28 rows selected.
       
      SQL> select object_name, object_type from user_objects;
       
      OBJECT_NAME  OBJECT_TYPE
      -----------  -----------
      T1           TABLE
       
       
      SQL> exit
       
      Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
      

      Comment by @FranckPachot — March 10, 2017 @ 10:58 am BST Mar 10,2017 | Reply

      • Franck,

        There are, indeed, many reasons why the update will serialise – and the manual (a) doesn’t list them all (b) descibes some of them badly and (c) lists some which aren’t true. The manual also fails to point out which Note you might get for which issue – and the “single fragment” note applies to some very particular circumstances.

        Just to make life harder for you – here’s the output from a call to dbms_metadata.get_ddl() for the table before running the first update:

        DBMS_METADATA.GET_DDL('TABLE','T1')
        -------------------------------------------------------------------------------- 
          CREATE TABLE "TEST_USER"."T1" 
           (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,  
                "OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE,  
                "SUBOBJECT_NAME" VARCHAR2(128),
                "OBJECT_ID" NUMBER NOT NULL ENABLE,
                "DATA_OBJECT_ID" NUMBER,
                "OBJECT_TYPE" VARCHAR2(23),
                "CREATED" DATE NOT NULL ENABLE,
                "LAST_DDL_TIME" DATE NOT NULL ENABLE,
                "TIMESTAMP" VARCHAR2(19), 
                "STATUS" VARCHAR2(7),
                "TEMPORARY" VARCHAR2(1),
                "GENERATED" VARCHAR2(1),
                "SECONDARY" VARCHAR2(1),
                "NAMESPACE" NUMBER NOT NULL ENABLE,
                "EDITION_NAME" VARCHAR2(128),
                "SHARING" VARCHAR2(13),
                "EDITIONABLE" VARCHAR2(1),
                "ORACLE_MAINTAINED" VARCHAR2(1)
           ) SEGMENT CREATION IMMEDIATE
          PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
         NOCOMPRESS NOLOGGING
          STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
          BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
          TABLESPACE "TEST_8K_ASSM"
        
        
        1 row selected.
        
        

        If there were an invisible LOB column it would appear here with its definition, but marked as INVISIBLE.

        You were so close, though, that it would be uncharitable of me to lead you astray. There was a CLOB, but I had set it unused. That way it won’t show in the dbms_metadata call, it could only be seen in user_tab_cols (with a funny “date and time” name) or user_objects – though not in user_lobs.

        Comment by Jonathan Lewis — March 10, 2017 @ 11:22 am BST Mar 10,2017 | Reply

  2. Hi,
    DML could use parallel on partitioned table only. t1 table is not partitioned. Is it a such case? ;-)
    Beste Regards,
    Lucas

    Comment by Lucas B. — March 9, 2017 @ 11:41 pm BST Mar 9,2017 | Reply

    • *t1 table is not partitioned or have only one partition.

      Comment by Lucas B. — March 10, 2017 @ 12:18 am BST Mar 10,2017 | Reply

      • Second shot just before sleep – there was a trigger.

        Good night,
        Lucas B.

        Comment by Lucas B. — March 10, 2017 @ 12:25 am BST Mar 10,2017 | Reply

        • Lucas,

          Parallel DML is allowed on non-partitioned table – with some restrictions, and triggers are one of the things that can result in parallel DML being disabled.

          Unfortunately the manuals are not sufficiently informative about triggers – so it’s easy to be fooled if you don’t do exhaustive testing. There is some connection between the type of DML and the type of trigger, for example my update will not be affected by the presence of a “before INSERT for each row” trigger but it will serialise if there’s a “before UPDATE for each row” trigger.

          Some of the conflicts will, of course, appear obvious AFTER the event and then some of the obvious ones that don’t cause problems at first sight might cause problems in different circumstances – what’s the difference, for example, between and UPDATE and a delete followed by an INSERT if row movement is enabled ? (It’s interesting to note that some of the restrictions apply to tables where row movement is enabled).

          Comment by Jonathan Lewis — March 10, 2017 @ 11:45 am BST Mar 10,2017

  3. Hi Jonathan,
    Looking at this line from the serial plan – “PDML disabled because single fragment or non partitioned table used”
    I feel it has to do something with bitmap indexes
    regards
    Srivenu Kadiyala

    Comment by Srivenu KADIYALA — March 10, 2017 @ 4:56 am BST Mar 10,2017 | Reply

    • Srivenu,

      The manuals do make the claim that PDML is not supported on a table with bitmap indexes if the table is not partitioned. This doesn’t seem to be true in 12c; here’s the plan for my update after creating a bitmap index on the column I’m updating:

      
      update t1 set object_type = initcap(object_type)
      
      Plan hash value: 1247720252
      
      -------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                    | Name     | Starts | E-Rows | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |
      -------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT                             |          |      2 |        |     6 (100)|        |      |            |     16 |00:00:07.09 |      43 |
      |   1 |  PX COORDINATOR                              |          |      2 |        |            |        |      |            |     16 |00:00:07.09 |      43 |
      |   2 |   PX SEND QC (RANDOM)                        | :TQ10002 |      0 |  84418 |     6   (0)|  Q1,02 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |
      |   3 |    INDEX MAINTENANCE                         | T1       |     16 |        |            |  Q1,02 | PCWP |            |     16 |00:00:13.39 |    1673 |
      |   4 |     PX RECEIVE                               |          |     16 |  84418 |     6   (0)|  Q1,02 | PCWP |            |    337K|00:00:11.27 |       0 |
      |   5 |      PX SEND RANGE                           | :TQ10001 |      0 |  84418 |     6   (0)|  Q1,01 | P->P | RANGE      |      0 |00:00:00.01 |       0 |
      |   6 |       UPDATE                                 | T1       |     16 |        |            |  Q1,01 | PCWP |            |      0 |00:00:51.64 |     172K|
      |   7 |        PX RECEIVE                            |          |     16 |  84418 |     6   (0)|  Q1,01 | PCWP |            |    168K|00:00:39.76 |       0 |
      |   8 |         PX SEND HASH (BLOCK ADDRESS)         | :TQ10000 |      0 |  84418 |     6   (0)|  Q1,00 | S->P | HASH (BLOCK|      0 |00:00:00.01 |       0 |
      |   9 |          PX SELECTOR                         |          |     16 |        |            |  Q1,00 | SCWC |            |    168K|00:00:04.33 |   77533 |
      |  10 |           TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     16 |  84418 |     6   (0)|  Q1,00 | SCWC |            |    168K|00:00:02.64 |   77533 |
      |  11 |            BITMAP CONVERSION TO ROWIDS       |          |     16 |        |            |  Q1,00 | SCWC |            |    168K|00:00:00.89 |      22 |
      |  12 |             BITMAP INDEX FULL SCAN           | T1_B1    |     16 |        |            |  Q1,00 | SCWP |            |     68 |00:00:00.01 |      22 |
      -------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Note
      -----
         - Degree of Parallelism is 8 because of table property
      
      
      28 rows selected.
      
      
      

      (If I add a full(t1) hint the plan looks just like the first plan in the posting – the optimizer was a little fooled by the presence of the bitmap index to choose what is actually a bad plan thanks to its strategy of calculating the cost of an update like this as just the cost of identifying the required rowids)

      Comment by Jonathan Lewis — March 10, 2017 @ 11:38 am BST Mar 10,2017 | Reply

  4. 11.2.0.4 Database:

    SQL>  create table t1 as select * from dba_objects;
    
    Table created.
    
    SQL> alter table t1 add constraint t1_u unique(object_id) deferrable initially deferred enable;
    
    Table altered.
    
    SQL> alter session force parallel dml;
    
    Session altered.
    
    SQL> update t1 set object_name = lower(object_name) where data_object_id is null;
    
    260978 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------
    SQL_ID  b16abyv8p2790, child number 0
    -------------------------------------
    update t1 set object_name = lower(object_name) where data_object_id is
    null
    
    Plan hash value: 121765358
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT      |          |       |       |   379 (100)|          |        |      |            |
    |   1 |  UPDATE               | T1       |       |       |            |          |        |      |            |
    |   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)| :TQ10000 |   246K|    18M|   379   (1)| 00:00:05 |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     PX BLOCK ITERATOR |          |   246K|    18M|   379   (1)| 00:00:05 |  Q1,00 | PCWC |            |
    |*  5 |      TABLE ACCESS FULL| T1       |   246K|    18M|   379   (1)| 00:00:05 |  Q1,00 | PCWP |            |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access(:Z>=:Z AND :Z<=:Z)                  -- > hack to avoid formatting problem with wordpress
           filter("DATA_OBJECT_ID" IS NULL)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> alter table t1 drop constraint t1_u drop index;
    
    Table altered.
    
    SQL> update t1 set object_name = lower(object_name) where data_object_id is null;
    
    260978 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------
    SQL_ID  b16abyv8p2790, child number 0
    -------------------------------------
    update t1 set object_name = lower(object_name) where data_object_id is
    null
    
    Plan hash value: 3991856572
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT      |          |       |       |   379 (100)|          |        |      |            |
    |   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
    |   2 |   PX SEND QC (RANDOM) | :TQ10000 |   246K|    18M|   379   (1)| 00:00:05 |  Q1,00 | P->S | QC (RAND)  |
    |   3 |    UPDATE             | T1       |       |       |            |          |  Q1,00 | PCWP |            |
    |   4 |     PX BLOCK ITERATOR |          |   246K|    18M|   379   (1)| 00:00:05 |  Q1,00 | PCWC |            |
    |*  5 |      TABLE ACCESS FULL| T1       |   246K|    18M|   379   (1)| 00:00:05 |  Q1,00 | PCWP |            |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access(:Z>=:Z AND :Z<=:Z)                  -- > hack to avoid formatting problem with wordpress
           filter("DATA_OBJECT_ID" IS NULL)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    28 rows selected.
    

    Regards,

    Arian

    Comment by Arian — March 10, 2017 @ 10:07 am BST Mar 10,2017 | Reply

    • Arian,

      Thanks for the suggestion.

      Deferrable constraints can cause the update step to serialise, but for 12.1.0.2 the Note section makes a specific statement about it, viz:
      PDML disabled because deferrable constraints are used

      Comment by Jonathan Lewis — March 10, 2017 @ 11:29 am BST Mar 10,2017 | Reply

  5. […] Quiz Night […]

    Pingback by Priority Support Infogram, 31 March 2017 - GIN Personal Web Site — April 17, 2017 @ 3:13 pm BST Apr 17,2017 | Reply

  6. Dear Jonathan
    Thank you for this post, I have been looking for an explanation for the “PDML disabled because single fragment or non partitioned table used” message since middle of last year.
    My case is a DELETE on a view which joins the range partitioned table to an global temp table containing the relevant range IDs.
    The reason why I am looking into it is the fact that the serial execution does Bloom pruning, the parallel execution does a PARTITION RANGE ALL if any triggers exis on the view.
    https://docs.oracle.com/database/121/VLDBG/GUID-6626C70C-876C-47A4-8C01-9B66574062D8.htm#GUID-6626C70C-876C-47A4-8C01-9B66574062D8
    says that PDML is not supported on tables with triggers but views are not explicitely mentioned.

    The plans below are for serial, parallel with triggers and parallel without triggers in that order.

    Any ideas are welcome.
    br, Patrik

    Plan hash value: 454579139
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT                |           |    68 |   222K|  6818K  (1)| 00:04:27 |       |       |
    |   1 |  DELETE                         | M#PARTAB  |       |       |            |          |       |       |
    |   2 |   VIEW                          | M#PARTAB  |    68 |   222K|  6818K  (1)| 00:04:27 |       |       |
    |*  3 |    HASH JOIN                    |           |    68 | 31552 |  6818K  (1)| 00:04:27 |       |       |
    |   4 |     PART JOIN FILTER CREATE     | :BF0000   |    10 |   130 |     3  (34)| 00:00:01 |       |       |
    |   5 |      VIEW                       |           |    10 |   130 |     3  (34)| 00:00:01 |       |       |
    |   6 |       SORT UNIQUE               |           |    10 |   520 |     3  (34)| 00:00:01 |       |       |
    |*  7 |        TABLE ACCESS STORAGE FULL| PIDSTABLE |    10 |   520 |     2   (0)| 00:00:01 |       |       |
    |   8 |     PARTITION RANGE JOIN-FILTER |           | 10000 |  4404K|  6818K  (1)| 00:04:27 |:BF0000|:BF0000|
    |*  9 |      TABLE ACCESS STORAGE FULL  | PARTAB    | 10000 |  4404K|  6818K  (1)| 00:04:27 |:BF0000|:BF0000|
    -------------------------------------------------------------------------------------------------------------
     
    Note
    -----
       - PDML disabled because object is not decorated with parallel clause
       - Global temporary table session private statistics used
    
    Plan hash value: 4195767298
     
    -------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT                       |           |    68 |   222K|  3782K  (1)| 00:02:28 |       |       |        |      |            |
    |   1 |  DELETE                                | M#PARTAB  |       |       |            |          |       |       |        |      |            |
    |   2 |   PX COORDINATOR                       |           |       |       |            |          |       |       |        |      |            |
    |   3 |    PX SEND QC (RANDOM)                 | :TQ10002  |    68 |   222K|  3782K  (1)| 00:02:28 |       |       |  Q1,02 | P->S | QC (RAND)  |
    |   4 |     VIEW                               | M#PARTAB  |    68 |   222K|  3782K  (1)| 00:02:28 |       |       |  Q1,02 | PCWP |            |
    |*  5 |      HASH JOIN                         |           |    68 | 31552 |  3782K  (1)| 00:02:28 |       |       |  Q1,02 | PCWP |            |
    |   6 |       PX RECEIVE                       |           |    10 |   130 |     3  (34)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
    |   7 |        PX SEND BROADCAST               | :TQ10001  |    10 |   130 |     3  (34)| 00:00:01 |       |       |  Q1,01 | P->P | BROADCAST  |
    |   8 |         VIEW                           |           |    10 |   130 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |   9 |          HASH UNIQUE                   |           |    10 |   520 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |  10 |           PX RECEIVE                   |           |    10 |   520 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |  11 |            PX SEND HASH                | :TQ10000  |    10 |   520 |     3  (34)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
    |  12 |             HASH UNIQUE                |           |    10 |   520 |     3  (34)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
    |  13 |              PX BLOCK ITERATOR         |           |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
    |* 14 |               TABLE ACCESS STORAGE FULL| PIDSTABLE |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
    |  15 |       PX PARTITION RANGE ALL           |           | 10000 |  4404K|  3782K  (1)| 00:02:28 |     1 |   709 |  Q1,02 | PCWC |            |
    |* 16 |        TABLE ACCESS STORAGE FULL       | PARTAB    | 10000 |  4404K|  3782K  (1)| 00:02:28 |     1 |   709 |  Q1,02 | PCWP |            |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    
     
    Note
    -----
       - Degree of Parallelism is 2 because of hint
       - PDML disabled because single fragment or non partitioned table used
       - Global temporary table session private statistics used
       
    
     Plan hash value: 478881396
     
    ------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT                      |           |     1 |   108 |  3782K  (1)| 00:02:28 |       |       |        |      |            |
    |   1 |  PX COORDINATOR                       |           |       |       |            |          |       |       |        |      |            |
    |   2 |   PX SEND QC (RANDOM)                 | :TQ10002  |     1 |   108 |  3782K  (1)| 00:02:28 |       |       |  Q1,02 | P->S | QC (RAND)  |
    |   3 |    DELETE                             | PARTAB    |       |       |            |          |       |       |  Q1,02 | PCWP |            |
    |   4 |     PX RECEIVE                        |           |     1 |   108 |  3782K  (1)| 00:02:28 |       |       |  Q1,02 | PCWP |            |
    |   5 |      PX SEND HASH (BLOCK ADDRESS)     | :TQ10001  |     1 |   108 |  3782K  (1)| 00:02:28 |       |       |  Q1,01 | P->P | HASH (BLOCK|
    |*  6 |       HASH JOIN RIGHT SEMI            |           |     1 |   108 |  3782K  (1)| 00:02:28 |       |       |  Q1,01 | PCWP |            |
    |   7 |        JOIN FILTER CREATE             | :BF0001   |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |   8 |         PART JOIN FILTER CREATE       | :BF0000   |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |   9 |          PX RECEIVE                   |           |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |  10 |           PX SEND BROADCAST           | :TQ10000  |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
    |  11 |            PX BLOCK ITERATOR          |           |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
    |* 12 |             TABLE ACCESS STORAGE FULL | PIDSTABLE |    10 |   520 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
    |  13 |        JOIN FILTER USE                | :BF0001   | 10000 |   546K|  3782K  (1)| 00:02:28 |       |       |  Q1,01 | PCWP |            |
    |  14 |         PX PARTITION RANGE JOIN-FILTER|           | 10000 |   546K|  3782K  (1)| 00:02:28 |:BF0000|:BF0000|  Q1,01 | PCWC |            |
    |* 15 |          TABLE ACCESS STORAGE FULL    | PARTAB    | 10000 |   546K|  3782K  (1)| 00:02:28 |:BF0000|:BF0000|  Q1,01 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------------------------------------
     
     
    Note
    -----
       - Degree of Parallelism is 2 because of hint
       - Global temporary table session private statistics used
    

    Comment by Patrik Kleindl — May 24, 2017 @ 2:23 pm BST May 24,2017 | Reply

    • Patrik,

      There are many places in the manuals where the descriptions are ambiguous or not quite complete. If you find that creating a trigger on a view results in the the DML going serial and the manual says that triggers on tables block parallel DML then it’s a reasonably safe bet that the manuals should say “tables or views” but the author didn’t think about the combination of DML and views.

      If what you see contradicts the manuals I’d raise an SR; if the manuals seem to be incomplete but the case is really important I’d raise an SR.
      In your case I’d also try to create a very small test to see if it’s any particular feature of the view-based trigger that has the effect just in case the current trigger can be modified to a form where the restriction doesn’t appear. (I’d be pessimistic about this, though, because I’ve seen several examples where Oracle has code takes a generic approach to solving a problem that blocks activities that appear to be special “safe” cases).

      Comment by Jonathan Lewis — May 26, 2017 @ 11:27 am BST May 26,2017 | Reply

      • Hello Jonathan,
        sorry for the late reply but the SR took a while and led to some answers and more questions.
        1) Oracle has confirmed that the usage of “instead of” triggers on views is treated the same way as on tables.
        The documentation should be updated in a future version and the optimizer message for this case will be changed to
        “PDML disabled because instead of triggers defined ” instead of the general and misleading message “PDML disabled because single fragment or non partitioned table used”

        2) Our main problem was the fact that then plan with the disabled PDML also did not use the Bloom Filter and changed to a PARTITION RANGE ALL.
        The answer from Oracle regarding this is worth sharing because it might be related to https://jonathanlewis.wordpress.com/2016/07/08/dml-and-bloom/:
        “Initially, we didn’t allow bloom filtering/bloom pruning for DML queries at all.
        Starting 12.1, we relaxed the restriction and allowed for the following case

        – parallel DML only (not for serial DML)
        – serial query

        The customer case is a parallel query with serial DML, and bloom filter/pruning needs to be used in the parallel plan.
        With DML, there might be cases where nodes under DML are restarted, but bloom filter/pruning do not support restart yet.
        Bloom filter/pruning has to be executed only once if it’s part of parallel plan.
        For parallel DML and serial query, we know that bloom filter/pruning will be executed only once and that’s why we allowed bloom filtering/pruning for those cases.

        There are several cases even in non-DMLs where bloom filter/pruning is disabled due to the same reason that we don’t allow bloom pruning for the customer cases.”

        So now we know why no Bloom Pruning is used which leads to the next problem:

        3) Why the plan changes to PARTITION RANGE ALL and not PARTITION RANGE SUBQUERY could not be explained yet, I tried adapting the hint from the outline but it did not work
        Even on SELECTs I get mixed results where similar queries alternate between those two.
        A simplified test-case:
        part_table is a range-partitioned table, partitions is a table which holds the partition IDs.

        
        select /*+ use_hash(p r) */* from part_table r inner join
        (select id from partitions where ) p on r.plot_fk = p.id;
        
        create table p as select id from partitions where ;
        
        select /*+ use_hash(p r) */* from part_table r inner join p on r.plot_fk = p.id;
        
        alter session set "_bloom_pruning_enabled"=false;
        
        Same queries as above.
        

        For me I get Bloom pruning on both queries when it is enabled, but PARTITION RANGE SUBQUERY on the first and PARTITION RANGE ALL on the second when I disable Bloom pruning.

        Data types etc. are the same on partitions and p, also tried it with the same unique constraint/index.

        Comment by Patrik Kleindl — July 31, 2017 @ 9:33 am BST Jul 31,2017 | Reply

  7. Patrik,

    Thanks for the follow-up.

    The explicit subquery version of your code may have lost something in copying, or maybe the WHERE simply shouldn’t be there.

    The rules about when subquery pruning occurs are multi-part – something about percentage of rows from the driving table, percentage of partitions eliminated, and so on … I don’t remember the details – but I do remember thinking that the optimizer seemed to be very pessimistic about the possible benefits.

    It’s possible that “unparsed” text for the two queries is slightly different and produces different arithmetic for the two cases, leading to subquery pruning being sufficiently valuable in just one of the cases.

    I have the following notes from a demo script originally dated 2003, though last tested on 11.1.0.6 – so any comments may be out of date:

    
    rem     The decision to do this is costed. Clearly, the cost of pre-running
    rem     the query has to be less than the benefit of hammering the entire
    rem     partitioned table.  The feature is enabled, and the costs balanced
    rem     through the parameters:
    rem
    rem             _subquery_pruning_enabled = true
    rem             _subquery_pruning_cost_factor = 20
    rem             _subquery_pruning_reduction = 50
    rem
    rem     Two conditions have to be true:
    rem             a)      the initial subquery must return less than
    rem                     "_subquery_pruning_reduction" percent of the driver
    rem
    rem             b)      "cost of scanning whole of partition table"
    rem                     divided by
    rem                     "cost of running initial query"
    rem                     must be GREATER THAN
    rem                     "_subquery_pruning_cost_factor"
    
    

    Comment by Jonathan Lewis — August 2, 2017 @ 11:10 am BST Aug 2,2017 | Reply

    • Jonathan,
      thank you for your reply.
      Yes, the where condition got lost because I put it in < > brackets, the point was that the condition used was exactly the same and resulted in the same number for rows (3) on a table which had 70 partitions.

      Thanks too for the hidden parameters, I will try to test if those make a difference.

      I don’t have the example handy but I have seen cases when anything other than comparison with defined values seemed to cause very strange estimates for partition pruning. In some cases the optimizer regarded a full scan over several 100 partitions less costly than partition pruning which contained less keys than then number of partitions.

      Comment by Patrik Kleindl — August 3, 2017 @ 3:50 pm BST Aug 3,2017 | Reply

    • Jonathan,
      I tried changing the suggested parameters but all I can manage is to break the cases where the subquery pruning already works.
      Any straight join (a inner join b) will not use the subquery pruning, any join with a subquery (a inner join (select id from b where x)) seems to work, regardless if both variants return the same number of IDs.
      First plan is the join with the original subquery.
      Second plan is a join with a table created with the original subquery.
      Third plan is a join with a subquery which shows that the number of rows returned from the subquery and the expected rows after the hash join don’t seem to matter.
      Interestingly the cost is the same for all plans.

      --------------------------------------------------------------------------------------------------------
      | Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      --------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT          |            |   229 | 52212 |    87   (2)| 00:00:01 |       |       |
      |*  1 |  HASH JOIN                |            |   229 | 52212 |    87   (2)| 00:00:01 |       |       |
      |*  2 |   TABLE ACCESS FULL       | PARTITIONS |     4 |   100 |     3   (0)| 00:00:01 |       |       |
      |   3 |   PARTITION RANGE SUBQUERY|            | 10001 |  1982K|    84   (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
      |   4 |    TABLE ACCESS FULL      | PARTAB     | 10001 |  1982K|    84   (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
      --------------------------------------------------------------------------------------------------------
       
      -----------------------------------------------------------------------------------------------
      | Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      -----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT     |        | 10001 |  2021K|    87   (2)| 00:00:01 |       |       |
      |*  1 |  HASH JOIN           |        | 10001 |  2021K|    87   (2)| 00:00:01 |       |       |
      |   2 |   TABLE ACCESS FULL  | P      |     3 |    12 |     3   (0)| 00:00:01 |       |       |
      |   3 |   PARTITION RANGE ALL|        | 10001 |  1982K|    84   (2)| 00:00:01 |     1 |    55 |
      |   4 |    TABLE ACCESS FULL | PARTAB | 10001 |  1982K|    84   (2)| 00:00:01 |     1 |    55 |
      -----------------------------------------------------------------------------------------------
       
      ----------------------------------------------------------------------------------------------------
      | Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT          |        | 10001 |  2060K|    87   (2)| 00:00:01 |       |       |
      |*  1 |  HASH JOIN                |        | 10001 |  2060K|    87   (2)| 00:00:01 |       |       |
      |*  2 |   TABLE ACCESS FULL       | P3     |    97 |   776 |     3   (0)| 00:00:01 |       |       |
      |   3 |   PARTITION RANGE SUBQUERY|        | 10001 |  1982K|    84   (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
      |   4 |    TABLE ACCESS FULL      | PARTAB | 10001 |  1982K|    84   (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
      ----------------------------------------------------------------------------------------------------
      

      Comment by Patrik Kleindl — August 4, 2017 @ 8:54 am BST Aug 4,2017 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.