查看 sql 执行计划的方法有许多种, 10046 事件就是其中的一种. 与其他查看 sql 执行计划不同, 当我们遇到比较复杂的 sql 语句, 我们可以通过 10046 跟踪 sql 得到执行计划中每一个步骤的逻辑读, 物理读以及花费的时间等. 这种细粒度的跟踪对于我们分析 sql 性能尤其有用.
一般来说, 使用 10046 事件得到 sql 执行计划的步骤如下:
1. 激活当前 session 10046 事件
2. 在当前 session 中执行 sql 语句
3. 关闭当前 session 10046 事件
执行完上述步骤后, 通常会自动生成一个 trace 文件. 在 oracle 11g 中, trace 文件一般放在$oracle_base/diag/rdbms/{database_name}/$oracle_sid/trace 目录下. 如果使用 oradebug 激活跟踪 10046后, 可以使用 oradebug tracefile_name 得到刚刚生成的 trace 文件的完整路径.
name type value------------------------------------ ----------- ------------------------------background_dump_dest string g:\app\davidd\diag\rdbms\david \david\trace
刚刚提到的 oradebug 激活跟踪 10046 事件, 我想大部分 dba 都会使用. oradebug 是个功能强大非常好用的工具, 使用 oradebug help 将会看到它的功能很多
sql> oradebug helphelp [command] describe one or all commandssetmypid debug current processsetospid set os pid of process to debugsetorapid ['force'] set oracle pid of process to debugsetorapname set oracle process name to debugshort_stack get abridged os stackcurrent_sql get current sqldump [addr] invoke named dumpdumpsga [bytes] dump fixed sgadumplist print a list of available dumpsevent set trace event in processsession_event set trace event in sessiondumpvar [level] print/dump a fixed pga/sga/uga variabledumptype print/dump an address with type infosetvar modify a fixed pga/sga/uga variablepeek [level] print/dump memorypoke modify memorywakeup wake up oracle processsuspend suspend executionresume resume executionflush flush pending writes to trace fileclose_trace close trace filetracefile_name get name of trace filelkdebug invoke global enqueue service debuggernsdbx invoke cgs name-service debugger-g parallel oradebug command prefix-r parallel oradebug prefix (return outputsetinst set instance list in double quotessgatofile dump sga to file; dirname in double quotesdmpcowsga dump & map sga as cow; dirname in double quotesmapcowsga map sga as cow; dirname in double quoteshanganalyze [level] [syslevel] analyze system hangffbegin flash freeze the instanceffderegister ff deregister instance from clusterffterminst call exit and terminate instanceffresumeinst resume the flash frozen instanceffstatus flash freeze status of instanceskdsttpcs helps translate pcs to nameswatch watch a region of memorydelete watchpoint delete a watchpointshow watchpoints show watchpointsdirect_access fixed table accesscore dump core without crashing processipc dump ipc informationunlimit unlimit the size of the trace fileprocstat dump process statisticscall [arg1] ... [argn] invoke function with arguments
使用 oradebug 跟踪 10046 命令如下:
sql> oradebug setmypidstatement processed.// 激活 10046 事件sql> oradebug event 10046 trace name context forever,level 12;statement processed.sql> select /*+ leading(t3) use_merge(t4) */ * 2 from t3, t4 3 where t3.id = t4.t3_id and t3.n = 1100;10 rows selected.// 在当前 session 关闭 10046 事件sql> oradebug event 10046 trace name context off;statement processed.// 使用 oradebug tracefile_name 可以直接看到生成的 trace 文件的位置sql> oradebug tracefile_name;g:\app\davidd\diag\rdbms\david\david\trace\david_ora_2176.trc
其中, 10046 按照收集信息的内容分为以下等级:
level 0 停用sql跟踪，相当于sql_trace=false
level 1 标准sql跟踪，相当于sql_trace=true
level 4 在level 1的基础上增加绑定变量的信息
level 8 在level 1的基础上增加等待事件的信息
level 12 在level 1的基础上增加绑定变量和等待事件的信息
分析读懂 trace 文件
现在我们打开 g:\app\davidd\diag\rdbms\david\david\trace\david_ora_2176.trc 看看生成的 trace 文件的内容
parsing in cursor #22 len=92 dep=0 uid=0 oct=3 lid=0 tim=900460923321 hv=1624778336 ad='34671d90' sqlid='g0rdyg9hdh9m0'select /*+ leading(t3) use_merge(t4) */ *from t3, t4where t3.id = t4.t3_id and t3.n = 1100end of stmtparse #22:c=0,e=10777,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3831111046,tim=900460923319exec #22:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3831111046,tim=900460923482wait #22: nam='sql*net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=900460923512fetch #22:c=15625,e=23922,p=0,cr=119,cu=0,mis=0,r=1,dep=0,og=1,plh=3831111046,tim=900460947462wait #22: nam='sql*net message from client' ela= 221 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=900460947755wait #22: nam='sql*net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=900460947803fetch #22:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=1,plh=3831111046,tim=900460947864stat #22 id=1 cnt=10 pid=0 pos=1 obj=0 op='merge join (cr=119 pr=0 pw=0 time=28 us cost=193 size=1280 card=10)'stat #22 id=2 cnt=1 pid=1 pos=1 obj=0 op='sort join (cr=15 pr=0 pw=0 time=0 us cost=6 size=63 card=1)'stat #22 id=3 cnt=1 pid=2 pos=1 obj=83550 op='table access full t3 (cr=15 pr=0 pw=0 time=0 us cost=5 size=63 card=1)'stat #22 id=4 cnt=10 pid=1 pos=2 obj=0 op='sort join (cr=104 pr=0 pw=0 time=11 us cost=187 size=650000 card=10000)'stat #22 id=5 cnt=10000 pid=4 pos=1 obj=83552 op='table access full t4 (cr=104 pr=0 pw=0 time=8603 us cost=29 size=650000 card=10000)'
从上面的 trace 文件我们可以看出 sql 语句经过了 parse(解析) -> exec(执行) -> fetch(从游标中获取数据) 几个过程, 其中第一句说明了当前跟踪执行的 sql 语句的概况,比如使用游标号, sql 语句的长度, 递归深度等等基本信息:
parsing in cursor #22 len=92 dep=0 uid=0 oct=3 lid=0 tim=900460923321 hv=1624778336 ad='34671d90' sqlid='g0rdyg9hdh9m0'
cursor cursor number
len sql 语句长度
dep sql 语句递归深度
uid user id
oct oracle command type
lid privilege user id
tim timestamp，时间戳
hv hash id
ad sql address 地址, 用在 v$sqltext
sqlid sql id
接着, 下面的语句说明了 sql 语句具体的执行过程以及每一个步骤消耗 cpu 的时间等性能指标
parse #22:c=0,e=10777,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3831111046,tim=900460923319exec #22:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3831111046,tim=900460923482fetch #22:c=15625,e=23922,p=0,cr=119,cu=0,mis=0,r=1,dep=0,og=1,plh=3831111046,tim=900460947462
c cpu 消耗的时间
e elapsed time
p number of physical reads 物理读的次数
cr number of buffers retrieved for cr reads 逻辑读的数据块
cu number of buffers retrieved in current mode (current 模式读取的数据块)
mis cursor missed in the cache 库缓存中丢失的游标, 硬解析次数
r number of rows processed 处理的行数
dep 递归深度
og optimizer mode 【1：all_rows, 2：first_rows, 3：rule, 4：choose】
plh plan hash value
tim timestamp 时间戳
以及执行过程中的发生的等待事件
wait #22: nam='sql*net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=900460923512
nam an event that we waited for 等待事件
ela 此操作消耗的时间
p3 block 块号
trm timestamp 时间戳
最后显示的是该游标的执行计划
stat #22 id=1 cnt=10 pid=0 pos=1 obj=0 op='merge join (cr=119 pr=0 pw=0 time=28 us cost=193 size=1280 card=10)'stat #22 id=2 cnt=1 pid=1 pos=1 obj=0 op='sort join (cr=15 pr=0 pw=0 time=0 us cost=6 size=63 card=1)'stat #22 id=3 cnt=1 pid=2 pos=1 obj=83550 op='table access full t3 (cr=15 pr=0 pw=0 time=0 us cost=5 size=63 card=1)'stat #22 id=4 cnt=10 pid=1 pos=2 obj=0 op='sort join (cr=104 pr=0 pw=0 time=11 us cost=187 size=650000 card=10000)'stat #22 id=5 cnt=10000 pid=4 pos=1 obj=83552 op='table access full t4 (cr=104 pr=0 pw=0 time=8603 us cost=29 size=650000 card=10000
cnt 当前行源返回的行数
pid parent id of this row source 当前行源的父结点 id
pos position in explain plan 执行计划的位置
obj object id of row source (if this is a base object)
op the row source access operation
例如, 执行步骤 merge join 消耗的逻辑读为 119, 物理读为 0, 耗费的时间为 28 us, 成本 cost 193，返回 10 条记录
使用 tkprof 命令翻译 trace 文件
我们也可以使用 tkprof 命令对 trace 文件进行翻译，得到一个容易理解的 trace 汇总报表文件
c:\documents and settings\davidd> tkprof g:\app\davidd\diag\rdbms\david\david\trace\david_ora_2176.trc d:\trace.trctkprof: release 11.2.0.1.0 - development on thu dec 18 18:51:44 2014copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.
tkprof 翻译的 trace 文件的汇总报表如下：
trace file: g:\app\davidd\diag\rdbms\david\david\trace\david_ora_2176.trcsort options: default********************************************************************************count = number of times oci procedure was executedcpu = cpu time in seconds executing elapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call********************************************************************************select /*+ leading(t3) use_merge(t4) */ *from t3, t4where t3.id = t4.t3_id and t3.n = 1100call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 1 0.00 0.00 0 0 0 0execute 1 0.00 0.00 0 0 0 0fetch 2 0.00 0.00 0 119 0 10------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 0 119 0 10misses in library cache during parse: 1optimizer mode: all_rowsparsing user id: sysrows row source operation------- --------------------------------------------------- 10 merge join (cr=119 pr=0 pw=0 time=0 us cost=193 size=1280 card=10) 1 sort join (cr=15 pr=0 pw=0 time=0 us cost=6 size=63 card=1) 1 table access full t3 (cr=15 pr=0 pw=0 time=0 us cost=5 size=63 card=1) 10 sort join (cr=104 pr=0 pw=0 time=0 us cost=187 size=650000 card=10000) 10000 table access full t4 (cr=104 pr=0 pw=0 time=8733 us cost=29 size=650000 card=10000)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 20.23 20.23********************************************************************************overall totals for all non-recursive statementscall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 1 0.00 0.00 0 0 0 0execute 1 0.00 0.00 0 0 0 0fetch 2 0.00 0.00 0 119 0 10------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 0 119 0 10misses in library cache during parse: 1elapsed times include waiting on following events: event waited on times max. wait total waited ---------------------------------------- waited ---------- ------------ sql*net message to client 3 0.00 0.00 sql*net message from client 3 20.23 30.20overall totals for all recursive statementscall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 0 0.00 0.00 0 0 0 0execute 0 0.00 0.00 0 0 0 0fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 0 0.00 0.00 0 0 0 0misses 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: g:\app\davidd\diag\rdbms\david\david\trace\david_ora_2176.trctrace file compatibility: 11.1.0.7sort 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. 122 lines in trace file. 0 elapsed seconds in trace file.
其中，misses in library cache during parse ：1 意思是解析的时候库缓存丢失游标, 也就是说发生了一次硬解析