主页 / dba / 问题 / 119249
Accepted
Robotron
Asked: 2015-10-27 14:11:51 +0800 CST

使用 autotrace 附加提示

  • 772

我正在执行成功执行的直接路径插入。当我尝试自动跟踪执行时，我得到了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？
oracle oracle-12c

1 个回答

  1. Best Answer

    对原始输出的回答：

    这不是由自动跟踪引起的。即使没有自动跟踪，您也会遇到同样的错误，只需重复相同的直接路径插入即可。在第一次插入后发出提交。

    这是记录在案的限制：ORA-12700 到 ORA-19400

    ORA-12838: 并行修改后无法读取/修改对象

    原因：在同一个事务中，试图在一个表被并行或直接加载修改后添加读取或修改语句。这是不允许的。

    操作：重写事务，或将其分成两个事务：一个包含初始修改，第二个包含并行修改操作。

    编辑后对新输出的回答：

    好的，所以你有一个回滚，你开始了一个新的事务。在这种情况下，autotrace 确实是间接的罪魁祸首。上面的规则仍然适用，正如上面的描述所说，即使是读取引用也会导致上面的错误。如果启用了自动跟踪，它会隐式运行如下内容（在您的原始语句完成后）：

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

    您可以通过为错误 12838 启用 errorstack 跟踪来轻松确认这一点（对于这个简短的演示，在沙箱环境中没问题，但这不是我在真实数据库上执行此操作的方式）：

    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
    • 3

相关问题