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 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 and by introducing "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 the 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 to enable parallel DML mode in a session:

ALTER SESSION ENABLE PARALLEL DML;

• Enable unrestricted parallel DML mode in a specific SQL statement, include 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, After 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