Oracle Temp Usage

Temp Tablespace Usage Session

1. Sort, Lob, With 절 사용 시, Temp 를 과도하게 사용 할 수 있음 

2. 기본적으로 Temp Tablespace 는 DB 당 하나만 존재하여, Temp 공간 부족 시, 전체 서비스 영향 줌

   ( User 별로 Temp Tablespace 를 할당가능 )

3. 지난 시점의  Temp 점유 세션/SQL 확인 

    GV$ACTIVE_SESSION_HISTORY(DBA_HIST_ACTIVE_SESS_HISTORY).PGA_ALLOCATED 

    GV$ACTIVE_SESSION_HISTORY(DBA_HIST_ACTIVE_SESS_HISTORY).TEMP_SPACE_ALLOCATED 

  

select  /*+ ORDERED */  TOT.TABLESPACE_NAME AS T_NAME
        ,se.inst_id
        ,se.username
        ,se.sid, se.serial#
        ,'alter system kill session '''||se.sid||','||se.SERIAL#||''' immediate ;' as "Kill(SQL)"
        ,sp.spid
        ,'kill -9 '||sp.spid as "Kill(OS)"
        ,segtype
        ,NVL(se.sql_id,se.prev_sql_id) AS SQL_INFO_SESS
        ,NVL(SU.SQL_ID,se.prev_sql_id) AS SQL_INFO_SORT
        ,se.status
        ,se.event
        ,se.wait_time AS WAIT_T
        ,se.seconds_in_wait AS WAIT_T_S
        ,se.module
        ,se.action
        ,se.MACHINE
        ,sum(su.extents)
        ,sum(su.blocks * to_number(rtrim(8192)))/1024/1024 as Space     
        ,SUM(TOT.MB) AS "TEMP_TOT(MB)"
        ,SUM(sum(su.blocks * to_number(rtrim(8192)))/1024/1024) OVER() AS UTIL_TEMP
        ,ROUND(((sum(su.blocks * to_number(rtrim(8192)))/1024/1024)/(SUM(TOT.MB))*100),1) AS "RATIO(%)"
        ,sum(lob.CACHE_LOBS) as cache_lobs
        ,sum(lob.NOCACHE_LOBS) as nocache_lobs
from     gv$sort_usage   su
        ,( SELECT /*+ NO_MERGE */ TABLESPACE_NAME, SUM(MAXBYTES/1024/1024) MB
           FROM   DBA_TEMP_FILES
           GROUP BY TABLESPACE_NAME ) TOT
        ,gv$session      se
        ,gv$process      sp
        ,gv$temporary_lobs lob
where    SU.TABLESPACE = TOT.TABLESPACE_NAME
and      su.session_addr = se.saddr
and      su.session_num  = se.serial#
and      su.inst_id = se.inst_id
and      se.type ='USER'
and      se.inst_id = sp.inst_id
and      se.paddr = sp.addr
and      se.sid= lob.sid
and      se.inst_id= lob.inst_id
group by TOT.TABLESPACE_NAME, se.inst_id, se.username, se.sid, se.SERIAL#, sp.spid, segtype, su.extents
       , NVL(se.sql_id,se.prev_sql_id), NVL(SU.SQL_ID,se.prev_sql_id)
       , se.status, se.event, se.wait_time, se.seconds_in_wait, se.module, se.action, se.MACHINE, tablespace, segtype
-- having    sum(su.blocks * to_number(rtrim(8192)))/1024/1024 > 10
order by SPACE desc, TABLESPACE, se.username, se.sid ;