Post 12.2 Upgrade Slow Performance with High Calls to Gather_database_stats
(Doc ID 2387466.1)
Last updated on MARCH 03, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- Database post 12.2 upgrade issues with high redo occurs during the gather_database_stats procedure
- Top sqls from AWR report shows internal recursive sqls for dbms statistics and segment advisor:
b6usrg82hwsa3 call dbms_stats.gather_database_stats_job_proc ( )
by5m10t5s8fpq SELECT OBJOID, CLSOID, (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, DECODE(INST, :1, -1, 1))), WT, INST, DECODE(BITAND(STATUS, 8388608), 0, 0, 1), SCHLIM, ISLW, INST_ID FROM ( select a.obj# OBJOID, a.class_oid CLSOID, a.job_status STATUS, a.flags FLAGS, a.priority PRI, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, a.schedule_id SCHOID, a.last_start_date LSDATE, a.last_enabled_time LETIME, decode(a.schedule_limit, NULL, decode(bitand(a.flags, 4194304), 4194304, b.schedule_limit, NULL), a.schedule_limit) SCHLIM, 0 ISLW, a.instance_id INST_ID from sys.scheduler$_job a, sys.scheduler$_program b, (select /*+ no_merge */ database_role from v$database) v where a.program_oid = b.obj#(+) and ( (a.database_role = v.database_role) or (a.database_role = 'ALL' ) or (a.databa se_role is null and v.database_role = 'PRIMARY')) union all select c.obj#, c.class_oid, c.job_status, c.flags, d.priority, d.job_weight, decode(c.running_instance, NULL, 0, c.running_instance), c.schedule_id, c.last_start_date, c.last_enabled_time, d.schedule_limit, 1, c.instance_id from sys.scheduler$_comb_lw_job c, sys.scheduler$_program d where c.program_oid =
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 |
Cause |
Solution |
References |