滚动游标失效(Rolling Cursor Invalidations)

在Oracle 10g中DBMS_STATS包针对GATHER_TABLE/INDEX_STATS和DELETE_TABLE/INDEX_STATS等收集统计信息的存储过程提供了AUTO_INVALIDATE选项；该参数允许用户指定是否让那些对统计信息有依存关系的游标失效，举例来说如果SQL游标涉及到的表，索引，列或固有对象的统计信息收到以上存储过程修改时，使用NO_INVALIDATE选项可以指定是否让这些受到影响的游标失效，何时失效。 NO_INVALIDATE选项可以有以下三种值:

TRUE : 不让相关游标失效
FALSE: 立即让相关游标失效
AUTO_INVALIDATE(default):让Oracle自己决定何时让游标失效。

--   no_invalidate - Do not invalide the dependent cursors if set to TRUE.
--      The procedure invalidates the dependent cursors immediately
--      if set to FALSE.
--      Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
--      invalidate dependend cursors. This is the default. The default
--      can be changed using set_param procedure.

当统计信息为DBMS_STATS包所修改，新的尚未在共享池中缓存的游标将直接使用这些统计信息；对于已经存在的共享池中游标缓存，我们无法在原始子游标的基础上更新它们的执行计划；这些旧的子游标将被新的参考最新统计信息的子游标替代，这个过程包含一次硬解析以便获得新的优化树和执行计划；换而言之传统的立即游标失效(Immediate Cursor Invalidation)就是在统计信息更新后立即导致原始子游标的失效，而我们所说的滚动游标失效(Rolling Cursor Invalidations)是在统计信息成功更新的前提下保证原始子游标不立即失效；设想如果系统中有一张业务相关表，一旦我们更新了该表的统计信息可能导致大量共享失效，短期内硬解析将十分频繁并占用大量cpu，而且很多时候我们并不期望执行计划有显著变化；为了防止dbms_stats包统计信息时不要越帮越忙，就可以考虑到使用NO_INVALIDATE选项。我们来看看RCI的具体表现:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

/* 测试使用版本10.2.0.4 */

SQL> create table MACLEAN (t1 int);
Table created.

SQL>  select /* cache_me */ 1 from MACLEAN;
no rows selected

SQL> select sql_text,sql_id,invalidations,parse_calls from v$sql where sql_text like '%cache_me%' and sql_text not like '%v$sql%';

SQL_TEXT                                                                         SQL_ID        INVALIDATIONS PARSE_CALLS
-------------------------------------------------------------------------------- ------------- ------------- -----------
 select /* cache_me */ 1 from MACLEAN                                            0728m2fz7yw9f             0           1

SQL> exec dbms_stats.gather_table_stats(NULL,'MACLEAN',no_invalidate => FALSE);
PL/SQL procedure successfully completed

/* 以FORCE VALIDATE形式强制让游标失效 */

SQL> select sql_text,sql_id,invalidations,parse_calls from v$sql where sql_text like '%cache_me%' and sql_text not like '%v$sql%';

no rows selected

SQL> select /* cache_me */ 1 from MACLEAN;

         1
----------

SQL> select sql_text,sql_id,invalidations,parse_calls from v$sql where sql_text like '%cache_me%' and sql_text not like '%v$sql%';

SQL_TEXT                                                                         SQL_ID        INVALIDATIONS PARSE_CALLS
-------------------------------------------------------------------------------- ------------- ------------- -----------
 select /* cache_me */ 1 from MACLEAN                                            1q2vdmcu1pr6j             1           1

/* 可以看到这里INVALIDATIONS次数上升到1 */

SQL> exec dbms_stats.gather_table_stats(NULL,'MACLEAN',no_invalidate => TRUE);

PL/SQL procedure successfully completed

/* 当我们选择NO_INVALIDATE为TRUE时，游标不会因其依赖的统计信息过期而失效 */

SQL> select sql_text,sql_id,invalidations,parse_calls from v$sql where sql_text like '%cache_me%' and sql_text not like '%v$sql%';

SQL_TEXT                                                                         SQL_ID        INVALIDATIONS PARSE_CALLS
-------------------------------------------------------------------------------- ------------- ------------- -----------
 select /* cache_me */ 1 from MACLEAN                                            1q2vdmcu1pr6j             1           1

 /* 当我们使用默认值又会如何呢? * /

 SQL> alter system flush shared_pool;
System altered

SQL> col name for a35;
SQL> col value for a20;
SQL> col describ for a60;

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_optimizer%invalid%';

NAME                                VALUE                DESCRIB
----------------------------------- -------------------- ------------------------------------------------------------
_optimizer_invalidation_period      18000                time window for invalidation of cursors of analyzed objects

/* 当使用默认选项AUTO_INVALIDATE时，Oracle会在由隐式参数_optimizer_invalidation_period所指定的时间后让游标失效 */

SQL> alter system set "_optimizer_invalidation_period"=1;
System altered.

SQL> drop table MACLEAN;
Table dropped.

SQL> create table MACLEAN AS select * from dba_objects;
Table created.

SQL> alter system flush shared_pool;
System altered.

SQL> select count(*) from MACLEAN;

  COUNT(*)
----------
     50776

SQL>  select sql_id,invalidations,parse_calls from v$sql where sql_text='select count(*) from MACLEAN';

SQL_ID        INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
4n922u3xbqp8d             0           1

SQL> exec dbms_stats.gather_table_stats('MACLEAN','MACLEAN',no_invalidate => dbms_stats.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.

/*  实际测试中可以发现并不像预期的那样如_optimizer_invalidation_period为1则1s内生效，
     大约有一个最短period在60s左右，这很令人不愉悦！
*/

SQL> !sleep 30

/* 休眠30秒 */

SQL> select count(*) from MACLEAN;
  COUNT(*)
----------
     50776

SQL> select sql_id,invalidations,parse_calls from v$sql where sql_text='select count(*) from MACLEAN';

SQL_ID        INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
4n922u3xbqp8d             0           2
4n922u3xbqp8d             0           1

SQL> select child_number,roll_invalid_mismatch from V$SQL_SHARED_CURSOR where sql_id='4n922u3xbqp8d';

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

/* 可以看到多出了一个子游标，child_number为1的子游标因为roll_invalid_mismatch而无法共享 */

SQL> alter system set "_optimizer_invalidation_period"=18000;
System altered.

默认的AUTO_INVALIDATE选项真的能够帮到我们吗？理论上相关的游标缓存仍会在_optimizer_invalidation_period指定的时间后同时失效进而引发大规模的硬解析，似乎这一特性并不能帮助我们完全避免因游标集体失效而可能造成的性能问题，当然它仍会是有益的！