High temp space consumption by OEM agent DBSNMP

Solaris 10, Oracle, OEM agent

Agent (DBSNMP) is consuming CPU and huge amount of temp space by selecting from v$lock

    /* OracleOEM */ WITH blocked_resources AS
(select id1 ,id2 ,SUM(ctime) as blocked_secs ,MAX(request) as max_request ,COUNT(1) as blocked_count
from v$lock
where request > 0 group by id1,id2 ) ,blockers AS
(select L.* ,BR.blocked_secs ,BR.blocked_count
from v$lock L ,blocked_resources BR
where BR.id1 = L.id1 and BR.id2 = L.id2 and L.lmode > 0 and L.block <> 0 )
select B.id1||'_'||B.id2||'_'||S.sid||'_'||S.serial# as id ,'SID,SERIAL:'||S.sid||','||S.serial#||',LOCK_TYPE:'||B.type||',PROGRAM:'||S.program||',MODULE:'||S.module||',ACTION:'||S.action||',MACHINE:'||S.machine||',OSUSER:'||S.osuser||',USERNAME:'||S.username as info ,B.blocked_secs ,B.blocked_count
from v$session S ,blockers B
where B.sid = S.sid;

Check and gather statistics for fixed objects.
select OWNER, TABLE_NAME, object_type, LAST_ANALYZED from dba_tab_statistics where table_name IN


Useful Info
Check the following Oracle Support documents
Query Against v$lock Run from OEM Performs Slowly [ID 1328789.1]
High temp space consumption by DBSNMP [ID 1389377.1]
EM Agent DBSNMP Using Up Excessive Temp Space In Database [ID 748251.1]

No comments:

Post a Comment