重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 因为在一个维护窗口内多次执行自动统计信息收集导致统计信息历史信息变得非常大 [打印本页]

作者: 刘泽宇 时间: 2025-12-21 11:38
标题: 因为在一个维护窗口内多次执行自动统计信息收集导致统计信息历史信息变得非常大
概要

统计信息在一天内或一个维护窗口内多次收集：
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)

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)