dbms_stats Invalidation

While working on a demo a while back I discovered the hard way that things do indeed change! Of course this is nothing new but by sharing my experience I hope to avoid others re-inventing the same wheel and to learn you something that you might not already know.

The demo I was working on is used to demonstrate how bind peeking works, the problems it can cause and how the Oracle11g Adaptive Cursor Sharing feature solves these problems. In this demo I call DBMS_STATS to create a histogram and I expected that dependent cursors would be marked INVALID afterwards but this simply didn’t happen. I almost lost my believe/confidence in myself and Oracle ;-)

Somehow I forgot, or maybe completely missed, the fact that cursors are invalidated in a rolling fashion since the introduction of Oracle10g. Before Oracle10g cursors were marked INVALID immediately by DBMS_STATS (or ANALYZE .. STATISTICS).

Before diving into the Oracle10g behavior, we start by looking at the pre-Oracle10g way of invalidating cursors.

Immediate Cursor Invalidation

Lets start by creating a table, gather object statistics on it, execute a query against it, grab its SQL_ID and take a look at the child cursor in the library cache:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> create table foo as select * from dba_users;

Table created.

SQL> exec dbms_stats.gather_table_stats(null,'foo')

PL/SQL procedure successfully completed.

SQL> select count(*) from foo;

  COUNT(*)
----------
        13

SQL> select prev_sql_id sql_id
  2  from v$session where sid = sys_context('userenv', 'SID');

SQL_ID
-------------
5dkz0zau0v8yh

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          1           1          1             0

The above shows that there is one child cursor in the library cache that has been hard parsed and executed once. See what happens if we execute the query again:

SQL> select count(*) from foo;

  COUNT(*)
----------
        13

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          2           2          1             0

As shown above we see that the child cursor was soft parsed and executed again. The next step is to gather object statistics again using DBMS_STATS and let DBMS_STATS invalidate dependant cursors by setting the parameter NO_INVALIDATE to FALSE:

SQL> exec dbms_stats.gather_table_stats(null,'foo',no_invalidate => false)

PL/SQL procedure successfully completed.

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          2           2          1             1

Looking at the child cursor we see that it has been marked INVALID but hasn’t been parsed again. The parse will be performed on a next execution as shown below:

SQL> select count(*) from foo;

  COUNT(*)
----------
        13

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          1           1          2             1

Based on the output above we can conclude that the child cursor has been hard parsed again, after being marked INVALID, and that this new incarnation of the child cursor has been executed once. All of the above will also happen if you still use the deprecated ANALYZE .. STATISTICS command instead of DBMS_STATS.

SQL> analyze table foo compute statistics;

Table analyzed.

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          1           1          2             2

Again we see that the child cursor has been marked INVALID but hasn’t been parsed again. The parse will occur on the next execution:

SQL> select count(*) from foo;

  COUNT(*)
----------
        13

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          1           1          3             2

Like we concluded in the case of DBMS_STATS, we can conclude that the child cursor has been hard parsed again and re-executed after being marked INVALID.

This behavior. was what I expected while working on my Oracle11g demo.

Now that we have a basic understanding of how things worked in the past, it is time to take a look at how cursor invalidations work in more recent versions of Oracle.

Rolling Cursor Invalidation

Starting with Oracle10g cursors are marked for rolling invalidation instead of marked INVALID immediately. On the next execution of the query the Oracle server will generate a random number between 0 and the value of the _optimizer_invalidation_period parameter, which has a default value of 18000, and the cursor will remain valid for this number of seconds. Upon every following execution Oracle will check if this random selected timeout has expired. If that is the case then the cursor will be hard parsed again. Before demonstrating rolling cursor invalidation, we’ll lower the value of _optimizer_invalidation_period in order to avoid long waiting times:

SQL> alter system set "_optimizer_invalidation_period" = 300 scope=memory;

System altered.

SQL> exec dbms_stats.gather_table_stats(null,'foo',no_invalidate => DBMS_STATS.AUTO_INVALIDATE)

PL/SQL procedure successfully completed.

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          1           1          3             2

Instead of marking the child cursor INVALID immediately, it is now marked for rolling invalidation behind the scenes. Nothing will happen with respect to cursor invalidation unless we execute the query again:

SQL> select count(*) from foo;

  COUNT(*)
----------
        13

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          2           2          3             2

We can’t look behind the scenes but the random invalidation timeout has been generated and the child cursor will remain valid. The query will be executed without a parse as long as this timeout hasn’t been expired. In order to see what happens when the timeout is over we have to wait at least _optimizer_invalidation_period seconds.:

SQL> !sleep 300

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          2           2          3             2

Despite the fact that we know the timeout has expired the child cursor is not marked INVALID and as a matter of fact it won’t at all. Lets see what happens when we execute the query again now that the timeout is over:

SQL> select count(*) from foo;

  COUNT(*)
----------
        13

SQL> select child_number,executions,parse_calls,loads,invalidations
  2  from v$sql where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------ ---------- ----------- ---------- -------------
           0          2           2          3             2
           1          1           1          1             0

Instead of hard parsing the child cursor again as shown before, we now see another child cursor showing up in the library cache. This new child cursor was hard parsed and executed once. The reason for this new child can be found in V$SQL_SHARED_CURSOR as shown below:

SQL> select child_number,ROLL_INVALID_MISMATCH from v$sql_shared_cursor where sql_id = '5dkz0zau0v8yh';

CHILD_NUMBER R
------------ -
           0 N
           1 Y

This indicates that the new child was born because the previous child had a mismatch due to a rolling cursor invalidation.

Before finishing this blog post we need to set the _optimizer_invalidation_period parameter back to its default value:

SQL> alter system set "_optimizer_invalidation_period" = 18000 scope=memory;

System altered.

Conclusion

Things will change without notice! ;-) Cursor invalidation has changed in the past and the reason for this change is that previously all
dependent cursors were invalidated immediately after gathering new object statistics. This massive invalidation might cause a serious
degradation in performance right after statistics gathering due to a high number of hard parses. Rolling cursor invalidation spreads the invalidation of cursors out over a longer period of time thereby avoiding the performance
degradation caused by hard parsing the invalidated cursors.

Reference: Metalink note:557661.1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-708005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-708005/