Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Performance problem with Oracle server
We're experiencing "general slowness" with our application which has an
Oracle back-end. As far as possible, I've eliminated the front-end
(Forms) and network as bottle necks. Now, I'm not a DBA, but I did run
a query on the v_at_system_event table as follows:
SQL> col EVENT format a30
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 round(AVERAGE_WAIT/100,2) > 0
8 and EVENT not in ('pmon timer','smon timer','rdbms ipc
reply','rdbms ipc message','SQL*Net message from client')
9 order by decode(EVENT,'SQL*Net message from
client',-1,TIME_WAITED) desc;
And here's the output:
EVENT TOTAL_WAITS TOTAL_TIMEOUTS MINS_WAITEDSECS_PER_WAIT
------------------------------ ----------- -------------- ----------- ------------- db file sequential read 90866422 0 7638
.01
jobq slave wait 36588 34709 1835 3.01 buffer busy waits 15291768 296 1618
.01
PL/SQL lock timer 35445 35439 1202 2.03 enqueue 29681 20584 1125 2.27 latch free 3547660 1935540 903
.02
pipe get 4417 1802 76 1.03 log file sync 208723 6 45
.01
log file parallel write 380461 0 41
.01
write complete waits 2303 2302 39 1.02 log file sequential read 17030 0 7
.03
log file switch completion 2165 0 5
.13
library cache load lock 163 36 3 1.05 local write wait 287 131 2 .5 process startup 1298 3 1
.04
library cache pin 354 6 1
.11
row cache lock 19 9 1 1.8 SQL*Net message from dblink 165 0 0
.14
wait list latch free 252 0 0
.02
control file heartbeat 1 1 0 4.09 BFILE open 106 0 0
.02
BFILE closure 106 0 0
.01
log buffer space 31 0 0
.04
db file parallel read 45 0 0
.02
BFILE internal seek 106 0 0
.01
BFILE read 106 0 0
.01
control file single write 12 0 0
.01
switch logfile command 3 0 0
.03
checkpoint completed 1 0 0
.08
reliable message 1 0 0
.01
BFILE get path object 2 0 0
.01
31 rows selected.
(Paste into monospaced font to get nice columns.)
In my lay opinion, events jobq slave wait, buffer busy waits, PL/SQL lock timer,enqueue, and latch free seem to take up a lot of time relative to 'db file sequential read'.
Is my crude analysis correct? I'd like an expert opinion.
Thanks. Received on Mon Feb 27 2006 - 12:05:39 CST