Oracle AWR 阙值影响历史执行计划

      最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划，对于这个问题是由于缺省情况下，Oracle 设定的阙值并非捕获所有的sql语句，所以无法看到某些sql历史执行计划乃正常现象。在Oracle 9i的时候，我们可以通过设定不同的快照level获得不同程度的详细信息。也可以单独配置收集sql的阙值，如指定sql的执行次数，磁盘读的次数，解析调用的数量等。所有超出这个设置的sql语句都收集到snapshot之中。Oracle 10g，11g也有相应的设置。下面来描述这个问题。

1、缺省阙值的情形

--环境，下面的演示基于Oracle 10g
scott@CNMMBO> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--下面的查询awr配置
scott@CNMMBO> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION                 TOPNSQL
---------- -------------------- ------------------------- ----------
 938506715 +00000 01:00:00.0    +00007 00:00:00.0         DEFAULT

--发布sql查询
scott@CNMMBO> select * from dept where loc='CHICAGO';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

--获得sql_id
scott@CNMMBO> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ------------------------------------------
000000009F942760 2626775672 2jbkb5qf92ums            3          0 select * from dept where loc='CHICAGO'

--从v$sql_plan获得缓冲区的执行计划
scott@CNMMBO> get sql_plan_curr.sql
  1  set linesize 135
  2  col id format 99
  3  col operation format a25
  4  col options format a25
  5  col object_name format a25 wrap
  6  SELECT id,
  7           operation,
  8           options,
  9           object_name,
 10           bytes,
 11           cpu_cost,
 12           io_cost
 13  FROM v$sql_plan
 14     WHERE sql_id = '&input_sql_id'
 15* ORDER BY id;
scott@CNMMBO> @sql_plan_curr.sql      -->此时可以查询到对应sql的执行计划
Enter value for input_sql_id: 2jbkb5qf92ums

 ID OPERATION                 OPTIONS                   OBJECT_NAME      BYTES   CPU_COST    IO_COST
--- ------------------------- ------------------------- --------------- ------- ---------- ----------
  0 SELECT STATEMENT
  1 TABLE ACCESS              FULL                      DEPT                20      36567          3

--下面尝试从dba_hist_sql_plan获得执行计划
scott@CNMMBO> get sql_plan_his.sql
  1  set linesize 135
  2  col id format 99
  3  col operation format a25
  4  col object_name format a25 wrap
  5  SELECT id,
  6           operation,
  7           options,
  8           object_name,
  9           bytes,
 10           cpu_cost,
 11           io_cost
 12      FROM dba_hist_sql_plan
 13     WHERE sql_id = '&input_sql_id'
 14* ORDER BY id;
scott@CNMMBO> @sql_plan_his     --查询无法获得执行计划
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

scott@CNMMBO> exec dbms_workload_repository.create_snapshot();  -->执行一次快照，写入缓冲区的内容倒snapsho

PL/SQL procedure successfully completed.

scott@CNMMBO> @sql_plan_his     -->依旧无法获得执行计划
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

2、修改阙值后的情形

--下面我们将topnsql参数设置为最大值，以确保任意sql只要执行一次即可写入到快照
scott@CNMMBO> exec dbms_workload_repository.modify_snapshot_settings(topnsql=>'MAXIMUM');

PL/SQL procedure successfully completed.

--校验awr配置
scott@CNMMBO> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION                 TOPNSQL
---------- -------------------- ------------------------- ----------
 938506715 +00000 01:00:00.0    +00007 00:00:00.0         MAXIMUM

--先看看dba_hist_sql_plan，此时肯定是不存在，因为没有执行快照
scott@CNMMBO> @sql_plan_his
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

--再次执行一下原来的sql语句
scott@CNMMBO> select * from dept where loc='CHICAGO';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

--此时执行手动创建快照实现写入
scott@CNMMBO> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

--再次查看，sql执行计划已经写入到awr快照
scott@CNMMBO> @sql_plan_his
Enter value for input_sql_id: 2jbkb5qf92ums

 ID OPERATION                 OPTIONS                   OBJECT_NAME      BYTES   CPU_COST    IO_COST
--- ------------------------- ------------------------- --------------- ------- ---------- ----------
  0 SELECT STATEMENT
  1 TABLE ACCESS              FULL                      DEPT                20      36567          3

--同时我们也可以通过DBMS_XPLAN.display_awr查看到相应的执行计划
--Author : Robinson
-- Blog  : http://blog.csdn.net/robinson_0612
scott@CNMMBO> @sql_plan_disp_awr
Enter value for input_sqlid: 2jbkb5qf92ums

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 2jbkb5qf92ums
--------------------
select * from dept where loc='CHICAGO'

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

--恢复缺省值
scott@CNMMBO> exec dbms_workload_repository.modify_snapshot_settings(topnsql=>'DEFAULT');

PL/SQL procedure successfully completed.

3、修改awr阙值的过程
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention   IN  NUMBER    DEFAULT NULL,
   interval    IN  NUMBER    DEFAULT NULL,
   topnsql     IN  NUMBER    DEFAULT NULL,
   dbid        IN  NUMBER    DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention   IN  NUMBER    DEFAULT NULL,
   interval    IN  NUMBER    DEFAULT NULL,
   topnsql     IN  VARCHAR2,
   dbid        IN  NUMBER    DEFAULT NULL);

--主要给出topnsql，具体可参照Oracle reference
topnsql
If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.

If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.

4、小结
    a、所有当前执行的sql的执行计划位于v$sql_plan视图，按照LRU算法淘汰
    b、符合sql捕获条件的sql执行计划在awr快照生成之后会被填充到dba_hist_sql_plan数据字典
    c、导致sql执行计划无法从dba_hist_sql_plan获得应考虑修改awr快照配置topnsql参数
    d、awr快照同时受到statistics_level参数的影响。如果其值为all时，收集100条top sql,为typical时收集30条

 更多参考

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

Oracle ROWID

NULL 值与索引(一)

NULL 值与索引(二)

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标

Oracle 表空间与数据文件Oracle 密码文件Oracle 参数文件Oracle 联机重做日志文件(ONLINE LOG FILE)Oracle 控制文件(CONTROLFILE)Oracle 归档日志Oracle 回滚(ROLLBACK)和撤销(UNDO)Oracle 数据库实例启动关闭过程Oracle 10g SGA 的自动化管理Oracle 实例和Oracle数据库(Oracle体系结构)