High temp space consumption by OEM agent DBSNMP

Environment

Solaris 10, Oracle 11.2.0.2, OEM agent 11.1.0.1.0

Symptoms

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;

Solution

Check and gather statistics for fixed objects.

select OWNER, TABLE_NAME, object_type, LAST_ANALYZED from dba_tab_statistics where table_name IN

('X$KSUSE',

'X$KDNSSF',

'X$KSQEQ',

'X$KTADM',

'X$KTATRFIL',

'X$KTATRFSL',

'X$KTATL',

'X$KTSTUSC',

'X$KTSTUSS',

'X$KTSTUSG',

'X$KTCXB',

'X$KSQRS',

'X$KSLWT',

'X$KSLED');

exec dbms_stats.GATHER_FIXED_OBJECTS_STATS;

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]