RE: Problems with huge shared_pool_size
- From: "Nahata, Naveen (US - Glen Mills)" <nnahata@xxxxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 15 Feb 2006 14:42:52 -0500
Thanx Mark,
I can't find the bug or the TAR reference right now but the bug was not
fixed until 10g.
We tried hard to get a one-off patch but the support said that it was
impossible since the fix could not be backported.
Naveen
________________________________
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Wednesday, February 15, 2006 2:26 PM
To: Nahata, Naveen (US - Glen Mills); oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nahata, Naveen (US -
Glen Mills)
Sent: Wednesday, February 15, 2006 1:41 PM
To: oracle-l@xxxxxxxxxxxxx
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]
Other related posts:
