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

Didn't you have a similar question already that was answered?

If an oracle system is doing real work, it has to use something to do the work.

What would the oracle software require to perform work?

Isn't it cpu cycles?

Well tuned oracle system should be cpu constrained.

I recommend you purchase and read Cary Millsap's book "Optimizing Oracle Performance". Received on Wed Dec 27 2006 - 14:59:50 CST