查看SQL执行计划的几种方式

注意事项：

1.使用DDL操作可以让ORACLE再次执行目标SQL时使用硬解析，但其影响范围太广，因为一旦对某个表执行了DDL操作，再次执行与这个表相关的所有SQL时就会全部使用硬解析。
    这是很不好的，特别是对于OLTP类型的应用系统而言，因为这可能会导致短时间内硬解析数量剧增，进而影响系统的性能。

2.可以使用DBMS_SHARED_POOL.PURGE来删除指定的缓存在库缓存中的Shared Cursor,该方式影响范围仅限于该目标SQL所对应的Shared Cursor，
    也就是说它可以做到只让Oracle在执行目标SQL时使用硬解析，在执行所有其他SQL时都和原来一样保持不变。
    SQL> SELECT SQL_TEXT,SQL_ID,ADDRESS,HASH_VALUE FROM  v$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T1%';
    SQL> EXEC SYS.DBMS_SHARED_POOL.PURGE('address,hash_value','c')   --其中这里第二个输入参数是常量c，表示要删除的是Shared Cursor。

3.Child Cursor中除了会存储目标SQL的解析树和执行计划之外，还会存储该SQL所使用的绑定变量的类型和长度，这意味着即使该SQL的SQL文本没有发生任何改变，
    只要其SQL文本中文本型绑定变量的定义发生了改变，那么该SQL再次执行时可能还是做了硬解析（参考3.2.5 绑定变量分级）

第一种方式：explain plan命令

explain plan for +目标SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

补充：SELECT DBMS_METADATA.GET_DDL('TABLE','PLAN_TABLES$','SYS') FROM DUAL   --查看原代码

      SELECT SID FROM v$MYSTAT WHERE ROWNUM<2;       --查看当前SESSION的SESSION ID

第二种方式：DBMS_XPLAN包

A.用于在SQLPLUS中查看刚刚执行过的SQL的执行计划
  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED'))   

B.用于查看指定SQL的执行计划（只要目标SQL的执行计划所在的Child Cursor还没有被age out出Shared Pool,就可以用这种方法来查看该SQL的执行计划）
  SQL>SELECT SQL_TEXT,SQL_ID,HASH_VALUE,CHILD_NUMBER FROM v$SQL WHERE SQL_TEXT LIKE 'SELECT EMPNO,ENAME%';
      /* 只要目标SQL所对应的Child Cursor还在Library Cache中，我们就可以从v$SQL中查到目标SQL的Child Cursor的详细信息，包括SQL ID,SQL HASH VALUE,Child Cursor Number等 
  
  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',child_cursor_number,'ADVANCED')) ;

  案例：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3yfu3wh150aqt','0','ADVANCED')) ;

C.用于查看指定SQL的所有历史执行计划。
   使用方法A、B能够显示目标SQL执行计划的前提条件是该SQL的执行计划还在Shared Pool中，而如果该SQL的执行计划已经被age out出Shared Pool，
   那么只有该SQL的执行计划被ORACLE采集到AWR Repository中，我们就可以使用方法C来查看该SQL的所有历史执行计划。
   
   SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('79glp919t7x4u')) ;

  案例：模拟实验环境
    1.手工收集一下AWR报告，采集完成后清空Shared Pool
      SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
      SQL> ALTER SYSTEM FLUSH SHARED_POOL;  --请勿随意在生产环境执行此语句
    2.查看目标SQL的执行计划是否被age out出Shared Pool了
      SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM  v$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T1%';  --此时已没有记录了
    3.由于之前我们已经通过手工采集AWR报告的方式将目标SQL的执行计划采集到了AWR Repository中，所以现在我们可以通过执行方法C来得到该SQL的所有历史执行计划：
      SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('79glp919t7x4u')) ;

第三种方式：AUTOTRACE开关

SQL> set autot ?
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL>SET AUTOTRACE OFF (set autot off) 不显示  只有查询结果
    SET AUTOTRACE ON (set autot on) 启动  : 查询结果  执行计划  统计信息 
    SET AUTOTRACE TRACEONLY（set autot trace）  ：不显示查询结果 只显示：执行结果数量 执行计划  统计信息 
    SET AUTOTRACE TRACEONLY EXPLAN (set autot  trace exp) ;;：只显示：执行计划
    SET AUTOTRACE TRACEONLY STATISTICS(set autot  trace stat)  ; 只显示 ：执行结果数量  统计信息 

缺陷：1.必须要等到语句真正执行完毕后，才可以出结果
      2.无法看到表被访问了多少次。

第四种方式：statistics_level=all (可以看到表的访问次数)

SQL> alter session set statistics_level=all;
SQL> 执行语句
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

第五种方式:10046事件

优点：1.可以看出SQL语句对应的等待事件。
      2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无所遁形。
      3.可以方便的看出处理的行数，产生的逻辑读。
      4.可以方便的看出解析时间和执行时间。
      5.可以跟踪整个程序包。

缺陷：1.无法判断表被访问了多少次。
      2.步骤繁琐。
      3.执行计划中的谓词部分不能清晰的展示出来。

第六种方式;AWR SQL报告

A.先根据AWR报告找到目标SQL 
  生成AWR报告
  SQL>@?\rdbms\admin\awrrpt.sql

B.手工执行脚本$ORACLE_HOME/rdbms/admin/awrsqrpt.sql,并依次输入报告类型、要查看的快照范围、目标SQL ID和所有生成的AWR SQL报告的名称；
   SQL> @?\rdbms\admin\awrsqrpt.sql';(建议的快照范围和AWR报告的范围一致)

--------------------------------额外补充--------------------------------------------------

非dba用户set autotrace提示启用 STATISTICS 报告时出错

创建plustrace角色，然后将角色赋给特定用户。Oracle已经提供了角色plustrace的创建脚本

@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/plustrce.sql

把角色赋权给zw用户

SQL> grant plustrace to zw;

SQL> conn zw/zw
		Connected.
SQL> set autotrace traceonly;

把角色赋予给所有用户

SQL> grant plustrace to public;