RE: Shared Pool Tuning
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <rlsmith@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Sep 2004 15:37:00 -0400
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
Other related posts:
