Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problems with huge shared_pool_size

RE: Problems with huge shared_pool_size

From: Bobak, Mark <>
Date: Wed, 15 Feb 2006 14:25:31 -0500
Message-ID: <>

First, get Steve Adams' book, Oracle 8i Internal Services. Read it thoroughly, particularly the shared pool section of Chapter 6: Memory.  

He mentions a script available on his website, called shared_pool_lru_stats.sql which can be helpful in determining if the shared pool is oversized. The chapter of the book mentioned above does a nice job discussing the output of that script, and how it may be interpreted.  

Also, as for setting _kghdsidx_count = 1, well, I have some significant concerns there. Do you know, specifically, what bug you are supposedly working around? I'd open a TAR (sorry, SR) with Oracle and ask them to validate whether you should still be setting that, based on the version of Oracle, patches applied, etc. If you're artificially limiting the number of shared pool latches to 1, then it seems reasonable that latch contention would increase, particularly as the size of the shared pool increases, but the number of shared pool latches remains limited to 1.  

Hope that helps,  



Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"Exception: Some dividends may be reported as qualified dividends but are not qualified dividends. These include:

  --IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends  

[] On Behalf Of Nahata, Naveen (US - Glen Mills)
Sent: Wednesday, February 15, 2006 1:41 PM To:
Subject: Problems with huge shared_pool_size

Hi All,

Sorry for a big email, but I'm also providing a small history so that you can understand the non-technical part of the issue as well.

We have a huge shared pool (7 GB). I know shared pools are typically larger in Oracle Apps environment (we are on Oracle Apps 11.5.8 DB HP-UX) but 7GB sounds absurd to me.

This shop used to have near hangs due to latch free (because of literal SQLs swamping the system) and the DBA here considered it wise to keep increasing the shared pool size by a Gig every time a hang happened, resulting in this monstrous shared pool.

Since then CURSOR_SHARING=exact was used as a workaround to get rid of hard parsing and it resulted in lack of "latch free" waits and this is being attributed to the increase in shared pool.

Even now, occasionally I see lot of latch free waits for library cache and shared pool latches. I know this is because of the large size of shared pool along with the undocumented parameter (_kghsidx_count=1), which sets the no. of child shared pool latches to 1 (Suggested by oracle as a workaround for a bug).

Given this situation, I need to convince the management to reduce the size of shared pool. I can show them the occasional latch free's as a reason to do it, but they believe that the cure is to increase the size because it has worked in the past.

Any ideas on what other metrics I can generate to prove that a huge shared pool is not really desirable?


This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message.

Any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. [v.E.1]

-- Received on Wed Feb 15 2006 - 13:25:31 CST

Original text of this message