cpu usage

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 27 Dec 2006 12:52:32 -0800
Message-ID: <1167252752.853194.326580@n51g2000cwc.googlegroups.com>

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 Received on Wed Dec 27 2006 - 14:52:32 CST