Re: DB CPU is much lower than CPU Time Reported by TOP SQL consumers
Date: Wed, 14 Nov 2012 08:05:28 +0100
Message-ID: <CAFuw4v=yMzy=4UrttaO-Ks8fOUogoWnwN3p3JOoH2W9AQhhvsw_at_mail.gmail.com>
> Unless the calls to dbms_application_info to set the module information have been done correctly (with stack pushing and popping) it's possible that two statements with different reported modules in statspack were USUALLY run from
> the same module, and only optimized in different modules.
Unfortunetly, not even "usually".
The data in STATS$SQL_SUMMARY, which is used for the top SQL reports, is gathered from the child cursor information in V$SQL, but aggregated by parent cursor (GROUP BY OLD_HASH_VALUE, ADDRESS) and the module information that ends up in STATS$SQL_SUMMARY is actually "MAX(MODULE)" from all child cursors for the same parent cursor.
So, if an SQL is executed from multiple modules you will see the module with the highest ASCII sort order in the report.
If you would execute all top statements additionally just once from module "ZZZ", this module would look like the only evil contributor for all top sql statements in the report, but actually just the detail data is hidden from the report due to the aggregation.
I would try the following:
-Do snapshots in shorter intervals, like 15 minutes (less data
aggregated) and check if this sheds some light into what's going on.
-Run Tanel's session snapper script
(http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper) in ash
mode.This samples v$session and will give you unaggregated data.
-Look at the Statspack data not just by generating reports between two
snapshots, but look at the snapshot data from longer periods of time.
(You might want to try out my Mumbai tool
http://marcusmonnig.wordpress.com/mumbai/ for that. You can also run
snapper from within it.)
Cheers,
Marcus
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 14 2012 - 08:05:28 CET