Re: Problems running Oracle / Performance

Alexander Zbiek wrote:

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

You wrote: "The system moved from an older computer to this new one" and this tells us almost nothing.

How was the database moved? Are the Oracle configuration files the same? Is the version the same? What version and edition of Oracle? Were the tables and data moved but not all indexes recreated? Are the tables and data there but statistics were not created with DBMS_STATS? We need to know a lot more to help you.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)