ASH에서 Temp Tablespace 사용량 분석: 정렬 연산 등의 임시 테이블스페이스 사용량을 분석.

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스의 성능을 지속적으로 유지하는 것은 필수적인 과제다. 특히 Temp Tablespace(임시 테이블스페이스)의 사용량이 증가하면 성능 저하가 발생할 가능성이 높다.

 

Temp Tablespace는 대량의 정렬(SORT) 연산, 해시(Hash) 조인, 인덱스 생성, 그룹핑(Group By), 윈도우 함수 등의 작업을 수행할 때 디스크에 데이터를 임시로 저장하는 공간이다. 일반적으로 이러한 작업은 메모리(SGA의 PGA)에서 처리되지만, 사용 가능한 메모리가 부족하면 Temp Tablespace를 사용하게 되며, 이로 인해 디스크 I/O 부하가 급격히 증가할 수 있다.

 

이러한 문제를 해결하기 위해 ASH(Active Session History) 데이터를 활용하여 특정 SQL이 Temp Tablespace를 얼마나 사용하고 있는지 분석하는 과정이 필요하다. ASH 데이터를 활용하면 어떤 SQL이 임시 테이블스페이스를 많이 사용하는지, 특정 시간대에 Temp Tablespace 사용량이 급증하는지, 특정 작업이 디스크 I/O에 어떤 영향을 미치는지 실시간으로 분석할 수 있다.

 

본 글에서는 ASH 데이터를 활용하여 Temp Tablespace 사용량을 분석하는 방법과, 이를 최적화하는 기법을 상세히 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 저하 문제를 조기에 감지하고 대응할 수 있도록 한다.

---

Temp Tablespace 사용량이 증가하는 주요 원인

Temp Tablespace의 사용량이 증가하면 디스크 I/O 부하가 커지고, SQL 실행 시간이 길어지면서 전체적인 시스템 성능이 저하될 가능성이 높다. 일반적으로 다음과 같은 상황에서 Temp Tablespace 사용량이 급격히 증가할 수 있다.

대량 정렬(SORT) 연산

• ORDER BY, GROUP BY, DISTINCT 등의 SQL 연산을 수행할 때 메모리(PGA)에서 처리할 수 없으면 Temp Tablespace를 사용하게 된다.
• 정렬 대상 데이터가 많거나, 대량의 데이터셋에서 DISTINCT 연산을 수행할 때 Temp Tablespace 사용량이 급증할 수 있다.

해시(Hash) 조인 및 Merge Join 연산

• 대용량 테이블 간의 조인을 수행할 때 해시(Hash) 또는 Merge Join이 발생하면, 임시 데이터 저장을 위해 Temp Tablespace가 사용된다.
• 특히 인덱스를 사용하지 않는 조인 연산이 발생하면 Temp Tablespace 사용량이 급격히 증가할 수 있다.

인덱스 생성 및 재구성(Index Rebuild)

• CREATE INDEX 또는 ALTER INDEX ... REBUILD 등의 작업을 수행할 때 임시 공간이 필요하며, 이 과정에서 Temp Tablespace를 사용한다.
• 대량의 인덱스를 생성하는 경우 Temp Tablespace 부족으로 인해 작업이 지연될 수도 있다.

PGA 메모리 부족

• Oracle은 기본적으로 정렬 및 해시 연산을 PGA 메모리에서 처리하지만, PGA 할당이 부족하면 Temp Tablespace를 사용하게 된다.
• PGA 메모리 크기를 적절히 조정하지 않으면 Temp Tablespace가 과도하게 사용될 수 있다.

이러한 문제를 해결하기 위해서는 ASH 데이터를 활용하여 Temp Tablespace 사용량이 많은 SQL을 분석하고 최적화하는 과정이 필요하다.

---

ASH 데이터를 활용한 Temp Tablespace 사용량 분석 방법

1. 특정 SQL의 Temp Tablespace 사용량 확인

특정 SQL이 Temp Tablespace를 얼마나 사용하고 있는지 분석하려면 ASH 데이터를 조회하여 'direct path read temp' 및 'direct path write temp' 대기 이벤트를 분석할 수 있다.

SELECT    sql_id,    COUNT(*) AS temp_usage  FROM    v$active_session_history  WHERE    event IN (     'direct path read temp', 'direct path write temp'   )    AND sample_time BETWEEN SYSDATE - (1 / 1440)    AND SYSDATE  GROUP BY    sql_id  ORDER BY    temp_usage DESC;

 

• event = 'direct path read temp': Temp Tablespace에서 데이터를 읽을 때 발생하는 이벤트.
• event = 'direct path write temp': Temp Tablespace에 데이터를 기록할 때 발생하는 이벤트.
• temp_usage: 특정 SQL에서 Temp Tablespace를 사용한 횟수.

이 데이터를 분석하면 어떤 SQL이 Temp Tablespace를 많이 사용하고 있는지 확인할 수 있으며, 해당 SQL의 실행 계획을 최적화할 수 있다.

---

2. 특정 세션의 Temp Tablespace 사용량 분석

Temp Tablespace를 과도하게 사용하는 특정 세션을 분석하려면 다음 SQL을 실행한다.

SELECT    session_id,    sql_id,    event,    COUNT(*) AS temp_io_count  FROM    v$active_session_history  WHERE    event IN (     'direct path read temp', 'direct path write temp'   )    AND sample_time BETWEEN SYSDATE - (1 / 1440)    AND SYSDATE  GROUP BY    session_id,    sql_id,    event  ORDER BY    temp_io_count DESC;

• session_id: Temp Tablespace를 많이 사용하는 세션 ID.
• sql_id: 실행된 SQL의 ID.
• event: Temp Tablespace와 관련된 대기 이벤트.
• temp_io_count: Temp Tablespace에서 읽기/쓰기가 발생한 횟수.

이 데이터를 활용하면 특정 세션이 Temp Tablespace를 과도하게 사용하는지 파악하고, 해당 세션을 튜닝할 수 있다.

---

3. 특정 테이블이 Temp Tablespace 사용량에 미치는 영향 분석

특정 테이블이 Full Table Scan 또는 비효율적인 조인으로 인해 Temp Tablespace를 과도하게 사용하는지 확인하려면 다음 SQL을 실행한다.

SELECT    o.object_name,    COUNT(*) AS temp_usage  FROM    v$active_session_history a    JOIN dba_objects o ON a.current_obj # = o.object_id  WHERE event IN ('direct path read temp', 'direct path write temp')       AND sample_time BETWEEN SYSDATE - (1/1440) AND SYSDATE GROUP BY o.object_name ORDER BY temp_usage DESC;

• object_name: Temp Tablespace 사용량이 높은 테이블명.
• temp_usage: 해당 테이블이 Temp Tablespace에서 읽기/쓰기를 수행한 횟수.

이 데이터를 활용하면 특정 테이블에서 발생하는 비효율적인 SQL을 찾아 인덱스 추가 또는 실행 계획을 최적화할 수 있다.

---

Temp Tablespace 사용량 최적화를 위한 해결 방법

1. PGA 메모리 크기 조정

• PGA 크기를 늘려 정렬 및 해시 조인 연산이 메모리에서 처리되도록 조정한다.
• 현재 설정 확인:
   

  SHOW PARAMETER pga_aggregate_target;
• PGA 크기 조정:
  ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=SPFILE;

---

2. 인덱스 활용 및 실행 계획 최적화

• ORDER BY, GROUP BY가 포함된 SQL의 경우 적절한 인덱스를 추가하여 정렬 연산을 최소화한다.
• 해시 조인이 아닌 Nested Loop Join이 적절한 경우 실행 계획을 변경하여 조인 방식을 최적화한다.

CREATE INDEX emp_dept_idx ON employees(department);

---

3. 불필요한 Temp Tablespace 사용 방지

• DISTINCT 연산을 줄이고 GROUP BY를 최적화한다.
• 대용량 데이터 조작 시 LIMIT을 설정하여 처리량을 조정한다.

SELECT DISTINCT name FROM employees; -- 비효율적인 SQL

SELECT name FROM employees GROUP BY name; -- 최적화된 SQL

---

 

ASH 데이터를 활용하면 특정 SQL이 Temp Tablespace를 과도하게 사용하는지를 실시간으로 분석하고, 정렬 연산 및 해시 조인으로 인해 발생하는 디스크 I/O 부하를 줄일 수 있다.

 

특히, PGA 크기를 조정하고, 적절한 인덱스를 추가하며, 실행 계획을 최적화하면 Temp Tablespace 사용량을 크게 줄일 수 있다.