Re: Shared Pool Tuning
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Sep 2004 13:45:10 -0700
Have you tried cursor_sharing = force? --Terry ----- Original Message ----- We are using compiled code that cannot change. It is not using reusable code. It is doing thousands of inserts across a dblink which is generating a new sql statement for every insert. The job crawls until we do a flush. Then it runs very fast. =20 Ron -----Original Message----- From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]=20 Sent: Tuesday, September 28, 2004 2:37 PM To: Smith, Ron L.; oracle-l@xxxxxxxxxxxxx Subject: 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Smith, Ron L. Sent: Tuesday, September 28, 2004 2:59 PM To: oracle-l@xxxxxxxxxxxxx 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 -- http://www.freelists.org/webpage/oracle-l
- RE: Shared Pool Tuning
- From: Smith, Ron L.
- RE: Shared Pool Tuning