How many SQL's are actually captured and displayed by AWR ?

While looking into a performance problem, we generally take an AWR report and directly look at the top resource consuming SQL statements. But what do you do when the most expensive SQL is missing from the AWR report ?

How do I check if my AWR report is complete and has captured all or most of the SQL statements ?

Let us have a look at this section of the AWR report.

WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
VISHAL-TEST	9999999999	VISHAL-TEST	4	10.2.0.4.0	YES	abcd.host.com

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	18305	07-Jan-13 15:00:30	38	1.6
End Snap:	18306	07-Jan-13 16:00:05	38	1.6
Elapsed:		59.59 (mins)
DB Time:		58.40 (mins)

SQL ordered by Executions

• Total Executions: 9,914
• Captured SQL account for 68.9% of Total

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
3,989	42	0.01	0.00	0.00	c4nhd1ntptxq7		select message_level, sequenc...
715	0	0.00	0.00	0.00	6ssrk2dqj7jbx		select job, nvl2(last_date, ...
238	238	1.00	0.00	0.00	gj5r9jj2xad7f	JDBC Thin Client	SELECT 1, status, '', arch...
114	114	1.00	0.00	0.00	0ws7ahf1d78qa		select SYS_CONTEXT('USERENV', ...
114	114	1.00	0.00	0.00	459f3z9u4fb3u		select value$ from props$ wher...
104	1,872	18.00	0.00	0.00	0h6b2sajwb74n		select privilege#, level from ...
104	104	1.00	0.00	0.00	0k8522rmdzg4k		select privilege# from sysauth...
104	0	0.00	0.00	0.00	1uzhrfn574t12	sqlplus@vcd01024.ute.fedex.com (TNS V1-V3)	SELECT ATTRIBUTE, SCOPE, NUMER...
104	104	1.00	0.00	0.00	5qgz1p0cut7mx	sqlplus@vcd01024.ute.fedex.com (TNS V1-V3)	BEGIN DBMS_OUTPUT.DISABLE; END...
104	104	1.00	0.00	0.00	5ur69atw3vfhj		select decode(failover_method,...
104	0	0.00	0.00	0.00	c4pc3jhzjcmc7	sqlplus@vcd01024.ute.fedex.com (TNS V1-V3)	SELECT CHAR_VALUE FROM SYSTEM....
104	104	1.00	0.00	0.00	d6vwqbw6r2ffk	sqlplus@vcd01024.ute.fedex.com (TNS V1-V3)	SELECT USER FROM DUAL
104	104	1.00	0.00	0.00	dyk4dprp70d74	SQL*Plus	SELECT DECODE('A', 'A', '1', '...
104	104	1.00	0.00	0.00	g4y6nw3tts7cc	sqlplus@vcd01024.ute.fedex.com (TNS V1-V3)	BEGIN DBMS_APPLICATION_INFO.SE...

It is very important to understand this section of the AWR report. 

Well, there are two things to consider.

1. How much data is being captured in the AWR repository ?

2. How much data is being displayed in the AWR report ?

Looking at this AWR report, I see total executions at 9914. (This number comes from DBA_HIST_SYSSTAT ). 68.9 % of this 9914 = 6830 executions were captured in the AWR repository.

What happened to the other SQL statements ? Why did Oracle fail to capture them ?

Could be the case where these missing SQL statements might have consumed a lot of CPU and that information is now lost.

By default AWR captues a snap every 1 hour. It takes a snap of V$SQL every hour and keeps this data safe in DBA_HIST_SQLSTAT and other related tables. Let us consider this scenario

At 10 AM - You take an AWR snap . S1

At 10.15 AM - You run a resource intensive SQL statement

At 10.45 AM -  SQL execution got completed. It has done a lot of damage by now.

At 10.50 AM - You flush the shared pool manually. / SQL gets aged out of shared pool automatically.

At 11 AM - You take another AWR snap S2

Now, if you generate an AWR report between these two snaps. You would see that the SQL which you ran is missing ! That's an interesting takeaway. AWR base tables are populated through V$SQL and if there is something missing in V$SQL, AWR too would miss the same.

Coming back to our AWR report, 

Total executions were - 9914

6830 executions were actually captured by the AWR repository.

Rest is lost ! ( Well, not completely, we will talk about ASH a little later. ASH takes samples every  second and may give us some more information )

Now lets us add up the numbers in the "executions" column of this AWR report. 

3989 + 715 + 238 + ... = 6106

AWR told us it captured 6830 executions, but when I add up these numbers I get 6106.

Well, this is quite simple. AWR does not display everything that is present in DBA_HIST_SQLSTAT

You can directly query DBA_HIST_SQLSTAT if you want to get a list of all the SQLs.

From 11.2 it is possible to control the number of SQL statements displayed in the AWR report. It defaults to 30 and can be altered using the following procedure.

SQL> exec dbms_workload_repository.awr_set_report_thresholds(top_n_sql=>50,top_n_sql_max=>50);