[oracle SAG 예제자료] 세션별 Cursor 사용 확인 SQL .02

[정민이]

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

Username - Name of the user
Recursive Calls - Total number of recursive calls
Opened Cursors - Total number of opened cursors
Current Cursors - Number of cursor currently in use

select        user_process username,
        "Recursive Calls",
        "Opened Cursors",
        "Current Cursors"
from  (
        select        nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
                        sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
                        sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
                        sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
        from        v$session ss,
                v$sesstat se,
                v$statname sn
        where        se.STATISTIC# = sn.STATISTIC#
        and        (NAME  like '%opened cursors current%'
        or          NAME  like '%recursive calls%'
        or          NAME  like '%opened cursors cumulative%')
        and        se.SID = ss.SID
        and        ss.USERNAME is not null
        group        by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order        by USER_PROCESS,"Recursive Calls"