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.<\/p>\n\n\n\n
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_<…>)<\/p>\n\n\n\n
And it was executing following recursive SQL:<\/p>\n\n\n\n
SQL_ID: as2dr3ag24gay\nselect not_stale.obj# from (select s.obj# obj#, count(*) cnt, max(timestamp) max_time from\noptstat_snapshot$ s, wri$_optstat_tab_history t where s.obj# = t.obj# and bitand(s.flags, :gather_stats_flag) > 0 and\nt.analyzetime = (select max(analyzetime) from wri$_optstat_tab_history where analyzetime < s.timestamp) and\ndbms_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,\n(select obj# obj#, count(*) cnt from optstat_snapshot$ where bitand(flags, :gather_stats_flag) > 0 group by obj#) total,\n(select obj#, max(timestamp) max_time from optstat_snapshot$ group by obj#) max where not_stale.obj# = total.obj# and\nnot_stale.cnt > 0.5 * total.cnt and not_stale.obj# = max.obj# and not_stale.max_time = max.max_time and\ndbms_stats_internal.check_advisor_obj_filter(:rule_id, not_stale.obj#, :exec_mode) = 'T'<\/code><\/pre>\n\n\n\nOn 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<\/p>\n\n\n\n
To resolve the issue, you can apply patch for Bug 34201470
<\/strong>(Please note that this fix above does the same thing the workaround does below, it only turns off AUTO_STATS_ADVISOR_TASK)<\/p>\n\n\n\n To workaround: exec dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’, ‘FALSE’);<\/strong><\/p>\n\n\n\nPlease 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.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … <\/p>\n