Last, times, we noticed high cpu and memory usage on our database server. Top memory consuming process was some ora_j000_* process which are spawned as part of execution of auto task scheduler job . It was using also lots of swap.
After reviewing, we found that the process was running “Optimizer statistics advisor” task name AUTO_STATS_ADVISOR_TASK ( job name ORA$AT_OS_OPT_SY_<…>)
And it was executing following recursive SQL:
SQL_ID: as2dr3ag24gay
select not_stale.obj# from (select s.obj# obj#, count(*) cnt, max(timestamp) max_time from
optstat_snapshot$ s, wri$_optstat_tab_history t where s.obj# = t.obj# and bitand(s.flags, :gather_stats_flag) > 0 and
t.analyzetime = (select max(analyzetime) from wri$_optstat_tab_history where analyzetime < s.timestamp) and
dbms_stats_internal.is_stale(s.obj#, null, null, s.inserts + s.updates + s.deletes, t.rowcnt, 0) is null group by s.obj#) not_stale,
(select obj# obj#, count(*) cnt from optstat_snapshot$ where bitand(flags, :gather_stats_flag) > 0 group by obj#) total,
(select obj#, max(timestamp) max_time from optstat_snapshot$ group by obj#) max where not_stale.obj# = total.obj# and
not_stale.cnt > 0.5 * total.cnt and not_stale.obj# = max.obj# and not_stale.max_time = max.max_time and
dbms_stats_internal.check_advisor_obj_filter(:rule_id, not_stale.obj#, :exec_mode) = 'T'On oracle support site, we found that the issue is related to the following bug:
Bug 34201470 – JNNN PROCESSES CONSUMING 100% CPU AND HIGH MEMORY DURING AUTOTASK EXECUTION
To resolve the issue, you can apply patch for Bug 34201470
(Please note that this fix above does the same thing the workaround does below, it only turns off AUTO_STATS_ADVISOR_TASK)
To workaround: exec dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’, ‘FALSE’);
Please note that the Statistics Preference AUTO_STATS_ADVISOR_TASK is available ONLY when patch 26749785 is present otherwise, there is nothing that can be done.