OS Environment: Oracle Linux 9.6 (64bit)
DB Environment: Oracle AI Database 23.26.3.0.0 ai
Starting with Oracle Database 26ai, a table can be queried or modified again within the same transaction after a Direct Path Insert.
In Oracle 19c, when a Direct Path Insert was performed using the APPEND hint, accessing the same table again before a COMMIT or ROLLBACK resulted in an ORA-12838 error.
In 26ai, this restriction is removed by default.
The behavior is controlled by the hidden parameter _online_direct_load. Setting this parameter to 0 restores the behavior of previous versions, where the same table cannot be accessed again after an append insert until the transaction is completed.
Reference: Oracle 26ai – Direct Load (Direct Path Insert) Transaction Restriction Removed
In addition to the _online_direct_load hidden parameter, the NO_MULTI_APPEND hint tested in this article can also restore the previous behavior.
When APPEND and NO_MULTI_APPEND are used together, Oracle 26ai raises ORA-12838 when the same table is queried or modified again after a Direct Path Insert, similar to Oracle 19c.
Tests
- Check the MULTI_APPEND hint
- Check the test environment
- Create a sample table
- Test default APPEND behavior
- Test APPEND + MULTI_APPEND
- Test APPEND + NO_MULTI_APPEND
- Compare with NOAPPEND
- Compare the results
1. Check the MULTI_APPEND Hint
First, check whether the hints exist in V$SQL_HINT.
SQL>
set lines 200 pages 1000
col name for a30
col inverse for a30
col version for a15
select name, inverse, target_level, version
from v$sql_hint
where name in ('APPEND', 'NOAPPEND',
'MULTI_APPEND', 'NO_MULTI_APPEND')
order by name;
NAME INVERSE TARGET_LEVEL VERSION
------------------------------ ------------------------------ ------------ ---------------
APPEND NOAPPEND 1 8.1.0
MULTI_APPEND NO_MULTI_APPEND 1 23.1.0
NOAPPEND APPEND 1 8.1.0
NO_MULTI_APPEND MULTI_APPEND 1 23.1.0MULTI_APPEND and NO_MULTI_APPEND are available and show a version of 23.1.0.
2. Check the Test Environment
Check the database version.
SQL>
set lines 200 pages 1000
select * from v$version;
BANNER
---------------------------------------------------------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
--------------------------------------------------------------------------------------------------------------------------------- ----------
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production
Version 23.26.3.0.0
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production 0The database version used for this test is 23.26.3.0.0.
Check the COMPATIBLE parameter.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 23.6.0
noncdb_compatible boolean FALSEThe COMPATIBLE parameter is set to 23.6.0.
3. Create a Sample Table
Create EMP2 from the EMP table.
SQL>
drop table emp2 purge;
create table emp2 as select * from emp;Check the number of rows.
SQL>
select count(*) from emp2;
COUNT(*)
----------
14The table currently contains 14 rows.
4. Test Default APPEND Behavior
Set STATISTICS_LEVEL to ALL so the execution plan can be checked.
SQL> alter session set statistics_level = all;
Session altered.First, use only the existing APPEND hint without explicitly specifying MULTI_APPEND.
SQL> insert /*+ append */ into emp2 select * from emp2;
14 rows created.14 rows are inserted, bringing the total to 28 rows.
Before issuing a COMMIT, check the execution plan.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 1283500778
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:00:00.01 | 13 | 1 | | | |
| 1 | LOAD AS SELECT | EMP2 | 1 | 0 |00:00:00.01 | 13 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14 |00:00:00.01 | 3 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | EMP2 | 1 | 14 |00:00:00.01 | 2 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
33 rows selected.The execution plan shows LOAD AS SELECT, and the outline contains LOAD_METHOD(... HIGH_WATER_MARK), confirming that the APPEND hint was applied and a Direct Path Insert was used.
Now query EMP2 without committing the transaction.
SQL> select count(*) from emp2;
COUNT(*)
----------
28All 28 rows can be queried successfully.
In Oracle 19c, this is where ORA-12838 would normally occur. In 26ai, however, the table can be queried without ending the transaction.
Reference: Oracle 26ai – Direct Load (Direct Path Insert) Transaction Restriction Removed
Run another APPEND insert without committing.
SQL> insert /*+ append */ into emp2 select * from emp2;
28 rows created.Another 28 rows are inserted successfully.
Check the row count again.
SQL> select count(*) from emp2;
COUNT(*)
----------
56The table now contains 56 rows.
This confirms that in Oracle 26ai, when only the APPEND hint is used, a table can be queried and another Direct Path Insert can be performed within the same transaction.
Rollback the test data.
SQL> rollback;
Rollback complete.5. Test APPEND + MULTI_APPEND
Set STATISTICS_LEVEL to ALL again.
SQL> alter session set statistics_level = all;
Session altered.This time, use APPEND together with MULTI_APPEND.
SQL> insert /*+ append multi_append */ into emp2 select * from emp2;
14 rows created.Check the execution plan before committing.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 1283500778
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:00:00.01 | 45 | 1 | | | |
| 1 | LOAD AS SELECT | EMP2 | 1 | 0 |00:00:00.01 | 45 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14 |00:00:00.01 | 8 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | EMP2 | 1 | 14 |00:00:00.01 | 7 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
33 rows selected.As with the previous test, the execution plan shows LOAD AS SELECT, and the outline contains LOAD_METHOD(... HIGH_WATER_MARK).
The LOAD_METHOD and LOAD_TYPE values are also the same as when only the APPEND hint was used.
Query EMP2 without committing.
SQL> select count(*) from emp2;
COUNT(*)
----------
28The query succeeds and returns 28 rows.
In Oracle 19c, this operation would result in ORA-12838, but it works normally in 26ai.
Run another APPEND + MULTI_APPEND insert without committing.
SQL> insert /*+ append multi_append */ into emp2 select * from emp2;
28 rows created.The insert succeeds.
Check the row count again.
SQL> select count(*) from emp2;
COUNT(*)
----------
56The table now contains 56 rows.
The behavior is the same as using APPEND alone. With APPEND + MULTI_APPEND, Oracle 26ai allows the same table to be queried and another Direct Path Insert to be performed within the same transaction.
Rollback the test data.
SQL> rollback;
Rollback complete.6. Test APPEND + NO_MULTI_APPEND
Set STATISTICS_LEVEL to ALL.
SQL> alter session set statistics_level = all;
Session altered.This time, use APPEND together with NO_MULTI_APPEND.
SQL> insert /*+ append no_multi_append */ into emp2 select * from emp2;
14 rows created.Before committing, check the execution plan.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 1283500778
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:00:00.01 | 13 | 1 | | | |
| 1 | LOAD AS SELECT | EMP2 | 1 | 0 |00:00:00.01 | 13 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14 |00:00:00.01 | 8 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | EMP2 | 1 | 14 |00:00:00.01 | 7 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
33 rows selected.The execution plan still shows LOAD AS SELECT, and the outline contains LOAD_METHOD(... HIGH_WATER_MARK).
This means that NO_MULTI_APPEND does not disable Direct Path Insert. The LOAD_METHOD and LOAD_TYPE values are the same as in the previous APPEND tests.
Now try to query EMP2 without committing.
SQL> select count(*) from emp2;
select count(*) from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/This time, ORA-12838 occurs, which is the same behavior seen in Oracle 19c.
Try another Direct Path Insert without committing.
SQL> insert /*+ append no_multi_append */ into emp2 select * from emp2;
insert /*+ append no_multi_append */ into emp2 select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/Again, ORA-12838 occurs.
Therefore, when APPEND and NO_MULTI_APPEND are used together in Oracle 26ai, the Direct Path Insert itself still occurs, but the same table cannot be queried or modified again within the same transaction.
This is effectively the same transaction restriction seen in Oracle 19c.
Rollback the test data.
SQL> rollback;
Rollback complete.7. Compare with NOAPPEND
Next, compare NO_MULTI_APPEND with NOAPPEND.
First, perform the insert using only the NOAPPEND hint.
SQL> insert /*+ noappend */ into emp2 select * from emp2;Check the execution plan before committing.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 2941272003
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:00:00.01 | 37 |
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | 1 | 0 |00:00:00.01 | 37 |
| 2 | TABLE ACCESS FULL | EMP2 | 1 | 14 |00:00:00.01 | 7 |
----------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
32 rows selected.This execution plan shows LOAD TABLE CONVENTIONAL.
The outline also shows:
LOAD_METHOD(... CONVENTIONAL)This confirms that NOAPPEND disables Direct Path Insert and uses a conventional insert instead.
In this case, the table can be queried without committing.
SQL> select count(*) from emp2;
COUNT(*)
----------
28Another insert also works normally.
SQL> insert into emp2 select * from emp2;
28 rows created.Rollback the test data.
SQL> rollback;
Rollback complete.8. Compare the Results
The behavior observed in the tests can be summarized as follows:
| Hint | Insert Method | Access Same Table Before COMMIT | Result |
|---|---|---|---|
APPEND | Direct Path Insert | Yes | Works |
APPEND MULTI_APPEND | Direct Path Insert | Yes | Works |
APPEND NO_MULTI_APPEND | Direct Path Insert | No | ORA-12838 |
NOAPPEND | Conventional Insert | Yes | Works |
NOAPPEND
- Opposite of the
APPENDhint - Does not use Direct Path Insert
- Uses a conventional insert
- The same table can be queried within the same transaction
NO_MULTI_APPEND
- Can be used together with
APPEND - Direct Path Insert is still used
- The execution plan shows
LOAD AS SELECT - Querying or modifying the same table after the Direct Path Insert results in
ORA-12838
MULTI_APPEND
- Can be used together with
APPEND - Direct Path Insert is still used
- The execution plan shows
LOAD AS SELECT - The same table can be queried or modified again after the Direct Path Insert without an error
- This is also the default behavior in Oracle 26ai when only
APPENDis specified
Conclusion
In Oracle 26ai, after performing a Direct Path Insert with the APPEND hint, the same table can be queried or another Direct Path Insert can be performed without ending the transaction.
Using APPEND alone and explicitly specifying APPEND + MULTI_APPEND produced the same behavior in this test.
On the other hand, when APPEND + NO_MULTI_APPEND was used, the Direct Path Insert itself still occurred, but accessing the same table afterward resulted in ORA-12838, just as it would in Oracle 19c.
The important point is that NO_MULTI_APPEND does not change an APPEND insert into a conventional insert.
Instead, it appears to restore the transaction restriction that was removed for Direct Path Inserts in 26ai.
In that sense, NO_MULTI_APPEND can be thought of as a hint-level way to get behavior similar to disabling the newer online direct load behavior controlled by the _online_direct_load hidden parameter.