Re: cpu usage

hopehope_123 wrote:
> Hi ,
>
> I am working on a database system which its cpu usage at the top
> waiter list in statspack outputs.
> Each statspack output ( in 60 min. intervals ) show same sql statement.
> ( in the list of most buffer_gets )
>
> After examining the outputs , i came to a conclusion that cpu usage is
> not problem.
>
> note also: log file sync and log file parallel write events are also in
> the list, i know, my aim is to examine the cpu usage.
>
> I will be appreciated if you can comment .
>
> The server has 8 cpus.
>
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> --------- ------------------ -------- ---------
> -------------------
> Begin Snap: 652 27-Dec-06 10:00:04 66 257.3
> End Snap: 653 27-Dec-06 11:00:02 73 356.9
> Elapsed: 59.97 (mins)
>
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 7,084
> 75.67
> log file sync 166,839 819
> 8.75
> log file parallel write 356,741 529
> 5.65
> db file sequential read 38,676 484
> 5.17
> latch free 286,350 219
> 2.34
>
> With 8 cpus, in 1 hour , i have total 8*3600=28800 sec. cpu time,
> only 7084 sec. is spent.
> So oracle db uses %24 of the total cpu time of the server.
>
>
> Here is the sql list:
>
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 26,870,884 48,108 558.6 42.5 1470.76 1662.69
> 1647928882
> Module: JDBC Thin Client
> BEGIN xxxx(:1,:2,:3,:4) END;
>
>
> This proc is called 48108 in 1 hour , and takes 1470.76 secs. One call
> takes 1470.76/48108 = 30msec.
>
>
> Since average execution time of this procedure is 30 msec. , i think
> there is not much thing to do with this sql. If in a way , i achieve
> to decrease the response time of this sql to 10 msec. ,
> than total time of this sql is also decreased proprtionaly ( 2/3)
>
> So i dont think the cpu time is an important factor and problematic on
> this server.
>
> What do you think?
>
> Kind Regards,
> hope

I think, you are, as usual, NOT reading responses, and NOT following up advice provided, but instead continue to impose arbritary criteria on your system.
Could you please explain why you are asking for advice here, if you already intend to ignore all advice that doesn't suit you,as you *think* you know the 'solution'?

You also have the irritating habit to repost questions using different nicks, could you please stop doing that?

Finally: I you don't intend to accept advice, please don't ask for it!!!

-- 
Sybrand Bakker
Senior Oracle DBA