1.10046 事件的两种玩法。
使用10046事件是在Oracle数据库中查看目标SQL的执行计划的另外一种方法。10046事件的
特别之处在于显示执行步骤消耗的物理读,逻辑读和花费的时间。整个对于诊断复杂
SQL的性能时非常重要,这是其他方法没有的。
当然使用 GATHER_PLAN_STATISTICS(HINT)+DBMS_XPLAN 包也可以达到10046的效果。
2.使用10046事件方法一
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> select t1.id,t1.name from test.test01 t1,test.test03 t2 where t1.id=t2.id;
ID NAME
---------- ------------------------------
1 1xsq1
1 1xsq1
SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc
SQL> oradebug event 10046 trace name context off
Statement processed.
3.使用tkprof翻译trace文件
tkprof /oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc /home/oracle/fermdg_ora_3141.trc
more /home/oracle/fermdg_ora_3141.trc
TKPROF: Release 11.2.0.4.0 - Development on Sat Feb 3 16:35:19 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 9y3dt1pbmwdb1 Plan Hash: 3157290088
select t1.id,t1.name
from
test.test01 t1,test.test03 t2 where t1.id=t2.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 16 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 16 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2 2 2 NESTED LOOPS (cr=16 pr=0 pw=0 time=357 us cost=9 size=48 card=2)
2 2 2 NESTED LOOPS (cr=14 pr=0 pw=0 time=370 us cost=9 size=48 card=2)
2 2 2 TABLE ACCESS FULL TEST03 (cr=7 pr=0 pw=0 time=214 us cost=3 size=6 card=2)
2 2 2 INDEX RANGE SCAN IDX_ID (cr=7 pr=0 pw=0 time=147 us cost=2 size=0 card=1)(object id 87368)
2 2 2 TABLE ACCESS BY INDEX ROWID TEST01 (cr=2 pr=0 pw=0 time=52 us cost=3 size=21 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 12.39 12.39
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 16 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 16 0 2
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 30.10 59.04
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
********************************************************************************
Trace file: /oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
84 lines in trace file.
0 elapsed seconds in trace file.
cr:逻辑读,内存查找数据。
pr:物理读,数据文件读取数据。
time:耗费时间
card:实际返回结果集的行数。
pw:物理写
同时也可以看到执行计划。
10046使用方法二
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit --不限制trace文件大小。
Statement processed.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> select t1.id,t2.name from test.test01 t1,test.test03 t2 where t1.id=t2.id;
ID NAME
---------- --------------------
1 xsq1
1 xsq1
SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3473.trc
SQL> alter session set events '10046 trace name context off';
Session altered.
4.总结
10046事件可以查看如下内容:
cr:逻辑读,内存查找数据。
pr:物理读,数据文件读取数据。
time:耗费时间
card:实际返回结果集的行数。
pw:物理写
同时也可以看到执行计划,对象ID,解析调用执行情况,SQL_ID,SQL_HASH_VALUE,SQL_TEXT;