RE: Problems with huge shared_pool_size

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, http://www.ixora.com.au/ 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
 

--

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:

• Dividends you received on any share of stock that you held for less than 61 days during the 121-day period that began 60 days before the ex-dividend date. The ex-dividend date is the first date following the declaration of a dividend on which the purchaser of a stock is not entitled to receive the next dividend payment. When counting the number of days you held the stock, include the day you disposed of the stock but not the day you acquired it. See the examples below. Also, when counting the number of days you held the stock, you cannot count certain days during which your risk of loss was diminished. See Pub. 550 for more details."

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nahata, Naveen (US - Glen Mills)
Sent: Wednesday, February 15, 2006 1:41 PM To: oracle-l_at_freelists.org
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 9.2.0.6 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?

Naveen

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]

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 15 2006 - 13:25:31 CST