version_count增加的原因(2)

最新推荐文章于 2021-04-28 23:06:30 发布

ciliting2867

最新推荐文章于 2021-04-28 23:06:30 发布

阅读量160

点赞数

本文探讨了在Oracle数据库中使用dbms_stats进行统计信息收集时，no_invalidate参数的不同设置对SQL执行计划的影响，并通过实测案例展示了这些设置如何影响SQL版本计数与失效情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当用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';

--发现在对表做分析后，执行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> 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> 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';

--执行多次查询后，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> 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';

经过数次反复的查询及收集，发现仍然没有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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-613321/