How to invalidate a curosr?

I read from somewhere that issuing harmless DDL, for examples COMMENT and GRANT statements, could be a good way to invalidate a cursor. I have not got chance to verify it until today. I set up a test and find that the following method can invalidate a cursor.

- flush the shared_pool
- grant: i.e. grant select on t to system;
- comment: i.e. comment on table t is 'I am a table';
- dbms_stats.gather_table_stats (no_invalidate should not be true);

Of course if I add a new column to a table, I am sure I can invalidate the cursor too, but this is not a choice obviously in the middle of day, even I do want to add a column. This is not a harmless DDL.

Below is the test abount 'COMMENT':

(1) I first created a table with one row having value 1 and 1999 rows having value 99 for its only column id. I had a histogram on that columne collected. So if I have predicate id=1, index scan should be used and if I have id=99, full table scan is preferred.

denis@TESTDB920> create table t
  2  as
  3  select
  4   case when rownum=1
  5   then 1
  6   else 99
  7   end id,
  8   a.*
  9    from all_objects a
 10  where rownum <=2000 
 11  / 
Table created. 
denis@TESTDB920> create index t_idx on t(id);

Index created.

denis@TESTDB920> begin
  2   dbms_stats.gather_table_stats
  3   (user,
  4    'T',
  5    method_opt=>'for all indexed columns size 254');
  6  end;
  7  /

PL/SQL procedure successfully completed.

(2) I then executed a SQL statement twice with different bind variable values, using value 1 first.

denis@TESTDB920> variable id number
denis@TESTDB920> exec :id := 1

PL/SQL procedure successfully completed.

denis@TESTDB920> select /* denis */ sum(object_id) from t where id= :id;

SUM(OBJECT_ID)
--------------
            97

denis@TESTDB920>
denis@TESTDB920> exec :id := 99

PL/SQL procedure successfully completed.

denis@TESTDB920> select /* denis */ sum(object_id) from t where id= :id;

SUM(OBJECT_ID)
--------------
       6361608

denis@TESTDB920> select hash_value, address, child_number, executions, plan_hash_value, invalidations from
  2  v$sql where sql_text like 'select%denis%' and sql_text not like 'select hash_value%';

HASH_VALUE ADDRESS          CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE INVALIDATIONS
---------- ---------------- ------------ ---------- --------------- -------------
1403451991 000000039F798070            0          2      3178687684             0

(3) Now I invalidated the cursor by executiong a COMMENT stateemnt, noticed from v$sql: invalidations=1

denis@TESTDB920> comment on table t is 'I am ok';

Comment created.

denis@TESTDB920>
denis@TESTDB920> select hash_value, address, child_number, executions, plan_hash_value, invalidations from
  2  v$sql where sql_text like 'select%denis%' and sql_text not like 'select hash_value%';

HASH_VALUE ADDRESS          CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE INVALIDATIONS
---------- ---------------- ------------ ---------- --------------- -------------
1403451991 000000039F798070            0          0               0             1

(4) Next I executed the same sql statement 4 times. Since the cursor was invalidated, Oracle would need to re-parse the sql. As I first used bind value 99 as compared to 1 in the step 2, as expected the cursor got exected with different plan, notice the plan hash value is different from that in the step 2

denis@TESTDB920>
denis@TESTDB920> exec :id := 99

PL/SQL procedure successfully completed.

denis@TESTDB920> select /* denis */ sum(object_id) from t where id= :id;

SUM(OBJECT_ID)
--------------
       6361608

denis@TESTDB920> select /* denis */ sum(object_id) from t where id= :id;

SUM(OBJECT_ID)
--------------
       6361608

denis@TESTDB920> select /* denis */ sum(object_id) from t where id= :id;

SUM(OBJECT_ID)
--------------
       6361608

denis@TESTDB920> select /* denis */ sum(object_id) from t where id= :id;

SUM(OBJECT_ID)
--------------
       6361608

denis@TESTDB920> select hash_value, address, child_number, executions, plan_hash_value, invalidations from
  2  v$sql where sql_text like 'select%denis%' and sql_text not like 'select hash_value%';

HASH_VALUE ADDRESS          CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE INVALIDATIONS
---------- ---------------- ------------ ---------- --------------- -------------
1403451991 000000039F798070            0          4      1842905362             1

update 10/21/2013 -

we can use: sys.dbms_shared_pool.purge  procedure to purge a particular cursor. For example:

SQL> select address, hash_value from v$sqlarea
  2  where sql_text = 'select count(c2) from skew where c1 = :x';
ADDRESS  HASH_VALUE
-------- ----------
27308318 2934790721

SQL> exec sys.dbms_shared_pool.purge('&address, &hash_value','c')
PL/SQL procedure successfully completed.

see : http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/