ITPub博客

首页 > 数据库 > Oracle > [20210305]Oracle Rolling Invalidate Window Exceeded(3).txt

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

原创 Oracle 作者:lfree 时间:2021-03-05 16:38:04 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2894
  • 访问量
    6673002