Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Problem

RE: Performance Problem

From: Burton, Laura <>
Date: Tue, 26 Aug 2003 11:29:39 -0800
Message-ID: <>

No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips & Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit.

Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on.

If you know of any other gotcha's, please let me know. I may not have picked up on it in my research.

Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though the disk/memory read was not above 95%, it was at 92% so that is probably why no performance gain was noticed. We are using PL/SQL procedures heavily. The stats on the Library Cache looked good though.

I read something this weekend about how using 'logical' drives to separate the different files can cause a performance hit. I am using logical disks, and I plan to change when I can, but I'm not sure yet how much that will help. I have redistributed some of the rollback segments so that they are not all located on the same disk. However since some of the drives are logical, that may not have done any good. I've rebuilt indexes, changed extent sizes to reduce the amount of extents, added rollback segments, etc. In lieu of this, code is next...


-----Original Message-----
Sent: Tuesday, August 26, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L

Did you analyze the sys schema by mistake. This can stop the fastest database. We had a contractor do that to an 8.0.5 database once, and only



Please see the official ORACLE-L FAQ:

Author: Burton, Laura

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Aug 26 2003 - 14:29:39 CDT

Original text of this message