Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with Oracle server
bubblecat_at_gmail.com wrote:
> 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
That is a pretty foolish thing to do. Just because the average wait time rounds to zero doesn't mean the totatl wait time isn't important.
> 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
>
...
> (Paste into monospaced font to get nice columns.)
>
> In my lay opinion, events jobq slave wait, buffer busy waits, PL/SQL
> lock timer,
Both jobq slave wait and PL/SQL lock timer are idle waits, so generally they aren't relevant.
> enqueue, and latch free seem to take up a lot of time
> relative to 'db file sequential read'.
Minutes are minutes, and 7638 is more than 1618 + 1125 + 903.
Xho
-- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GBReceived on Mon Feb 27 2006 - 21:08:22 CST