玉面飞龙的BLOG

DBMS_STATS包的很多子过程都有这么一个option，来决定是否马上invalidate相关对象的游标。

no_invalidate

Does not invalidate 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 dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure

10g中默认是AUTO_INVALIDATE，就是说分析表后，游标不会马上invalidate，已经存在的SQL的执行计划不会受新的统计信息影响。可以手工DDL invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。

如果想要dbms-stats分析立马见效,需要使用no_invalidate=false option或者DBA自己手工invalidate游标。

该参数为60；表示60秒后,新的统计信息将起作用。

SQL> show parameters optimizer_invalidation
NAME                            VALUE
————————————–
_optimizer_invalidation_period    60

SQL> select sql_text,LOADED_VERSIONS,EXECUTIONS,loads,INVALIDATIONS,PARSE_CALLS from v$sql
where sql_text=’select * from test_table where object_id=1′;

SQL_TEXT
—————————————————————-
LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS
————— ———- ———- ————- ———–
select * from test_table where object_id=1
1          1          1             0           1

SQL> select * from test_table where object_id=1;
no rows selected

SQL> select sql_text,LOADED_VERSIONS,EXECUTIONS,loads,INVALIDATIONS,PARSE_CALLS from v$sql
where sql_text=’select * from test_table where object_id=1′;

SQL_TEXT
———————————————————————
LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS
————— ———- ———- ————- ———–
select * from test_table where object_id=1
1          2          1             0           2

SQL> select * from test_table where object_id=1;
no rows selected

SQL> /
no rows selected

SQL> /
no rows selected

SQL> select sql_text,LOADED_VERSIONS,EXECUTIONS,loads,INVALIDATIONS,PARSE_CALLS from v$sql
where sql_text=’select * from test_table where object_id=1′;
SQL_TEXT
—————————————————————–
LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS
————— ———- ———- ————- ———–
select * from test_table where object_id=1
1          5          1             0           5

10g中no_invalidate默认为AUTO_INVALIDATE

SQL> exec dbms_stats.gather_table_stats(user,’test_table’);
PL/SQL procedure successfully completed.

大概等待60秒后，重新执行同样的SQL

SQL> select * from test_table where object_id=1;
no rows selected

SQL> /
no rows selected

SQL> /
no rows selected

SQL> select sql_text,LOADED_VERSIONS,EXECUTIONS,loads,INVALIDATIONS,PARSE_CALLS from v$sql
where sql_text=’select * from test_table where object_id=1′;

SQL_TEXT
—————————————————————-
LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS
————— ———- ———- ————- ———–
select * from test_table where object_id=1
1          5          1             0           5

select * from test_table where object_id=1
1          3          1             0           3

发现又多了一个子游标，执行了3次。但是原先的游标并没有标记为invalidate,它的INVALIDATIONS列仍然是0。

察看为什么有2个子游标，发现ROLL_INVALID_MISMATCH列不同。在这种情况下，Oracle内部有特殊的处理。

SQL> select sql_id,CHILD_ADDRESS,CHILD_NUMBER,ROLL_INVALID_MISMATCH from
v$sql_shared_cursor where sql_id=’57vcxy0xkw7bb’;

SQL_ID             CHILD_ADDRESS    CHILD_NUMBER ROL
—————— —————- ———— —
57vcxy0xkw7bb      00000003CDE80D78            0 N
57vcxy0xkw7bb      00000003CD98B5E8            1 Y