在分析表的是否有一个参数no_invalidate：缺省值是DBMS_STATS.AUTO_INVALIDATE.AUTO_INVALIDATE。
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游标。
--说明一下，我个人感觉这个参数理解起来很烦，validate表示有效，no_invalidate反了2次,也是表示有效的意思。
dbms_stats收集统计信息时候no_invalidate参数
用于是否与收集相关object的cursor失效，defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true：当收集完统计信息后，收集对象的cursor不会失效（不会产生新的执行计划，子游标）
false:当收集完统计信息后，收集对象的cursor会立即失效（新的执行计划，新的子游标）
no_invalidate=>DBMS_STATS.AUTO_INVALIDATE，分析表后，游标不会马上invalidate，已经存在的SQL的执行计划不会受新的统计信息影响。可以手工
DDL invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,
Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。
1.建立测试环境:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t as select rownum id , 'test' name from dual connect by levelSQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
SQL> select count(*) from t;
COUNT(*)
----------
64
--获取sql_id
SQL> @dpc
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T | 64 | 3 (0)|
---------------------------------------------------------
sqlid='cyzznbykb509s'
2.测试1(no_invalidate => false):
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 1 0
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => false);
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 1 1
--分析后no_invalidate => false,v$sql 的INVALIDATIONS=1.光标失效。
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 2 1
3.测试2(no_invalidate => true):
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => true);
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 2 1
--分析后no_invalidate => true,v$sql 的INVALIDATIONS=1（没有变化与上次一样）.说明光标没有失效。
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 2 2 2 1
--再次执行查询，发现PARSE_CALLS增加了1次，loads没有变化。
4.测试3（no_invalidate => DBMS_STATS.AUTO_INVALIDATE):
缺省隐藏参数_optimizer_invalidation_period设置的时间太长=18000（5个小时），我缩短一些。
SQL> alter system set "_optimizer_invalidation_period" = 300 scope=memory;
System altered.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 2 2 2 1
--马上执行，select count(*) from t;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 3 3 2 1
--可以发现v$sql 的INVALIDATIONS=1（没有变化与上次）.说明光标没有失效。执行计划以及使用原来的光标。
--等一段时间300秒，再测试：
SQL> host sleep 300
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 3 3 2 1
cyzznbykb509s 1 1 1 1 0
--可以发现原来的光标无效，生成新的子光标。看看为什么不能共享？
SQL> @share cyzznbykb509s
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''cyzznbykb509s''',
SQL_TEXT = select count(*) from t
SQL_ID = cyzznbykb509s
ADDRESS = 000000009353C428
CHILD_ADDRESS = 0000000093623B88
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select count(*) from t
SQL_ID = cyzznbykb509s
ADDRESS = 000000009353C428
CHILD_ADDRESS = 000000009362C7E0
CHILD_NUMBER = 1
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
--原来的光标无效，是由于ROLL_INVALID_MISMATCH。最后修改隐含参数回来。
SQL> alter system set "_optimizer_invalidation_period" = 18000 scope=memory;
System altered.
总结：
缺省分析DBMS_STATS.AUTO_INVALIDATE，如果处理不好，一些性能问题会延迟出现，在优化时注意。
share脚本如下：
SET serveroutput on size 100000;
DECLARE
c NUMBER;
col_cnt NUMBER;
col_rec DBMS_SQL.desc_tab;
col_value VARCHAR2 (4000);
ret_val NUMBER;
BEGIN
c := DBMS_SQL.open_cursor;
DBMS_SQL.parse
(c,
'select q.sql_text, s.*
from v$sql_shared_cursor s, v$sql q
where s.sql_id = q.sql_id
and s.child_number = q.child_number
and q.sql_id like ''&1''',
DBMS_SQL.native
);
DBMS_SQL.describe_columns (c, col_cnt, col_rec);
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.define_column (c, idx, col_value, 4000);
END LOOP;
ret_val := DBMS_SQL.EXECUTE (c);
WHILE (DBMS_SQL.fetch_rows (c) > 0)
LOOP
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.COLUMN_VALUE (c, idx, col_value);
IF col_rec (idx).col_name IN
('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER',
'SQL_TEXT')
THEN
DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)
|| ' = '
|| col_value
);
ELSIF col_value = 'Y'
THEN
DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)
|| ' = '
|| col_value
);
END IF;
END LOOP;
DBMS_OUTPUT.put_line
('--------------------------------------------------');
END LOOP;
DBMS_SQL.close_cursor (c);
END;
/
SET serveroutput off;