Re: DB CPU is much lower than CPU Time Reported by TOP SQL consumers
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: Ls Cheng <exriscer@xxxxxxxxx>
- Date: Sat, 1 Dec 2012 05:01:19 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
