Tuesday, March 23, 2010

SQLでOSの資源を操作したい時 - Java Stored Procedureの活用

SQLとPL/SQLの多様な機能に慣れてみればOracleがインストールされているOSシステムの資源をSQLやPL/SQLで制御したくなります。SQLとPL/SQLの活用を極大化したい自然な願いだと思います。Java Stored Procedureがこのような要求事項を解決するに一番強力なツールと言えます。


実際に私が開発しているトラブルシューチングパックというライブラリーも内部的にJava Stored Procedureを幅広く活用しています。


簡単な例を通じて説明してみます。次のような要求事項があります。


  • オラクルが設置されたシステムの特定のフォルダーのファイルリストをSQL分を通じて得たいです。

次のように解決できます。

UKJA@ukja1106> connect sys/oracle@ukja1106 as sysdba
Connected.

Session altered.

Elapsed: 00:00:00.00

SID SERIAL# PID
---------- ---------- ----------
129 12732 12376

Elapsed: 00:00:00.00
SYS@ukja1106>
SYS@ukja1106> exec dbms_java.grant_permission('UKJA', 'SYS:java.io.FilePermission', -
> 'c:\temp', 'read ,write, execute, delete');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SYS@ukja1106>
SYS@ukja1106> connect ukja/ukja@ukja1106
Connected.

Session altered.

Elapsed: 00:00:00.00

SID SERIAL# PID
---------- ---------- ----------
129 12734 10400

Elapsed: 00:00:00.00
UKJA@ukja1106>
UKJA@ukja1106> create or replace and compile java source named FileList
2 as
3 import java.io.*;
4 import java.util.*;
5 import java.sql.*;
6 import oracle.sql.*;
7
8 public class FileList {
9
10 public static oracle.sql.ARRAY getFileList() throws Exception {
11
12 File f = new File("c:\\temp");
13 String[] flist = f.list();
14
15 Connection conn = DriverManager.getConnection("jdbc:default:connection:");
16
17 ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", conn);
18 ARRAY array = new ARRAY(desc, conn, flist);
19 return array;
20 }
21
22 }
23 ;
24 /

Java created.

Elapsed: 00:00:00.18
UKJA@ukja1106>
UKJA@ukja1106> create or replace type varchar2_array as table of varchar2(1000);
2 /

Type created.

Elapsed: 00:00:00.01
UKJA@ukja1106>
UKJA@ukja1106>
UKJA@ukja1106> create or replace function get_file_list
2 return varchar2_array
3 as language java
4 name 'FileList.getFileList() return oracle.sql.ARRAY';
5 /

Function created.

Elapsed: 00:00:00.00
UKJA@ukja1106>
UKJA@ukja1106> select * from table(get_file_list);

COLUMN_VALUE
--------------------------------------------------------------------------------
1269319491031.sql
1269319491031.txt
20100323.log
343225818.out
343225818.trc

Elapsed: 00:00:00.03
UKJA@ukja1106>

本当に簡単で強いせす。活用によって多くの作業を自動化できるはずです。

Thursday, March 11, 2010

Oracle 11.1.0.6のトリガーバグ分析 - ティパックの活用

この頃トラブルシューティングパック(Troubleshooting Pack。以下ティパック)という名前のライブラリーを開発しています。オラクルが提供するトラブルシューティング機能(診断イベント、ダンプ、X$ビュー)を利用して普遍的ないくつかの性能問題について意味のあるレポートを提供する目的で企画されました。


次のアーティクルにOracle 11.1.0.6の面白いトリガー関係のバグが紹介されています。


このバグで現れる現象ががティパックを活用して問題を分析できる良い例に見えて簡単に紹介します。


まず上のアーティクルで説明している問題は次のようです。


UKJA@ukja1106> create table t1(c1 int);

Table created.

UKJA@ukja1106> create table t2(c1 int);

Table created.

次のように二つのトリガーを作ります。二つ目のトリガーをどんな意味もないトリガーだけれど開発規則によって追加されました。何もしないトリガーなので性能に与える影響はほとんどないはずです。

UKJA@ukja1106> create or replace trigger t1_trg1 after insert on t1 for each row
2 begin
3 insert into t2 values(:new.c1);
4 end;
5 /

Trigger created.

UKJA@ukja1106>
UKJA@ukja1106> create or replace trigger t1_trg2 after insert on t2 for each row
2 begin
3 declare
4 numrows number;
5 begin
6 numrows := 1;
7 end;
8 end;
9 /

Trigger created.

どころが、Oracle 11.1.0.6で次のように繰り返しにテーブルT1に対してInsertを行うとだんだんその性能が遅れていきます。

UKJA@ukja1106> declare
2 v_start_time number := dbms_utility.get_time;
3 begin
4 for idx in 1 .. 50000 loop
5 insert into t1 values(idx);
6
7 if mod(idx, 1000) = 0 then
8 dbms_output.put_line(idx || 'th exe = ' || (dbms_utility.get_time - v_start_time)/100);
9 v_start_time := dbms_utility.get_time;
10 end if;
11 end loop;
12 end;
13 /
1000th exe = .14
2000th exe = .17
3000th exe = .16
4000th exe = .17
5000th exe = .15
6000th exe = .19
7000th exe = .16
8000th exe = .2
9000th exe = .19
10000th exe = .2
11000th exe = .21
12000th exe = .23
13000th exe = .22
14000th exe = .23
15000th exe = .25
16000th exe = .24
17000th exe = .23
18000th exe = .27
19000th exe = .28
20000th exe = .26
21000th exe = .27
22000th exe = .28
23000th exe = .3
24000th exe = .31
25000th exe = .3
26000th exe = .34
27000th exe = .31
28000th exe = .35
29000th exe = .36
30000th exe = .43
31000th exe = .52
32000th exe = .45
33000th exe = .49
34000th exe = .43
35000th exe = .68
36000th exe = .64
37000th exe = .89
38000th exe = .53
39000th exe = .44
40000th exe = .47
41000th exe = .48
42000th exe = .52
43000th exe = .56
44000th exe = .59
45000th exe = .64
46000th exe = .69
47000th exe = .7
48000th exe = .77
49000th exe = .78
50000th exe = .84

PL/SQL procedure successfully completed.

Oracle 10gR2やOracle 11.1.0.7ではこのような現象は発生しません。Oracle 11.1.0.6のバグに見えます。


上のアーティクルでは次のような二つの現象を報告しています。


  1. 実行回数が増えるほどCPU使用時間が増えていく(SQL*Trace)
  2. 実行回数が増えるほどPGA使用量が増えていく(V$SESSTAT)

上の現象から見るとたぶんわけのわからない理由のためメモリーを使いすぎて、その過程でCPU使用時間も増えるように見えます。


この問題をティパック(Troubleshooting Pack)を通じて分析してみます。


  • Session Snapshot機能を通じてどんな性能指標がおもに増加するか観察する。
  • PGA Heap Dump分析を通じてPGAのサイズが増加する原因を分析します。
  • Call Stack Traceの分析を通じてどんな係数をおもに呼び出しながらCPUを使用するか分析する。

下にその結果があります。


Session Snapshotで得た結果はアーティクルの結果と一致します。500,000件のInsertが終わったあとPGAのサイズが1.6Mから45Mまで増加したのがわかります。


01. statistics

NAME DIFF VALUE1 VALUE2
----------------------------------- -------------- -------------- --------------
session pga memory 45,285,376 1,663,952 46,949,328
session pga memory max 45,154,304 1,795,024 46,949,328
session uga memory 45,104,696 891,300 45,995,996
session uga memory max 44,973,768 1,022,228 45,995,996
...

PGAのサイズが増加した原因を分析するためにPGAレポートを生成します。

UKJA@ukja1106> select * from table(tpack.get_pga_report);

...
02. Size per Chunk Type

Heap Name Chunk Type Count Chunk Size Heap Size Ratio(%)
-------------------- ---------- ---------- ---------- ---------- ----------
...
kxs-heap-p perm 1 0 20.9 0
kxs-heap-p freeable 250005 20.9 20.9 99.6
pesom.c:Proces free 2 0 0 68.3
...


03. Size Per Object Type
Heap Name Obj Type Count Chunk Si Heap Siz Ratio
-------------------- -------------------- ---------- -------- -------- -----
...
kxs-heap-p kxt.c: PL/SQL p 150000 16.3 20.9 77.7
kxs-heap-p kxt.c: Trigger 100000 4.5 20.9 21.8
kxs-heap-p none 3334 0 20.9 .3
kxs-heap-p kxtcin:kxscplx 1 0 20.9 0
...


Kxs-heap-pヒープのfreeable Chunk(再活用可能なChunk)の数が250,005個に達して、20M程度のサイズを占めています。メモリーりーくが疑われます。KXSはKernel Execution Shared Cursorの略語です。Shared Cursorと関連したメモリー問題の可能性があります。PGA内のShared Cursorの領域はSESSION_CACHED_CURSORパラメータで制御されます。


Ksx-heap-pヒープのkxt.c: PL/SQL p、kxt.c: Triggerオブジェクトの数が250,000個です。この値はkxs-heap-pヒープのfreeable Chunkの数とほとんど一致しています。KXTはKernel Execution Triggerの略語です。


情報を総合してみると、トリガー実行関係のオブジェクトがShared Cursor領域で再活用されないまま繰り返しに追加され、メモリーとCPUの使用量が一緒に増加していると仮定できます。


Call Stack Trace分析を通じて実際にどんなコードがおもに呼び出されているのか確認してみます。


UKJA@ukja1106> select * from table(tpack.stacktrace_profile_report(&1,5,0.1));
old 1: select * from table(tpack.stacktrace_profile_report(&1,5,0.1))
new 1: select * from table(tpack.stacktrace_profile_report(134,5,0.1))


STACK_TRACE
--------------------------------------------------------------------------------
HIT_CNT HIT_PCT
---------- ----------
->00000000->7C80B710->_OracleThreadStart@4()+764->_opimai()+92->_opimai_real()+1
30->_sou2o()+45->_opidrv()+807->_opiodr()+1224->_opiino()+1067->_opitsk()+1278->
_ttcpip()+2733->_opiodr()+1224->_kpoal8()+2299->_opiexe()+14438->_kkxexe()+1046-
>_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->_pfrinstr
_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()+1357->_r
piswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1312->_opi
exe()+5344->_insexe()+1027->_qerltcFetch()+896->_qerltcInsertValuesRop()+243->_q
erltcLoadStateMachine()+609->__PGOSF492__qerltcNoKdtBufferedInsRowCBK()+336->__V
Infreq__qesltcAfterRowProcessing()+238->_qesltcExecuteAfterRowTriggers()+1299->_
kxtexe()+483->_rpiswu2()+560->_kxtex1()+478->_kkxtexe()+901->_peiet_execute_trig
ger()+47->_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->
_pfrinstr_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()
+1357->_rpiswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1
312->_opiexe()+1823->_kksfbc()+12485->_kxtInstantiateAllTriggers()+77->_kxtCreat
eTriggerInst()+250->_kxsPersistentHeapAllocFree()+42->_kghalf()+188->00000000->7
C80B710->_OracleOradebugThreadStart@4()+825->_ssthreadsrgruncallback()+432->_ksd
xcb()+1780->_ksdxfdmp()+850->_ksedst_tracecb()+53->_ksedst1()+91->_skdstdst()+11
4
8 16

->00000000->7C80B710->_OracleThreadStart@4()+764->_opimai()+92->_opimai_real()+1
30->_sou2o()+45->_opidrv()+807->_opiodr()+1224->_opiino()+1067->_opitsk()+1278->
_ttcpip()+2733->_opiodr()+1224->_kpoal8()+2299->_opiexe()+14438->_kkxexe()+1046-
>_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->_pfrinstr
_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()+1357->_r
piswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1312->_opi
exe()+1823->_kksfbc()+12485->_kxtInstantiateAllTriggers()+77->_kxtCreateTriggerI
nst()+250->_kxsPersistentHeapAllocFree()+42->_kghalf()+188->00000000->7C80B710->
_OracleOradebugThreadStart@4()+825->_ssthreadsrgruncallback()+432->_ksdxcb()+178
0->_ksdxfdmp()+850->_ksedst_tracecb()+53->_ksedst1()+91->_skdstdst()+114
3 6

->00000000->7C80B710->_OracleThreadStart@4()+764->_opimai()+92->_opimai_real()+1
30->_sou2o()+45->_opidrv()+807->_opiodr()+1224->_opiino()+1067->_opitsk()+1278->
_ttcpip()+2733->_opiodr()+1224->_kpoal8()+2299->_opiexe()+14438->_kkxexe()+1046-
>_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->_pfrinstr
_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()+1357->_r
piswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1312->_opi
exe()+5344->_insexe()+1027->_qerltcFetch()+896->_qerltcInsertValuesRop()+243->_q
erltcLoadStateMachine()+609->__PGOSF492__qerltcNoKdtBufferedInsRowCBK()+336->__V
Infreq__qesltcAfterRowProcessing()+238->_qesltcExecuteAfterRowTriggers()+1299->_
kxtexe()+483->_rpiswu2()+560->_kxtex1()+478->_kkxtexe()+901->_peiet_execute_trig
ger()+47->_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->
_pfrinstr_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()
+1357->_rpiswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1
312->_opiexe()+1823->_kksfbc()+12485->_kxtInstantiateAllTriggers()+77->_kxtCreat
eTriggerInst()+304->_kxsPersistentHeapAllocFree()+42->_kghalf()+188->00000000->7
C80B710->_OracleOradebugThreadStart@4()+825->_ssthreadsrgruncallback()+432->_ksd
xcb()+1780->_ksdxfdmp()+850->_ksedst_tracecb()+53->_ksedst1()+91->_skdstdst()+11
4
3 6

...


Call Stack Traceを見ると下の呼びさしパターンが一番普遍的に発生していることがわかります。

_kxtInstantiateAllTriggers
_kxtCreateTriggerInst
_kxsPersistentHeapAllocFree

PGAレポートと完璧に一致する係数呼び出しパターンです。トリガーオブジェクトを続けて生成し、その過程でメモリーを続けて割り当てされています。


確かに、メモリー関係のバグの可能性が高そうです。こんな情報があればメタリンクをもっと正確に検索できます。下にメタリンクの検索結果があります。




バグ番号6400175が見えるでしょう。ほとんど完璧に一致する現象を報告しています。


オラクルが提供するいくつかの基本的な性能データを通じてオラクルでの性能異常現象を体系的に分析できるということを見せてくれる良い例だと思われます。


PS) 上の例で紹介されたティパックの機能は次のようなデータを利用しています。このデータたちを直接検索し、分析できると誰でも同一な分析が可能です。


  • V$SESSTAT
  • PGA Heap Dump (oradebug dump heapdump 0x20000001)
  • Call Stack (oradebug dump callstack 1)

Thursday, March 4, 2010

Errorstackダンプで問題のSQLを突き止めること

オラクルでエラーが発生したとき、どのSQL文が問題なのかを突き止める必要があります。例えば、alert.logファイルに次のようなエラーメッセージが記録されています。

Fri Mar 05 09:47:53 2010
ORA-1652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS

どんなSQL文が犯人なのかを知らなければ、解決は難しいでしょ。


このような場合でやってみれるのがErrorStackダンプです。ErrorStackダンプを診断イベントと一緒に使えばエラーを起こしたSQL文がトレースファイルに記録されるようにすることができます。


簡単な例で説明します。まず小さなサイズ(10m)のテーブルスペースを作ります。


UKJA@ukja1021> create tablespace very_small_tbs
2 datafile size 10m;

Tablespace created.

ORA-01652エラーが発生したら、ErrorStackダンプを実行するように診断イベントを掛けます。

UKJA@ukja1021> alter system set events '1652 trace name errorstack level 1, forever';

Session altered.

10mより大きいテーブルを作ればORA-01652エラーが発生します。

UKJA@ukja1021> create table tbig(c1)
2 tablespace very_small_tbs
3 as
4 select rpad('x',1000) from dual
5 connect by level <= 10000
6 ;
select rpad('x',1000) from dual
*
ERROR at line 4:
ORA-01652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS

UKJA@ukja1021> alter system set events '1652 trace name context off';

Session altered.

Alert.logファイルには次のようなエラーメッセージが残ります。

Fri Mar 05 09:47:53 2010
ORA-1652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS

プロセスのダンプファイルにはエラー発生時のSQL文とCallStackトレースが記録してあります。

ORA-01652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS
Current SQL statement for this session:
create table tbig(c1)
tablespace very_small_tbs
as
select rpad('x',1000) from dual
connect by level <= 10000
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedst+38 CALLrel _ksedst1+0 0 1
_ksedmp+898 CALLrel _ksedst+0 0
_ksddoa+2088 CALLreg 00000000 1
_ksdpcg+238 CALLrel _ksddoa+0 A9615C0 93C78C0
_ksdpec+230 CALLrel _ksdpcg+0 674 C04A478 1
__PGOSF89__ksfpec+1 CALLrel _ksdpec+0 674
18
_kgesev+88 CALLreg 00000000 A0C6760 674
_ksesec2+39 CALLrel _kgesev+0 A0C6760 93C0020 674 2 C04A4E4
_ktsxterr+316 CALLrel _ksesec2+0 674 0 80 0 1 E C04A55E
_ktfbtgex1+969 CALLrel _ktsxterr+0 792DE5C 80 0
_ktsxs_add+1766 CALLrel _ktfbtgex1+0 C04AD8C 3D C04AA50 80 18 A 3
0 0 C04AD50 37B3EE88
_ktsxssr_sadd+1409 CALLrel _ktsxs_add+0 C04B048 C04AD8C 80 A 3 0 18 1
C04B11C C04AE08 C04ADC0 0
C04AD50
_ktrsexec+372 CALL??? 00000000 C04B0D8
_ktelwbl+770 CALLrel _ktrsexec+0 C04B0D8
_kdblba+168 CALLrel _ktelwbl+0 792DE5C 1
_kdblGetBlockDba+58 CALLrel _kdblba+0
_kdblgb+26 CALLrel _kdblGetBlockDba+0 C04B3C8 792DD9C
_kdblailb+2101 CALLrel _kdblgb+0
_kdblai+1560 CALLrel _kdblailb+0 C04B3C8 792DC9C 792DD9C 0 1 1
_klclil1r+187 CALLrel _kdblai+0
_qerltRop+514 CALLrel _klclil1r+0 792DBEC
_qercbiFetch+935 CALLreg 00000000 34C4F034 7FFF
_rwsfcd+95 CALL??? 00000000 34C4F384 1C72EB4 34C4F034
7FFF
_qerltFetch+368 CALL??? 00000000 34C4F148 1C72EB4 34C4F034
7FFF
_ctcdrv+7674 CALL??? 00000000 34C4F034 1D28394 C04CE30 1
_opiexe+12257 CALLrel _ctcdrv+0 34EE5F50 C04D548 C04D510
_opiosq0+6088 CALLrel _opiexe+0 4 0 C04D8C0
_kpooprx+232 CALLrel _opiosq0+0 3 E C04D9D8 A4
_kpoal8+775 CALLrel _kpooprx+0 C04F6F8 C04E224 6D 1 0 A4
_opiodr+1099 CALLreg 00000000 5E 17 C04F6F4
_ttcpip+1273 CALLreg 00000000 5E 17 C04F6F4 0
_opitsk+1017 CALL??? 00000000
_opiino+1087 CALLrel _opitsk+0 0 0
_opiodr+1099 CALLreg 00000000 3C 4 C04FC8C
_opidrv+819 CALLrel _opiodr+0 3C 4 C04FC8C 0
_sou2o+45 CALLrel _opidrv+0 3C 4 C04FC8C
_opimai_real+112 CALLrel _sou2o+0 C04FC80 3C 4 C04FC8C
_opimai+92 CALLrel _opimai_real+0 2 C04FCB8
_OracleThreadStart@ CALLrel _opimai+0
4+708
7C80B710 CALLreg 00000000

ErrorStackダンプはそのレベルによっていろいろな有効な情報を提供してくれます。次のアーティクルで詳細な情報を得られます。

Monday, March 1, 2010

SQL文章レベルの診断イベント

次のアーティクルを見るとOracle 11gからSQL文章レベルで診断イベントを適用する機能が追加されたのが分かります。

すなわち、特定のSQL IDに該当するSQL文章に対して10046イベントや10053イベントのような診断イベントを適用できます。Oracle 10gやその以下のバージョンでは診断イベントの最低のレベルはセッションでありました。SQL文章レベルで診断イベントを適用できることになるにつれて、システムに与える影響を最小化しながら正確に特定のSQL文章だけを分析できるようになりました。


簡単な使用例は次のようです。


UKJA@ukja1106> select * from t1 where c1 = 1;

C1 C2
---------- ----------
1 1

UKJA@ukja1106>
UKJA@ukja1106> col prev_sql_id new_value sql_id
UKJA@ukja1106> select prev_sql_id from v$session where sid = userenv('sid');

PREV_SQL_ID
-------------
3c3yp27cag5mv

UKJA@ukja1106>
UKJA@ukja1106> select sql_text from v$sql where sql_id = '&sql_id';
old 1: select sql_text from v$sql where sql_id = '&sql_id'
new 1: select sql_text from v$sql where sql_id = '3c3yp27cag5mv'

SQL_TEXT
--------------------------------------------------------------------------------
select * from t1 where c1 = 1

UKJA@ukja1106>
UKJA@ukja1106> alter system flush shared_pool;

System altered.

UKJA@ukja1106> -- Enable 10046 Event
UKJA@ukja1106> alter system set events 'sql_trace[sql:&sql_id] level=12';
old 1: alter system set events 'sql_trace[sql:&sql_id] level=12'
new 1: alter system set events 'sql_trace[sql:3c3yp27cag5mv] level=12'

System altered.

UKJA@ukja1106> -- Enable 10053 Event
UKJA@ukja1106> alter system set events 'trace[sql_costing][sql:&sql_id]';
old 1: alter system set events 'trace[sql_costing][sql:&sql_id]'
new 1: alter system set events 'trace[sql_costing][sql:3c3yp27cag5mv]'

System altered.

UKJA@ukja1106>
UKJA@ukja1106> select * from t1 where c1 = 1;

C1 C2
---------- ----------
1 1

UKJA@ukja1106>
UKJA@ukja1106> select * from t1 where c1 = 2;

C1 C2
---------- ----------
2 2

UKJA@ukja1106> -- Disable 10046 Event
UKJA@ukja1106> alter system set events 'sql_trace[sql:&sql_id] off';
old 1: alter system set events 'sql_trace[sql:&sql_id] off'
new 1: alter system set events 'sql_trace[sql:3c3yp27cag5mv] off'

System altered.

UKJA@ukja1106> -- Disable 10053 Event
UKJA@ukja1106> alter system set events 'trace[sql_costing][sql:&sql_id] off';
old 1: alter system set events 'trace[sql_costing][sql:&sql_id] off'
new 1: alter system set events 'trace[sql_costing][sql:3c3yp27cag5mv] off'

System altered.

トレースファイルを見てみたら指定されたSQL IDに当たるSQL文章だけに対して診断イベントが適用されたことが分かります。

-- 10053 Event
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=3c3yp27cag5mv) -----
select * from t1 where c1 = 1
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
...

-- 10046 Event
PARSING IN CURSOR #6 len=29 dep=0 uid=88 oct=3 lid=88 tim=1619996238190 hv=3634861691 ad='355836ec' sqlid='3c3yp27cag5mv'
select * from t1 where c1 = 1
END OF STMT
EXEC #6:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1619996238186
WAIT #6: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=375 tim=1619996238266
FETCH #6:c=0,e=50,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1619996238382
WAIT #6: nam='SQL*Net message from client' ela= 258 driver id=1413697536 #bytes=1 p3=0 obj#=375 tim=1619996238690
FETCH #6:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=1619996238739
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=79656 op='TABLE ACCESS BY INDEX ROWID T1 (cr=4 pr=0 pw=0 time=0 us cost=2 size=7 card=1)'
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=79657 op='INDEX RANGE SCAN T1_N1 (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'

まだこの機能が公式的に文書化されてはいないようです。従って本番環境で使用する前に十分な検証をしなければなりません。Oracle 11.1.0.6のテスト環境ではこの機能のせいでセッションが非正常終了する場合もたまにありました。