ORACLE统计信息与执行计划

lyl801013

10G之后统计信息收集后为什么执行计划不会被立马淘汰在10G之前，使用DBMS_STATS收集统计信息将会导致与此对象相关的游标失效，下次执行此的时候将会进行HARD PARSE，除非收集的时候NO_INVALIDATE设置为TRUE。由于硬解析会消耗大量的CPU，还会导致大量的library cache 和 shared pool 的LATCH竞争，因此如果由于统计信息收集导致大量的的游标失效，可能会带来HARD PARSE风暴，造成系统的负担。但是如果采用NO_INVALIDATE=TRUE的方法，由于游标不失效，游标无法利用到新的统计信息，除非下一次进行HARD PARSE，譬如CURSOR>从10G开始，DBMS_STATS.GATHER_TABLE_STATS过程 NO_INVALIDATE 参数提供了一个AUTO_INVALIDATE选项，这个参数让用户在统计信息收集后，控制什么时候游标失效。 NO_INVALIDATE 这个参数有一下3个选项：TRUE: does not invalidate the dependent cursors FALSE: invalidates the dependent cursors immediately AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursorsAUTO_INVALIDATE选项使得游标失效的时间得以控制，从而避免了HARD PARSE的风暴。有了这个选项，当统计信息收集后，游标按照如下的方式进行何时失效：1、当对象的统计信息被修改后，依赖于此对象的当前CACHED CURSORS被标记为rolling invalidation，此时假设时间为T1.2、下一次，当SESSION进行PARSE上面被标记为rolling invalidation的CURSOR的时候，记录时间戳T2，这个时间戳加上参数_optimizer_invalidation_period(以秒为单位，默认是18000秒，5个小时)的值， 就作为此游标的失效时刻TMAX。这次PARSE还是会共享游标，进行SOFT PARSE，不会利用到新的统计信息。3、在随后的游标PARSE时，ORACLE会检查当前的时刻是否超出了TMAX时间。如果没有，还会利用原来的游标，如果超出了，ORACLE会进行HARD PARSE，利用最新的统计信息产生一个子游标，同时在V$SQL_SHARED_CURSOR记录不能共享的原因，即ROLL_INVALID_MISMATCH被设置为YES.从上面也可以看出：如果一个游标在被标记为rolling invalidation,后面再也没有进行过PARSE，那么这个游标也不会被invalidated，当然可以手工的FLUSH出去，或者内存不够的时候,通过LRU算法淘汰出去。如果一个游标在被标记为rolling invalidation，仅仅进行过一次PARSE，那么这个游标也不会被invalidated,因为第一次仅仅记录一个时间戳。游标需要在第二次或者第N次的时候去进行判断游标是否被invalidated。说了这么多，看下面一个演示：Microsoft Windows XP [版本 5.1.2600](C) 版权所有 1985-2001 Microsoft Corp.C:\Documents and Settings\htaix>SQLPLUS PLSQL/PLSQLSQL*Plus:>Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到:Oracle Database 10g Enterprise Edition>With the Partitioning, OLAP and Data Mining optionsSQL> SET TIME ON11:17:20 SQL>11:17:21 SQL> SELECT COUNT(1) FROM T;  COUNT(1)----------     4977711:19:13 SQL>>会话已更改。11:19:27 SQL> SELECT EXECUTIONS,OBJECT_STATUS,INVALIDATIONS,LAST_ACTIVE_TIME11:19:32   2  FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM T';EXECUTIONS OBJECT_STATUS       INVALIDATIONS LAST_ACTIVE_TIME---------- ------------------- ------------- -------------------         1 VALID                           0 2013-01-10 11:17:2611:19:33 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,'T',NO_INVALIDATE=>FALSE);PL/SQL 过程已成功完成。11:20:24 SQL> SELECT EXECUTIONS,OBJECT_STATUS,INVALIDATIONS,LAST_ACTIVE_TIME11:20:27   2  FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM T';未选定行11:20:27 SQL>NO_INVALIDATE=>FALSE方式会导致游标立马失效，这也是9I的默认行为。11:28:27 SQL>>系统已更改。11:29:02 SQL>>系统已更改。11:29:06 SQL> select last_analyzed from user_tables where table_name='T';LAST_ANALYZED-------------------2013-01-10 11:25:4811:29:34 SQL> select count(1) from t;  COUNT(1)----------     4977711:29:47 SQL> select sql_id from v$sql where sql_text='select count(1) from t';SQL_ID-------------1pvh3df63vc4h11:30:05 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -1pvh3df63vc4h 6A4879E8 692CCD1C            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 N11:30:40 SQL> select child_number,parse_calls,executions,first_load_time,11:31:22   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           1          1 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:29:44我们看到一个子游标在11:29:44被执行。11:31:23 SQL> select count(1) from t;  COUNT(1)----------     4977711:33:34 SQL> select child_number,parse_calls,executions,first_load_time,11:33:50   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           2          2 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:33:32这次我们看到子游标执行了2次，同时最后更新时间为11:33:32.下面我们进行统计信息收集， 默认的选项就是AUTO_INVALIDATE。11:33:51 SQL> exec dbms_stats.gather_table_stats(null,'T');PL/SQL 过程已成功完成。11:35:39 SQL> select last_analyzed from user_tables where table_name='T';LAST_ANALYZED-------------------2013-01-10 11:35:1911:36:05 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h'11:36:16   2  ;SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -1pvh3df63vc4h 6A4879E8 692CCD1C            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 N11:36:34 SQL> select child_number,parse_calls,executions,first_load_time,11:36:41   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           2          2 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:33:3211:36:41 SQL> select count(1) from t;  COUNT(1)----------     4977711:37:13 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -1pvh3df63vc4h 6A4879E8 692CCD1C            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 N11:37:32 SQL> select child_number,parse_calls,executions,first_load_time,11:37:38   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           3          3 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:37:11统计信息被更新后，上面的select count(1) from t会进行一次SOFT PARSE，我们在PARSE_CALLS列可以看到，EXECUTIONS和LAST_ACTIVE_TIME都被更新，同时游标会被标记为rolling invalidation，这个时候即使时间超出了_optimizer_invalidation_period设置的值也不会导致游标失效，统计信息收集后的第一次PARSE仅仅是记录时间戳。我们大约稍等2分钟。在等待了2分钟后，我们重新执行select count(1) from t。我们先查询一下当前游标情况：11:42:54 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -1pvh3df63vc4h 6A4879E8 692CCD1C            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 N11:43:03 SQL> select child_number,parse_calls,executions,first_load_time,11:43:08   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           3          3 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:37:1111:43:09 SQL> select count(1) from t;  --这个SQL将导致游标失效  COUNT(1)----------     49777这次SQL的执行将会进行检查时间是否超出了_optimizer_invalidation_period设置的值，如果超出了就会进行HARD PARSE，否则还是SOFT PARSE。11:43:32 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -1pvh3df63vc4h 6A4879E8 692CCD1C            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 N1pvh3df63vc4h 6A4879E8 6B3A363C            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 N11:43:42 SQL> select child_number,parse_calls,executions,first_load_time,11:43:48   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           3          3 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:37:11           1           1          1 2013-01-10/11:29:45                    2013-01-10/11:43:21                2013-01-10 11:43:2011:43:49 SQL>我们可以看到一个新的子游标表被创建，并且被执行了一次，而老的游标执行了3次，先前的游标已经不能再被共享。v$sql_shared_cursor同时记录了不能共享的原因，后续的SQL将会共享新的游标，如下：11:43:49 SQL> select count(1) from t;  COUNT(1)----------     4977712:52:55 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -1pvh3df63vc4h 6A4879E8 692CCD1C            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 N1pvh3df63vc4h 6A4879E8 6B3A363C            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 N12:53:06 SQL> select child_number,parse_calls,executions,first_load_time,12:53:08   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME------------ ----------- ---------- -------------------------------------- -------------------------------------- -------------------           0           3          3 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:37:11           1           2          2 2013-01-10/11:29:45                    2013-01-10/11:43:21                2013-01-10 12:52:54上面的测试环境是WINDOWS 10.2.0.1单实例。SQL> SELECT * FROM V$VERSION;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition>PL/SQL>CORE    10.2.0.1.0      ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production上面的情况有个例外，如果SQL采用了并行并且跨了RAC的多个实例，那么统计信息收集后，游标立即失效。以下是官方说法：parallel SQL are immediately invalidated in order to ensure consistency between execution plans of slaves and Query Coordinator across multiple RAC instances. This is not a problem as parallel SQL are usually heavy and therefore hard-parse resources are insignificant to their total resource usage.简单总结以下：从10G开始，统计信息收集的时候如果NO_INVALIDATE采用的AUTO_INVALIDATE(默认情况下就是AUTO_INVALIDATE)，那么统计信息收集后，与此对象相关的游标不会里面失效。下一次PARSE的时候将会重用这个游标，并且记录一个时间点T1，后续的PARSE的时候将会对比当前时间戳T2和T1的间隔是否超出了_optimizer_invalidation_period设定的值，如果没有超出将会进行SOFT PARSE，后面的PARSE继续检验是否超出了_optimizer_invalidation_period设定的值如果超出了就会进行HARD PARSE，否则将还会进行SOFT PARSE，一直循环下去，直到游标失效或者被AGED OUT出去。　　PS：一旦执行了DBMS_STATS.GATHER_TABLE_STATS 如果NO_INVALIDATE=>FALSE 那么以前的子CURSOR会立即被刷新出SHARE POOL。不管统计数据发不发生变化，下次执行时都会产生一次hard parse、
　　而使用DBMS_STATS.AUTO_INVAILDATE也一样不管统计数据发不发生变化，下次执行时都会产生一次hard parse。这时会显示V$SQL中该语句会有2条记录。如果数据不变那么他的PLAN_HASH_VALUE不会发生改变(执行机会不变），但CHILD_ADDRESS会变化（可见生成了新的CURSOR，发生了硬解析）。如果数据变化则二者都会变化。
　　数据在发送大变化后 不会自动触发自动统计功能，需要手动执行。
　　感谢TOMSYAN 给予指导