[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秒一定出現。
--//實際上這樣的情況主要每天都分析的表，最容易出現這樣的情況。