ITPub博客

首页 > Linux操作系统 > Linux操作系统 > version_count增加的原因(2)

version_count增加的原因(2)

原创 Linux操作系统 作者:lsq_008 时间:2009-08-26 20:02:27 0 删除 编辑

当用dbms_stats对表作统计信息收集时,其中有个no_invalidate的参数,该参数有三种设置:
(1)true。与该表相关的sql永不失效。
(2)false。与该表相关的sql立即失效。
(3)DBMS_STATS.AUTO_INVALIDATE。由oracle决定相关的sql何时失效,实际上这个时间是由一个隐含参数_optimizer_invalidation_period决定的。
针对第三种设置,测试如下:

SQL> alter system set "_optimizer_invalidation_period" = 1;

System altered.

SQL> create table t1 as select * from dba_users;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
        15

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    1               1          1          1             0           1 2CE58B90

SQL> insert into t1 select * from t1;

15 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    1               1          2          1             0           2 2CE58B90

--发现在对表做分析后,执行1次查询,相关sql没有invalid,version count也没变化

SQL>  exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    1               1          3          1             0           3 2CE58B90

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    1               1          4          1             0           4 2CE58B90

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    2               2          8          2             0           8 2CE58B90

--执行多次查询后,version_count已经变为2,但invalidations仍然为0.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    2               2         11          2             0          11 2CE58B90

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> /

  COUNT(*)
----------
        30

SQL> select sql_text,version_count,loaded_versions,executions,loads,invalidations,parse_calls,address
  2  from v$sqlarea where sql_text ='select count(*) from t1';

SQL_TEXT                       VERSION_COUNT LOADED_VERSIONS EXECUTIONS      LOADS INVALIDATIONS PARSE_CALLS ADDRESS
------------------------------ ------------- --------------- ---------- ---------- ------------- ----------- --------
select count(*) from t1                    3               3         14          3             0          14 2CE58B90

经过数次反复的查询及收集,发现仍然没有invalid,而version_count反而增加了。

SQL> select * from v$sql_shared_cursor where address='2CE58B90';

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
- - - - - - - - -
5bc0v4my7dvr5 2CE58B90 2CDCD0E4            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

5bc0v4my7dvr5 2CE58B90 2CD24DFC            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

5bc0v4my7dvr5 2CE58B90 2CDF1ECC            2 N N N N N N N N N N N N N N N N N N N N N N N 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


SQL> select sql_id,address,child_address,child_number,ROLL_INVALID_MISMATCH from v$sql_shared_cursor where address='2CE58B90';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER R
------------- -------- -------- ------------ -
5bc0v4my7dvr5 2CE58B90 2CDCD0E4            0 N
5bc0v4my7dvr5 2CE58B90 2CD24DFC            1 Y
5bc0v4my7dvr5 2CE58B90 2CDF1ECC            2 Y

由此可见,在进行统计信息收集时,如果no_invalidate设置为默认,有可能造成sql的version_count增加的问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-613321/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    324
  • 访问量
    1227890