[Oracle] DBA_HIST_ACTIVE_SESS_HISTORY (ASH의 과거 데이터)

1. DBA_HIST_ACTIVE_SESS_HISTORY란?

• 무엇?
  V$ACTIVE_SESSION_HISTORY(ASH)의 내용을 AWR 스냅샷에 주기적으로 저장해 두는 히스토리 뷰
  → “과거의 ASH”라고 보면 됨.
• 기본 개념
  • ASH는 1초마다 “지금 active 상태인 세션”을 샘플링해서 한 줄씩 적재.
  • 이 중 일부가 AWR 스냅샷에 저장되면서 DBA_HIST_ACTIVE_SESS_HISTORY에 남음.
  • 각 로우 = “특정 시점에 active였던 한 세션의 스냅샷”.
• 주의 (라이선스)
  • ASH, AWR, DBA_HIST_* 뷰 직접 조회는 Diagnostics Pack 라이선스에 포함되는 기능.
  • 운영 DB에서 활용할 땐 반드시 라이선스 보유 여부를 확인해야 함.
• 현재 ASH vs. 히스토리 ASH
  • 현재 시점 중심 → V$ACTIVE_SESSION_HISTORY
  • 과거 시점까지 포함한 장기 히스토리 → DBA_HIST_ACTIVE_SESS_HISTORY

---

2. 이 뷰로 볼 수 있는 주요 분석 관점

1. 시간대별 부하 패턴 분석 (피크 타임, CPU vs Wait 비율)
2. Top SQL / Top 세션 분석 (DB Time, Active Sample 기준)
3. 대기 이벤트 / Wait Class 분석 (IO, Concurrency, Network 등)
4. 락/Blocking Session, 경합 상황 분석
5. RAC 환경에서 인스턴스/GC 관련 분석
6. 사용자/모듈/서비스 관점의 업무 트래픽 분석
7. 실행 계획 변경·성능 저하 전후 분석
8. 에러/장애 발생 시점 근원 원인 분석

---

3. 시간대별 부하 패턴 분석

3-1. 피크 타임과 Active Session 수준

sample_time을 기준으로 일정 단위(예: 1분)로 묶어서
시간대별 Active Session 수를 보면, 피크 구간을 직관적으로 볼 수 있음.

SELECT
  TRUNC(sample_time, 'MI') AS tm,            -- 분 단위로 자르기
  COUNT(*) AS active_sess,                   -- 전체 active 샘플 수
  SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu_sess,
  SUM(CASE WHEN session_state = 'WAITING' THEN 1 ELSE 0 END) AS wait_sess
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
GROUP BY TRUNC(sample_time, 'MI')
ORDER BY tm;

이걸로 할 수 있는 분석:

• 언제 사용자가 몰리는지 (피크 타임)
• 그 피크 타임에
  • CPU에 물려 있는지
  • Wait(대기)에 묶여 있는지
    → CPU 부족인지, IO/락/네트워크 문제인지 1차 감이 옴.

---

4. Top SQL / Top 세션 분석

4-1. “DB Time에 많이 기여한 SQL” 찾기

ASH 샘플 개수는 곧 “해당 SQL이 Active 상태였던 시간”에 비례하므로
샘플 수를 기준으로 Top SQL을 뽑을 수 있음.

SELECT
  sql_id,
  COUNT(*) AS active_samples,
  ROUND(COUNT(*) / 60, 1) AS active_minutes
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY active_samples DESC
FETCH FIRST 10 ROWS ONLY;

이걸로 할 수 있는 것:

• 특정 시간 구간 동안 DB Time을 많이 먹은 SQL 후보 찾기
• AWR SQL 리포트( DBA_HIST_SQLSTAT )의 Top SQL과 교차 확인
• 장애 발생 시간대에 어떤 SQL이 가장 오래, 많이 돌았는지 파악

4-2. 특정 세션/사용자 관점에서 분석

예를 들어 특정 사용자의 세션이 느렸다고 하면:

SELECT
  sample_time,
  session_id, session_serial#,
  sql_id,
  event,
  session_state,
  module, action,
  machine, program
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND user_id = (SELECT user_id FROM dba_users WHERE username = 'APP_USER')
ORDER BY sample_time;

이걸로 할 수 있는 것:

• 그 사용자의 세션이 언제 어떤 SQL을 실행했고, 어떤 이벤트로 기다렸는지 타임라인으로 확인
• 클라이언트 MODULE / ACTION / MACHINE / PROGRAM 기준으로 어떤 배치/어떤 화면이 문제였는지 추적

---

5. Wait Event / Wait Class 분석

5-1. Top Wait Event

SELECT
  wait_class,
  event,
  COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND session_state = 'WAITING'
GROUP BY wait_class, event
ORDER BY samples DESC
FETCH FIRST 15 ROWS ONLY;

이걸로 할 수 있는 것:

• IO( User I/O, System I/O ),
  Concurrency, Application, Network 등 Wait Class별 비중 확인
• 대표적인 이벤트(예: db file sequential read, enq: TX - row lock contention 등) 파악
• “CPU 문제인지, IO 문제인지, 락 문제인지”를 그 시간 구간에 대해 정리 가능

5-2. 특정 Wait Class만 집중 분석

예: 락/경합 관련만 보고 싶을 때:

SELECT
  event,
  COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND session_state = 'WAITING'
  AND wait_class = 'Concurrency'
GROUP BY event
ORDER BY samples DESC;

---

6. 락 / Blocking Session 분석

DBA_HIST_ACTIVE_SESS_HISTORY에는 blocking 세션 정보도 같이 찍힘.

SELECT
  sample_time,
  session_id, session_serial#,
  sql_id,
  event,
  blocking_session,
  blocking_session_serial#,
  blocking_inst_id
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND session_state = 'WAITING'
  AND blocking_session IS NOT NULL
ORDER BY sample_time;

이걸로 할 수 있는 것:

• “그 시간에 어떤 세션이 다른 세션들을 막고 있었는지” 추적
• blocking_session / blocking_inst_id를 기준으로
  • 블로킹 세션의 SQL, 모듈, 사용자까지 역추적 (다시 DBA_HIST_ACTIVE_SESS_HISTORY를 자기 자신과 조인)
• 이미 지나간 시간대의 락 문제라도, ASH 히스토리만 남아 있다면 어느 정도 복원 가능

---

7. RAC 환경 / 멀티 인스턴스 분석

RAC 환경에서는 instance_number, blocking_inst_id, GC 관련 이벤트 등을 활용해서
인스턴스 간 부하 편차 및 Global Cache 병목을 볼 수 있음.

7-1. 인스턴스별 Active Session 비교

SELECT
  instance_number,
  COUNT(*) AS active_samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
GROUP BY instance_number
ORDER BY instance_number;

→ 특정 인스턴스만 유난히 부하가 높은지 확인.

7-2. GC 이벤트(예: gc cr request) 비중

SELECT
  instance_number,
  event,
  COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND event LIKE 'gc %'
GROUP BY instance_number, event
ORDER BY samples DESC;

→ RAC에서 자주 문제 되는 interconnect / gc 관련 병목의 정도와 위치 확인.

---

8. 사용자 / 모듈 / 서비스 관점 분석

애플리케이션 튜닝에서는 “SQL_ID”뿐 아니라
어떤 화면/어떤 배치/어떤 서비스가 문제인지 보는 것이 더 현실적일 때가 많음.

DBA_HIST_ACTIVE_SESS_HISTORY에는 아래와 같은 정보가 들어있음:

• user_id (→ DBA_USERS 조인해서 사용자명)
• module, action
• client_id
• machine, program
• service_hash (서비스 별로도 집계 가능)
• CDB 환경이라면 PDB 관련 컬럼도 버전에 따라 존재

예: 모듈별 부하

SELECT
  NVL(module, 'UNKNOWN') AS module,
  COUNT(*) AS active_samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
GROUP BY NVL(module, 'UNKNOWN')
ORDER BY active_samples DESC
FETCH FIRST 10 ROWS ONLY;

→ 어떤 모듈(예: ‘주문조회’, ‘배치_월마감’)이 DB 부하를 많이 유발하는지 파악.

---

9. 실행 계획 변경 / 성능 저하 전후 비교

같은 SQL_ID라 하더라도, 시점에 따라 plan_hash_value가 달라질 수 있음.
이걸 이용해서 실행 계획 변경 전후 성능 차이를 추적 가능.

SELECT
  sql_id,
  plan_hash_value,
  COUNT(*) AS active_samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:from, 'YYYYMMDDHH24MISS')
  AND TO_DATE(:to,   'YYYYMMDDHH24MISS')
  AND sql_id = :sql_id
GROUP BY sql_id, plan_hash_value
ORDER BY active_samples DESC;

활용 포인트:

• 특정 SQL_ID에 대해 과거에는 plan_hash_value = A였고, 최근에는 B로 바뀌었다면
  • 두 계획의 Active Sample 수를 비교 → 어느 쪽이 더 무거운지
  • AWR SQL Plan, Explain Plan과 연계해서 구체적인 플랜 비교
• 통계 갱신, 인덱스 생성/삭제, 파라미터 변경 이후 플랜 변화 감지

---

10. 에러 / 장애 시점 분석 (ORA-xxxx 대응)

alert.log나 모니터링 시스템에서 에러 시각만 알고 있을 때,
그 시각 전·후로 DBA_HIST_ACTIVE_SESS_HISTORY를 보면 “그때 DB 안에서 무슨 일이 있었는지”를 복원할 수 있음.

예: ORA-04036 (PGA_AGGREGATE_LIMIT) 발생 시점 전후로,
pga memory operation 이벤트를 기준으로 Top SQL을 뽑는 식.

SELECT
  sql_id,
  COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
  TO_DATE(:error_time, 'YYYYMMDDHH24MISS') - INTERVAL '10' MINUTE
  AND TO_DATE(:error_time, 'YYYYMMDDHH24MISS') + INTERVAL '5' MINUTE
  AND event = 'pga memory operation'
GROUP BY sql_id
ORDER BY samples DESC;