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/

Some concepts about Shared pool, lock and latches

In an interview, I was asked a question about how Oracle protects the shared pool if multiple users need to access and modify same memory location. (the exact question I can not recall, but it is about locks/latches). I failed to explain well.

Here are some reading notes about this topic.

What is "library cache lock"?

Oracle document describes "library cache lock" as one of the wait events as follows:

This event controls the concurrency between clients of the library cache.
It acquires a lock on the object handle so that either:
- One client can prevent other clients from accessing the same object
- The client can maintain a dependency for a long time which does
not allow another client to change the object

This lock is also obtained to locate an object in the library cache.


Library cache
* includes
- shared SQL areas
- private SQL areas
- PL/SQL procedures and packages
- Control structures: locks and library cache handles

Each SQL statment
* represented by
- a shared SQL area
- a private SQL area

Shared SQL area
* contains
- parse tree
- execution plan

Private SQL Area
* divided into
- persistent area: e.g. bind information
- run-time area
* located
- PGA or
- SGA (in the case of shared server)


Here is an article explaining the Oracle latch: http://www.pafumi.net/Oracle_Latches.html


Shared pool latch and libary cache latch

In the v$latch view, there are latch names such as shared pool and library cache:

Someone explained:

- Shared pool latch:

Shared pool latch is acquired to request/release space from the
shared pool free memory area and released immediately after that.

- Library cache latch

Library cache latch is held while searching for a parent cursor and
subsequent child cursor with similar attributes with parent cursor
pinned.