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:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.