[Oracle]获取执行计划的各方法总结 - 数据库编程

nd s.sid=m.sid and p.addr=s.paddr) p, (select t.INSTANCE FROM v$thread t,v$parameter v WHERE v.name='thread' AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i, (select value from v$parameter where name='user_dump_dest') d; exit tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc d:\10046.txt sys=no sort=prsela,exeela,fchela SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19) 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 12 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 12 0 2 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 94 Rows Row Source Operation ------- --------------------------------------------------- 2 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us) 2 NESTED LOOPS (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2) 2 INLIST ITERATOR (cr=5 pr=0 pw=0 time=16 us) 2 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2) 2 INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621) 2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622) 2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 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
优点：1.可以看出SQL语句对应的等待事件
2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。
3.可以方便的看出处理的行数，产生的物理逻辑读。
4.可以方便的看出解析时间和执行时间。
5.可以跟踪整个程序包

缺陷: 1.步骤繁琐，比较麻烦
2.无法判断表被访问了多少次。
3.执行计划中的谓词部分不能清晰的展现出来。

方法6. awrsqrpt.sql

步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap 和end snap)
步骤3：输入你的sql_id

[Oracle]获取执行计划的各方法总结(四)

方法6. awrsqrpt.sql