Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Shared Pool Tuning

RE: Shared Pool Tuning

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 28 Sep 2004 15:37:00 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB93@bosmail00.bos.il.pqe>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US