查看执行计划

 

1.工具介绍

 

总结：单纯估算用autotrace，真实调优用DBMS_XPLAN带参数

 

1、explain

因为绑定变量的原因，这个只能是估算

explain plan for select 3+5 from dual;

select * from table(dbms_xplan.display());

select * from table(dbms_xplan.display(table_name=>'PLAN_TABLE',statement_id=>null,format=>'ALL'));

 

用explain plan解释一个SQL，相关信息会默认被放到一个一个叫PLAN_TABLE的全局临时表中。可以用这个来查看。

参数：

table_name,默认'PLAN_TABLE'，如果别的一个表跟PLAN_TABLE有一样的表结构，也可以读取里面的信息。

statement_id 默认null，即查该session最后的一条explain plan解释的语句。

format   默认'TYPICAL'，全部是'BASIC','TYPICAL','ALL'，ALL的时候会显示PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed。其实除了指定这3个级别外，显示什么信息也可以再通过后面的备注（减号代表去除相关信息）

'ALL -PROJECTION -NOTE'  #ALL级别，但不要投射与NOTE信息 

'BASIC ROWS'  --BASE下本来没有ROWS信息，我们也可以给它加上。

还有其他选项，如:outline

每个连上来的用户都可以使用plan_table，不用特别的权限，也不用读取诸如v$plan这样的视图。

不会实际执行SQL，也不会在shared pool上生成该SQL的cursor，是生成了一个cursor不过是带上explain plan for字眼的，而没有独立的该sql的cursor产生。

 

 

2、autotrace 

真实／估算，忽略绑定变量，非执行. 可以看逻辑读等数量

SET AUTOTRACE

 

ON EXPLAIN                                                          只显示执行计划                                         估算，因为没执行

ON STATISTICS                                                     只显示执行的统计信息                               

AUTOTRACE ON                                                    包含2,3两项内容                                        真实，因为执行过 

AUTOTRACE TRACEONLY                                        与ON相似，但不显示语句的执行结果              真实，因为执行过

 

3.SQL_TRACE 

在12c中文档中提示：不再支持SQL_TRACE参数； 真实计划，需要用TKPROF工具解析，可以获得绑定变量值. 

alter session set sql_trace=true;

alter session set sql_trace=false;

exec dbms_system.set_sql_trace_in_session(9,437,true)

exec dbms_system.set_sql_trace_in_session(9,437,false)

 

4.EVENT 10053 10046

真实计划，研究执行计划产生的原因

10046为增强版sql_trace

参考《10046事件》

参考《10046&10053的区别》

 

10053是检查优化器行为的，实在搞不懂为什么走那个计划可以看看，用得较少。

10046可以检查一些等待事件的内容，也可以获取绑定变量，一般用得也比较少。

 

4.DBMS_XPLAN

真实计划

4.1 dbms_xplan.display_cursor

 

1. DBMS_XPLAN.DISPLAY_CURSOR(                         
2.  sql_id        IN  VARCHAR2  DEFAULT  NULL,        
3.  child_number  IN  NUMBER    DEFAULT  NULL,        
4.  format        IN  VARCHAR2  DEFAULT  'TYPICAL');  

 

IOSTATS：会展示该SQL的累计IO统计信息，加了last就显示最后一个。

MEMSTATS：只有开启了PGA自动内存管理，即pga_aggregate_target不是0，我们才能使用这项，会展示使用了多少memory，多少bytes被交换到磁盘，一般来说用了hash-join,sort,group by等比较需要内存的操作才会收集。

ALLSTATS：是'IOSTATS MEMSTATS'的同义词。  IO+内存

LAST：默认地展示都是该游标的累积统计信息，加了LAST才会显示最后一个。

RUNSTATS_TOT  --为了向后兼容，相当于IOSTATS

RUNSTATS_LAST --为了向后兼容，相当于IOSTATS LAST

 

 

查看该游标最后一次的实际统计信息执行计划

select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'allstats last'));

 

会将该游标的累积执行信息列出，例如游标执行过两次后，starts，A-Rows,buffers也是上面的两倍

select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'allstats'));

 

最详细，汇集了普通模式的all与详细模式的allstats，而且将默认不显示的outline信息也显示出来。

select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'all allstats last outline'));

 

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

| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |

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

| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 25 |00:00:00.01 | 3 | 2 |

|* 1 | COUNT STOPKEY | | 1 | | | | | 25 |00:00:00.01 | 3 | 2 |

| 2 | TABLE ACCESS FULL| T1 | 1 | 25 | 54400 | 3 (0)| 00:00:01 | 25 |00:00:00.01 | 3 | 2 |

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

 

gather_plan_statistics看更详细的输出--能达到10046的粒度了

alter session set statistics_level=all; 

select /*+ gather_plan_statistics */ from t1 where rownum<10;

 

Starts为该sql执行的次数。

E-Rows为执行计划预计的行数。

A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。

A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。

Buffers为每一步实际执行的逻辑读或一致性读。

Reads为物理读。

OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。

0/1/M 为最优/one-pass/multipass执行的次数。

 

 

select t.* 

from v$sql s,table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ADVANCED ALLSTATS LAST')) t where s.sql_id = '&SQL_ID' ;

 

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

 

4.2 dbms_xplan.display_awr.   展示awr信息库（因为shared pool中age out）中的执行计划--但是没有谓词条件

 

 

select * from table(dbms_xplan.display_awr('&sqlid',null,null,'ADVANCED +PEEKED_BINDS'));

 

 

参考《xplan.display_awr.sql》

 

sql_id   --输入存储在AWR中的sql_id，你可以先查dba_hist_sql_plan，dba_hist_sqltext，看看某个语句属于什么sql_id。

plan_hash_value  --如果是null的话该sql_id所有的执行计划会输出。默认null

db_id  --如果忽略，默认就是当前的database

format  --参考display()，还是'basic','typical','all'这样，默认typical

权限：用户需要select on DBA_HIST_SQL_PLAN,DBA_HIST_SQLTEXT,V$DATABASE的权限。

查sql_id，根据sql文本查出sql_id ，可以从dba_hist_sqltext查。

来源：展示的执行计划的信息，来源于dba_hist_sql_plan。

详细：是否可以用allstats这样查看更详细的执行计划，这可能得取决于你当时的sql有没开启手机详细运行时统计信息。

 

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

 

4.3 display_sql_plan_baseline   展示存储在SPM中的SQL执行计划

查看SPM中baseline的执行计划：DBMS_XPLAN.DISPLAY_sql_plan_baseline

SELECT *  FROM table(DBMS_XPLAN.DISPLAY_sql_plan_baseline('SQL_a074c4f7bacd50da','SQL_PLAN_a0x64yyxcun6u06957ae0','ALL')); 

SELECT * FROM  table(DBMS_XPLAN.DISPLAY_sql_plan_baseline(sql_handle => 'SQL_351fadd1a0ec16be' ));

SELECT *  FROM table(DBMS_XPLAN.DISPLAY_sql_plan_baseline(plan_name => 'SQL_PLAN_a0x64yyxcun6u06957ae0','ALL')); 

 

格式：

sql_handle  SPM中的sql_handle相当于v$sql中的sql_id，默认Null

plan_name  SPM中唯一标识一个执行计划，就像v$sql中的plan_hash_value。默认null。如果是null，那么上面的sql_handle就必须指定。

format：参考display()，默认typical。

执行计划来源于：DBA_SQL_PLAN_BASELINES

 

 

5.各种现成脚本是利器

 

display_cursor_9i.sql.   —因为9i没有display_cursor方法

printsql

---

 

2.执行计划阅读顺序：

由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行

从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。

 

靠光标大法即可，或者有些脚本里面带了执行的步骤

 

标量子查询 可能违反最右最上原则，参考《Oracle SQL优化分析步骤》

 

执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径(表还是索引)，而filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用

 

带*号的是filter过滤 或 驱动access

 

 

动态采样

如果在执行计划中有如下提示：

 

                     -dynamic sampling used for the statement

 

这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。 我们从而推断这个表可能没有做过分析

这里会出现两种情况：

（1）       如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。

（2）       如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。

在看执行计划的时候，除了看执行计划本身，还需要看谓词和提示信息。 通过整体信息来判断SQL 效率。

 

动态采样适用范围:

1.没有统计信息，比如生成的中间临时表

2.多列产生关联，比如人为指定（11g还引入多列统计信息，但是是生成了一个列，代价大）