![]() To workaround: exec dbms_t_global_prefs(‘AUTO_STATS_ADVISOR_TASK’, ‘FALSE’) (Please note that this fix above does the same thing the workaround does below, it only turns off AUTO_STATS_ADVISOR_TASK) To resolve the issue, you can apply patch for Bug 34201470 On oracle support site, we found that the issue is related to the following bug:īug 34201470 – JNNN PROCESSES CONSUMING 100% CPU AND HIGH MEMORY DURING AUTOTASK EXECUTION Not_stale.cnt > 0.5 * total.cnt and not_stale.obj# = max.obj# and not_stale.max_time = max.max_time andĭbms_stats_internal.check_advisor_obj_filter(:rule_id, not_stale.obj#, :exec_mode) = 'T' (select obj#, max(timestamp) max_time from optstat_snapshot$ group by obj#) max where not_stale.obj# = total.obj# and T.analyzetime = (select max(analyzetime) from wri$_optstat_tab_history where analyzetime 0 group by obj#) total, Optstat_snapshot$ s, wri$_optstat_tab_history t where s.obj# = t.obj# and bitand(s.flags, :gather_stats_flag) > 0 and Select not_stale.obj# from (select s.obj# obj#, count(*) cnt, max(timestamp) max_time from It was using also lots of swap.Īfter reviewing, we found that the process was running “Optimizer statistics advisor” task name AUTO_STATS_ADVISOR_TASK ( job name ORA$AT_OS_OPT_SY_)Īnd it was executing following recursive SQL: SQL_ID: as2dr3ag24gay Top memory consuming process was some ora_j000_* process which are spawned as part of execution of auto task scheduler job. Last, times, we noticed high cpu and memory usage on our database server. Select owner, name, queue_table, ENQUEUE_ENABLED, DEQUEUE_ENABLEDįrom dba_queues where name = 'AQ_SRVNTFN_TABLE_Q_1' and owner='SYS' Įxec dbms_aqadm.stop_queue( 'AQ_SRVNTFN_TABLE_Q_1', true, true ) Įxec dbms_aqadm.start_queue( 'AQ_SRVNTFN_TABLE_Q_1', true, true ) Select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where NAME = 'AQ_SRVNTFN_TABLE_Q_1' It is possible that DEQUEUE_ENABLED is set to NO.Ĭonfirm the status of the queue: - Workaround to apply on all CDB and PDB Where type = 'BACKGROUND' and module = 'Streams' and action like 'EMON%' and event = 'EMON slave idle wait'Ĭheck also that AQ_SRVNTFN_TABLE_1 is set to dequeue. where username = 'MY_USERNAME' - your user/schema name || nvl(lower(ssn.machine), ins.host_name) as process_name, Nvl(ssn.username, nvl(bgp.name, 'background')) 03-memory-usage-in-details.sql - Q Processes ![]() ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE SELECT SID,SERIAL#,OSUSER,PROCESS,MACHINE,PROGRAM,TYPE,MODULE,ACTION,EVENT WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'Īlter system set aq_tm_processes=1 scope=memory sid='*' SELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE ' Temporary workaround is to reset the AQ slaves so that it is not necessary to shutdown/startup the db instance show parameter aq_tm_processesĪlter system set aq_tm_processes=0 scope=memory sid='*' Pga_aggregate_limit=0 allows pga to grow unlimited hence the Q processes can consume all server memory. Q Processes take more and more memory and does not release the memory. ( select file_id, max(block_id blocks-1) hwmįrom dba_extents where tablespace_name= '
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |