Re: Bind variables peeked on first parse, but not on subsequent hard parses

From: Martin Bach <development@xxxxxxxxxxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 25 Nov 2009 10:01:50 +0000

Hmm, that's too bad.

The 10.2.0.4 option to purge a statement from the shared pool was
something I was really looking forward to using.

We have a system here that has some very unstable execution plans. When
a user phones that the batch performs poorly due to suboptimal execution
plans I usually comment on one of the tables which causes an
invalidation in the shared pool followed by a hard parse. Due to the
nature of the processing and the data distribution, that usually solves
the problem for this particular system (it's not a silver bullet :)

NB: this happens after intra-day loads which could insert anything
between 1 and 15 million rows into a partitioned table, causing stats to
be quite inaccurate.

Cheers,

Martin
--
Martin Bach
OCM 10g
http://martincarstenbach.wordpress.com

Allen, Brandon wrote:

So much for that idea - I downloaded and installed patch 5614566 successfully, ran 
dbmspool.sql to recreate the dbms_shared_pool package with the purge procedure, set event 
5614566 as instructed in MOS 751876.1 and restarted the instance.  When I ran the purge 
procedure, it said it executed successfully, however the query still stayed in the shared 
pool, and then when I tried to run purge again it failed with "ORA-6558: buffer in 
dbms_pipe is full", so I gave up at that point.  I'll have to find another way to 
test . . .


-----Original Message-----
From: Allen, Brandon

Unfortunately, dbms_shared_pool.purge doesn't exist in 10.2.0.2 so I'll have to 
install a patch to add it and do some more testing

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l



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

Follow-Ups:
- Re: Bind variables peeked on first parse, but not on subsequent hard parses
  - From: Kerry Osborne

Re: Bind variables peeked on first parse, but not on subsequent hard parses

Other related posts: