[Oracle] Temp 테이블스페이스 사용량, 세션 확인

SORT를 하는 경우 TEMP TABLESPACE를 사용하게 된다.

- ORDER BY SQL을 실행하는 경우

- ONLINE REDEFINITION시 ORDER BY 조건을 사용하는 경우 (Clustering reorg)

- 인덱스를 생성하는 경우 

 

대용량 SORT를 여러 세션에서 동시에 진행하는 경우 (특히 과도한 PARALLEL로 작업시)

 

CPU, MEMORY, TABLE/INDEX TABLESPACE에는 여유가 있더라도 , 

TEMP TABLESPACE FULL로 인한 장애가 발생할 수 있으니

 

대량의 REORG 작업 전에는 TEMP TABLESPACE를 늘려놓고 추이를 모니터링해야한다.  

 

* 압축 테이블을 전체 SORT하는 경우 압축이 해제된 용량만큼 TEMP TABLESPACE 사용할 수 있으니

  압축 테이블을 특히 주의

 

 

1. TEMP 테이블스페이스별 사용량 확인 SQL  

SELECT inst_id,
       tablespace_name,
       segment_file,
       Round(( total_blocks * 8 ) / 1024 / 1024, 2) TOTAL_GB,
       Round(( used_blocks * 8 ) / 1024 / 1024, 2)  USED_GB,
       Round(( free_blocks * 8 ) / 1024 / 1024, 2)  FREE_GB,
       Round(( max_blocks * 8 ) / 1024 / 1024, 2)   MAX_GB,
       free_requests,
       freed_extents
FROM   gv$sort_segment
ORDER  BY tablespace_name,
          inst_id;

 

 

2. 세션별 TEMP 테이블스페이스 사용량 확인 SQL 

SELECT B.tablespace,
       A.sql_id,
       A.username,
       A.osuser,
       Round(B.blocks * 8 / 1024 / 1024, 2) USED_GB,
       A.sid,
       A.serial#,
       C.spid,
       A.status
FROM   gv$session A,
       gv$sort_usage B,
       gv$process C
WHERE  A.saddr = B.session_addr
       AND A.paddr = C.addr
ORDER  BY B.tablespace,
          used_gb DESC,
          B.segfile#,
          B.segblk#,
          B.blocks;