欢迎使用CSDN-markdown编辑器

学习笔记之 oracle真正的执行计划

在某些情况下，oracle真正的执行计划并不是set autotrace traceon或者explain plan for这样设为自动追踪后产生的那一个。这样的执行计划只是优化器预计的执行计划。放到真正的语句中可能会产生不可知的微妙变化。
导致这些不同的原因大概有这些东西：
1、执行计划是和具体sql环境相关的
2、执行计划不考虑绑定变量的数据类型（所有绑定变量都是varchar2，然而优化器不认经过了转换的过滤字段使用索引这也就意味着本来好好的一个执行计划，在explain plan for中没毛病，但是放到trc追踪后或者display_cursor显示后不一样了 ）
3、生成执行计划不关心绑定变量（比如在一个条件等式判断中引入了变量，然后给了变量一个null。。。）
真的执行计划只有执行后才能看到。

• 一、hint或者statisticts_level
  使用hint /*+ gather_plan_statistics */强制收集执行信息或者使用statistics_level 置为typical或者all（all更全面，能显示时间相关以及buffer等信息）
  执行后使用v$sql视图查询相关hash值如：sql_id,child_id作为
  DISPLAY_CURSOR的输入，其中format一般设置为空或者all、allstats、allstats last.
  如果三个参数全部置为空，则显示上一条语句执行计划。特别注意这种情况下关掉serveroutput。不然会产生错误：
  BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check vsqlplan)同时使用上述方式获取执行计划时，可以将sql语句注释标记（使用/∗XXXX∗/），这样在查询vsql视图的时候会更容易

• 二、sql monitor
  自11g引入sql monitor 后在statistics_leve 为typical 或者all 就能使用dbms_sqltune.report_sql_monitor()监控任何消耗cpu或者IO超过5s的sql语句以及并行语句。
  select /*+ monitor*/ * from emp启动强制监视
  然后使用dbms_sqltune.report_sql_monitor获取执行计划:
  select dbms_sqltune.report_sql_monitor() from dual;

或者调用dbms_monitor包中的session_trace_enable、client_trace_enable、database_trace_enable启动
如果没有dbms_monitor权限，可以使用dbms_session包。仅仅包含session_trace_enable和 session_trace_disable使用方法与monitor包相同
这些过程会需要一些参数比如session_id、waits（时间信息默认true） 、binds（绑定变量信息默认true）、plan_stat（first_execution或者all_execution）大部分都有默认值.具体可以参考desc dbms_monitor
- 三、alter session set events
alter session set events’10046 trace name context forever, level 12’;即启动追踪.level可设置为0、1、4、8、16，32，64
0表示禁止，1启动最基础跟踪记录第一次执行的信息，4附加绑定变量,8附加等待时间详细信息，16附加每次执行后的执行计划信息，32不含执行计划，64视情况自动判断写入非第一次执行计划。各等级可组合使用。
如level12=4+8则意味着记录第一次执行信息并记录绑定变量和时间详细情况。

• 四、跟踪文件使用
  二和三小节产生的跟踪文件需要使用工具分析。可以使用max_dump_file_size控制trc文件的大小。vdiaginfo记录了当前会话trc文件的位置。需要别的会话的trc可以使用syscontext(′userenv′,′sid′)获取sid后连结vsession获取。
  找到跟踪文件之后，使用trcsess过滤tkprof分析。
  trcsess [output=output file name ] [session=session ID] [clientid=clientid] [service=service name] [action=action name] [module=module name] trace file names

  tkprof常用命令：
  tkprof inputfile.trc outfile sys=no sort=prsela,exeela,fchela##分析inputfile.trc 输出 outfile，不生成sys用户的相关输出，根据解析时间、执行时间、获取数据时间排序。
  但是trc产生的执行几乎也不一定是sql执行的真正计划，因为这样的计划也受数据库具体环境的影响。