oracle sql 执行计划分析_查看Oracle SQL执行计划方法比较、分析

1.SQL*PLUS AUTOTRACE：

为使所有的用户都能用到SQLPLUS AUTOTRACE，需要做以下操作。

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create or replace public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce.sql

SQL>

SQL> drop role plustrace;

drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>

SQL> set echo off

SQL> grant plustrace to public;

Grant succeeded.

SQL> connect test/test

Connected.

SQL> set autotrace on

SQL>

AUTOTRACE选项：

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SET AUTOTRACE ON;

SET AUTOTRACE TRACE EXPLAIN;

SET AUTOTRACE TRACE EXPLAIN STATISTICS;

启用AUTOTRACE功能，会在一个服务器进程对应2个会话，一个查询数据，一个记录执行计划和最终结果。SQLPLUS AUTOTRACE 是基于PLAN_TABLE表的方法来查询执行计划，内部实现其实和下面要讲到的方法相同：

EXPLAIN PLAN FOR

SELECT * FROM TABLE_NAME;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY(format=>'BASIC'));

2.其实下面要说到的方法已经在说SQLPLUS AUTOTRACE时说到了，EXPLAIN PLAN FOR 与DBMS_XPLAN的结合。

EXPLAIN PLAN [SET statement_id='xxx'] FOR

SELECT * FROM TABLE_NAME;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

加入SET statement_id='xxx'可以使plan_table存放多个执行计划。获取执行计划除了使用dbms_xplan.display外，还可以通过执行

utlxpls.sql //显示串行查询的计划结果

utlxplp.sql //显示并行查询的计划结果使用这种方法也是基于PLAN_TABLE表来完成。实际和SQLPLUS AUTOTRACE是一样的。

3.下面这种方法是直接查询V$SQL_PLAN表，直接查询V$SQL_PLAN没有进行很好的格式化，看起来不太方便，ORACLE 10g开始提供了新的包来很好的格式化了V$SQL_PLAN的结果。也是第4种查询方法。

4.DBMS_XPLAN.DISPLAY_CURSOR

SELECT * FROM TABLE_NAME;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

普通用户要使用DBMS_XPLAN.DISPLAY_CURSOR的话需要如下视图的权限：

grant select on v_$session to scott;

grant select on v_$sql_plan to scott;

grant select on v_$sql to scott;

这种实现和直接查询V$SQL_PLAN是相同的。

OK，到这步需要提到一个问题了，我们观察第一种和第二种方法是基于PLAN_TABLE表来生成的执行计划，第三种和第四种方法是基于V$SQL_PLAN视图来生成的执行计划的。通过实际和一些论坛上的经验发现对于一条SQL，这2种查询执行计划的结果是有可能不同的。当然第三种和第四种查询的执行计划是真实LIBRARY CACHE中真实的执行计划。而第一种和第二种方法生成的执行计划可以认为是预判断出来的。所以我们在实际的生产环境中有时候会遇到，在SQLPLUS中执行的速度很快，一旦用到存储过程或程序里面就会变得奇慢，很可能就是因为执行计划不同造成的。我们通过监控会发现2者生成的执行计划完全不同，具体执行时生成了错误的执行计划。这种情况有可能是由于参数CURSOR_SHARING=FORCE或者索引等造成的执行计划错误。

另外，通过DBMS_XPLAN.DISPLAY_AWR函数获取的执行计划来自DBA_HIST_SQL_PLAN视图，通过历史数据记录，甚至一些被老化的SQL执行计划仍然可以被查到

还可以加入一些参数值：

SQL> desc dbms_xplan;

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

TABLE_NAME                     VARCHAR2                IN     DEFAULT

STATEMENT_ID                   VARCHAR2                IN     DEFAULT

FORMAT                         VARCHAR2                IN     DEFAULT

FILTER_PREDS                   VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_ID                         VARCHAR2                IN

PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT

DB_ID                          NUMBER(38)              IN     DEFAULT

FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_ID                         VARCHAR2                IN     DEFAULT

CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT

FORMAT                         VARCHAR2                IN     DEFAULT

SELECT plan_table_output FROM TABLE (DBMS_XPLAN.DISPLAY('PLAN_TABLE','NO','ALL'));

SELECT plan_table_output FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('',2,'ALL')); //ALL对应的是FORMAT，有BASIC,TYPICAL,SERIAL,ALL几个值，每个值对应显示的内容不同，ALL显示的内容最详尽。默认是TYPICAL,SERIAL和TYPICAL显示是相同的，只是SERIAL去掉了对并行的显示。SELECT plan_table_output FROM TABLE (DBMS_XPLAN.AWR(''));

如果要让普通用户能够使用dbms_xplan.display_cursor和dbms_xplan.display_awr的话需要给普通用户授予SELECT_CATALOG角色。

5.SQL TRACE

启用：

alter session set sql_trace=on;

禁用：

alter session set sql_trace=off;

跟踪其他用户：

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,SQL_TRACE);

6.诊断事件(10046)

启用：

alter session set events '10046 trace name context forever,level 12';

禁用：

alter session set events '10046 trace name context off';

启用其他用户10046诊断:

exec DBMS_SYSTEM.SET_EV(SI,SE,EV,LE,NM);

开启:exec DBMS_SYSTEM.SET_EV(1056,232,10046,12,'');

关闭:exec DBMS_SYSTEM.SET_EV(1056,232,10046,0,'');

另外如何查看是否启用了10046事件：

SQL> alter session set events '10046 trace name context forever ,level 12';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/db_1/rdbms/log/test_ora_5529.trc

SQL> oradebug eventdump session

10046 trace name CONTEXT level 12, forever

7.使用oracle第三方工具：

plsql developer(F5)

Toad (Ctrl+E)