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 demonstration is based on Oracle 10g
Scott @ CNMMBO> select * from v $ version where rownum <2;

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

-- Query awr configuration below
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

-- Publish SQL queries
Scott @ CNMMBO> select * from dept where loc = 'Chicago ';

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

-- Obtain SQL _id
Scott @ CNMMBO> @ my_last_ SQL

ADDRESS HASH_VALUE SQL _ID COMMAND_TYPE PIECE SQL _TEXT
-------------------------------------------------------------------------------------------------------
2017100009f942760 2626775672 2jbkb542592ums 3 0 select * from dept where loc = 'boogs'

-- Obtain the execution plan of the buffer zone 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 statement.
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

-- Next, we will try to obtain the execution plan from 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 -- the query cannot obtain the execution plan.
Enter value for input_ SQL _id: 2jbkb5qf92ums

No rows selected

Scott @ CNMMBO> exec dbms_workload_repository.create_snapshot (); --> execute a snapshot and write the content in the buffer to Inverted snapsho.

PL/SQL procedure successfully completed.

Scott @ CNMMBO> @ SQL _plan_his --> the execution plan still cannot be obtained.
Enter value for input_ SQL _id: 2jbkb5qf92ums

No rows selected

1 2 3 Next Page