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 -> Performance problem with Oracle server

Performance problem with Oracle server

From: <bubblecat_at_gmail.com>
Date: 27 Feb 2006 10:05:39 -0800
Message-ID: <1141063539.616673.238870@z34g2000cwc.googlegroups.com>


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_WAITED
SECS_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

Original text of this message

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