ASH 데이터를 활용한 리소스 경합 분석: CPU, 메모리, 디스크 경합 문제를 해결하는 방법.

서버 이관 프로젝트를 진행하는 과정에서 데이터베이스 성능 최적화는 매우 중요한 과제다. 새로운 서버 환경에서는 CPU, 메모리, 디스크와 같은 주요 하드웨어 리소스의 사용 방식이 기존 환경과 달라질 수 있으며, 이에 따라 예상치 못한 성능 문제가 발생할 가능성이 높다.

 

특히, CPU 사용률이 급격히 증가하거나, 메모리 부족으로 인해 SQL 실행 속도가 저하되거나, 디스크 I/O 경합이 발생하면 애플리케이션 전체의 성능이 저하될 수 있다. 이러한 문제를 해결하려면 리소스 경합(Resource Contention)의 원인을 정확히 파악하고 최적화해야 한다.

 

Oracle은 ASH(Active Session History) 데이터를 활용하여 데이터베이스에서 발생하는 리소스 경합을 분석하고 해결하는 강력한 기능을 제공한다. ASH는 실시간으로 활성 세션의 상태를 기록하고, CPU, 메모리, 디스크 사용량을 분석할 수 있도록 지원한다. 이를 활용하면 CPU 부하가 높은 SQL을 찾고, 메모리 사용이 과도한 세션을 식별하며, 디스크 I/O 대기를 유발하는 트랜잭션을 분석할 수 있다.

 

본 글에서는 ASH 데이터를 활용하여 CPU, 메모리, 디스크 리소스 경합 문제를 분석하는 방법과, 이를 해결하는 최적화 전략을 상세히 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 문제를 효과적으로 감지하고 해결할 수 있도록 한다.

---

리소스 경합(Resource Contention)이란?

리소스 경합이란 다수의 프로세스나 세션이 동일한 리소스를 동시에 사용하려고 하면서 발생하는 성능 저하 현상을 의미한다. 일반적으로 CPU, 메모리, 디스크 I/O에서 경합이 발생할 수 있으며, 특정 애플리케이션이나 SQL이 과도한 리소스를 사용하면 다른 세션의 실행이 지연될 수 있다.

CPU 경합(CPU Contention)

• CPU 사용량이 급증하면 전체적인 시스템 성능이 저하될 수 있다.
• 주로 CPU 집약적인 SQL(대규모 조인, 복잡한 연산이 포함된 쿼리 등)이 실행될 때 발생.
• 다중 사용자 환경에서는 CPU 사용량이 특정 세션에 집중되면, 다른 세션의 SQL 실행 속도가 느려질 수 있다.

메모리 경합(Memory Contention)

• PGA(Process Global Area)와 SGA(Shared Global Area) 사용량이 과도하면, SQL 실행 속도가 저하될 수 있다.
• 메모리 부족이 발생하면 SQL이 Temp Tablespace를 사용하여 디스크 I/O를 발생시키면서, 추가적인 성능 저하를 유발할 수 있다.
• 대량의 정렬(SORT) 연산, 해시 조인(Hash Join), 인덱스 생성 등의 작업이 메모리 부족을 유발할 가능성이 높다.

디스크 I/O 경합(Disk I/O Contention)

• 디스크에서 데이터를 읽거나 쓰는 속도가 느려지면, SQL 실행 시간이 증가하고 전체적인 트랜잭션 속도가 저하될 수 있다.
• 풀 테이블 스캔(Full Table Scan), 인덱스 미사용, 대량의 데이터 로딩 등이 디스크 I/O 부하를 증가시키는 주요 원인이다.

---

ASH 데이터를 활용한 리소스 경합 분석 방법

1. CPU 경합 분석

CPU 사용량이 높은 SQL을 분석하려면 ASH 데이터를 활용하여 CPU에서 실행된 세션을 조회할 수 있다.

SELECT    sql_id,    COUNT(*) AS cpu_usage  FROM    v$active_session_history  WHERE    session_state = 'ON CPU'    AND sample_time >= SYSDATE - (30 / 1440) -- 최근 30분간 조회 GROUP BY    sql_id  ORDER BY    cpu_usage DESC;

• cpu_usage → 특정 SQL이 CPU를 점유한 횟수.

이 데이터를 활용하면 CPU 사용량이 높은 SQL을 식별하고, 실행 계획을 최적화하거나 병렬 처리를 적용하여 CPU 부하를 줄일 수 있다.

---

2. 메모리 경합 분석

메모리 경합이 발생하는 SQL을 분석하려면 Temp Tablespace를 과도하게 사용하는 세션을 조회해야 한다.

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 >= SYSDATE - (30 / 1440)  GROUP BY    sql_id  ORDER BY    temp_usage DESC;

• temp_usage → Temp Tablespace를 사용한 횟수.

이 데이터를 활용하면 메모리를 과도하게 사용하는 SQL을 식별하고, PGA 크기를 조정하거나 SQL 실행 계획을 최적화할 수 있다.

---

3. 디스크 I/O 경합 분석

디스크 I/O 부하를 유발하는 SQL을 분석하려면 User I/O 관련 대기 이벤트를 조회해야 한다.

SELECT    sql_id,    COUNT(*) AS io_waits  FROM    v$active_session_history  WHERE    wait_class = 'User I/O'    AND sample_time >= SYSDATE - (30 / 1440)  GROUP BY    sql_id  ORDER BY    io_waits DESC;

• io_waits → 디스크 I/O 대기로 인해 SQL이 지연된 횟수.

이 데이터를 활용하면 디스크 부하를 유발하는 SQL을 찾아 인덱스를 추가하거나, 실행 계획을 조정하여 디스크 사용량을 줄일 수 있다.

---

리소스 경합을 해결하기 위한 최적화 전략

1. CPU 경합 해결 방법

• CPU 사용량이 높은 SQL을 분석하고 실행 계획을 최적화.
• 병렬 처리(PARALLEL 힌트) 적용을 통해 CPU 부하를 분산.
• CPU 사용률이 높은 세션을 제한하도록 리소스 매니저 설정.

ALTER SYSTEM SET resource_manager_plan = 'DEFAULT_PLAN';

---

2. 메모리 경합 해결 방법

• PGA 메모리 크기를 조정하여 정렬 및 해시 조인 연산을 메모리 내에서 처리하도록 설정.
• 불필요한 Temp Tablespace 사용을 줄이기 위해 실행 계획을 최적화.
• SQL 실행 방식 변경(예: Hash Join을 Nested Loop Join으로 변경).

ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;

---

3. 디스크 I/O 경합 해결 방법

• 자주 조회되는 테이블에 인덱스를 추가하여 Full Table Scan을 방지.
• 대량의 데이터를 처리하는 SQL을 배치 작업으로 수행하여 부하를 줄임.
• I/O 부하가 큰 테이블을 파티셔닝하여 디스크 접근 효율을 향상.

CREATE INDEX emp_dept_idx ON employees(department);

---

ASH 데이터를 활용하면 데이터베이스에서 발생하는 CPU, 메모리, 디스크 I/O 경합 문제를 실시간으로 분석하고 최적화할 수 있다.

 

특히, CPU 사용량이 높은 SQL을 최적화하고, 메모리 사용이 과도한 세션을 식별하며, 디스크 I/O 부하를 줄이면 데이터베이스 성능을 크게 개선할 수 있다.