AutoTrace和Explain Plan 区别

要想拿到解释计划，我们一般是选中sql语句，按F5出现explain plan,想拿到执行计划，我们又不得不去服务器取得执行计划。后者太繁琐，前者又不能体现真实的执行计划（Oracle SQL execution plan is incorrect），总结一下：

解释计划是优化器在您运行时认为会发生的事情，执行计划实际上是在您运行查询时发生的。

我们需要轻量级的获取执行计划的方式，这里总结一下，供大家参考

1.autotrace

使用autotrace需要一些准备工作

1.1 授权

我们在下面路径下拿到这个文件

$oracle_home/sqlplus/admin/plustrce.sql

 你可以在sqlplus下执行上面的文件，当然也可以，以sys用户，dba权限，在plsql developer执行下列文本

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

1.2建表

同样，在下列路径下拿到该文件，执行

$oracle_home/rdbms/admin/utlxplan.sql 

 我的utlxplan.sql内容如下

create table PLAN_TABLE sharing=none (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(128),
        object_name        varchar2(128),
        object_alias       varchar2(261),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(128),
        other_xml          clob
);

 需要dba权限

1.3授权

我们创建了一个角色plustrace，而我们开发一般在apps用户下执行，那么我们可以把该角色赋权给apps,DBA权限下执行

GRANT PLUSTRACE TO APPS; 

1.4执行查询

在sqlplus命令窗口下依次执行

set autotrace on

select * from cux_t

结果如下

 看起来还是太繁琐了，我还是要登录服务器，有没有更好的方法？

参考资料：Using Autotrace in SQL*Plus

2.使用DBMS_XPLAN

dbms_xplan是oracle提供的工具包,具体有一下几个用法

• DISPLAY - 格式化和显示计划表的内容。

• DISPLAY_AWR - 格式化并显示 AWR 中存储的 SQL 语句的执行计划的内容。

• DISPLAY_CURSOR - 格式化并显示任何加载游标的执行计划的内容。

• DISPLAY_SQL_PLAN_BASELINE - 显示由 SQL 句柄标识的 SQL 语句的一个或多个执行计划

• DISPLAY_SQLSET - 格式化并显示存储在 SQL 调整集中的语句的执行计划的内容。

我们主要介绍display 和display_cursor

2.1display

2.1.1创建plan_table表

这一步我们在1.2已结完成过了

2.2.2执行查询

我们只需要分别执行下列SQL

EXPLAIN PLAN FOR 
select * from cux_t;

 

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 结果如下

 

 其实这个explanin plan for ,就是将解释计划写入到了plan_table里面

 解释计划语句在不执行查询本身的情况下生成查询的执行计划，允许显示性能不佳的查询的执行计划而不会影响数据库。 

 那么display方法仍然不是真正的执行计划，有没有更准确一点的方法？

2.2DISPLAY_CURSOR

 使用display_cursor相当简单，你只需要分别执行下面两段sql

select /*TOTO*/ * from cux_t;

SELECT t.*
  FROM v$sql s
      ,TABLE(dbms_xplan.display_cursor(s.sql_id
                                      ,s.child_number)) t
 WHERE sql_text LIKE '%TOTO%';

结果如下

 TOTO注释，只是方便我们找到这条SQL所在的sql_id,而DISPLAY_CURSOR来显示存储在游标缓存中的任何已加载游标的执行计划，我们通过sql_text找到了sql_id,传入display_cursor中，就找到了已缓存的执行计划。

参考资料:DBMS_XPLAN