Oracle Rolling Invalidate Window Exceeded(3).txt
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秒一定出现。
--//实际上这样的情况主要每天都分析的表，最容易出现这样的情况。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2761429/，如需转载，请注明出处，否则将追究法律责任。