Home > Oracle, Performance > Cursor Invalidation

Cursor Invalidation


Got a question: What will happen a running curosr got invalidated?  SQL execution will fail or invalidation is not at all possible?

There are two components for an executing cursor.  A session cached cursor (private area where row source are getting processed) in PGA and sharable execution plan (shared excution plan)  in SGA.  When a cursor invalidated, the shared cursor in SGA will get obsoleted while the session will continue the execution.

I have below test table ‘A’


SQL> select count(*) from a;

COUNT(*)
----------
 495392

SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED
 2 from v$sql where sql_id='b4u9fstm8xsrh';

no rows selected

The SQL ID ‘b4u9fstm8xsrh’ is not currently in the shared pool.   I started the SQL from other session which is going run long time as I am using a cartesian product.


SQL> select b.owner,c.owner from a b,a c;

I can see the SQL started and progressing the execution


SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='b4u9fstm8xsrh';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED
--------------- ---------- ------------- --------------
 1              1          0             40906

SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='b4u9fstm8xsrh';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED
--------------- ---------- ------------- --------------
 1              1          0             50491

I will  add a new column to the table ‘A’ while the SELECT is executing and cursor will be invalidated.


SQL> alter table a add c1 number;

Table altered.

SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='b4u9fstm8xsrh';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED
--------------- ---------- ------------- --------------
 1              1          1             143491

SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='b4u9fstm8xsrh';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED
--------------- ---------- ------------- --------------
 1              1          1             147226

So, the cursor in SGA got invalidated and the session opened cursor continue the execution.    When the cursor invalidated, the OBJECT_STATUS in V$SQL change from VALID to INVALID_UNAUTH.   An INVALID UNAUTHORISED cursor can not be shared any more.

As long as any user is executing a cursor or more accurately if the cursor open in any session PGA, the cursor can be observed in v$sql which is populated from X$KGLCURSOR_CHILD.  Once the cursor is closed in the PGA, the entries will be removed from X$KGLCURSOR_CHILD.    That means it is completely removed from the memory, not really.    If you re-run the SQL, you can see the SQL reports previous invalidation count along with the new execution details.  If the cursor was removed from shared pool,  from where it will get the previous invalidation count?

In reality, when ever a cursor got INVALIDATED and cursor in PGA is closed, it will be removed from V$SQL and its base table X$KGLCURSOR_CHILD.  But, there will be N+1 entries kept in the X$KGLCURSOR for future reference where N is number child curors.  When ever the cursor is re-parsed, the previous INVALIDATION count will be taken from X$KGLCURSOR and report in X$KGLCURSOR_CHILD (V$SQL).

INVALIDATING a running cursor will be more interesting in 12c as optimizer can change execution plan based on the previous row source count.  If the actual row count vs estimated row is not favoring the planned execution, then oracle will change the plan for the next step – is known as Adaptive Plan.  In the above situation, it will be interesting to see, how oracle will behave when the exectuing plan itself is INVALID.  Will test it when I have 12c with me.

About these ads
Categories: Oracle, Performance Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: