Oracle 23ai: Unrestricted Parallel DMLs

Overview:

• Oracle database allows DML statements to be executed in parallel mode by breaking the DML statements into exclusive smaller tasks.
• However, in releases prior to Oracle database 23ai, parallel DML operations had a limitation. Once an object is modified by a parallel DML statement, that object cannot be read or modified by later statements of the same transaction before ending the transaction by executing commit or rollback.
• Oracle Database 23ai removes that restriction by introducing the “Unrestricted Parallel DMLs or Unrestricted Direct Loads” feature.

Benefits: 

• In the same transaction session and before ending the transaction, you can:
  • Query the same table multiple times
  • Perform serial or parallel DML on the same table
  • Perform multiple direct loads 
• Overhead Reduced
  • Enable parallel DML in a session
  • Separate commits are not required after each parallel DML statement 
  • Take full advantage of using parallel DMLs in the same transaction 

Restrictions: 

• Heap-organized table only.
• No ASSM tablespaces

How to Enable Parallel DML Mode: 

• The parallel DML mode is required because parallel DML and serial DML have different locking, transaction, and disk space requirements and parallel DML is disabled for a session by default.
• When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.
• When parallel DML is enabled in a session, all DML statements in this session are considered for parallel execution.
• Run below SQL statement below to enable parallel DML mode in a session:

ALTER SESSION ENABLE PARALLEL DML;

• Enable unrestricted parallel DML mode in a specific SQL statement, including the ENABLE_PARALLEL_DML hint.

INSERT /*+ ENABLE_PARALLEL_DML */ 
…

• However, even if parallel DML is enabled, the DML operation may still execute serially if there are no parallel hints or no tables with a parallel attribute or if restrictions on parallel operations are violated.

Practice:

• In this practice, I’ll enable parallel DML and perform a parallel insert (non-unrestricted) without commit or rollback. After the insert, I’ll run a select from the same table and it will cause an error.
• Then I’ll perform the parallel insert using unrestricted parallel hint without commit or rollback, I’ll run select and additional insert without errors.

1. Create a new table.

SQL> create table test_dmls as select * from objects_objects;

Table TEST_DMLS created.

2. Alter the session and enable parallel DML.

SQL> alter session enable parallel dml;

Session altered.

3. Insert into the table with PARALLEL hint.

SQL> insert /*+ parallel(test_dmls 4) */ into test_dmls
            select /*+ parallel(test_dmls 4) */ * from test_dmls;

 76,237 rows inserted.

4. Select from the table without performing a commit or rollback. You will get and error.

SQL> select count(*) from test_dmls;

Error starting at line : 1 in command -
select count(*) from test_dmls
SQL ORA-12838: cannot read/modify an object after modifying it in parallel

More Details :
https://docs.oracle.com/error-help/db/ora-12838/

SQL> !oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or 
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.

5. Perform a commit.

SQL> commit;

Commit complete.

6. After the commit is done, now you can select again from the table.

SQL> select count(*) from test_dmls;

   COUNT(*)
___________
     152474

7. Perform a parallel insert using ENABLE_PARALLEL_DML hint.

SQL> insert /*+ enable_parallel_dml(test_dmls 4) */ into test_dmls
            select /*+ enable_parallel_dml(test_dmls 4) */ * from test_dmls;

152,474 rows inserted.

8. Select from the table without performing a commit or rollback. Now you will NOT get an error.

SQL> select count(*) from test_dmls;

   COUNT(*)
___________
     304948

9. Perform another parallel insert without a commit or rollback. No error.

SQL> insert /*+ enable_parallel_dml(test_dmls 4) */ into test_dmls
  2* select /*+ enable_parallel_dml(test_dmls 4) */ * from test_dmls;

304,948 rows inserted.

SQL> /

609,896 rows inserted.

SQL> /

1,219,792 rows inserted.

SQL> select count(*) from test_dmls;

   COUNT(*) 
___________ 
    2439584