Re: Performance problem with Oracle server
- From: bubblecat@xxxxxxxxx
- Date: 28 Feb 2006 06:40:26 -0800
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.
.
- Follow-Ups:
- Re: Performance problem with Oracle server
- From: frank.van.bortel@xxxxxxxxx
- Re: Performance problem with Oracle server
- References:
- Performance problem with Oracle server
- From: bubblecat
- Re: Performance problem with Oracle server
- From: Roman Klesel
- Performance problem with Oracle server
- Prev by Date: Re: Performance problem with Oracle server
- Next by Date: Re: lots of waiting on 'db file parallel write'
- Previous by thread: Re: Performance problem with Oracle server
- Next by thread: Re: Performance problem with Oracle server
- Index(es):
Relevant Pages
|
