Oracle AWR threshold affects historical execution plans

Oracle AWR threshold affects the historical execution plan. Some netizens recently mentioned why the historical execution plan of SQL statements cannot be viewed in dba_hist_ SQL _plan. This problem is caused by default, the threshold value set by Oracle does not capture all SQL statements, so it is normal to see some historical SQL Execution plans. In Oracle 9i, we can set different snapshot levels to obtain detailed information of different degrees. You can also separately configure the SQL collection threshold values, such as the number of SQL executions, the number of disk reads, and the number of resolution calls. All SQL statements that exceed this setting are collected to snapshot. Oracle 10g and 11g have corresponding settings. The problem is described below. 1. The default value is [SQL] -- environment. The following example is based on Oracle 10g scott @ CNMMBO> select * from v $ version where rownum <2; BANNER orders Oracle Database 10g Release 10.2.0.3.0-64bit Production -- The following query awr configuration scott @ CNMMBO> select * from region; DBID SNAP_INTERVAL retention topnsql ---------- -------------------- detail ---------- 938506715 + 00000 01:00:00. 0 + 00007 00:00:00. 0 DEFAULT -- publish an SQL query scott @ CNMMBO> select * from dept where loc = 'Chicago '; deptno dname loc ---------- ------------ getting 30 sales chicago -- get SQL _id scott @ CNMMBO> @ brief ADDRESS HASH_VALUE SQL _ID COMMAND_TYPE PIECE SQL _TEXT #-------- orders ------------ ---------- adjust limit 2626775672 limit 3 0 select * from dept where loc = 'Chicago '-- Obtain the buffer execution plan from 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 --> You can query the execution plan of the corresponding SQL. Enter value for input_ SQL _id: upload id operation options OBJECT_NAME BYTES CPU_COST IO_COST --- commandid ----------------- ------------ ---------- 0 select statement 1 table access full dept 20 36567 3 -- the following is an attempt to obtain the execution plan scott @ CNMMBO> get SQL _plan _ 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 -- the query cannot obtain the execution plan. Enter value for input_ SQL _id: 2jbkb542592ums no rows selected scott @ CNMMBO> exec retry (); --> execute a snapshot, write the content of the buffer into the inverted snapsho PL/SQL procedure successfully completed. scott @ CNMMBO> @ SQL _plan_his --> still unable to get the execution plan Enter value for input_ SQL _id: 2jbkb542592ums no rows selected 2. modify the threshold value [SQL]. Below we will set the topnsql parameter to the maximum value, to ensure that any SQL statement can be written to the snapshot scott @ CNMMBO> exec dbms_workload_repository.modify_snapshot_settings (topnsql => 'maximum ') Once executed; PL/SQL procedure successfully completed. -- check awr configuration 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 -- check dba_hist_ SQL _plan first. At this time, the snapshot scott @ CNMMBO> @ SQL _plan_his Enter value for input_ SQL _id is not executed: 2jbkb542592ums no rows selected -- run the original SQL statement scott @ CNMMBO> select * from dept where loc = 'Chicago 'again '; deptno dname loc ---------- ------------ ------------- 30 sales chicago -- execute manual snapshot creation to write to scott @ CNMMBO> exec dbms_workload_repository.create_snapshot (); PL/SQL procedure successfully completed. -- check again. The SQL Execution Plan has been written to the awr snapshot scott @ CNMMBO> @ SQL _plan_his Enter value for input_ SQL _id: 2jbkb542592ums id operation options OBJECT_NAME BYTES CPU_COST IO_COST --- zookeeper --------------- ------------ 0 select statement 1 table access full dept 20 36567 3 -- at the same time, we can also view the corresponding execution plan -- author: robinson scott @ CNMMBO> @ SQL _plan_disp_awr Enter value for input_sqlid: 2jbkb5qf92ums PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- SQL _ID =------------------ select * from dept where loc = 'Chicago 'Plan hash value: 3383998547 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement ||| 3 (100) | 1 | table access full | DEPT | 1 | 20 | 3 (0) | 00:00:01 | -------------------------------------------------------------------------- 13 rows selected. -- restore the DEFAULT scott @ CNMMBO> exec dbms_workload_repository.modify_snapshot_settings (topnsql => 'default'); PL/SQL procedure successfully completed. 3. 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); -- provides topnsql. For details, refer to Oracle reference topnsqlIf NUMBER: top n SQL size. the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse cals, retriable 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.