oracle： 执行计划2-- 如何生成与获取执行计划

1       生成执行计划

Oracle执行计划，分为预估执行计划 和实际执行计划。

 

通过told、PL\SQL developer、sql developer、explain plan for 或 set autotrace traceonly等获取的执行计划都是预估的执行计划。

 

1.1      预估执行计划生成（explain plan for）

 

Explainplan for 会让优化器对sql语句进行解析，生成查询计划。执行explain plan命令后，oracle会将解释生成的执行计划插入sys.plan_table$中。Explain plan只产生SQL的执行计划，并不执行SQL语句。因此产生的执行计划可能不准。因为：

1） 当前的环境可能和执行计划生成时的环境不同；

2） 不会考虑绑定变量的数据类型

3） 不进行变量窥视

 

1.2      执行SQL语句，产生实际执行计划

 

一条合法的语句在执行之后，就会在内存中至少产生一条执行计划。可以从v$sql_plan查询。

每一条执行计划对应一个游标。一条语句产生的第一个游标的child_number为0.

 

预估和实际执行计划有时候有很大的差别。所以调优的时候需要对比实际执行计划和预估执行计划。

 

实际执行计划怎么查看呢？

 

 

 

2       v$sql_plan(不常用)

 

执行SQL后，通过 sql_id来查看其执行计划

 

Select * from v$sql_plan where sql_ID=’7nhmhhss9’;

 

通过v$sql_plan视图查看实际执行计划，可读性太差，尤其是复杂sql语句，不常用

 

3       DBMS_XPLAN.DISPLAY_CURSOR

 

DBMS_XPLAN这个包在oracle 9i用来查看explainplan生成的执行计划

 

DISPLAY：format and displaythe contents of a plan table（显示explainplan命令解释的执行计划）

DISPLAY_CURSOR: format and display the contents of the execution plan of anyloaded cursor.（显示内存中的执行计划）

DISPLAY_AWR: format and display the contents of the execution plan of a storedSQL statement in the AWR（AWR历史数据中的执行计划）

DISPLAY_SQLSET: format and display the contents of the execution plan ofstatements stored in a SQL tuning set.（SQL优化集中语句的执行计划）

DISPLAY_SQL_PLAN_BASELINE: (SQL执行计划基线)

 

如果想从动态视图里查询plan，需要下面几个步骤

 

3.1      获取SQL语句的游标

 

游标分为父游标和子游标，父游标由sql_id字段吧表示，子游标有child_number字段表示。

 

如果sql语句正在运行，可以从v$session中获得它的游标信息，如

Select status,sql_id,sql_child_number fromv$session where status=’ACTIVE’ and ….

 

如果知道SQL语句包含某些关键字，可以从v$sql视图中获得它的游标信息。如：

Select sql_ID, CHILD_NUMBER

From v$SQL

Where sql_text like‘%select ename from emp e%’;

有可能该SQL不在share pool里了，此时表面该SQL已经被踢出share pool.

 

3.2      获取库缓存中的执行计划

 

Select * from table(DBMS_XPLAN.DISPLAY_CURSOR(‘<sql_ID>’,<childnumber>));

 

DISPLAY_cursor用法

可以显示内存中一个或多个游标的执行计划

 

语法：

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id        IN VARCHAR2  DEFAULT  NULL,

   child_number  IN NUMBER    DEFAULT  NULL,

   format        IN VARCHAR2  DEFAULT  'TYPICAL');

参数：

Parameter

Description

sql_id

要显示执行计划的sql语句的ID

child_number

子游标序号

format

输出格式

同于display输出格式

 

 

3.3      获取前一次的执行计划

Set serveroutput off

Select * form table(dbms_xplan.display_cursor(null,null,’ALLSTATSLAST’));

不传递任何参数给display_cursor函数，会显示当前会话最后一条sql语句的执行计划。

 

3.4      查询历史执行计划（dbms_xplan.display_awr）

 

Awr会定时把动态性能视图中的执行计划保存到dba_hist_sql_plan视图中，如果你想要查看历史执行计划，可以使用如下SQL:

 

Select * from table(dbms_xplan.display_awr(‘sql_id’));

 

3.5      Sqlplus –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

 

3.6      生成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>execdbms_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文件，只是里面的内容更详细。

 

 

 

 

参考资料：

 

Oracle生成和显示执行计划的方法

http://blog.csdn.net/laoshangxyc/article/details/8629123

 

查看ORACLE的实际执行计划，查看oracle执行计划

http://code.logphp.com/article/11815.html

 

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

http://www.jb51.net/article/39723.htm