RE: High Resource consuming SQL in Statspack ?
Date: Thu, 27 Nov 2008 10:31:20 -0600 (CST)
So, the command is using about 5% of one CPU (1 minute out of 20). If you have 5 CPUs (to make the math easy), the statement is using 1% of total CPU resources. I wouldn't say that's good or bad. If it is coming from an application that cannot be changed then there isn't anything you can do about it. If it's a 'ping' type query from the application server you may be able to adjust the frequency. There was a thread on that a week or two back.
I wouldn't say any particular % of total is bad for a query. If I'm the only one on a system and execute 'select sysdate from dual' then I have used 100% of the total CPU used (Ok, ignoring recursive SQL, system generated SQL, etc).
> Elapsed (Time between the Snapshots): 20.12 (mins)
> Qs Is there a threshold value above which %Total may be considered BAD? ... My
> impression is %Total > 30% is BAD.
> -----Original Message-------------------------
> From: Keith Moore [mailto:kmoore_at_zephyrus.com]
> Sent: Thursday, November 27, 2008 9:42 PM
> What time period is the snapshot for?
> I've seen application running on application servers that make this call
> Each execution is only about 5 milliseconds but there are over 13,000
> executions. It the snapshot time period is something like 2 hours then I would
> conclude that the % of total is high because not much else is happening and
> it's nothing to worry about.