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

如果要让普通用户能够使用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)