Re: Problems running Oracle / Performance

ARMistice_at_gmx.de (Alexander Zbiek) wrote in message news:<32293e40.0401121509.1ef69f29_at_posting.google.com>...
> Hello *,
>
> I hope somebody could perhaps help me ... I dont know exactly what is
> needed to
> help me with my problem. Hopefully somebody can tell me that :)
>
> I am running an Oracle Server on a SuSE linux 9 (Linux linux
> 2.4.21-99-default #1 Wed Sep 24 13:30:51 UTC 2003 i686 i686 i386
> GNU/Linux)
> there is a RAID-5 and a RAID-1 system for the database files ...
> /dev/sdb1:
> Timing buffered disk reads: 166 MB in 3.02 seconds = 54.91
> MB/sec
> /dev/sda1:
> Timing buffered disk reads: 182 MB in 3.00 seconds = 60.65
> MB/sec
>
> The system moved from an older computer to this new one. We never
> encountered
> big performance problems on the old system. But now: load average:
> 3.40, 3.50, 4.10
> and thats not the worst. There are always running "oracle" processes
> with high CPU usage.
> Theres nothing else running on that system.
>
> I dont know where the problem could be, so I try to send some stuff
> from the DB v$ tables ... with hope somebody can tell something or
> instruct me what is needed.
>
> I have set all oracle parameters in the config files (init.ora ...)
> like the other old system.
> I could post a lot of stuff :) But I dont even know if its usefull...
>
> Many thanks in advance
> Alexander Zbiek
>
> ------------------------------------
> (select * from V$SYSTEM_EVENT order by 4)
> refresh controlfile command 1 0 0 0 1
> log file single write 6 0 0 0 4357
> reliable message 1 0 1 1 13792
> log file sequential read 6 0 1 0 6569
> db file parallel write 29578 12769 9 0 88981
> control file sequential read 177 0 11 0 111794
> log file switch completion 4 0 44 11 439501
> log file parallel write 79121 79119 50 0 503349
> SQL*Net more data from client 10 0 59 6 585928
> rdbms ipc reply 12 0 62 5 618289
> LGWR wait for redo copy 130 73 83 1 825382
> process startup 7 0 86 12 864135
> direct path write 55320 0 132 0 1318865
> SQL*Net break/reset to client 116 0 138 1 1380553
> direct path read 87318 0 208 0 2081204
> SQL*Net more data to client 30961 0 249 0 2489032
> SQL*Net message to client 790526 0 360 0 3598385
> control file heartbeat 1 1 399 399 3994192
> control file parallel write 13118 0 1939 0 19394361
> buffer busy waits 1917 0 9748 5 97477088
> library cache pin 49 46 14131 288 141306825
> db file parallel read 161651 0 27654 0 276535965
> log file sync 70288 75 70769 1 707687945
> library cache lock 244 238 72284 296 722835204
> latch free 21479 8384 74508 3 745078596
> db file scattered read 5181423 0 142164 0 1421641178
> db file sequential read 14569582 0 195077 0 1950769611
> smon timer 141 127 3763181 26689 37631807692
> dispatcher timer 670 670 3897771 5818 38977709965
> pmon timer 13651 13626 3924178 287 39241783368
> enqueue 34581 30220 9684063 280 96840631963
> rdbms ipc message 207087 118425 14849858 72 148498581411
> SQL*Net message from client 790523 0 23164262 29 231642622371
>
> -------------------------------
> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ( 'parse time cpu',
> 'parse time elapsed', 'parse count (hard)', 'CPU used by this session'
> )
>
> CPU used by this session 3452591
> parse time cpu 385659
> parse time elapsed 1474693
> parse count (hard) 35171
> -------------------------------

enqueues look very out of proportion to the other events. The main problem with just selecting from v$system_event, is that the figures are totaled since instance startup.

install statspack, take some snapshots in 15 minute intervals (while the system is under load).
generate reports across the 15 minute intervals and see what your top wait events are (such as enqueues).

if you don't know how to install statspack, there are numerous articles out there, OTN is a good source, here's one:

http://otn.oracle.com/deploy/performance/pdf/20TUNING_dialeris.pdf

Oracle Magazine no longer has the articles from 2000 available online.

upload a copy of a report to oraperf http://www.oraperf.com/ and review their comments.

Before you reply to this thread, make sure you at least include the oracle version and edition (although if you posted a statspack report, it would be included in there).

Pd Received on Tue Jan 13 2004 - 00:23:37 CST