使用自動跟踪附加提示

我正在執行成功執行的直接路徑插入。當我嘗試自動跟踪執行時，我得到ORA-12838: cannot read/modify an object after modifying it in parallel.

SQL> insert into emp_big select * from emp_big_temp;

411843 rows created.

Elapsed: 00:00:00.89

Execution Plan
----------------------------------------------------------
Plan hash value: 3203427748

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |   357K|    45M|  1164   (1)| 00:00:14 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP_BIG      |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | EMP_BIG_TEMP |   357K|    45M|  1164   (1)| 00:00:14 |
-----------------------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        15  recursive calls
     31909  db block gets
     12829  consistent gets
         0  physical reads
  34685216  redo size
       845  bytes sent via SQL*Net to client
       802  bytes received via SQL*Net from client
         3  SQL*Net roundtrips to/from client
         1  sorts (memory)
         0  sorts (disk)
    411843  rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.18
SQL>
SQL> insert /*+ append */ into emp_big select * from emp_big_temp;

411843 rows created.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
         0  recursive calls
      4438  db block gets
      4283  consistent gets
         0  physical reads
      7648  redo size
       829  bytes sent via SQL*Net to client
       816  bytes received via SQL*Net from client
         3  SQL*Net roundtrips to/from client
         1  sorts (memory)
         0  sorts (disk)
    411843  rows processed

問題：

1. 根據 ，表emp_big的並行度設置為 1 select table_name, degree from user_tables。為什麼Oracle強制並行插入？
2. 為什麼使用autotrace trace原因ORA-12838？

對原始輸出的回答：

這不是由自動跟踪引起的。即使沒有自動跟踪，您也會得到相同的錯誤，只需重複相同的直接路徑插入即可。在第一次插入後發出送出。

這是一個記錄在案的限制：ORA-12700 到 ORA-19400

ORA-12838: 並行修改對像後無法讀取/修改對象

原因：在同一個事務中，在並行****或直接載入修改表後，嘗試在表上添加讀取或修改語句。這是不允許的。

行動：重寫事務，或將其分解為兩個事務：一個包含初始修改，第二個包含並行修改操作。

編輯後對新輸出的回答：

好的，所以你有一個回滾，你開始了一個新的事務。在這種情況下，自動跟踪確實是間接的罪魁禍首。上面的規則仍然適用，正如上面的描述所說，即使是讀取引用也會導致上述錯誤。如果啟用了自動跟踪，它會隱式執行類似這樣的內容（在您的原始語句完成後）：

EXPLAIN PLAN SET STATEMENT_ID='PLUSxxxxxx' FOR insert /*+ append */ into emp_big select * from emp_big_temp;

您可以通過啟用錯誤 12838 的錯誤堆棧跟踪來輕鬆確認這一點（對於這個簡短的展示，在沙盒環境中很好，但這不是我在真實數據庫上執行此操作的方式）：

alter system set events '12838 trace name errorstack level 3';

如果我現在執行它：

SQL> create table t1 as select * from dba_objects where 1=2;

Table created.

SQL> alter system set events '12838 trace name errorstack level 3';

System altered.

SQL> set autotrace on
SQL> insert /*+ append */ into t1 select * from dba_objects;

20079 rows created.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

我將在警報日誌中找到錯誤和跟踪文件。跟踪文件包含以下內容：

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-12838: cannot read/modify an object after modifying it in parallel
----- Current SQL Statement for this session (sql_id=5x12x8czsd3t9) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS730007' FOR insert /*+ append */ into t1 select * from dba_objects

這就是導致錯誤的原因，因為它仍然是同一個事務。即使沒有自動跟踪，您也可以對其進行測試：

SQL> rollback;

Rollback complete.

SQL> set autotrace off
SQL> insert /*+ append */ into t1 select * from dba_objects;

20079 rows created.

SQL> explain plan for insert /*+ append */ into t1 select * from dba_objects;
explain plan for insert /*+ append */ into t1 select * from dba_objects
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> select count(*) from t1;
select count(*) from t1
                    *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

引用自：https://dba.stackexchange.com/questions/119249