Oracle Performance: Why AWR Reports are Limited

AWR Reports are Averages

Don’t get me wrong—I look at AWR reports all the time. I find them useful for getting a feel for how a database is running. They provide a lot of useful information. Keep in mind, however, that AWR numbers are actually roll-ups of the raw data.  Be definition, AWR reports list the averages for the snapshot period (typically, one hour.) In many cases, the average is good enough.

Here’s the problem: What if you are looking for a specific problem that only occurred for a brief time?  In this case, having an average is just not good enough.

When AWR is Not Enough

Only knew how to use AWR reports

Here’s an actual example I encountered recently: The in-house customer reported that his jobs were taking a long time—hours longer than he expected. Looking at the AWR report, the only Sql listed were very simple, efficient, and fast-running. One could easily be fooled into thinking that all the Sql was fine; however, that was not the case.

Looking carefully at the AWR report, in the section title,  SQL ordered by Elapsed Time, I noticed one important note.

Captured SQL account for   16.4 % of Total DB Time (s).

In other words, the AWR report was missing the vast majority of Sql actually run over that period! 

Find the Missing Sql

By using some queries on V$ActiveSession_history, I was able to spot  some of the other Sql that I had missed. Here is one simple way

with p1 as (select /*+parallel(a 8) */
distinct *
from v$active_session_history A
where wait_time = 0
and sample_time like '24-JAN-19 11.42%PM'
and sql_id is not null
) select module, sql_id, sql_exec_id, event, count(*) from p1
group by module, sql_id, sql_exec_id, event
order by 1

The reason that the AWR report had missed these other Sql, is that they didn’t use bind variables.  So each of these other Sql was unique. Since AWR only show the top resource consumers, none of the “missing” Sql qualified as a big resource consumer. I find these type of omissions as the biggest problem with not using bind variables. Many DBAs are fooled by this.

Know How to use Active Session History

Clever DBA

As long as you have other ways to zero-in on problem Sql, you won’t be fooled anymore by “missing” Sql. I find the Active Session History views to be one of the most powerful tools in my toolbox.