待機イベントが含まれている10046診断イベントの実行ーOracle11g

Oracle 11gで待機イベントが含まれている10046診断イベントの実行方法を整えてみました。

1. 伝統的な10046診断イベントの使い方は次のとおりです。レベル８以上なら待機イベントが記録されます。

alter session set events '10046 trace name context forever, level 8';

select /* 10046 */ count(*) from user_objects;

alter session set events '10046 trace name context off';

2. 11gからは次のようにsql_traceという「認識できる」名前の診断イベントが使えます。

-- sql_trace + level 8
alter session set events 'sql_trace level 8';

select /* sql_trace_1 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true
alter session set events 'sql_trace wait=true';

select /* sql_trace_2 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true, bind=true
alter session set events 'sql_trace wait=true, bind=true';

select /* sql_trace_3 */ count(*) from user_objects;

alter session set events 'sql_trace off';

11gから追加された新しい診断イベント機能は強すぎて、次のように特定のSQLを特定することもできます。あまりに有効な機能であるんでしょう。

-- SQL_IDを得て...
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;

col sql_id new_value sql_id1
select sql_id 
from v$sqlarea 
where sql_text = 'select /* sql_trace_4 */ count(*) from user_objects';

col sql_id new_value sql_id2
select sql_id 
from v$sqlarea 
where sql_text = 'select /* sql_trace_5 */ count(*) from user_objects';

-- 1つのSQLに対して
alter session set events 'sql_trace [sql: &sql_id1] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';

-- 複数のSQLに対して
alter session set events 'sql_trace [sql: &sql_id1 | &sql_id2] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';

3. DBMS_MONITORパッケージこそオラクルの公式的なお勧めの方法です。DBMS_MONITORパッケージもSQLを特定する機能が提供したらいいけど、開発者等がまだそこまでは考えていないようです。

exec dbms_monitor.session_trace_enable(waits=>true);

select /* dbms_monitor */ count(*) from user_objects;

exec dbms_monitor.session_trace_disable;

4. DBMS_SYSTEMパッケージやDBMS_SUPPORTパッケージなどの隠しパッケージも同様な機能を提供しています。でも、DBMS_MONITORパッケージが登場した以上必要がなくなったといえます。

col sid new_value sid
col serial# new_value se
select sid, serial# 
from v$session 
where sid = userenv('sid');

exec sys.dbms_system.set_ev(&sid, &se, 10046, 8, null);

select /* dbms_system */ count(*) from user_objects;

exec sys.dbms_system.set_ev(&sid, &se, 10046, 0, null);

SQL_ID値を利用して特定SQLに対してのみ診断イベントを行なう機能が特に有効に見えます。Oracle 11gの拡張された診断イベント機能は次の文書で詳細に紹介しています。

• ORADEBUG DOC