Why do my execution plans not change after gathering statistics? 

A look at Rolling Cursor Invalidations

In releases prior to Oracle 10g, the gathering of statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors. This was true unless NO_INVALIDATE parameter of the DBMS_STATS command was set to TRUE.

It was felt that gathering of statistics could actually have a negative impact on performance because of the fact that invalidation of a cached cursor due to gathering of statistics meant that it had to be hard-parsed the next time it is executed.

We know that excessive hard parsing could cause performance spikes because of the CPU usage taken by hard parse operations as well as contention for the library cache and shared pool latches.

In Oracle 10g the default for the NO_INVALIDATE parameter is now AUTO_INVALIDATE.

This means that Oracle will not immediately invalidate the cached cursors on gathering of fresh statistics, but wait for a period of time to elapse first.

This period of time is controlled by the parameter _optimizer_invalidation_period which defaults to a value of 18000 (seconds) or 5 hours.

This parameter specifies when dependent cursors cached in the library cache will get invalidated when statistics are gathered on referenced tables or indexes.

specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call.

So we may find that in some cases even after gathering statistics, the execution plan still remains the same. And it will remain the same unless the 5 hour period of time has elapsed or if the cursor had got invalidated for some other reason or if the cursor had been aged out of the shared pool and was then reloaded.

This reload would then trigger a new hard parse and consequently possibly a new execution plan as well.

Let us look at example of this case.

We create a table TESTME based on the ALL_OBJECTS view.

The data in the table is quite skewed – in my case the table had 18 distinct values for OBJECT_TYPE and there were about 55000 rows. The value SYNONYM for the OBJECT_TYPE column accounted for over 20,000 rows, but there were other values like RULE which just had 1 row.

We created an index on the OBJECT_TYPE column for the test.

SQL> create table testme as select * from all_objects;

Table created.

SQL> create index testme_ind on testme(object_type);

Index created.

Note the current value for NO_INVALIDATE is AUTO which is the default in 10g and above.

SQL> select dbms_stats.get_prefs('NO_INVALIDATE','SYSTEM','MYOBJECTS') from dual
  2  ;

DBMS_STATS.GET_PREFS('NO_INVALIDATE','SYSTEM','MYOBJECTS')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

We ran this SQL statement. Since it will return a single row, we can assume the CBO will choose the index in the execution plan.

SQL> select /*TESTME*/ distinct owner from testme where object_type='RULE';

SQL> select sql_id from v$sql where sql_text like 'select /*TESTME*/%';

SQL_ID
-------------
4a77d3s7xx1mc

SQL> select plan_hash_value,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TESTME*/%';

PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
--------------- ------------ ---------- ---------- -----------
     2890121575            0          1          1           1

We then updated the table and changed the value of OBJECT_TYPE to RULE for 25000 rows – about 50% of the table

SQL> update testme set object_type='RULE' where rownum < 25001;

25000 rows updated.

SQL> commit;

Commit complete.

After this UPDATE statement, we gather fresh statistics

SQL>  exec dbms_stats.gather_table_stats(USER,'TESTME');

PL/SQL procedure successfully completed.

We now run the same query again.

SQL> select /*TESTME*/ distinct owner from testme where object_type='RULE';

Since we are now selecting a high proportion of rows from the table, we would assume that the CBO would have chosen a full table scan instead now in place of the earlier Index scan.

But has the plan changed? – it does not appear so. The executions are now 2, but the plan hash value is still the same.

So looks like the optimizer is still using the old execution plan for this statement . It still considers the table to have only one row for the OBJECT_TYPE value RULE.

The cursor in this case could potentially remain in the shared pool for up to 5 hours with an unchanged execution plan.

SQL> select plan_hash_value,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TESTME*/%';

PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
--------------- ------------ ---------- ---------- -----------
     2890121575            0          2          1           2

SQL> select * from table(dbms_xplan.display_cursor('4a77d3s7xx1mc',null,null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  4a77d3s7xx1mc, child number 0
-------------------------------------
select /*TESTME*/ distinct owner from testme where
object_type=:"SYS_B_0"

Plan hash value: 2890121575

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |     3 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH UNIQUE                 |            |     1 |    28 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TESTME     |     1 |    28 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTME_IND |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Now let us change the value of the parameter _optimizer_invalidation_period to 60

SQL> alter system set "_optimizer_invalidation_period" = 60 scope=memory;  (default is 18000 or 5 hours)

System altered.

We gather statistics again

SQL> exec dbms_stats.gather_table_stats(USER,'TESTME');

After about a minute, execute the same statement again.

SQL> select /*TESTME*/ distinct owner from testme where object_type='RULE';

This time things are different.

We see a new child cursor as the earlier child cursor has got invalidated. The version count has also changed and we see a new plan hash value (which would indicate a changed execution plan as well)

SQL>  select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TESTME*/%';

SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
4a77d3s7xx1mc            0          2          3           2
4a77d3s7xx1mc            1          1          1           1

SQL> select version_count from v$sqlarea where sql_id='4a77d3s7xx1mc';

VERSION_COUNT
-------------
            2

SQL> select hash_value,address from v$sqlarea where sql_id='4a77d3s7xx1mc';

HASH_VALUE ADDRESS
---------- ----------------
266241644 0000000099A9BAD8

We see that the ROLL_INVALID_MISMATCH column of the V$SQL_SHARED_CURSOR view has a value ‘Y’. This is the reason the cursor has got invalidated.

ROLL_INVALID_MISMATCH indicates that the child had to be created because the original cursor could not be reused due to having been invalidated with rolling invalidation

SQL> select * from v$sql_shared_cursor where address='0000000099A9BAD8';

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
4a77d3s7xx1mc 0000000099A9BAD8 00000000915908F0            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 N N N N N N N N N N

4a77d3s7xx1mc 0000000099A9BAD8 000000008A9ED0A0            1 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 Y N N N N N N N N N N N N N N N