SQL 실행 계획과 ASH 데이터 비교 분석: 실행 계획과 세션 데이터를 함께 분석하여 문제 해결.

서버 이관 프로젝트를 진행하는 과정에서 데이터베이스 성능을 유지하고 최적화하는 것은 필수적인 작업이다. 기존 환경과 새로운 환경의 차이로 인해 SQL 실행 속도, 트랜잭션 처리량, 리소스 사용량 등이 달라질 가능성이 있으며, 예상치 못한 성능 저하가 발생할 수도 있다.

 

SQL 실행 속도와 관련된 문제를 해결하기 위해서는 실행 계획(Execution Plan)과 ASH(Active Session History) 데이터를 함께 분석하는 접근법이 필요하다. 실행 계획을 보면 SQL이 어떤 방식으로 실행되고 있는지, 인덱스를 효율적으로 활용하고 있는지 등을 파악할 수 있으며, ASH 데이터를 분석하면 SQL이 실행되는 동안 어떤 대기 이벤트가 발생했는지, CPU 및 I/O 사용량이 어떻게 변화하는지 등을 실시간으로 확인할 수 있다.

 

본 글에서는 SQL 실행 계획과 ASH 데이터를 비교 분석하는 방법을 설명하고, 이를 활용하여 데이터베이스 성능 문제를 해결하는 실무적인 접근법을 제시한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 저하 문제를 효과적으로 해결할 수 있도록 한다.

---

1. SQL 실행 계획과 ASH 데이터 분석의 필요성

1.1 SQL 실행 계획(Execution Plan)이란?

SQL 실행 계획은 쿼리가 실행될 때 옵티마이저(Optimizer)가 결정한 실행 경로를 보여주는 정보이다. 실행 계획을 보면 어떤 인덱스를 사용하는지, 어떤 조인 방식이 적용되는지, 테이블 스캔 방식이 무엇인지 등을 확인할 수 있다.

실행 계획 분석을 통해 해결할 수 있는 문제:

• 비효율적인 Full Table Scan 발생 여부 확인
• 인덱스 활용 여부 및 최적화 가능성 판단
• Nested Loop Join, Hash Join 등 조인 방식이 적절한지 분석
• SORT, GROUP BY, DISTINCT 연산이 성능에 미치는 영향 파악

1.2 ASH(Active Session History) 데이터란?

ASH 데이터는 Oracle이 매 초마다 현재 실행 중인 세션 정보를 수집하는 실시간 성능 분석 도구이다. SQL 실행 계획이 SQL이 실행되는 방식에 대한 이론적인 분석을 제공한다면, ASH 데이터는 SQL이 실제로 실행되는 동안 발생한 리소스 사용량과 대기 이벤트를 실시간으로 분석하는 기능을 한다.

ASH 분석을 통해 해결할 수 있는 문제:

• SQL 실행 시 발생하는 대기 이벤트(Wait Event) 분석
• CPU 및 I/O 부하가 높은 SQL 식별
• 트랜잭션 대기 및 블로킹 세션 감지

SQL 실행 계획과 ASH 데이터를 함께 분석하면, SQL의 실행 방식과 실제 리소스 사용 패턴을 종합적으로 평가할 수 있어 더 효과적인 성능 최적화가 가능하다.

---

2. SQL 실행 계획과 ASH 데이터 비교 분석 방법

SQL 실행 계획과 ASH 데이터를 비교 분석하는 과정은 다음과 같이 진행된다.

2.1 SQL 실행 계획 조회 및 분석

특정 SQL의 실행 계획을 확인하여 인덱스 사용 여부, 조인 방식, 실행 경로 등을 분석한다.

EXPLAIN PLAN FOR  SELECT    *  FROM    employees  WHERE    department = 'Sales'; SELECT    *  FROM    TABLE(DBMS_XPLAN.DISPLAY);

• 실행 계획을 분석하여 Full Table Scan이 발생하는지, 적절한 인덱스가 사용되는지 확인한다.

실행 계획 분석 예시

단계연산(Operation)대상 테이블접근 방식(Access Method)예상 비용(Cost)

1	SELECT STATEMENT	-	-	100
2	TABLE ACCESS FULL	employees	Full Table Scan	95

• 문제점: Full Table Scan이 발생하고 있어 실행 속도가 느려질 가능성이 큼.
• 해결책: 적절한 인덱스를 추가하여 Index Scan을 유도하는 방식으로 실행 계획을 변경할 필요가 있음.

CREATE INDEX emp_dept_idx ON employees(department);

---

2.2 ASH 데이터를 활용한 SQL 실행 현황 분석

SQL 실행 계획을 분석한 후, 실제 실행 중인 SQL이 어떤 리소스를 사용하는지 확인해야 한다.

SELECT    sql_id,    session_id,    event,    wait_class,    time_waited  FROM    v$active_session_history  WHERE    sql_id = 'SQL_ID'  ORDER BY    time_waited DESC;

• sql_id: 실행된 SQL의 고유 ID.
• session_id: SQL을 실행한 세션의 ID.
• event: SQL이 실행되는 동안 발생한 대기 이벤트(Wait Event).
• time_waited: 대기 시간(밀리초).

이 데이터를 분석하면 SQL이 실행 중 어떤 문제로 인해 성능이 저하되는지 실시간으로 파악할 수 있다.

---

2.3 실행 계획과 ASH 데이터 비교 분석

예제: 특정 SQL의 실행 계획과 ASH 데이터 비교

항목실행 계획 분석 결과ASH 데이터 분석 결과해결 방법

인덱스 사용 여부	Full Table Scan 발생	디스크 I/O 대기 시간 증가	적절한 인덱스 추가
조인 방식	Hash Join 사용	CPU 사용량 증가	Nested Loop Join 적용
대기 이벤트	-	"db file scattered read" 증가	Full Table Scan 최소화
블로킹 세션 여부	-	"enq: TX - row lock contention" 발생	트랜잭션 크기 최적화

실행 계획과 ASH 데이터를 비교하면, 이론적으로 예상된 실행 방식과 실제 실행 중 발생하는 문제를 종합적으로 분석할 수 있어 최적화 방향을 쉽게 설정할 수 있다.

---

3. 실행 계획과 ASH 데이터를 활용한 SQL 최적화 방법

3.1 인덱스 활용을 통한 실행 계획 개선

• 실행 계획에서 Full Table Scan이 발생하면, 인덱스를 추가하여 Index Scan을 유도한다.

CREATE INDEX emp_dept_idx ON employees(department);

• 결과: 실행 계획이 개선되면서 디스크 I/O 대기 시간 감소.

---

3.2 조인 방식 최적화

• 실행 계획에서 Hash Join이 발생하는 경우, Nested Loop Join을 적용하여 CPU 사용량을 줄일 수 있음.

SELECT       /*+ USE_NL(e d) */   e.emp_id,    e.emp_name,    d.dept_name  FROM    employees e    JOIN departments d ON e.dept_id = d.dept_id;

• 결과: 실행 속도 향상 및 CPU 부하 감소.

---

3.3 트랜잭션 크기 조정

• ASH 데이터에서 트랜잭션 락 대기가 많다면, 트랜잭션을 작은 단위로 나누어 커밋(Commit) 주기를 조정한다.

BEGIN FOR emp IN (   SELECT      emp_id    FROM      employees    WHERE      department = 'Sales' ) LOOP  UPDATE    employees  SET    salary = salary * 1.1  WHERE    emp_id = emp.emp_id; COMMIT; END LOOP; END;

 

• 결과: 트랜잭션 대기 시간 단축 및 성능 개선.

---

 

SQL 실행 계획과 ASH 데이터를 비교 분석하면 SQL 실행 방식과 실제 실행 중 발생하는 성능 저하 원인을 종합적으로 파악할 수 있다.

특히, Full Table Scan, 비효율적인 조인 방식, 트랜잭션 락 대기 등의 문제를 해결하기 위해 실행 계획을 최적화하고, ASH 데이터를 활용하여 실시간 리소스 사용 패턴을 분석하는 것이 중요하다.