This functionality has been backported to 10.2.0.4 and seems to work fine. You do have to set an event to get it to work though (see my previous post).
If you want to flush a single statement in versions prior to 10.2.0.4 you can create an outline on the statement and then drop the outline. This works most of the time.
There are short posts on both these techniques on my blog. Fairlie Rego's blog also has good post on the purge procedure. Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Nov 25, 2009, at 4:01 AM, Martin Bach wrote:
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 executionplans I usually comment on one of the tables which causes an invalidation in the shared pool followed by a hard parse. Due to thenature of the processing and the data distribution, that usually solvesthe problem for this particular system (it's not a silver bullet :) NB: this happens after intra-day loads which could insert anythingbetween 1 and 15 million rows into a partitioned table, causing stats tobe 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, BrandonUnfortunately, 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
-- //www.freelists.org/webpage/oracle-l
