[오라클 Admin] Oracle Temp 사용량 및 SQL 분석 방법

오라클 TEMP 사용량 분석 방법

 

작업 환경:
OS: Oracle Linux 8.9
DB: Oracle 19c

 

Oracle Temp 사용량 증가 원인 및 SQL 분석

- Temp 사용량이 급격히 증가하면 ORA-01652 에러가 나타난다.

- ORA-01652: Temp 지정된 공간을 전부 사용해 더 이상 늘릴수 없다.

- Temp 공간이 너무 작아도 문제지만 그렇다고 계속 증가하는 것도 좋은 방법은 아니다.

- 사용량이 급격히 증가되는 이유와 원인을 분석한다.

- 대부분의 문제는 개발자가 대량의 데이터를 추출하는 SQL을 사용할 때 나타난다.

- SQL을 빠르게 kill하고 담당 개발자와 좋은 언어(?)로 잘 설득합니다.

 

 

Oracle Temp 사용량 분석

- FREE_GB 컬럼 데이터가 얼마나 남았는지 확인

SELECT
      A.TABLESPACE_NAME AS "TABLESPACE"
    , ROUND(D.TOTAL_GB) AS "TOTAL_GB"
    , ROUND(D.TOTAL_GB - SUM (A.USED_BLOCKS * D.BLOCK_SIZE) /1024/1024/1024) AS "FREE_GB"
    , ROUND(SUM(A.USED_BLOCKS * D.BLOCK_SIZE)/1024/1024/1024,2)              AS "USED_GB"
FROM
      V$SORT_SEGMENT A
    , (
        SELECT
              B.NAME
            , C.BLOCK_SIZE
            , SUM(C.BYTES/1024/1024/1024) AS "TOTAL_GB"
        FROM
              V$TABLESPACE B
            , V$TEMPFILE C
        WHERE B.TS#= C.TS#
        GROUP BY B.NAME, C.BLOCK_SIZE
      ) D
WHERE A.TABLESPACE_NAME = D.NAME
GROUP BY A.TABLESPACE_NAME, D.TOTAL_GB;

 

 

 

Oracle Temp 사용량 TOP SQL 분석

SELECT
      RANK() OVER(ORDER BY MAX(A.TEMP_SPACE_ALLOCATED) DESC) AS "RANK"
    , A.SQL_ID
    , A.INSTANCE_NUMBER AS "DB"
    , (SELECT B.USERNAME FROM DBA_USERS B WHERE B.USER_ID = A.USER_ID) AS "USERNAME"
    , A.PROGRAM
    , A.MACHINE
    , MAX(ROUND(A.TEMP_SPACE_ALLOCATED/1024/1024))      AS "TEMP_SIZE_MB"
    , MAX(ROUND(A.TEMP_SPACE_ALLOCATED/1024/1024/1024)) AS "TEMP_SIZE_GB"
FROM DBA_HIST_ACTIVE_SESS_HISTORY A
WHERE 1=1
AND A.SAMPLE_TIME >= TRUNC(SYSDATE)
AND A.TEMP_SPACE_ALLOCATED >= 10*1024*1024 --10MB
AND SQL_ID IS NOT NULL
GROUP BY A.INSTANCE_NUMBER, A.SQL_ID, A.USER_ID, A.PROGRAM, A.MACHINE;