Oracle中获取执行计划的几种方法分析

1. 预估执行计划 – explain plan
explain plan以sql语句作为输入,得到这条sql语句的执行计划,并将执行计划输出存储到计划表中。
首先,在你要执行的sql语句前加explain plan for,此时将生成的执行计划存储到计划表中,语句如下:
explain plan for sql语句
然后,在计划表中查询刚刚生成的执行计划,语句如下:
select * from table(dbms_xplan.display);
注意:explain plan只生成执行计划,并不会真正执行sql语句,因此产生的执行计划有可能不准,因为:

1)当前的环境可能和执行计划生成时的环境不同;
2)不会考虑绑定变量的数据类型;
3)不进行变量窥视。

2. 查询内存中缓存的执行计划 (dbms_xplan.display_cursor)
如果你想获取正在执行的或刚执行结束的sql语句真实的执行计划(即获取library cache中的执行计划),可以到动态性能视图里查询。方法如下:

1)获取sql语句的游标
游标分为父游标和子游标,父游标由sql_id(或联合address和hash_value)字段表示,子游标由child_number字段表示。

如果sql语句正在运行,可以从v$session中获得它的游标信息,如:
select status, sql_id, sql_child_number from v$session where status=’active’ and ….

如果知道sql语句包含某些关键字,可以从v$sql视图中获得它的游标信息,如:
select sql_id, child_number, sql_text from v$sql where sql_text like ‘%关键字%‘

2)获取库缓存中的执行计划
为了获取缓存库中的执行计划,可以直接查询动态性能视图v$sql_plan和v$sql_plan_statistics_all等,但更方便的方法是以sql_id和子游标为参数,执行如下语句:
select * from table(dbms_xplan.display_cursor(‘sql_id’,child_number));

3)获取前一次的执行计划:
set serveroutput off
select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));

3. 查询历史执行计划(dbms_xplan.display_awr)
awr会定时把动态性能视图中的执行计划保存到dba_hist_sql_plan视图中,如果你想要查看历史执行计划,可以采用如下方法查询:
select * from table(dbms_xplan.display_awr(‘sql_id’);

4. 在用sqlplus做sql开发是(autotrace)
set autotrace是sqlplus工具的一个功能,只能在通过sqlplus连接的session中使用,它非常适合在开发时测试sql语句的性能,有以下几种参数可供选择:

set autotrace off —————- 不显示执行计划和统计信息,这是缺省模式
set autotrace on explain —— 只显示优化器执行计划
set autotrace on statistics — 只显示统计信息
set autotrace on —————– 执行计划和统计信息同时显示
set autotrace traceonly —— 不真正执行,只显示预期的执行计划,同explain plan

5. 生成trace文件查询详细的执行计划 (sql_trace, 10046)
sql_trace作为初始化参数可以在实例级别启用,也可以只在会话级别启用,在实例级别启用sql_trace会导致所有进程的活动被跟踪,包括后台进程及所有用户进程,这通常会导致比较严重的性能问题,所以在一般情况下,我们使用sql_trace跟踪当前进程,方法如下:

sql>alter session set sql_trace=true;
…被跟踪的sql语句…
sql>alter session set sql_trace=false;
如果要跟踪其它进程,可以通过oracle提供的系统包dbms_system. set_sql_trace_in_session来实现,例如:
sql> exec dbms_system.set_sql_trace_in_session(sid,serial#,true) –开始跟踪
sql> exec dbms_system.set_sql_trace_in_session(sid,serial#,false) –结束跟踪

生成trace文件后,再用tkprof 工具将sql trace 生成的跟踪文件转换成易读的格式,语法如下:
tkprof inputfile outputfile
10046事件是sql_trace的一个升级版,它也是追踪会话,生成trace文件,只是它里面的内容更详细,

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐