Re: DB CPU is much lower than CPU Time Reported by TOP SQL consumers
Date: Sat, 1 Dec 2012 05:01:19 -0600
Message-ID: <CACNsJndTbUo33oAv7TLdFZEYjHURJO+C17JStG4mmQ9eUi-eeA_at_mail.gmail.com>
Sorry for the late reply on this, I got swamped with other stuff lately.. but I've done some pretty detailed investigation on this and there's a lot of output to put here so I just placed them on my wiki which details on the Possible reasons and Troubleshooting SQL ordered by CPU - double counting http://goo.gl/OtHg1 <-- the main topic
and I've done some detailed test cases which are available on the below links, the workload is this http://goo.gl/MPtfQ which kinda matches the load on the first post where it's got PL/SQL lock timer and frequent fast SQLs
doublecounting-test0- 1st encounter (http://goo.gl/oGg5S) doublecounting-test1-killed (http://goo.gl/kAVKw) doublecounting-test2-finished (http://goo.gl/qAPXE)
each of the instrumentation are correlated by time the load spike occured
but here are the things that you have to focus on each of the
instrumentation:
collectl - check the column "User" and "Run" and "Avg1"
ASH - the number before the "CPU".. that's the number of AAS CPU it consumed
snapper - if it says "1600% ON CPU" that means it consumed 16 CPUs
(1600/100)
gas - the number of sessions and AVG_ETIME which is the elapsed time per
execute
sql_detail - the CPU_WAIT_EXEC which is the CPU WAIT
AWR - the Top 5 Timed Events and the "Captured SQL account for", and notice
the Executions if it's zero (killed) or has a value (finished)
Statspack - the Top 5 Timed Events and the "Captured SQL account for", and
notice the Executions if it's zero (killed) or has a value (finished)
-- Karl Arao karlarao.wordpress.com karlarao.tiddlyspot.com twitter.com/karlarao -- http://www.freelists.org/webpage/oracle-lReceived on Sat Dec 01 2012 - 12:01:19 CET