因为在一个维护窗口内多次执行自动统计信息收集导致统计信息历史信息变得非常大

刘泽宇

概要

统计信息在一天内或一个维护窗口内多次收集：
SQL> select * from (select OBJ#,count(*) from WRI$_OPTSTAT_TAB_HISTORY group by OBJ# order by count(*) desc) where rownum<=20;

      OBJ#   COUNT(*)
---------- ----------
     11059        107

SQL> select OBJ#,SAVTIME from WRI$_OPTSTAT_TAB_HISTORY where OBJ#=11059 order by 2;

      OBJ# SAVTIME
---------- ---------------------------------------------------------------------------
     11059 19-01-09 22:00:54.092693 +09:00
     11059 19-01-09 23:06:31.250199 +09:00
     11059 19-01-09 23:16:31.201414 +09:00
     11059 19-01-09 23:26:12.520840 +09:00
     11059 19-01-09 23:36:10.736521 +09:00
     11059 19-01-09 23:46:10.870514 +09:00
     11059 19-01-09 23:56:13.915061 +09:00
     11059 19-01-10 00:06:16.370713 +09:00
     11059 19-01-10 00:16:14.790135 +09:00
     11059 19-01-10 00:26:17.082250 +09:00
     11059 19-01-10 00:36:17.228125 +09:00
     11059 19-01-10 00:46:20.161185 +09:00
     11059 19-01-10 00:56:20.166299 +09:00
     11059 19-01-10 01:06:23.557626 +09:00
     11059 19-01-10 01:16:23.692279 +09:00
     11059 19-01-10 01:26:23.447924 +09:00
     11059 19-01-10 01:36:23.947311 +09:00
     11059 19-01-10 02:00:40.650842 +09:00

自动统计收集作业在一个维护窗口中多次运行：
col JOB_NAME form a40
col JOB_START_TIME form a35
col WINDOW_START_TIME form a35

select WINDOW_START_TIME,JOB_NAME,JOB_START_TIME
from DBA_AUTOTASK_JOB_HISTORY
where to_char(WINDOW_START_TIME,'yy-mm-dd')='19-01-09'
and JOB_NAME like 'ORA$AT_OS_OPT_SY%';

WINDOW_START_TIME                   JOB_NAME                                 JOB_START_TIME
----------------------------------- ---------------------------------------- -----------------------------------
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_21                      19-01-09 23:06:07.197571 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_1                       19-01-09 22:00:03.475906 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_43                      19-01-09 23:26:07.918281 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_45                      19-01-09 23:36:07.988550 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_47                      19-01-09 23:46:08.719216 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_41                      19-01-09 23:16:08.827209 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_61                      19-01-10 00:56:18.363366 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_81                      19-01-10 01:06:20.723754 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_85                      19-01-10 01:26:21.218847 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_49                      19-01-09 23:56:11.698821 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_51                      19-01-10 00:06:12.301477 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_59                      19-01-10 00:46:17.400691 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_83                      19-01-10 01:16:21.184000 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_53                      19-01-10 00:16:12.978494 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_55                      19-01-10 00:26:14.640288 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_57                      19-01-10 00:36:15.463163 ASIA/TOKYO
19-01-09 22:00:00.358819 +09:00     ORA$AT_OS_OPT_SY_87                      19-01-10 01:36:22.013708 ASIA/TOKYO

17 rows selected.



解决方案

1. 应用 <Patch 26724511>


原因

升级到12.2.


这是由于以下问题导致的

Bug 26724511 - High Child Cursor Count and Shared Pool Memory Consumption Due to Many Scheduler Autotask Jobs Created for the Same Task and Maintenance Window (Doc ID 26724511.8)