서버 이관 프로젝트를 진행하는 과정에서 데이터베이스 성능을 효율적으로 모니터링하고 최적화하는 것은 매우 중요한 과제다. 새로운 환경에서는 기존과 다른 성능 패턴이 나타날 수 있으며, 예상치 못한 병목 현상이나 리소스 경합이 발생할 가능성이 크다.
이러한 문제를 사전에 감지하고 해결하기 위해 Oracle은 강력한 성능 모니터링 도구인 ASH(Active Session History)와 OEM(Oracle Enterprise Manager)을 제공한다.
- ASH 데이터는 초 단위로 데이터베이스의 활성 세션 정보를 수집하여 실시간 분석을 가능하게 하는 기능이다. 이를 활용하면 CPU 사용률, 대기 이벤트, 세션 활동 등을 분석하여 성능 저하의 원인을 빠르게 파악할 수 있다.
- **OEM(Oracle Enterprise Manager)**은 데이터베이스 성능을 실시간으로 모니터링하고, ASH 데이터를 시각적으로 분석할 수 있도록 지원하는 강력한 관리 도구다.
이 두 가지 도구를 연계하여 활용하면 SQL 성능 분석, 리소스 경합 감지, 세션 추적, 문제 해결 등의 작업을 보다 직관적으로 수행할 수 있다.
본 글에서는 ASH 데이터를 OEM에서 활용하는 방법과, 이를 통해 데이터베이스 성능을 최적화하는 전략을 상세히 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 문제를 효과적으로 감지하고 해결할 수 있도록 한다.
ASH와 OEM을 연계하여 활용해야 하는 이유
1. 실시간 및 과거 성능 분석 가능
- ASH 데이터는 초 단위로 활성 세션을 기록하므로, 특정 시간대의 성능 문제를 빠르게 분석할 수 있다.
- OEM에서는 이 데이터를 시각적으로 분석할 수 있어 문제 원인을 직관적으로 파악할 수 있다.
2. 대기 이벤트 및 세션 활동 모니터링
- SQL 실행 중 발생하는 대기 이벤트(WAIT EVENT)를 ASH에서 분석하고, OEM에서 그래픽으로 표현하면 특정 세션이 어디에서 병목을 겪고 있는지 쉽게 파악할 수 있다.
- 세션별 리소스 사용량을 OEM에서 분석하면 CPU, 메모리, 디스크 I/O 경합 문제를 보다 명확하게 진단할 수 있다.
3. SQL 실행 패턴 분석 및 튜닝
- 자주 실행되는 SQL과 실행 시간이 긴 SQL을 ASH 데이터를 기반으로 분석하고, OEM에서 실행 계획(EXPLAIN PLAN) 및 튜닝 어드바이저(SQL Tuning Advisor) 기능을 활용하여 최적화할 수 있다.
4. 블로킹 세션 감지 및 해결
- OEM에서는 ASH 데이터를 활용하여 블로킹 세션을 실시간으로 감지하고, 문제를 유발하는 트랜잭션을 종료할 수 있는 기능을 제공한다.
ASH 데이터를 OEM에서 분석하는 방법
1. OEM에서 ASH 데이터 조회하기
OEM에서 ASH 데이터를 조회하려면 SQL Monitor 또는 Performance Hub를 활용하면 된다.
SQL Monitor에서 ASH 데이터 확인 방법
- OEM에 접속 후 “Performance” > “SQL Monitor”로 이동.
- 실행된 SQL 목록을 확인하고, 실행 시간이 긴 SQL을 클릭하여 상세 정보 조회.
- ASH 데이터를 활용하여 SQL 실행 중 발생한 대기 이벤트, CPU 사용량, 디스크 I/O 상태를 확인.
Performance Hub에서 ASH 데이터 활용
- OEM의 Performance Hub(성능 허브)에서 ASH Analytics 옵션을 선택.
- 특정 시간대를 선택하여 CPU 사용률이 급증한 구간의 세션 정보를 확인.
- 특정 SQL이 과도한 리소스를 사용했거나, 대기 이벤트가 많았는지 분석.
2. OEM에서 특정 SQL의 ASH 데이터 분석
특정 SQL의 성능을 분석하려면 SQL Execution Detail을 활용하면 된다.
- OEM에서 “Performance” > “SQL Details” 로 이동.
- 분석할 SQL ID를 입력하여 해당 SQL의 실행 기록을 조회.
- ASH 데이터를 기반으로 해당 SQL이 CPU에서 실행된 시간, 대기 이벤트 발생 시간, 실행 계획을 확인.
아래 SQL을 활용하면 ASH 데이터를 수동으로 조회할 수도 있다.
| SELECT sql_id, COUNT(*) AS execution_count, AVG(elapsed_time) AS avg_execution_time FROM v$active_session_history WHERE sql_id = 'SQL_ID' AND sample_time >= SYSDATE - (1) -- 최근 24시간 조회 GROUP BY sql_id ORDER BY avg_execution_time DESC; |
- execution_count → 해당 SQL이 실행된 횟수.
- avg_execution_time → 해당 SQL의 평균 실행 시간.
이 데이터를 OEM과 함께 활용하면 SQL 튜닝 어드바이저(SQL Tuning Advisor)를 실행하여 실행 계획을 최적화할 수 있다.
3. ASH 데이터를 활용한 블로킹 세션 감지
블로킹 세션이 발생한 경우 OEM에서 Blocking Sessions를 조회하면 어떤 세션이 다른 세션을 차단하고 있는지 시각적으로 확인할 수 있다.
- OEM에서 “Performance” > “Blocking Sessions” 메뉴로 이동.
- 블로킹 세션이 발생한 트랜잭션을 확인하고, 차단된 세션 정보를 조회.
- ASH 데이터를 활용하여 해당 세션이 실행한 SQL을 분석하고, 필요하면 강제 종료.
아래 SQL을 활용하면 수동으로 블로킹 세션을 조회할 수도 있다.
| SELECT blocking_session, session_id, COUNT(*) AS wait_count FROM v$active_session_history WHERE session_state = 'WAITING' AND blocking_session IS NOT NULL AND sample_time >= SYSDATE - (10 / 1440) -- 최근 10분간 조회 GROUP BY blocking_session, session_id ORDER BY wait_count DESC; |
- blocking_session → 다른 세션을 차단하고 있는 세션 ID.
- session_id → 블로킹으로 인해 대기 중인 세션 ID.
이 데이터를 OEM과 함께 활용하면 문제 세션을 종료하고 트랜잭션 충돌을 방지할 수 있다.
OEM을 활용한 ASH 데이터 기반 최적화 전략
1. SQL 튜닝 어드바이저 활용
- ASH 데이터를 기반으로 실행 시간이 긴 SQL을 식별한 후, OEM에서 SQL 튜닝 어드바이저를 실행하여 최적화 방법을 추천받을 수 있다.
| EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id => 'SQL_ID', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'sql_tuning_task' ); |
2. 대기 이벤트 최적화
- OEM의 ASH Analytics를 활용하여 특정 대기 이벤트가 증가하는 구간을 분석하고, 인덱스 추가 또는 실행 계획 변경을 적용.
- 대기 이벤트가 높은 SQL은 필요하면 병렬 처리(PARALLEL 힌트) 적용.
| SELECT event, COUNT(*) AS wait_count FROM v$active_session_history WHERE sample_time >= SYSDATE - (1) GROUP BY event ORDER BY wait_count DESC; |
3. 세션 모니터링 및 블로킹 세션 해결
- OEM에서 Blocking Sessions를 실시간으로 확인하고, 필요하면 세션을 강제 종료.
ASH 데이터를 활용하면 데이터베이스 성능 문제를 실시간으로 분석할 수 있으며, 이를 OEM과 연계하면 더욱 직관적으로 성능을 모니터링하고 최적화할 수 있다.
특히, SQL 실행 패턴 분석, 대기 이벤트 감지, 블로킹 세션 해결 등의 작업을 OEM에서 간편하게 수행할 수 있으며, SQL 튜닝 어드바이저를 통해 자동으로 SQL을 최적화할 수도 있다.
