RE: High Resource consuming SQL in Statspack ?

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).

Keith

> 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.
>
> Cheers
>
> -----Original Message-------------------------
> From: Keith Moore [mailto:kmoore_at_zephyrus.com]
> Sent: Thursday, November 27, 2008 9:42 PM
> Vivek,
>
> What time period is the snapshot for?
>
> I've seen application running on application servers that make this call
>
> frequently.
>
> 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.
>

--
http://www.freelists.org/webpage/oracle-l