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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
