SQL运行了，内存中也存在，AWR却查询不到的原因

主要原因：SQL执行过快或者频率过少没有及时捕获造成的

SQL> select     status from t1 where object_id=120;

STATUS
-------
VALID
VALID

--获取SQL_ID为2ay1m2zwrzxtb
SQL> @all

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2ay1m2zwrzxtb, child number 0
-------------------------------------
select     status from t1 where object_id=120

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=120)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "STATUS"[VARCHAR2,7]


28 rows selected.

--可以看到执行太快，没有捕获到
SQL> select count(1) from v$active_session_history where sql_id='2ay1m2zwrzxtb';

  COUNT(1)
----------
         0
         
SQL>  exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> Select * from table(dbms_xplan.display_awr('2ay1m2zwrzxtb'));

no rows selected

--构造一个超过一秒的稍长时间的PL/SQL，里面包含需要的SQL，让ASH捕获到
SQL> declare
  2  sql_str varchar2(4000);
  3  begin
  4  sql_str:='select     status from t1 where object_id=120';
  5  for i in 1..100000 loop
  6  execute immediate sql_str;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(1) from v$active_session_history where sql_id='2ay1m2zwrzxtb';

  COUNT(1)
----------
         3


SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

--已经出现了
SQL>  Select * from table(dbms_xplan.display_awr('2ay1m2zwrzxtb'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2ay1m2zwrzxtb
--------------------
select     status from t1 where object_id=120

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.

         

备注：AWR的演变过程图