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 coming some time tomorrow.

12 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


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.