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

--校验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

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. Spe