Tuesday, August 6, 2019

PDML Disabled on Nonpartitioned IOT

When a parallel instrumented update of Nonpartitioned IOT (Index-Organized Tables) doesn’t execute in parallel, xplan contains a Note:
     PDML disabled because non partitioned or single fragment IOT used
I will try to demonstrate this behavior, but I'm not sure if there exists any documentation about this restriction, and I don't know what means "single fragment".

Note: All tests are done in Oracle 18c.


1. PDML disabled: single fragment IOT used


Run following test code,

SQL > drop table test_iot_tab;

SQL> create table test_iot_tab (id number, sid number, 
       constraint test_iot_tab#p primary key(id)
     ) organization index; 

SQL > insert into test_iot_tab select level, -1 from dual connect by level <= 1e5;

SQL > commit;

SQL > exec dbms_stats.gather_table_stats(null, 'TEST_IOT_TAB');

SQL > set serveroutput off

SQL > update /*+ enable_parallel_dml parallel(t 4) */ test_iot_tab t set sid = sys_context('userenv','sid');

  100000 rows updated. 

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

  -------------------------------------
  SQL_ID  3zsrt91jd3x6s, child number 2
  -------------------------------------
  update /*+ enable_parallel_dml parallel(t 4) */ test_iot_tab t set sid
  = sys_context('userenv','sid')
  
  Plan hash value: 1206014583
  
  -----------------------------------------------------------------------------------
  | Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT |                |       |       |     5 (100)|          |
  |   1 |  UPDATE          | TEST_IOT_TAB   |       |       |            |          |
  |   2 |   INDEX FULL SCAN| TEST_IOT_TAB#P |   100K|   878K|     5   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------
  
  Note
  -----
     - PDML disabled because non partitioned or single fragment IOT used
  
  19 rows selected.

SQL > select status, ptx, xid, xidusn, xidslot, xidsqn, used_ublk, used_urec, ptx_xidusn, ptx_xidslt, ptx_xidsqn, s.* 
       from v$transaction t, v$px_session s
      where t.ses_addr=s.saddr(+);

 STATUS PTX XID              XIDUSN  XIDSLOT XIDSQN USED_UBLK USED_UREC PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
 ------ --- ---------------- ------  ------- ------ --------- --------- ---------- ---------- ---------- ----- --- ------- ----- --------- --------- ------------ ---------- ------- ------ ----------
 ACTIVE NO  52000600A3810000 82      6       33187  1311      100000    0          0          0

SQL > select * from v$px_process;

  no rows selected

SQL > commit;

SQL > select * from v$pq_tqstat;

  no rows selected
xplan is noted with PDML disabled because of single fragment IOT used.

v$transaction.ptx displys "NO", indicating no parallel transaction (PTX stands for parent transaction), hence no rows in v$px_session.

Both selects on v$px_process and v$pq_tqstat return no rows.

Note that for PDML, information from v$pq_tqstat is available only after a commit or rollback operation as documented by Oracle V$PQ_TQSTAT.

If v$pq_tqstat returns rows, it is from the previous committed parallel DML, not the current committed serial DML.

v$px_process returns rows from previous and current parallel executions. The non-null sid and serial# are PX process currently in use, or recently in use because PX server process is shut down if it has not been used within certain time interval (probably 5 minutes).

Technical Article Understanding Parallel Execution – Part 2 also lists various problems with view V$PQ_TQSTAT.

With hint: index_ffs, we can make the query part of update run in parallel, but not DML (UPDATE is not inside PX COORDINATOR in xplan).

SQL > set serveroutput off

SQL > update /*+ enable_parallel_dml parallel(t 4) index_ffs(t test_iot_tab#p) */ test_iot_tab t set sid = sys_context('userenv','sid');
  100000 rows updated.
  
SQL > select * from table(dbms_xplan.display_cursor);

  -------------------------------------
  SQL_ID  93s3czmfbquu1, child number 0
  -------------------------------------
  update /*+ enable_parallel_dml parallel(t 4) index_ffs(t
  test_iot_tab#p) */ test_iot_tab t set sid = sys_context('userenv','sid')
  
  Plan hash value: 2045397606
  
  ------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
  ------------------------------------------------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT         |                |       |       |    16 (100)|          |        |   |               |
  |   1 |  UPDATE                  | TEST_IOT_TAB   |       |       |            |          |        |   |               |
  |   2 |   PX COORDINATOR         |                |       |       |            |          |        |   |               |
  |   3 |    PX SEND QC (RANDOM)   | :TQ10000       |   100K|   878K|    16   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
  |   4 |     PX BLOCK ITERATOR    |                |   100K|   878K|    16   (0)| 00:00:01 |  Q1,00 | PCWC |            |
  |*  5 |      INDEX FAST FULL SCAN| TEST_IOT_TAB#P |   100K|   878K|    16   (0)| 00:00:01 |  Q1,00 | PCWP |            |
  ------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     5 - access(:Z>=:Z AND :Z<=:Z)
  
  Note
  -----
     - Degree of Parallelism is 4 because of table property
     - PDML disabled because non partitioned or single fragment IOT used
  
SQL > commit;  


2. Update by Package DBMS_PARALLEL_EXECUTE


Since PDML on IOT is not allowed, and it is not clear what means "single fragment IOT", one alternative is to use 11gR2 introduced package dbms_parallel_execute to manually update table in parallel.

First, we try the most common usage of this package: create_chunks_by_rowid (Oracle Docu wrote: Index-organized tables are not allowed for create_chunks_by_rowid).

SQL > exec dbms_parallel_execute.drop_task('test_iot_task');

SQL > exec dbms_parallel_execute.create_task (task_name => 'test_iot_task');

SQL> select * from user_parallel_execute_tasks where task_name = 'test_iot_task';

  TASK_NAME      CHUNK_TYPE  STATUS   TABLE_OWNER  TABLE_NAME  NUMBER_COLUMN  TASK_COMMENT  JOB_PREFIX
  -------------  ----------  -------  -----------  ----------  -------------  ------------  ----------
  test_iot_task  UNDECLARED  CREATED

SQL > begin
        dbms_parallel_execute.create_chunks_by_rowid
          (task_name   => 'test_iot_task',
           table_owner => 'K',
           table_name  => 'TEST_IOT_TAB',
           by_row      => true,   -- or false
           chunk_size  => 200);
      end;
      /

      ERROR at line 1:
      ORA-29491: invalid table for chunking
      ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 25
      ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 21
      ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 120
      ORA-06512: at line 2
We got ORA-29491, which says that create_chunks_by_rowid requires physical ROWID, but IOT can only provide logical rowid.
SQL > oerr ora 29491
 29491, 00000, "invalid table for chunking"
 // *Cause:  An attempt was made to chunk a table by ROWID, 
 //          but the table was not a physical table or the table was an IOT.
 //          physical table or the table is an IOT.
 // *Action: Use a table which has physical ROWID.
So we try another chunking method: create_chunks_by_number_col:

SQL > begin
        dbms_parallel_execute.create_chunks_by_number_col
         (task_name    => 'test_iot_task',
          table_owner  => 'K',
          table_name   => 'TEST_IOT_TAB',
          table_column => 'ID',
          chunk_size   => 200);
         end;
         /

      PL/SQL procedure successfully completed.

SQL > select * from user_parallel_execute_tasks where task_name = 'test_iot_task';

  TASK_NAME      CHUNK_TYPE    STATUS   TABLE_OWNER  TABLE_NAME    NUMBER_COLUMN  TASK_COMMENT  JOB_PREFIX
  -------------  ------------  -------  -----------  ------------  -------------  ------------  ----------
  test_iot_task  NUMBER_RANGE  CHUNKED  K            TEST_IOT_TAB  ID
  

SQL > declare
       l_sql_stmt varchar2(32767);
     begin
       l_sql_stmt := q'[update test_iot_tab t 
                        set    t.sid = sys_context('userenv','sid')
                        where id between :start_id and :end_id]';
     
       dbms_parallel_execute.run_task(task_name      => 'test_iot_task',
                                      sql_stmt       => l_sql_stmt,
                                      language_flag  => dbms_sql.native,
                                      parallel_level => 10);
     end;
     /
     
     PL/SQL procedure successfully completed.


SQL > select * from user_parallel_execute_tasks where task_name = 'test_iot_task';

  TASK_NAME      CHUNK_TYPE    STATUS    TABLE_OWNER  TABLE_NAME    NUMBER_COLUMN  TASK_COMMENT  JOB_PREFIX
  -------------  ------------  --------  -----------  ------------  -------------  ------------  ----------
  test_iot_task  NUMBER_RANGE  FINISHED  K            TEST_IOT_TAB  ID                           TASK$_81116
                                             
SQL > select sid, count(*) from test_iot_tab group by sid order by sid;

    SID  COUNT(*)
    ---  --------
     27      7000
    196     15400
    197     13600
    203      6200
    377     12600
    379      4600
    559     10800
    729     13600
    750     10200
    908      6000
    
    10 rows selected.
The above test shows that create_chunks_by_number_col is allowed, and table was updated by 10 parallel jobs.

The drawback of dbms_parallel_execute is that each chunk in each job is committed separately, therefore whole transaction is not atomic as documented on dbms_parallel_execute.run_task:
      This procedure executes the specified statement (sql_stmt) on the chunks in parallel. 
      It commits after processing each chunk. 


3. Partitioned Index-Organized Tables


Since PDML is disabled on Nonpartitioned IOT, we can look the case of Partitioned IOT.

SQL> drop table test_iot_tab_part;

SQL > create table test_iot_tab_part (part number, id number, sid number, 
       constraint test_iot_tab_part#p primary key(part, id)
     ) organization index
       partition by list (part)
        (partition p1 values (1),  
         partition p2 values (2),
         partition p3 values (3),
         partition p4 values (4)
        );

SQL > insert into test_iot_tab_part select mod(level, 4) + 1 part, level, -1 from dual connect by level <= 1e5;

SQL > commit;

SQL > exec dbms_stats.gather_table_stats(null, 'TEST_IOT_TAB_PART');

SQL > set serveroutput off

SQL > update /*+ enable_parallel_dml parallel(t 4) */ test_iot_tab_part t set sid = sys_context('userenv','sid');

     100000 rows updated. 

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

  -------------------------------------
  SQL_ID  75bsynpc6h5qq, child number 1
  -------------------------------------
  update /*+ enable_parallel_dml parallel(t 4) */ test_iot_tab_part t set
  sid = sys_context('userenv','sid')
  
  Plan hash value: 4043313740
  
  ---------------------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
  ---------------------------------------------------------------------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT         |                     |       |       |     2 (100)|          |       |       |        |      |            |
  |   1 |  PX COORDINATOR          |                     |       |       |            |          |       |       |        |      |            |
  |   2 |   PX SEND QC (RANDOM)    | :TQ10000            |   100K|  1171K|     2   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
  |   3 |    UPDATE                | TEST_IOT_TAB_PART   |       |       |            |          |       |       |  Q1,00 | PCWP |            |
  |   4 |     PX PARTITION LIST ALL|                     |   100K|  1171K|     2   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
  |   5 |      INDEX FULL SCAN     | TEST_IOT_TAB_PART#P |   100K|  1171K|     2   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
  ---------------------------------------------------------------------------------------------------------------------------------------------
  
  Note
  -----
     - Degree of Parallelism is 4 because of table property
  
  22 rows selected.

SQL > select status, ptx, xid, xidusn, xidslot, xidsqn, used_ublk, used_urec, ptx_xidusn, ptx_xidslt, ptx_xidsqn, s.* 
       from v$transaction t, v$px_session s
      where t.ses_addr=s.saddr(+);

  STATUS PTX XID              XIDUSN XIDSLOT XIDSQN USED_UBLK USED_UREC PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN SADDR            SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
  ------ --- ---------------- ------ ------- ------ --------- --------- ---------- ---------- ---------- ---------------  --- ------- ----- --------- --------- ------------ ---------- ------- ------ ----------
  ACTIVE YES 5600120012830000 86     18      33554  331       25001     98         11         21043      00000000B7BFCF38 203 27206   392   53164     1         1            1          1       4      4
  ACTIVE YES 53001300117B0000 83     19      31505  331       25001     98         11         21043      00000000B62F60B8 750 29571   392   53164     1         1            1          2       4      4
  ACTIVE YES 61001B00DC4C0000 97     27      19676  331       25001     98         11         21043      00000000B7BF5B70 206 58371   392   53164     1         1            1          3       4      4
  ACTIVE YES 5E0019009B6D0000 94     25      28059  331       25001     98         11         21043      00000000B7DB8C78 377 55026   392   53164     1         1            1          4       4      4
  ACTIVE YES 62000B0033520000 98     11      21043  1         1         98         11         21043      00000000B7D94990 392 53164   392

SQL > select * from v$px_process;

  SERV STATUS     PID SPID     SID SERIAL# IS_GV
  ---- --------- ---- ------ ----- ------- -----
  P000 IN USE      49 32017    203   27206 FALSE
  P001 IN USE      58 32019    750   29571 FALSE
  P002 IN USE      61 32021    206   58371 FALSE
  P003 IN USE      62 32023    377   55026 FALSE
  
  4 rows selected.

SQL > commit;

SQL > select * from v$pq_tqstat;

  DFO_NUMBER TQ_ID SERVER_TYP NUM_ROWS BYTES  OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCES
  ---------- ----- ---------- -------- ----- ---------- ----------- ----- -------- ------
           1     0 Producer          2   286          0           0     1        0 P003  
           1     0 Producer          2   286          0           0     1        0 P002  
           1     0 Producer          2   286          0           0     1        0 P001  
           1     0 Producer          2   286          0           0     1        0 P000  
           1     0 Consumer          8  1144          0           0    15        2 QC    
  
  5 rows selected.
The above test shows that update on Partitioned IOT is executed in parallel, i.e. PDML enabled.

Oracle performs two-phase commit protocol in PDML (analogue to distributed transactions) and documented in VLDB and Partitioning Guide (Release 12.2) - 8.5.3.5 Transaction Restrictions for Parallel DML:
    To ensure user-level transactional atomicity, the coordinator uses a two-phase commit protocol 
    to commit the changes performed by the parallel process transactions.
Oracle8 Server Migration (A54650_01) - Oracle8 Enhancements has some details:
    Changes to Fixed Views
  
    The following fixed views contain new information about parallel DML:
  
       V$SESSION: This fixed view contains a new column for ENABLE PARALLEL DML mode.
       V$TRANSACTION: The existing column STATUS has two new values, PTX PREPARED and PTX COMMITTED (where PTX stands for parent transaction). 
                     This fixed view also contains new columns (where XID stands for transaction identifier):
             PTX (value YES or NO)
             PTX_XIDUSN
             PTX_XIDSLT
             PTX_XIDSQN
       V$PQ_SESSTAT: This fixed view contains a new row: DML Parallelized.
       V$PQ_SYSSTAT: This fixed view contains a new row: DML Initiated.
In fact, for above PDML example, if we suspend one parallel slave (e.g. P002) before issuing commit in PX COORDINATOR session, v$transaction.status shows that COORDINATOR session and P002 have status: ACTIVE, but all other PX slaves are marked with status: PTX PREPARED.

--- Launch PDML in PX COORDINATOR session

SQL > update /*+ enable_parallel_dml parallel(t 4) */ test_iot_tab_part t set sid = sys_context('userenv','sid');

SQL > select * from v$px_process;

  SERV STATUS           PID SPID                       SID SERIAL# IS_GV
  ---- --------- ---------- ------------------------ ----- ------- -----
  P000 IN USE            49 6950                       206    5492 FALSE
  P001 IN USE            58 6952                       750   22721 FALSE
  P002 IN USE            61 6954                       203   35975 FALSE
  P003 IN USE            62 6956                       377   60192 FALSE
  
  4 rows selected. 


--- Suspend one parallel slave in ORADEBUG session

SQL(oradebug) > oradebug setospid 6954
  Oracle pid: 61, Unix process pid: 6954, image: oracle@testdb (P002)

SQL(oradebug) > oradebug suspend
  Statement processed.


--- return to PX COORDINATOR session, issue commit 

SQL > commit;


--- in a monitor session, show PTX PREPARED phase

SQL (monitor) > select status, ptx, xid, xidusn, xidslot, xidsqn, used_ublk, used_urec, ptx_xidusn, ptx_xidslt, ptx_xidsqn, s.* 
       from v$transaction t, v$px_session s
      where t.ses_addr=s.saddr(+);

  STATUS       PTX XID              XIDUSN XIDSLOT XIDSQN USED_UBLK USED_UREC PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN SADDR            SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
  ------------ --- ---------------- ------ ------- ------ --------- --------- ---------- ---------- ---------- ---------------  --- ------- ----- --------- --------- ------------ ---------- ------- ------ ----------
  PTX PREPARED YES 5C001600566A0000 92     22      27222  331       25001     94         15         28085      00000000B7BF5B70 206 5492    392   53164     1         1            1          1       4      4
  PTX PREPARED YES 5A000400B4680000 90     4       26804  331       25001     94         15         28085      00000000B62F60B8 750 22721   392   53164     1         1            1          2       4      4
  ACTIVE       YES 5500190020780000 85     25      30752  331       25001     94         15         28085      00000000B7BFCF38 203 35975   392   53164     1         1            1          3       4      4
  PTX PREPARED YES 5F000100C0600000 95     1       24768  331       25001     94         15         28085      00000000B7DB8C78 377 60192   392   53164     1         1            1          4       4      4
  ACTIVE       YES 5E000F00B56D0000 94     15      28085  3         3         94         15         28085      00000000B7D94990 392 53164   392
When a DML doesn’t execute in parallel, it is not always obvious as demonstrated by Jonathan's Blog Quiz Night (March 9, 2017) on unused CLOB column.

I did exercise below to understand the Quiz.

SQL > drop table test_unused_lob_col_tab;

SQL > create table test_unused_lob_col_tab (id number, sid number, unused_clob clob);

SQL > alter table test_unused_lob_col_tab set unused (unused_clob);

SQL > select * from user_unused_col_tabs where table_name = 'TEST_UNUSED_LOB_COL_TAB';

  TABLE_NAME                   COUNT
  ----------------------- ----------
  TEST_UNUSED_LOB_COL_TAB          1

-- DBA_TAB_COLUMNS view filters out system-generated hidden columns and invisible columns
SQL > select table_name, column_name, data_type, column_id                                            
      from  dba_tab_columns                                                                                       
      where table_name = 'TEST_UNUSED_LOB_COL_TAB';                                                                
                                                                                                                   
  TABLE_NAME                COLUMN_NAME                    DATA_TYPE   COLUMN_ID                                
  ------------------------- ------------------------------ ---------- ----------                                
  TEST_UNUSED_LOB_COL_TAB   ID                             NUMBER              1                                
  TEST_UNUSED_LOB_COL_TAB   SID                            NUMBER              2      
  
  2 rows selected. 


--DBA_TAB_COLS has 4 different Column ID: 
--  COLUMN_ID           ,Sequence number of the column as created,  NULL when HIDDEN_COLUMN='YES'
--  SEGMENT_COLUMN_ID   ,Sequence number of the column in the segment, NULL when VIRTUAL_COLUMN = 'YES'
--  INTERNAL_COLUMN_ID  ,Internal sequence number of the column, NOT NULL
--  COLLATED_COLUMN_ID  ,Internal sequence number for virtual column generates a collation key (since 18c)

SQL > select column_name, data_type, hidden_column, virtual_column, column_id, segment_column_id, internal_column_id             
    from dba_tab_cols                                                                                          
   where table_name = 'TEST_UNUSED_LOB_COL_TAB';
  
  COLUMN_NAME                DATA_TYPE HIDDEN VIRTUAL COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
  -------------------------- --------- ------ ------- --------- ----------------- ------------------ 
  ID                         NUMBER    NO     NO      1         1                 1
  SID                        NUMBER    NO     NO      2         2                 2
  SYS_C00003_19080610:24:47$ CLOB      YES    NO                3                 3                                                                                       
 
  3 rows selected. 
  

-- Both LOGIC_COLUMN_ID and SEGMENT_COLUMN_ID are displayed.
-- LOGIC_COLUMN_ID is 0 for hidden column.                                                                                                              
SQL > select o.object_name, o.subobject_name, o.object_id, c.name column_name, c.col# logic_column_id, c.segcol# segment_column_id
            ,(case 
                when     bitand(c.property, 32768) = 32768 
                     and bitand(c.property, 1) != 1 
                     and bitand(c.property, 1024) != 1024 
                then 'YES' 
                else 'NO' 
              end)  unused
            ,bitand(c.property, 32768) unused_chk, bitand(c.property, 1) adt_attr, bitand(c.property, 1024) ntab_setid
          --,c.unusablebefore#, c.unusablebeginning#
       from sys.col$ c, dba_objects o
      where c.obj# = o.object_id 
        and object_name = 'TEST_UNUSED_LOB_COL_TAB'
      order by segment_column_id;
  
  OBJECT_NAME             OBJECT_ID COLUMN_NAME                LOGIC_COLUMN_ID   SEGMENT_COLUMN_ID UNUSED UNUSED_CHK ADT_ATTR NTAB_SETID
  ----------------------- --------- -------------------------- ----------------- ----------------- ------ ---------- -------- ----------
  TEST_UNUSED_LOB_COL_TAB 3754451   ID                         1                 1                 NO     0          0        0
  TEST_UNUSED_LOB_COL_TAB 3754451   SID                        2                 2                 NO     0          0        0
  TEST_UNUSED_LOB_COL_TAB 3754451   SYS_C00003_19080610:24:47$ 0                 3                 YES    32768      0        0

    --Note 1: unused column_name seems a concatenation of column_id with unused timestamp.
    --        two columns: unusablebefore#, unusablebeginning# in sys.col$ are empty.
  
    --Note 2: virtual_column has only internal_column_id in dba_tab_cols, its segment_column_id is empty.
    --        In above query of sys.col$, segcol# (segment_column_id) is 0.

SQL > select dbms_metadata.get_ddl('TABLE', 'TEST_UNUSED_LOB_COL_TAB', 'K') from dual;

       CREATE TABLE "K"."TEST_UNUSED_LOB_COL_TAB" 
         ( "ID"  NUMBER, 
           "SID" NUMBER
         ) SEGMENT CREATION DEFERRED 
       PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
       NOCOMPRESS LOGGING
       TABLESPACE "TEST_USER" 

SQL > set serveroutput off

SQL > update /*+ enable_parallel_dml parallel(t 4) */ test_unused_lob_col_tab t set sid = sys_context('userenv','sid');  

  0 rows updated.

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

  -------------------------------------
  SQL_ID  82vs0ct3p2nfj, child number 1
  -------------------------------------
  update /*+ enable_parallel_dml parallel(t 4) */ test_unused_lob_col_tab
  t set sid = sys_context('userenv','sid')
  
  Plan hash value: 4086267116
  
  ------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
  ------------------------------------------------------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT      |                         |       |       |     2 (100)|          |     | |            |
  |   1 |  UPDATE               | TEST_UNUSED_LOB_COL_TAB |       |       |            |          |     | |            |
  |   2 |   PX COORDINATOR      |                         |       |       |            |          |     | |            |
  |   3 |    PX SEND QC (RANDOM)| :TQ10000                |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
  |   4 |     PX BLOCK ITERATOR |                         |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
  |*  5 |      TABLE ACCESS FULL| TEST_UNUSED_LOB_COL_TAB |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
  ------------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     5 - access(:Z>=:Z AND :Z<=:Z)
  
  Note
  -----
     - Degree of Parallelism is 4 because of table property
     - PDML disabled because single fragment or non partitioned table used

SQL > alter table test_unused_lob_col_tab drop unused columns;

SQL > select * from user_unused_col_tabs where table_name = 'TEST_UNUSED_LOB_COL_TAB';

  no rows selected

SQL > update /*+ enable_parallel_dml parallel(t 4) */ test_unused_lob_col_tab t set sid = sys_context('userenv','sid');  

  0 rows updated.

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

  -------------------------------------------------------------------------------------------------------------------------------
  SQL_ID  82vs0ct3p2nfj, child number 1
  -------------------------------------
  update /*+ enable_parallel_dml parallel(t 4) */ test_unused_lob_col_tab
  t set sid = sys_context('userenv','sid')
  
  Plan hash value: 3773939051
  
  ------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
  ------------------------------------------------------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT      |                         |       |       |     2 (100)|          |     | |            |
  |   1 |  PX COORDINATOR       |                         |       |       |            |          |     | |            |
  |   2 |   PX SEND QC (RANDOM) | :TQ10000                |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
  |   3 |    UPDATE             | TEST_UNUSED_LOB_COL_TAB |       |       |            |          |  Q1,00 | PCWP |            |
  |   4 |     PX BLOCK ITERATOR |                         |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
  |*  5 |      TABLE ACCESS FULL| TEST_UNUSED_LOB_COL_TAB |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
  ------------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     5 - access(:Z>=:Z AND :Z<=:Z)
  
  Note
  -----
     - Degree of Parallelism is 4 because of table property
If we look carefully the above xplan Note, and that of Nonpartitioned IOT:
     PDML disabled because single fragment or non partitioned table used
     PDML disabled because non partitioned or single fragment IOT used
they are not exact the same, probably dynamically composed according to the parsed statement.

By the way, in Oracle 12c, when set a column unused, it could hit:
  Bug 26965236 : DELETE FROM TSDP_SENSITIVE_DATA$ CAUSING ENQ: TM - CONTENTION WAITS
as discussed in Blog TM lock and no transaction commit.