Optimizer Statistics History Information Become Huge due to Automatic Statistics Gathering Job Run Multi Times in One Maintenance Window
(Doc ID 2501155.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
- Object's statistics information was gathered multi times one day or one maintenance window:
SQL> select * from (select OBJ#,count(*) from WRI$_OPTSTAT_TAB_HISTORY group by OBJ# order by count(*) desc) where rownum<=20;
OBJ# COUNT(*)
---------- ----------
11059 107SQL> 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
- Automatic Statistics Gathering job run multi times in one maintenance window:
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.
Note:
This issue also have another symptom like the below issue occurs every 10 minutes in maintenance window.
<Document 2127675.1> ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c)
Changes
Update to 12.2.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |