Analyze an ORA-12801分析并行 parallel 12801 实际原因

"ORA-06512: at "PKG_P_DATA", line 19639
ORA-06512: at "PKG_P_DATA", line 19595
ORA-06512: at "PKG_P_DATA", line 14471-JOB 调用

-ORA-12801: error signaled in parallel query server P009, instance rac2:dwh2 (2)

 

Error: ORA-12801
------------------------------
ORA-12801: error signaled in parallel query server P02F, instance rac2:dwh2 (2)
ORA-12829: Deadlock - itls occupied by siblings at block 16320 of file 139

这个错误正常是不会显示的，不过可以在SQL monitor中见到。

This Document provides additional information to help DBAs and developers analyze ORA-12801 errors during parallel query execution. 

TROUBLESHOOTING STEPS

A parallel query has a query coordinator ( QC)  and parallel execution processes.

If a  parallel execution process fails and he can send the message back to the QC  

then the QC puts a ORA-12801 on top of the Oracle Error message.

In case the parallel execution process can not send a Error message back to the QC, 

as example when he crashed, then a ORA-12805 is reported.

Here is an example.

We use the emp table from the scott schema. The table emp has a column ename that is 

a character field.

In  serial the query

SQL> select  *   from emp e where  to_number(ename) =10;

fails with

Error  in Line 1:

ORA-01722: invalid number

We force now a parallel run of the query and we get

SQL> select /*+ parallel(e) */  * from emp e where to_number(ename) =10;

fails with

Error in Line 1:

ORA-12801: error signaled in parallel query server P001

ORA-01722: invalid number

We see the ORA-12801 message on top of the real problem the ORA-01772.

The ORA-12801 contains the information that the parallel execution process P001 threw the

ORA-01722.

The ORA-01722 needs to be investigated.

In general the error message under the ORA-12801 are showing the real problem and the ORA-12801

can be ignored.

For some failures the parallel slave writes a trace files.
In 10.2 and previous versions of the database, the trace files will created in background_dump_dest
The following command can be used to show the location of the traces files
 

show parameter background_dump_dest

In 11.1 and newer version of the database the traces are in one folder.
You can use the adrci tool to get an overview over the last creates trace files.You can use the following command:
 

adrci> show tracefile -t 

In some case there is only a ORA-12801 error message.

Then the event 10397 should be set

  

ALTER SYSTEM SET EVENTS '10397 trace name context forever, level 1';

Levels:

  Any level prevents ORA-12801 being added to the top of the error stack.

Description:

  When set this event disables the ORA-12801 message when a parallel

  query server gets a SQL error. Instead the slave's actual error

  is reported as if signaled in the foreground.

  This can be useful if the front end tool needs the slave error at

  the top of the error stack. Eg: For tools which only report the

  top error to the user.

Sql*plus script ends abnormally with the following errors:
 
ORA-20001: INACTIVE_ITEMS - ORA-20001: <item name> -
ORA-20002: -12801 ORA-12801: error signaled in parallel query server P037
ORA-12829: Deadlock - itls occupied by siblings at block 166708 of file 45
Error in PROCEDURE <procedure name> line 173

Details of error:
Error: ORA 12829
Text: Deadlock - itls occupied by siblings at block %s of file %s
---------------------------------------------------------------------------
Cause: parallel statement failed because all itls in the current block are occupied by siblings of the same transaction.
Action: increase MAXTRANS of the block or reduce the degree of parallelism for the statement. Reexecute the statement.
Report suspicious events in trace file to Oracle support representative if error persists.