Oracle AWR threshold affects historical execution plans

Some netizens recently mentioned why the historical execution plan of SQL statements cannot be viewed in dba_hist_ SQL _plan. This problem is due to the fact that the threshold value set by Oracle does not capture all SQL statements by default, therefore, it is normal to see some SQL history 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. default threshold

-- Environment, the following example is based on Oracle 10gscott @ 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 _idscott @ cnmmbo> @ my_last_sqladdress hash_value SQL _id command_type piece SQL _text =---------- else ------------ 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: 2jbkb542592umsno rows selectedscott @ cnmmbo> exec retry (); --> execute a snapshot, write the content of the buffer into snapshopl/SQL procedure successfully completed. scott @ cnmmbo> @ SQL _plan_his --> the execution plan still cannot be obtained. Enter value for input_ SQL _id: 2jbkb542592umsno rows selected

2. modify the threshold

-- 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_hisenter value for input_ SQL _id is not executed: 2jbkb542592umsno 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_hisenter 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 -- Blog: http://blog.csdn.net/robinson_0612scott@CNMMBO> @ SQL _plan_disp_awrenter value for input_sqlid: --------------------------------------------------------------------------------------- SQL _id 2jbkb5qf92ums ------------------ 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. process of modifying AWR Threshold Value
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 is provided. For details, refer to 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 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.

 

4. Summary
A. The execution plan of all currently executed SQL statements is in the V $ SQL _plan view and eliminated according to the LRU algorithm.
B. the SQL Execution Plan that meets the SQL capture conditions will be filled into the dba_hist_ SQL _plan data dictionary after the AWR snapshot is generated.
C. The SQL Execution Plan cannot be obtained from dba_hist_ SQL _plan. You should consider modifying the topnsql parameter in AWR snapshot configuration.
D. AWR snapshots are also affected by the statistics_level parameter. If the value is all, 100 top SQL statements are collected, and 30 are collected for typical.

 

More references

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Oracle rowid

Null Value and index (1)

Null Value and index (2)

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor

Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)