Re: Problems running Oracle / Performance

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 13 Jan 2004 06:54:17 -0800
Message-ID: <1074005581.839972@yasure>

Alexander Zbiek wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1073961963.21249_at_yasure>...
>

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

>
>
> Hello,
>
> I already thought that ... thanks for the help.
>
> Okay .. the database has been moved with ( exp user/pwd_at_db FILE=...
> COMPRESS=Y FULL=Y BUFFER=32768 ) and imported with a simple ( imp
> user/pwd_at_db fromuser=... touser=... ).
> (the second time we executed the import we got warnings of already
> existing procedures and tables / so we dropped all the tables and
> "imp"ed again, there have been warnings again because of existing
> procedures.. but everything seems to be fine ... so we ignored them)
>
> I also ran a: [ exec dbms_utility.analyze_schema('bsw','COMPUTE');
> ]
>
> Both oracles are the same version: 9.2.0.1.0
>
> All the indexes seem to exist: I browsed over the tables and couldn't
> see any differences.
>
> The config files arent exactly the same .... I made a fresh new
> install on the
> new computer. But after I ran into problems I checked every oracle
> parameter and
> set it to the same values as the old server. (from db_block_size over
> large_pool_size to shared_pool_size)
>
> I dont know how to ran DBMS_STAT or how to check that :/ ... Perhaps
> the second import failed .. and there havent been stats calculated?
>
> So ... again not quite a lot of information ... but many thanx from my
> side for listening to me ...
> Alexander Zbiek

My only thought, other than those you already know, is that optimizer statistics should not be created with analyze_schema but rather with DBMS_STATS.GATHER_SCHEMA_STATS.

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

Received on Tue Jan 13 2004 - 08:54:17 CST