Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow Database Aft Upgrade from 8i to 9.2

RE: Slow Database Aft Upgrade from 8i to 9.2

From: Allen, Brandon <>
Date: Thu, 21 Jul 2005 12:03:27 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C450236130C@NT15.oneneck.corp>

Hi Laura,

You might want to try running statspack and uploading the results to for analysis.

It is typical to require a significantly larger shared pool after an upgrade from 8i to 9i, so you might want to increase that, but I wouldn't do it blindly - check your stats first to see if it's necessary, otherwise you might be wasting space that could be better allocated to your buffer cache or elsewhere.

Most likely, your problem is the result of queries using different execution plans from what was used in 8i. You can find the top statements in statspack, OEM Top SQL, or in v$sql and then look at their execution plans compared to what they used to be in 8i. Or perform traces of the specific slow-running queries and do a before/after upgrade comparison on them.


-----Original Message-----

[]On Behalf Of Burton, Laura Sent: Thursday, July 21, 2005 11:54 AM
Subject: Slow Database Aft Upgrade from 8i to 9.2

Can someone direct me to a place to troubleshoot your database? Is there a way to check for 'Memory Leaks'? I have a tuning book and have been running scripts to find how much is being used, what statements are the worst, etc., but is there a 'common' size for the Shared Pool, Large Pool, Sort, etc? I know it depends on the size of the database. I guess I am just looking for a starting point, i.e. if small blah, blah; if large blah blah, or large pool is ??% the size of the Shared Pool and total db memory should not be more than ??% of physical memory.

Thanks, Laura


Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

-- Received on Thu Jul 21 2005 - 14:04:46 CDT

Original text of this message