Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with Oracle server

Re: Performance problem with Oracle server

From: <bubblecat_at_gmail.com>
Date: 28 Feb 2006 06:40:26 -0800
Message-ID: <1141135374.626333.290420@t39g2000cwt.googlegroups.com>


OK, Frank/Xho/Roman, tks for your comments. Our dbase is 9.2.0.5.0.

I edited the query based on what u said, and ran it again:

SQL> col EVENT format a30 heading 'Event'
SQL> col TOTAL_WAITS format 999,999,999 heading 'Waits'
SQL> col TOTAL_TIMEOUTS format 999,999,999 heading 'Timeouts'
SQL> col MINS_WAITED format 999,999 heading 'Total|Waited|(Minutes)'
SQL> col SECS_PER_WAIT format 9,999.99 heading 'Average|Wait|(Seconds)'
SQL> select EVENT,

  2 TOTAL_WAITS,
  3 TOTAL_TIMEOUTS,
  4 round(TIME_WAITED/6000) MINS_WAITED,   5 round(AVERAGE_WAIT/100,2) SECS_PER_WAIT   6 from V$SYSTEM_EVENT
  7 where EVENT not in ('pmon timer','smon timer','rdbms ipc reply','rdbms ipc message')
  8 order by MINS_WAITED desc;
                                                             Total
Average
                                                            Waited
Wait
Event                                 Waits     Timeouts (Minutes)
(Seconds)
------------------------------ ------------ ------------ ---------
---------
SQL*Net message from client      82,948,635            0   315,437

.23
db file sequential read 125,360,178 0 9,652
.00
db file scattered read 74,566,347 0 4,255
.00
jobq slave wait 62,099 58,927 3,117 3.01 PL/SQL lock timer 59,312 59,305 2,011 2.03 buffer busy waits 18,059,575 346 1,882
.01
enqueue 32,270 22,079 1,202 2.24 latch free 4,511,028 2,748,645 1,113
.01
pipe get 6,342 2,919 112 1.06 log file parallel write 548,996 0 54
.01
log file sync 272,566 7 54
.01
write complete waits 2,339 2,337 40 1.02 SQL*Net more data to client 20,199,490 0 11
.00
log file sequential read 20,735 0 9
.02
log file switch completion 2,359 0 5
.13
library cache load lock 269 57 5 1.04 control file parallel write 53,151 0 4
.00
control file sequential read 55,311 0 3
.00
local write wait 341 131 2
.43
SQL*Net more data from client 223,086 0 2
.00
SQL*Net break/reset to client 51,140 0 2
.00
library cache pin 586 6 2
.17
process startup 2,320 3 1
.03
row cache lock 21 9 1 1.64 SQL*Net message to client 82,949,434 0 1
.00
LGWR wait for redo copy 19,891 2,882 1
.00
wait list latch free 260 0 0

.02

<...>

Frank - Re your point that 'SQL*Net message from client' event should be included, I'm not sure I follow this. Indeed this event comes out on top, but isn't that normal? You would get this event every time the dbase waits for the front-end, right? So assuming users don't respond at lightning speed and even go for coffee with the form still open, isn't it to be expected that this event accounts for most of the waiting time? Please explain if I misunderstand this event.

Roman - CPU utilization is on the high side, I/O is fine, mem is fine. But what I'm trying to find out is if there is anything amiss other than insufficient CPU capacity, so that we can tune/optimize before we add more CPU's/mem/disks.

Xho - I know the read times exceed the latch/enqueue times, but what I meant was: Is it normal for latch/enqueue to be so high given the sequential/scattered read times? I mean, wouldn't latch/enqueue normally be lower for a properly tuned system?

Thanks all. Received on Tue Feb 28 2006 - 08:40:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US