Statistics gathering & Cursor invalidation

Does gathering the statistics for tables invalidates the dependent cursors?

Lets take a simple query
=====================

SQL> select count(*) from emp;
COUNT(*)
———-
15

SQL> select sql_id, sql_text from v$sqlarea where sql_text like ‘select %’ and parsing_schema_name = ‘SCOTT’
/

SQL_ID SQL_TEXT
—— ——–
g59vz2u4cu404 select count(*) from emp

SQL> select * from v$sql_shared_cursor where sql_id=’g59vz2u4cu404′
/

SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
————- —————- —————- ———— – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
g59vz2u4cu404 0700000059879D88 070000005916A880 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

Now let’s compute the statistics by using OLD approach

SQL> analyze table emp compute statistics;

Table analyzed.

What happened?
===============

SQL> select sql_id, sql_text from v$sqlarea where sql_text like ‘select %’ and parsing_schema_name = ‘SCOTT’
/
no rows selected

SQL> select * from v$sql_shared_cursor where sql_id=’g59vz2u4cu404′
/
no rows selected

++++++++++++ Cursor is flushed out ++++++++++++++++++

Now try same thing, but using DBMS_STATS

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’SCOTT’,TABNAME=> ‘EMP’);

PL/SQL procedure successfully completed.

SQL> select sql_id, sql_text from v$sqlarea where sql_text like ‘select %’ and parsing_schema_name = ‘SCOTT’
/
SQL_ID SQL_TEXT
—— ——–
g59vz2u4cu404 select count(*) from emp

SQL> select * from v$sql_shared_cursor where sql_id=’g59vz2u4cu404′
/
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
————- —————- —————- ———— – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
g59vz2u4cu404 0700000059879D88 070000005916A880 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

++++++++++++ Cursor is still in valid state ++++++++++++++++++

So what exactly happened?
======================

This behaviour is controlled by NO_INVALIDATE parameter of DBMS_STATS.GATHER_TABLE_STATS procedure.

The default value of this parameter can be checked by

SQL> select dbms_stats.get_param(‘no_invalidate’) from dual
/
DBMS_STATS.AUTO_INVALIDATE

AUTO_INVALIDATE (i.e. Oracle will decide, when to invalidate) will invalidate the cursor after a default value of 18000 seconds i.e. 5 hours. This will restrict the cursors getting invalidated immediately (which will avoid hard parse). This process is named as ‘Rolling Cursor Invalidations’.

This interval can be changed by setting the hidden parameter _optimizer_invalidation_period.

Once the cursor is invalidated, value of ROLL_INVALID_MISMATCH column in v$sql_shared_cursor will be changed to ‘Y’. Next call to the sql statement will be a hard parse.

We can also set the value of NO_INVALIDATE to TRUE, which will prevent cursor invalidations and would continue using existing execution plans until hard-parsed.

SQL> exec DBMS_STATS.SET_PARAM(‘NO_INVALIDATE’,’FALSE’);

This approach may generate sub-optimal plans, as existing cursors will not be using new statistics.

Advertisements
This entry was posted in Oracle Database Architecture and tagged . Bookmark the permalink.

2 Responses to Statistics gathering & Cursor invalidation

  1. Thanks! Great description of the roll_invalid_mismatch!

  2. Renuka says:

    Thanks, Good description

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