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.
