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