RE: Shared Pool Tuning

Ron,

Frequent commits are not the solution for shared pool problems, and in fact, frequent commits may cause other problems. Allow your transaction size and commit frequency be determined by a logical interpretation of your business rules. =20

As to shared pool sizing and analysis, some things to consider: 1.) How do you know it's shared pool size that's hurting you? What have you done to ensure you've correctly identified the=20 problem? Are you seeing symptoms like ORA-4031? What else do you see?

2.) Are you using bind variables? This is absolutely critical for efficient shared pool sizing and utilization.

3.) If lack of binds is not a problem, why is the size increasing? Are you seeing lots of child cursors in=20 V$SQL? If so, why? Investigating V$SQL_SHARED_CURSOR may be informative.

4.) Has your application changed recently? Is it=20 sending the database a larger number of unique SQL statements? Perhaps you simply need to consider increasing the shared_pool_size parameter.

4.) As Ruth mentioned, flushing the shared pool *might* be a stop-gap measure. I hesitate to mention it at all, because it can sometimes cause more damage than do good. It's really not a long-term solution. I would concentrate on the points raised in #1-#3 above.

Hope that helps,

-Mark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Smith, Ron L. Sent: Tuesday, September 28, 2004 2:59 PM To: oracle-l_at_freelists.org
Subject: Shared Pool Tuning

Our shared pool is filling up and slowing down batch updates. Is there a way to correct this problem besides frequent commits, or enlarging the shared pool?

Ron=3D20
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2004 - 14:32:38 CDT