基于案例SQL优化第九课作业分享

默认统计信息收集：

1. 11g默认启动了统计信息收集的任务，默认运行时间是周一到周五晚上10点和周6，周天的早上6点

2. 你也可以关闭自动统计新收集任务，选择手工收集的方式，但是一般不建议这样操作。

动态统计信息：

1. 统计信息默认情况下是每天晚上10点半后收集，如果新建对象还没来得级收集统计信息，就采用动态采样的方式。

2. 具体在set autotrace 跟踪的执行计划中，可以看到类似：- dynamic sampling used for this statement (level=2)

3. 除非你用类似/*+dynamic_sampling(t 0) */的HINT关闭这个动态采样。

4. 在收集过统计信息后，Oracle就不会采用动态采样。

注：建索引过程中，默认会收集索引相关的统计信息。

5.Oracle在建索引过程中，会自动收集统计信息。

―――――――――――――――――――――作业―――――――――――――――――――――――――――――――

布置人:wabjtam123 

一、获取执行计划的方法有哪几种，各有什么优缺点，如何选择。

A、获取执行计划的方法：

1. explain plan for获取；

2. set autotraceon ；

3. statistics_level=all;

4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取

5. 10046 trace跟踪

6. awrsqrpt.sql

B、各种方法的优缺点：

1>、explain plan for获取

优点： 

1、非真实运行产生的统计信息，无需真正执行，快捷方便；

缺陷： 

1、没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

           2、无法判断是处理了多少行；

           3、无法判断表被访问了多少次。   

2>、set autotrace on获取

有如下几种方式：

                     set autotrace on                 (得到执行计划，输出运行结果）

                     set autotrace traceonly          (得到执行计划，不输出运行结果）

         set autotrace traceonly explain  (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）

                     set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）

优点： 

1、可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

           2、虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出。

缺陷：

1.必须要等到语句真正执行完毕后，才可以出结果；

              2.无法看到表被访问了多少次。  

3>、statistics_level=all;

说明：

步骤1：alter session set statistics_level=all ;

步骤2：在此处执行你的SQL

步骤3：select * from table(dbms_xplan.display_cursor(null,null,‘allstats last‘));

另注：

  1. 如果你用 /*+ gather_plan_statistics */的方法，可以省略步骤1，直接步骤2,3。

  2. 关键字解读（其中OMem、1Mem和User-Mem在后续的课程中会陆续见到）：

    Starts为该sql执行的次数。

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

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

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

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

    Reads为物理读。

    OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，

    这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的

    1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，

         就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存

         大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的

    User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,

           大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)

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

0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

优点：

1.可以清晰的从STARTS得出表被访问多少。

         2.可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。

              3.虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少

缺陷：

1.必须要等到语句真正执行完毕后，才可以出结果。

              2.无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。

              3.看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）

4>、 通过dbms_xplan.display_cursor输入sql_id参数直接获取

说明：

步骤1： select  * from table(dbms_xplan.display_cursor(‘&sq_id‘)); （该方法是从共享池里得到）

注：

  1. 还有一个方法，select  * from table(dbms_xplan.display_awr(‘&sq_id‘));（这是awr性能视图里获取到的）

  2. 如果有多执行计划，可以用类似方法查出

    select * from table(dbms_xplan.display_cursor(‘cyzznbykb509s‘,0));

select * from table(dbms_xplan.display_cursor(‘cyzznbykb509s‘,1));

优点：

1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；

              2.可以得到真实的执行计划。               

缺陷 ：

1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

              2.无法判断是处理了多少行； 

              3.无法判断表被访问了多少次。

5>、 10046 trace跟踪

  步骤1：alter session set events ‘10046 trace name context  forever,level 12‘; （开启跟踪）

  步骤2：执行你的语句

  步骤3：alter session set events ‘10046 trace name context off‘;   （关闭跟踪）

  步骤4：找到跟踪后产生的文件

  步骤5：tkprof  trc文件  目标文件  sys=no sort=prsela,exeela,fchela  （格式化命令）    

--优点：1.可以看出SQL语句对应的等待事件

        2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。

        3.可以方便的看出处理的行数，产生的物理逻辑读。

        4.可以方便的看出解析时间和执行时间。

        5.可以跟踪整个程序包

--缺陷: 1.步骤繁琐，比较麻烦

        2.无法判断表被访问了多少次。

        3.执行计划中的谓词部分不能清晰的展现出来。

6>、 awrsqrpt.sql

  步骤1：@?/rdbms/admin/awrsqrpt.sql

  步骤2：选择你要的断点（begin snap 和end snap)

  步骤3：输入你的sql_id    

C、获取执行计划方法如何选择：

1.如果某SQL执行很长时间才出结果或返回不了结果，这时就只能用方法1（explain plan for获取）；

2.跟踪某条SQL最简单的方法是方法1（explain plan for获取），其次就是方法2（set autotraceon）；

3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4（通过dbms_xplan.display_cursor输入sql_id参数直接获取）和方法6（awrsqrpt.sql）；

4.如果SQL中含有函数，函数中套有SQL等多层调用，想准确分析只能使用方法5（10046 trace跟踪）；

5.要想确保看到真实的执行计划，不能用方法1（explain plan for获取）和方法2（set autotraceon）；

6.要想获取表被访问的次数，只能使用方法3（statistics_level=all）；

二. 说说如何辨别低效的SQL

1、返回行与逻辑读比率。请关注执行计划中的真是返回值与产生逻辑的的比例。一般而言，每获取一行开销5个以下的逻辑读是属于基本比较满意的。

2、评估值（E-Rows）准确的重要性。请关注Oracle的执行计划中的评估是否准确，这很重要，错误的评估往往意味着低效的执行计划。

3、类型转换需认真关注。请关注执行计划中的Predicate Information (identified by operation id)部分，这里例1出现的类似  filter(TO_NUMBER....这种情况的，就是发生了类型转换。需引起关注。

4、请小心递归调用部分。6种获取执行计划的方法中，只有 autotrace 的方式可以看出递归调用的次数（recursive calls），这方面的经验和想法很重要！

5、表访问次数需敏感。6种获取执行计划的方法中，只有 statisitcs_level=all 的方式可以看出表访问次数（STARTS)，这个很重要！如果NL而且返回次数很多，尤其注意。

6、注意表真实访问行数。A-ROWS 与 COUNT STOPKEY(其中A-ROWS是 statistics_level=all 方法 而执行计划中的COUNT STOPKEY是所有方法都可查）

7、谨慎的观察排序与否。sorts (memory) sorts (disk) (autotrace的方法,其中如果出现sorts(disk)有值，说明再磁盘中排序了，情况就糟了。）  

三. 请根据《解释读懂执行计划3_联合型(相关联)04_树形.sql》的脚本的输出执行计划，用联合型+单独型的方式，画出执行计划的访问草图。

SELECT /*+ connect_by_filtering  */ level, rpad(‘-‘,level-1,‘-‘)||ename

AS ename, prior ename AS manager FROM emp START WITH mgr IS NULL

CONNECT BY PRIOR empno = mgr

访问草图如下：

——0——1——2

——0——1——3——4

——0——1——3——5——6     

基于案例SQL优化第九课作业分享,古老的榕树,5-wow.com