ASH 데이터와 TKPROF 비교: SQL 추적 도구인 TKPROF와 ASH의 차이점.

서버 이관 프로젝트를 진행하는 과정에서 데이터베이스 성능을 지속적으로 모니터링하고 최적화하는 것은 매우 중요한 과제다. 특히, SQL 성능 저하의 원인을 분석하고 최적화하기 위해 다양한 성능 추적 도구를 활용해야 한다.

 

Oracle에서는 SQL 실행 및 성능을 분석하는 대표적인 두 가지 도구로 **ASH(Active Session History)와 TKPROF(SQL Trace Analyzer)**를 제공한다.

• ASH는 초 단위로 활성 세션 데이터를 기록하여, 실행 중인 SQL과 대기 이벤트를 실시간으로 분석하는 도구다.
• TKPROF는 SQL 트레이스를 수행하여 특정 세션에서 실행된 SQL의 실행 통계를 분석하는 도구다.

두 도구는 모두 SQL 성능 분석에 활용되지만, 분석 방식과 적용 범위가 다르다. ASH는 데이터베이스 전체의 성능을 실시간으로 모니터링하는 데 유용하며, TKPROF는 특정 SQL을 보다 깊이 있게 분석하는 데 적합하다.

 

본 글에서는 ASH와 TKPROF의 차이점을 비교하고, 각 도구가 어떤 상황에서 유용하게 활용될 수 있는지 분석한다. 이를 통해 서버 이관 후 SQL 튜닝과 성능 분석을 효과적으로 수행할 수 있도록 한다.

---

ASH와 TKPROF의 개요

ASH(Active Session History)란?

ASH(Active Session History)는 Oracle이 초 단위로 활성 세션 정보를 수집하여 성능 분석을 가능하게 하는 기능이다.

• SGA(Shared Global Area) 메모리에 저장되며, 데이터베이스의 실시간 성능 모니터링에 활용된다.
• 실행 중인 SQL의 성능을 분석하고, 대기 이벤트(WAIT EVENT), CPU 사용량, 세션 활동 등을 모니터링할 수 있다.
• 과거 일정 시간 동안의 성능 문제를 분석할 수 있으며, 필요할 경우 AWR(Automatic Workload Repository) 스냅샷과 함께 활용할 수 있다.

TKPROF(SQL Trace Analyzer)란?

TKPROF는 SQL 트레이스를 수행하여 특정 세션에서 실행된 SQL의 실행 통계를 상세히 분석하는 도구다.

• SQL의 실행 횟수, 실행 시간, 디스크 I/O, CPU 사용량 등을 상세하게 확인할 수 있다.
• 실행 계획(EXPLAIN PLAN)을 분석하여 비효율적인 SQL이 어떤 방식으로 실행되고 있는지 파악할 수 있다.
• 특정 SQL의 실행 성능을 튜닝할 때, 실행 계획을 비교하고 최적화하는 데 유용하다.

---

ASH와 TKPROF의 주요 차이점 비교

비교 항목ASH (Active Session History)TKPROF (SQL Trace Analyzer)

데이터 수집 방식	초 단위로 활성 세션 기록 (실시간)	특정 세션의 SQL 실행을 트레이스하여 상세 기록
저장 위치	SGA(메모리)	Trace 파일(디스크)
보관 기간	메모리 내 일정 시간 동안 유지	명시적으로 트레이스를 수행한 경우만 저장
실시간 분석 가능 여부	가능	불가능 (트레이스 완료 후 분석)
성능 분석 범위	전체 데이터베이스의 성능 모니터링	특정 SQL 및 세션에 대한 상세 분석
대표적인 분석 데이터	CPU 사용량, 대기 이벤트, 세션 활동 등	실행 횟수, 응답 시간, 디스크 I/O, 실행 계획 등
활용 목적	단기적인 성능 문제 분석 및 모니터링	특정 SQL의 실행 성능을 상세하게 분석

---

ASH 데이터를 활용한 SQL 성능 분석 방법

1. 현재 실행 중인 SQL 조회

현재 데이터베이스에서 실행 중인 SQL을 확인하려면 ASH 데이터를 활용하여 활성 세션을 조회할 수 있다.

SELECT    sql_id,    COUNT(*) AS execution_count  FROM    v$active_session_history  WHERE    sample_time >= SYSDATE - (10 / 1440) -- 최근 10분 조회  GROUP BY sql_id ORDER BY execution_count DESC;

• execution_count → 실행 중인 SQL의 빈도 확인.

이 데이터를 활용하면 현재 실행 중인 SQL을 모니터링하고, 특정 SQL이 데이터베이스 성능을 저하시킬 가능성이 있는지 분석할 수 있다.

---

2. 특정 SQL의 대기 이벤트 분석

SQL 실행 시 대기 이벤트(WAIT EVENT)가 많이 발생하면 성능 저하가 발생할 수 있다.

SELECT    sql_id,    event,    COUNT(*) AS wait_count  FROM    v$active_session_history  WHERE    sample_time >= SYSDATE - (10 / 1440)  GROUP BY    sql_id,    event  ORDER BY    wait_count DESC;

• wait_count → SQL 실행 시 발생한 대기 이벤트 횟수.

이 데이터를 분석하면 SQL이 어떤 이유로 성능 저하를 일으키는지 파악할 수 있으며, 불필요한 대기 시간을 줄이는 최적화 작업을 수행할 수 있다.

---

TKPROF를 활용한 SQL 실행 분석 방법

1. SQL Trace 활성화 및 TKPROF 분석

SQL 실행 성능을 자세히 분석하려면 SQL 트레이스를 활성화하고 TKPROF를 활용하여 실행 성능을 분석해야 한다.

1) SQL Trace 활성화

먼저, 특정 세션에서 SQL 트레이스를 활성화한다.

ALTER SESSION SET sql_trace = TRUE;

이후, 애플리케이션에서 실행하는 SQL을 수행한 뒤, 트레이스를 비활성화한다.

ALTER SESSION SET sql_trace = FALSE;

2) TKPROF 실행

SQL Trace 파일이 생성된 후, 이를 분석하기 위해 TKPROF를 실행한다.

tkprof tracefile.trc output.txt explain=username/password sys=no

• tracefile.trc → SQL 실행 정보가 저장된 파일.
• output.txt → 분석된 결과 파일.
• explain=username/password → SQL 실행 계획 분석 추가.

---

2. TKPROF 실행 결과 분석

TKPROF 결과 파일에서는 각 SQL의 실행 횟수, 평균 실행 시간, 디스크 I/O, 실행 계획 등을 확인할 수 있다.

1) SQL 실행 횟수 및 응답 시간 분석

• call count → SQL이 몇 번 실행되었는지 확인.
• cpu → SQL 실행 시 사용된 CPU 시간.
• elapsed → SQL 실행에 소요된 총 시간.
• disk, query → 디스크 및 메모리에서 읽은 블록 수.

이 데이터를 활용하면 SQL 실행 성능을 세부적으로 분석하고, 디스크 I/O 부하가 높은 SQL을 최적화할 수 있다.

---

ASH와 TKPROF 활용 전략

ASH가 적합한 경우

• 실시간 성능 모니터링이 필요한 경우.
• 데이터베이스 전체의 성능을 분석하고, 현재 실행 중인 SQL의 성능을 모니터링할 때.
• 특정 시간 동안의 CPU 사용량, 대기 이벤트, 세션 활동을 분석할 때.

TKPROF가 적합한 경우

• 특정 SQL의 실행 성능을 상세하게 분석해야 하는 경우.
• SQL 실행 계획과 디스크 I/O, CPU 사용량을 비교하여 성능을 최적화해야 할 때.
• SQL 실행 시간을 줄이기 위해 실행 패턴을 세부적으로 분석해야 할 때.

---

ASH와 TKPROF는 모두 데이터베이스 성능 분석에 필수적인 도구지만, 분석 방식과 활용 목적이 다르다.

 

ASH는 데이터베이스의 전체적인 성능을 실시간으로 모니터링하는 데 적합하며, TKPROF는 특정 SQL의 실행 패턴을 상세하게 분석하는 데 최적화되어 있다.