ablog

不器用で落着きのない技術者のメモ

V$SQL_PLAN に問い合わせると ORA-03113、ORA-07445 が発生する場合の対処法

Oracle

実行中のSQLの実行計画を見ようと思ったら、

SQL> conn / as sysdba
SQL> @show_proc_ses_lock.sql
OSPID  BLOCK    SID SERIAL# SQL_ID        USERNAME   STATUS   TY LMODE REQUEST        ID1        ID2 LOCK_TIME  HASH_VALUE ADDRESS          PLAN_HASH_VALUE SQL ...
----- ------ ------ ------- ------------- ---------- -------- -- ----- ------- ---------- ---------- ---------- ---------- ---------------- --------------- -------------------- ...
12867           238     216 f60k7vrfk16bp SCOTT      ACTIVE   PS     4       0          1         16    35.2    3709901173 000000044773AD48      2621466036 INSERT ...
...

SQL> select * from table(dbms_xplan.display('v$sql_plan',null,null,'PLAN_HASH_VALUE=2621466036'));

select * from table(dbms_xplan.display('v$sql_plan',null,null,'PLAN_HASH_VALUE=2621466036'))
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

ありゃ＞＜

アラートログを見てみると、

$ less /u01/app/oracle/admin/orcl/bdump/alert_orcl.log

...

Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_15238.trc:
ORA-07445: exception encountered: core dump [msqopnws()+1837] [SIGSEGV] [Address not mapped to object]
 [0x000000010] [] []

ORA-07445 が発生している。
ぐぐってみると、
Session terminates when querying v$sql_plan - ora-solutions.net - Martin Decker
こんなのが見つかったので試してみると、

SQL> alter session set "_cursor_plan_unparse_enabled"=false;

お、

SQL> select * from table(dbms_xplan.display('v$sql_plan',null,null,'PLAN_HASH_VALUE=2621466036'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- ...
Plan hash value: 2621466036

-------------------------------------------------------------------------------- ...
| Id  | Operation                                                    | Name      ...
-------------------------------------------------------------------------------- ...
|   0 | INSERT STATEMENT                                             |           ...
|   0 | INSERT STATEMENT                                             |           ...
|   0 | INSERT STATEMENT                                             |           ...
|   1 |  PX COORDINATOR                                              |           ...
|   1 |  PX COORDINATOR                                              |           ...
|   1 |  PX COORDINATOR                                              |           ...

...

いけた！


追記(2010/08/04):
その他の対応案。

  • 共有プールをフラッシュする。
SQL> conn / as sysdba
SQL> alter system flush shared_pool;
  • statspack のスナップショット取得で発生する場合は、レベルを 5 以下にする。
SQL> conn perfstat/perfstat
SQL> exec statspack.modify_statspack_parameter (i_snap_level=>5)
  • SGA_TARGET を設定して自動共有メモリー管理を使っている場合、高負荷状態において共有プールに適切なサイズのメモリが割り当てられずこういう現象が起こるのではないかと思った。自動共有メモリー管理を使わずに、バッファキャッシュ、共有プールなどを固定で指定すると解決するのではないかとか妄想してみた。