Problems with huge shared_pool_size
- From: "Nahata, Naveen (US - Glen Mills)" <nnahata@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 15 Feb 2006 13:40:59 -0500
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:
