SQL_ID missing in dba_hist_sqlstat? Why

We have got into a trouble today with dba_hist_sqlstat when we are working on a performance issue.

This performance issue is about a statement that running very slow compared to its previous run of 20 mins. Thats another story.

As this story continues yesterday and today, we found our sql statement comfortably in v$sql and active session history without any issues yesterday.

When it comes to today’s troubleshooting, and trying to pull from awr view dba_hist_sqlstat we could not find the same and lead to another discovery which may helpful to you all as well.

So we kept our performance issue aside (; ) and then started looking this. ofcourse we resolved that issue later part.

AWR does not collect information for all statements that are in sql area indeed collects the number of statements that are according to TOPNSQL setting in awr control settings.

DBMS_workload_repository.modify_snapshot_settings takes the following inputs.

retention=> value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800),
            max value can be set by passing a value of 0 which means forever. 
            So one would want to set for X days the value would be X * 24 (hours per day) * 60 (minutes per hour)
interval => 60min (snap at this interval), a value of 0 will turn off AWR

topnsql => top N sql size, specify value of NULL will keep the current setting

TOPNSQL:-
The topnsql is used to specify the number of SQL to collect for each criteria like elapsed time, CPU time, parse calls, shareable memory, version count. The topnsql is normally set to a small number like 10, because you only want to see the most current SQL statements. This SQL information is normally purged after a period of time, after which the SQL source code is no longer needed. And it depends on statistics level parameter (Typical 30, ALL 100)

If STATISTICAL_LEVEL=TYPICAL, MMON captured each 30 TOP SQL in above criteria. So totally it captured 420 SQL. (30 SQL * 14 Criteria = 420 sql)
If STATISTICAL_LEVEL=ALL, MMON captured each 100 TOP SQL in above criteria.So totally it captured 1400 SQL. (100 SQL * 14 Criteria = 1400 sql)

Criteria Category:-
============

1. Elapsed Time (ms)
2. CPU Time (ms)
3. Executions
4. Buffer Gets
5. Disk Reads
6. Parse Calls
7. Rows
8. User I/O Wait Time (ms)
9 Cluster Wait Time (ms)
10. Application Wait Time (ms)
11. Concurrency Wait Time (ms)
12. Invalidations
13. Version Count
14. Sharable Mem(KB)

Modify snapshot settings
================

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800,  interval=>60, topnsql=>100);
PL/SQL procedure successfully completed.

shows retention and interval after it was modified

SQL>  select * extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
topnsql from dba_hist_wr_control;

Snapshot Interval     Retention Interval     topnsql
================    ==================    ========   
60                  64800                100

References:-

Bug 8484269 : ORA-13530: INVALID TOPNSQL 50001, MUST BE IN THE RANGE (30, 50000)

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm

http://www.freelists.org/post/oracle-l/AW-Missing-SQL-in-DBA-HIST-SQLSTAT,1