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