首页 > 代码库 > 10G之后统计信息收集后为什么执行计划不会被立马淘汰

10G之后统计信息收集后为什么执行计划不会被立马淘汰

在10G之前,使用DBMS_STATS收集统计信息将会导致与此对象相关的游标失效,下次执行此
的时候将会进行HARD PARSE,除非收集的时候NO_INVALIDATE设置为TRUE。
由于硬解析会消耗大量的CPU,还会导致大量的library cache 和 shared pool 的LATCH竞争,因此
如果由于统计信息收集导致大量的的游标失效,可能会带来HARD PARSE风暴,造成系统的负担。
 
但是如果采用NO_INVALIDATE=TRUE的方法,由于游标不失效,游标无法利用到新的统计信息,
除非下一次进行HARD PARSE,譬如CURSOR RELOAD,手工FLUSH SHARED POOL,cursor aged out.
 
从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 cursors
 
 
AUTO_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/PLSQL
 
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 10 11:16:43 2013
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> SET TIME ON
11:17:20 SQL>
11:17:21 SQL> SELECT COUNT(1) FROM T;
 
  COUNT(1)
----------
     49777
 
11:19:13 SQL> ALTER SESSION SET NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS‘;
 
会话已更改。
 
11:19:27 SQL> SELECT EXECUTIONS,OBJECT_STATUS,INVALIDATIONS,LAST_ACTIVE_TIME
11: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:26
 
11: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_TIME
11: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> ALTER SYSTEM FLUSH SHARED_POOL;
 
系统已更改。
 
 
11:29:02 SQL> alter system set "_optimizer_invalidation_period"=120;
 
系统已更改。
 
11:29:06 SQL> select last_analyzed from user_tables where table_name=‘T‘;
 
LAST_ANALYZED
-------------------
2013-01-10 11:25:48
 
11:29:34 SQL> select count(1) from t;
 
  COUNT(1)
----------
     49777
 
11:29:47 SQL> select sql_id from v$sql where sql_text=‘select count(1) from t‘;
 
SQL_ID
-------------
1pvh3df63vc4h
 
11: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 N
 
11: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)
----------
     49777
 
11: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:19
 
 
11: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 N
 
11: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:32
 
11:36:41 SQL> select count(1) from t;
 
  COUNT(1)
----------
     49777
 
11: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 N
 
11: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 N
 
11: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:11
 
11: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 N
1pvh3df63vc4h 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 N
 
11: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:20
 
11:43:49 SQL>
 
 
 
我们可以看到一个新的子游标表被创建,并且被执行了一次,而老的游标执行了3次,先前的游标已经不能再被共享。
v$sql_shared_cursor同时记录了不能共享的原因,后续的SQL将会共享新的游标,如下:
 
 
 
11:43:49 SQL> select count(1) from t;
 
  COUNT(1)
----------
     49777
 
12: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 N
1pvh3df63vc4h 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 N
 
12: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 Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL 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出去。
 
转载:http://blog.chinaunix.net/uid-22948773-id-3469364.html

10G之后统计信息收集后为什么执行计划不会被立马淘汰