[20210305]Oracle Rolling Invalidate Window Exceeded(3).txt

[20210305]Oracle Rolling Invalidate Window Exceeded(3).txt --//昨天看了链接 --//https://blog.dbi-services.com/oracle-rolling-invalidate-window-exceeded3/->Oracle Rolling Invalidate Window Exceeded(3) --//自己重复测试看看： 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立测试脚本： create table DEMO as select * from dual; alter system set "_optimizer_invalidation_period"=15 scope=memory; exec dbms_stats.gather_table_stats(user,'DEMO'); --//建立脚本aaa.txt,原作者是将它的执行脚本执行后展开分析，感觉不是很好，应该是写成脚本，然后在加上后面的分析。 $ cat aaa.txt alter system flush shared_pool; alter system flush shared_pool; column REASON format a100 set time on set echo on host sleep 30 select * from DEMO; select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; exec dbms_stats.gather_table_stats(user,'DEMO'); select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; host sleep 30 select * from DEMO; select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; exec dbms_stats.gather_table_stats(user,'DEMO'); select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; host sleep 30 select * from DEMO; select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; 3.执行分析： 15:52:05 SCOTT@book> @ aaa.txt 15:52:07 SCOTT@book> alter system flush shared_pool; System altered. 15:52:08 SCOTT@book> alter system flush shared_pool; System altered. 15:52:08 SCOTT@book> column REASON format a100 15:52:08 SCOTT@book> set time on 15:52:08 SCOTT@book> set echo on 15:52:08 SCOTT@book> host sleep 30 15:52:38 SCOTT@book> select * from DEMO; D - X 15:52:38 SCOTT@book> select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; CHILD_NUMBER REASON ------------ ----------------------------------------------------------------------------------------------------            0 15:52:38 SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO'); PL/SQL procedure successfully completed. 15:52:38 SCOTT@book> select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; OWNER  TABLE_NAME PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME ------ ---------- ------------------------------ ------------------------------ --------------------------------- SCOTT  DEMO                                                                     2021-03-05 15:52:05.089218 +08:00 SCOTT  DEMO                                                                     2021-03-05 15:52:38.487667 +08:00 15:52:38 SCOTT@book> host sleep 30 15:53:08 SCOTT@book> select * from DEMO; D - X 15:53:08 SCOTT@book> select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; CHILD_NUMBER REASON ------------ ----------------------------------------------------------------------------------------------------            0 --//第2次执行分析过了30秒后执行并没有产生新的子光标。 15:53:08 SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO'); PL/SQL procedure successfully completed. 15:53:08 SCOTT@book> select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; OWNER  TABLE_NAME PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME ------ ---------- ------------------------------ ------------------------------ --------------------------------- SCOTT  DEMO                                                                     2021-03-05 15:52:05.089218 +08:00 SCOTT  DEMO                                                                     2021-03-05 15:52:38.487667 +08:00 SCOTT  DEMO                                                                     2021-03-05 15:53:08.652373 +08:00 15:53:08 SCOTT@book> host sleep 30 15:53:38 SCOTT@book> select * from DEMO; D - X 15:53:38 SCOTT@book> select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; CHILD_NUMBER REASON ------------ ----------------------------------------------------------------------------------------------------            0 <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</rea              son><size>2x4</size><invalidation_window>1614930791</invalidation_window><ksugctm>1614930818</ksugct              m></ChildNode>            1 --//第3次执行分析过了30秒后执行并产生新的子光标。 SYS@book> @ share 0m8kbvzchkytt old  15:           and q.sql_id like ''&1''', new  15:           and q.sql_id like ''0m8kbvzchkytt''', SQL_TEXT                       = select * from DEMO SQL_ID                         = 0m8kbvzchkytt ADDRESS                        = 000000007E3BF1E0 CHILD_ADDRESS                  = 000000007D2BDD70 CHILD_NUMBER                   = 0 REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1614930791</invalidation_window><ksugctm>1614930818</ksugctm></ChildNode> -------------------------------------------------- SQL_TEXT                       = select * from DEMO SQL_ID                         = 0m8kbvzchkytt ADDRESS                        = 000000007E3BF1E0 CHILD_ADDRESS                  = 000000007D6EC738 CHILD_NUMBER                   = 1 ROLL_INVALID_MISMATCH          = Y REASON                         = -------------------------------------------------- PL/SQL procedure successfully completed. --//我开始看了很久不明白作者实验的意图，视乎作者想说的是第2次分析等30秒一定会建立新的子光标。 --//实际上即使第3次即使不分析过一定时间也会出现新的子光标。 --//修改如下： $ cat aaa.txt alter system flush shared_pool; alter system flush shared_pool; column REASON format a100 set time on set echo on --//host sleep 30 select * from DEMO; select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; exec dbms_stats.gather_table_stats(user,'DEMO'); select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; host sleep 30 select * from DEMO; select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; --//exec dbms_stats.gather_table_stats(user,'DEMO'); --//select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; --// host sleep &&1 select * from DEMO; select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; --//执行如下： 16:25:43 SCOTT@book> @ aaa.txt 5 16:26:23 SCOTT@book> alter system flush shared_pool; System altered. 16:26:23 SCOTT@book> alter system flush shared_pool; System altered. 16:26:23 SCOTT@book> column REASON format a100 16:26:23 SCOTT@book> set time on 16:26:23 SCOTT@book> set echo on 16:26:23 SCOTT@book> --//host sleep 30 16:26:23 SCOTT@book> select * from DEMO; D - X 16:26:23 SCOTT@book> select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; CHILD_NUMBER REASON ------------ ----------------------------------------------------------------------------------------------------            0 16:26:23 SCOTT@book> 16:26:23 SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO'); PL/SQL procedure successfully completed. 16:26:24 SCOTT@book> select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; OWNER  TABLE_NAME PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME ------ ---------- ------------------------------ ------------------------------ --------------------------------- SCOTT  DEMO                                                                     2021-03-05 15:52:05.089218 +08:00 SCOTT  DEMO                                                                     2021-03-05 15:52:38.487667 +08:00 SCOTT  DEMO                                                                     2021-03-05 15:53:08.652373 +08:00 SCOTT  DEMO                                                                     2021-03-05 16:08:35.590503 +08:00 SCOTT  DEMO                                                                     2021-03-05 16:17:11.290579 +08:00 SCOTT  DEMO                                                                     2021-03-05 16:19:52.977908 +08:00 SCOTT  DEMO                                                                     2021-03-05 16:21:23.084524 +08:00 SCOTT  DEMO                                                                     2021-03-05 16:25:12.445233 +08:00 SCOTT  DEMO                                                                     2021-03-05 16:26:23.971873 +08:00 9 rows selected. 16:26:24 SCOTT@book> 16:26:24 SCOTT@book> host sleep 30 16:26:54 SCOTT@book> select * from DEMO; D - X 16:26:54 SCOTT@book> select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; CHILD_NUMBER REASON ------------ ----------------------------------------------------------------------------------------------------            0 16:26:54 SCOTT@book> 16:26:54 SCOTT@book> --//exec dbms_stats.gather_table_stats(user,'DEMO'); 16:26:54 SCOTT@book> --//select * from dba_tab_stats_history where table_name='DEMO' order by stats_update_time; 16:26:54 SCOTT@book> --// 16:26:54 SCOTT@book> host sleep &&1 16:26:59 SCOTT@book> select * from DEMO; D - X 16:26:59 SCOTT@book> select child_number,reason from v$sql_shared_cursor where sql_id='0m8kbvzchkytt'; CHILD_NUMBER REASON ------------ ----------------------------------------------------------------------------------------------------            0 <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</rea              son><size>2x4</size><invalidation_window>1614932817</invalidation_window><ksugctm>1614932818</ksugct              m></ChildNode>            1 --//注：我尝试了参数1，2，3,4都没有出现新的子光标，设置5秒后出现,感觉这个时间间隔不确定。 --//正常15秒一定出现。 --//实际上这样的情况主要每天都分析的表，最容易出现这样的情况。