From time to time I’ve looked at an AWR report and pointed out to the owner the difference in work load visible in the “SQL ordered by” sections of the report when they compare the summary figure with the sum of the work done by the individual statements. Often the summary will state that the captured SQL in the interval represents some percentage of the total workload in the high 80s to mid 90s – sometimes you might see a statement that the capture represents a really low percentage, perhaps in the 30s or 40s.
You have to be a little sensible about interpreting these figures, of course – at one extreme it’s easy to double-count the cost of SQL inside PL/SQL, at the other you may notice that every single statement reported does about the same amount of work so you can’t extrapolate from a pattern to decide how significant a low percentage might be. Nevertheless I have seen examples of AWR reports where I’ve felt justified in suggesting that at some point in the interval some SQL has appeared, worked very hard, and disappeared from the library cache before the AWR managed to capture it.
Now, from Nigel Noble, comes another explanation for why the AWR report might be hiding expensive SQL – a bug, which doesn’t get fixed until 12.2 (although there are backports in hand).
Oracle Scratchpad 
One of the most annoying oddity in AWR top SQL section that, as documented, “The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count)”. I always thought that this is a bug in documentation, because there is also top SQL’s by Buffer Gets, Physical Reads, Executions. But I made some tests which showed that tops by Buffer Gets, Physical Reads and Executions are taken from tops by Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count. Therefore, AWR can easily miss, for example, SQL that produced most buffer gets is the system.
Comment by Vyacheslav Rasskazov — March 30, 2013 @ 1:26 am GMT Mar 30,2013 |
Vyacheslav,
Thanks for this note – it’s something I hadn’t notice (I just assumed automatically that Oracle was doing the same thing for each category). Can you give us a very brief sketch of how you tested this – presumably something along the lines of setting up N pieces of SQL which matched the collection criteria and one extra which didn’t but which should have appeared in (e.g.) the top buffer gets. Perhaps Oracle assumes that top CPU will automatically be top buffers, with similar assumption for other lists.
There’s been an interesting little follow-up to Nigel’s post in one of the comments – linking to another blog item.
Comment by Jonathan Lewis — June 9, 2013 @ 10:59 am BST Jun 9,2013 |
[…] In my experience per-session metrics proved to be more accurate than per-SQL statement metrics. Although the latter metrics measure actual SQL resource consumption (no sampling), the drawback is that they might not track all the relevant SQL statements that the database executed. Indeed, SQL statements might go out of library cache and therefore not being counted in AWR views and reports! See for example: https://jonathanlewis.wordpress.com/2013/03/29/missing-sql/ […]
Pingback by Demystifying Oracle database capacity management with workload characterization | Moviri – It's all about performance — February 11, 2014 @ 5:43 pm GMT Feb 11,2014 |