10002事件
用途：跟踪DDL SQL，分析DDL语句数据库内部执行的过程
##开启trace
alter session set events ‘10002 trace name context forever,level 1’;
##创建测试表
create table test1 (id int,name varchar2(10));
##trace文件路径
select para_name,para_value from vdm_ini where para_name like '%TRACE_PATH%'; ##查看trace文件，贴出部分内容 [dmdba@DM8-SINGLE-101 trace] pwd
/home/dmdba/DAMENG/trace
[dmdba@DM8-SINGLE-101 trace]$ ls -lrth
total 4.0K
-rw-r–r-- 1 dmdba dinstall 3.0K Mar 10 22:23 DMSERVER_0310_2223_139620664028904.trc

DM Database Server x64 V8[1-3-162-2024.07.03-234060-20108-ENT ], Jul 12 2024 05:48:03 built.

*** 2025-03-10 22:23:53.203000000
*** Start trace 10002 event [SESSION ID:139620664028904] ***
[DDL SQL]:
create table test1 (id int,name varchar2(10));
中间略…
INSERT INTO SYS.SYSOBJINFOS(ID, TYPE$, INT_VALUE, STR_VALUE) VALUES(1027, ‘ALTTIME’, 0, TO_CHAR(SYSDATE(),‘YYYY-MM-DD HH24:MI:SS’));
SF_REMOVE_DICT_OBJ(150994945, 1027, ‘SCHOBJ’, ‘UTAB’, 1);
}
SP_DCT_TAB_DDL_REG(1027, 1, 1);
SF_LOCK_APPLY_ID();

SP_RESTORE_DDLCOMMIT_FLAG(V_DC_FLAG_LEVEL);

}
catch (exception ex){

SP_RESTORE_DDLCOMMIT_FLAG(V_DC_FLAG_LEVEL);

SF_REMOVE_DICT_OBJ(150994945, 1027, ‘SCHOBJ’, ‘UTAB’, 1);
SF_SET_GEN_INS_UREC_FLAG(1);

if (ISO_LEVEL != -1)
SF_SET_TRX_ISO_LEVEL(ISO_LEVEL);
throw;
}
}
SF_LOCK_APPLY_ID();
COMMIT;
}
}

-------------------------- END --------------------------
##关闭10002事件
alter session set events ‘10002 trace name context forever,level 1’;
10053事件
用途：提供的一种供用户查看内部优化实现细节的途径，借此观察并了解错误计划的原因。
SQL> alter session set events ‘10053 trace name context forever,level 1’;
操作已执行
已用时间: 2.346(毫秒). 执行号:1147.
SQL> explain select t1.*,t2.name from test1 t1,test2 t2 where t1.id=t2.id;

1 #NSET2: [1, 16, 104]
2 #PRJT2: [1, 16, 104]; exp_num(3), is_atom(FALSE)
3 #HASH2 INNER JOIN: [1, 16, 104]; KEY_NUM(1); KEY(T1.ID=T2.ID) KEY_NULL_EQU(0)
4 #CSCN2: [1, 4, 52]; INDEX33555481(TEST1 as T1); btr_scan(1)
5 #CSCN2: [1, 4, 52]; INDEX33555482(TEST2 as T2); btr_scan(1)

已用时间: 0.789(毫秒). 执行号:0.
SQL> alter session set events ‘10053 trace name context off’;
操作已执行
已用时间: 0.735(毫秒). 执行号:1148.
##文件部分内容
less DMSERVER_0310_2250_139621071011848.trc
*** cross join optimization start…

---------------- [Join Order 1 of 2] ----------------
{ “T1” }{ “T2” }

<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: T1.ID = T2.ID —> st: 1.00000
stat_info(1027,0,‘C’)= {
#Valid = ‘N’,
#Type = ‘-’,
#Card = 4,
#NDV = 1,
#Nulls = 0,
#LP = 9000,
#LVLS = 3,
#CLUF = 0,
#NK = 0,
#NS = 0}
stat_info(1028,0,‘C’)= {
#Valid = ‘N’,
#Type = ‘-’,
#Card = 4,
#NDV = 1,
#Nulls = 0,
#LP = 9000,
#LVLS = 3,
#CLUF = 0,
#NK = 0,
#NS = 0}

l_card: 4, r_card: 4, estimate join card: 16(X), st: 1.00000;
— st_left = 1.000, st_right = 1.000, st_other = 1.000, n_stdesc = 1

---------------- single table access path probe for T2 ----------------
*** path 1: INDEX33555482 (FULL search), cost: 0.04497

best access path: INDEX33555482 (FULL search), cost: 0.04497

---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 6.58703
*** index inner join is not available
*** hash inner join cost: 0.94368
*** merge inner join is not available

current best plan(1, 1), cost: 0.94368
hash inner join[0x7efc105ea910] (cost: 0.94368, rows: 16)
base table[0x7efc105eaf38] (T1, INDEX33555481, FULL SEARCH) (cost: 0.04497, rows: 4)
base table[0x7efc105eb560] (T2, INDEX33555482, FULL SEARCH) (cost: 0.04497, rows: 4)

---------------- [Join Order 2 of 2] ----------------
{ “T2” }{ “T1” }

---------------- single table access path probe for T1 ----------------
*** path 1: INDEX33555481 (FULL search), cost: 0.04497

best access path: INDEX33555481 (FULL search), cost: 0.04497

---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join is not available
*** index inner join is not available
*** hash inner join cost: 0.94368
*** merge inner join is not available

no better plan found

*** BEST PLAN FOR THIS STATEMENT ***
project[0x7efc105b6578] n_exp(3) (cost: 0.94368, rows: 16)
hash inner join[0x7efc105b70f0] (cost: 0.94368, rows: 16)
base table[0x7efc105b7aa8] (T1, INDEX33555481, FULL SEARCH) (cost: 0.04497, rows: 4)
base table[0x7efc105b80e0] (T2, INDEX33555482, FULL SEARCH) (cost: 0.04497, rows: 4)

-------------------------- END --------------------------