Problems running Oracle / Performance

From: Alexander Zbiek <ARMistice_at_gmx.de>
Date: 12 Jan 2004 15:09:19 -0800
Message-ID: <32293e40.0401121509.1ef69f29@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
-------------------------------

Received on Mon Jan 12 2004 - 17:09:19 CST