(tested on 10.2.0.4.1)
I needed this today and I want to share the solution i found in Harald’s blog.
One single query started to work slow. It takes more than an hour, instead of a few seconds. An, of course, nobody touched this piece of code for months.
It was easy to see that the plan makes FTS of one of the biggest tables in the database. And I could see this only in v$sql_plan – if I try explain plan it promises to use an index.
I used SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id')); and I saw the plan gone wrong today. The old executions were using the index access. What caused this change (a bad one) – nobody knows. I did not invest much time on this. The question is how to remove the query out of the library cache, so that it could generate a new (old) indexed plan.
The most dramatic approach is alter system flush shared_pool. It’s like putting your house on fire to kill that mises inside. Yes, this plan will possibly get better, another one may get worse, and that will be combined with a lot of CPU usage until all statements get parsed again. No, thanks.
Another idea is to invalidate the cursor by running some DDL on one of the tables referenced by the query. We have plenty of options for this – like putting a comment (yes, this is a DDL). An alternative approach is to set column statistics for some column (w/o actually changing the values). But again, this is too “general”- all statements on this table will be invalidated.
Harald’s solution is much more elegant. The package dbms_shared_pool has a procedure purge, which makes what I need – removes “something” (like a cursor or a package) from the library cache. This is new in 11.1, but it is so useful, that Oracle decided to backport it to 10.2.0.4. But there are some details.
First, our dbms_shared_pool did not provide this procedure (although the database is 10.2.0.4.1). This can be fixed using:
@?/rdbms/admin/dbmspool
Then I tested it again and again – it did not work at all. The procedure executes just fine, but does not make any change. Then I started reading the comments in Harald’s blog and I found this:
alter session set events '5614566 trace name context forever';
This fixed the procedure:
select address, hash_value from v$sqlarea where sql_id = '1mqxxf66dtgdp';
exec sys.dbms_shared_pool.PURGE('0000000A5C166350, 2363276725', 'C');
The value of the third parameter (C) stands for “Cursor”. The other options are:
— Value Kind of Object to keep
— —– ———————-
— P package/procedure/function
— Q sequence
— R trigger
— T type
— JS java source
— JC java class
— JR java resource
— JD java shared data
— C cursor
This is it. It works, and it is useful.