Q Processes take more and more memory and does not release the memory.
pga_aggregate_limit=0 allows pga to grow unlimited hence the Q processes can consume all server memory. <\/p>\n\n\n\n
Temporary workaround is to reset the AQ slaves so that it is not necessary to shutdown\/startup the db instance<\/p>\n\n\n\n
show parameter aq_tm_processes\n\n--Reset AQ slaves:\n\nconnect \/ as sysdba\nalter system set aq_tm_processes=0 scope=memory sid='*' ;\n\n--delete AQ SESSIONS\n\nSELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE;'\nFROM V$SESSION \nWHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'\nORDER BY SID, SERIAL#;\n\n--Start back AQ slaves:\n\nconnect \/ as sysdba\nalter system set aq_tm_processes=1 scope=memory sid='*';\n<\/code><\/pre>\n\n\n\nQueries to Monitor Q Processes.<\/p>\n\n\n\n
--PGA Memory Stat\nSELECT name, round(((value\/1000)\/1024),2) as value_mb, round((((value\/1000)\/1024)\/1024),2) as value_gb FROM V$PGASTAT;\n\n--Find the Q Processes\nSELECT SID,SERIAL#,OSUSER,PROCESS,MACHINE,PROGRAM,TYPE,MODULE,ACTION,EVENT \nFROM V$SESSION \nWHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'\nORDER BY SID, SERIAL#;\n\n--ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;\nSELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE;'\nFROM V$SESSION \nWHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'\nORDER BY SID, SERIAL#;\n\n--03-memory-usage-in-details.sql - Q Processes\nselect\n to_char(ssn.sid, '999999') as session_id,\n ssn.serial# as session_serial,\n nvl(ssn.username, nvl(bgp.name, 'background'))\n || '::'\n || nvl(lower(ssn.machine), ins.host_name) as process_name,\n to_char(prc.spid, '999999999') as pid_thread,\n to_char((se1.value \/ 1024) \/ 1024, '999g999g990d00') as current_size_mb,\n to_char((se2.value \/ 1024) \/ 1024, '999g999g990d00') as maximum_size_mb\nfrom\n v$statname stat1,\n v$statname stat2,\n v$session ssn,\n v$sesstat se1,\n v$sesstat se2,\n v$bgprocess bgp,\n v$process prc,\n v$instance ins\nwhere\n stat1.name = 'session pga memory'\n and stat2.name = 'session pga memory max'\n and se1.sid = ssn.sid\n and se2.sid = ssn.sid\n and se2.statistic# = stat2.statistic#\n and se1.statistic# = stat1.statistic#\n and ssn.paddr = bgp.paddr (+)\n and ssn.paddr = prc.addr (+)\n and ssn.sid in (\n select sid\n from v$session\n --where username = 'MY_USERNAME' -- your user\/schema name\n where type = 'BACKGROUND' and module = 'Streams' and action like 'EMON%' and event = 'EMON slave idle wait'\n )\norder by\n maximum_size_mb desc;<\/code><\/pre>\n\n\n\nCheck also that AQ_SRVNTFN_TABLE_1 is set to dequeue. It is possible that DEQUEUE_ENABLED is set to NO.<\/p>\n\n\n\n
AQ_SRVNTFN_TABLE_1
ENQUEUE_ENABLE=YES
DEQUEUE_ENABLED=NO<\/p>\n\n\n\n
confirm the status of the queue:<\/p>\n\n\n\n
-- Workaround to apply on all CDB and PDB\nselect ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where NAME = 'AQ_SRVNTFN_TABLE_Q_1';\n\nselect owner, name, queue_table, ENQUEUE_ENABLED, DEQUEUE_ENABLED \nfrom dba_queues where name = 'AQ_SRVNTFN_TABLE_Q_1' and owner='SYS';\n\nexec dbms_aqadm.stop_queue( 'AQ_SRVNTFN_TABLE_Q_1', true , true );\nexec dbms_aqadm.start_queue( 'AQ_SRVNTFN_TABLE_Q_1' , true, true );\n\n\n<\/code><\/pre>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Q Processes take more and more memory and does not release the memory. pga_aggregate_limit=0 allows pga to grow unlimited hence the Q processes can consume all server memory. Temporary workaround is to reset the AQ slaves so that it is not necessary to shutdown\/startup the db instance Queries to Monitor Q Processes. Check also that … <\/p>\n