Oracle 23c New Features for DBA'sCame across this good table while scrolling the documentation,
When you need to do a parallel DML and that involves partitions, give a serious thought about the DML locks that can acquire. Below table gives you a handy info for the same.
Source:- here
| Type of Statement | Coordinator Process Acquires: | Each Parallel Execution Server Acquires: |
| Parallel UPDATE or DELETE into partitioned table; WHERE clause pruned to a subset of partitions or subpartitions | 1 table lock SX 1 partition lock X for each pruned partition or subpartition |
1 table lock SX 1 partition lock NULL for each pruned partition or subpartition owned by the query server process 1 partition-wait lock S for each pruned partition or subpartition owned by the query server process |
| Parallel row-migrating UPDATE into partitioned table; WHERE clause pruned to a subset of partitions or subpartitions | 1 table lock SX 1 partition X lock for each pruned partition or subpartition 1 partition lock SX for all other partitions or subpartitions |
1 table lock SX 1 partition lock NULL for each pruned partition or subpartition owned by the query server process 1 partition-wait lock S for each pruned partition owned by the query server process 1 partition lock SX for all other partitions or subpartitions |
| Parallel UPDATE, MERGE, DELETE, or INSERT into partitioned table | 1 table lock SX Partition locks X for all partitions or subpartitions |
1 table lock SX 1 partition lock NULL for each partition or subpartition 1 partition-wait lock S for each partition or subpartition |
| Parallel INSERT into partitioned table; destination table with partition or subpartition clause | 1 table lock SX 1 partition lock X for each specified partition or subpartition |
1 table lock SX 1 partition lock NULL for each specified partition or subpartition 1 partition-wait lock S for each specified partition or subpartition |
| Parallel INSERT into nonpartitioned table | 1 table lock X | None |
For example:-
Consider a table with 600 partitions running with a DOP of 100. Assume all partitions are involved in a parallel UPDATE or DELETE statement with no row-migrations.
The coordinator acquires:
- 1 table lock SX
- 600 partition locks X
Total server processes acquire:
- 100 table locks SX
- 600 partition locks NULL
- 600 partition-wait locks S
-Hope this helps
Geek DBA
Follow Me!!!