서버 이관 프로젝트를 진행하면서 데이터베이스 성능 문제를 빠르게 감지하고 해결하는 것은 매우 중요한 작업이다. 특히, 특정 사용자가 실행하는 SQL이 성능 저하를 유발하거나, 과도한 리소스를 사용하여 시스템 전체의 성능에 영향을 미칠 가능성이 있다.
이러한 경우, 어떤 사용자가 어떤 SQL을 실행했는지 추적하고, 실행 패턴을 분석하여 성능을 최적화하는 것이 필요하다. 그러나 일반적인 SQL 로그만으로는 실시간 분석이 어렵고, 과거 데이터까지 조회하려면 별도의 로그 관리가 필요하다.
이를 해결하기 위해 Oracle의 ASH(Active Session History) 데이터를 활용하면 특정 사용자의 SQL 실행 내역을 실시간으로 추적하고 분석할 수 있다. ASH 데이터는 초 단위로 활성 세션을 기록하여, 특정 사용자의 SQL 실행 빈도, 실행 시간, 대기 이벤트(WAIT EVENT), 리소스 사용량 등을 파악하는 데 유용하다.
본 글에서는 ASH 데이터를 활용하여 특정 사용자의 SQL 활동을 분석하는 방법과, 이를 통해 성능 문제를 해결하는 최적화 전략을 상세히 설명한다. 이를 통해 서버 이관 후 예상치 못한 성능 저하를 신속하게 감지하고 해결할 수 있도록 한다.
특정 사용자의 SQL 활동을 추적해야 하는 이유
1. 특정 사용자의 SQL이 데이터베이스 성능 저하를 유발하는 경우
- 일부 사용자가 과도한 트랜잭션을 실행하면, CPU와 메모리 사용량이 증가하여 다른 사용자의 SQL 성능에도 영향을 미칠 수 있다.
- 불필요한 대량 조회(Full Table Scan)나, 비효율적인 조인(SQL Join) 사용이 특정 사용자의 SQL에서 자주 발생하는지 확인해야 한다.
2. 특정 애플리케이션 계정에서 실행되는 SQL 분석 필요
- 애플리케이션 계정으로 실행되는 SQL을 추적하면, 데이터베이스와 애플리케이션 간의 성능 문제를 파악할 수 있다.
- 특정 애플리케이션에서 반복 실행되는 SQL이 리소스를 과도하게 사용하는 경우, SQL 튜닝이나 애플리케이션 코드 수정이 필요할 수 있다.
3. 보안 및 감사(Audit) 목적
- 특정 사용자가 예상치 못한 대량의 데이터를 조회하거나 변경할 경우, 이를 신속하게 감지해야 한다.
- 보안 정책을 준수하는지 확인하고, 특정 사용자의 데이터베이스 접근 내역을 모니터링할 수 있다.
ASH 데이터를 활용한 특정 사용자의 SQL 분석 방법
1. 특정 사용자가 실행한 SQL 조회
특정 사용자가 실행한 SQL을 분석하려면 ASH 데이터를 활용하여 사용자(Session)별 SQL 실행 내역을 조회해야 한다.
| SELECT session_id, user_id, sql_id, COUNT(*) AS execution_count FROM v$active_session_history WHERE sample_time >= SYSDATE - (1) -- 최근 24시간 조회 AND user_id = ( SELECT user_id FROM dba_users WHERE username = '사용자명' ) GROUP BY session_id, user_id, sql_id ORDER BY execution_count DESC; |
- user_id → 특정 사용자의 ID를 기준으로 SQL 실행 내역 조회.
- execution_count → 해당 사용자가 실행한 SQL 횟수.
이 데이터를 활용하면 특정 사용자가 실행한 SQL 중 실행 빈도가 높은 SQL을 분석하고, 성능 저하를 유발하는 SQL을 식별할 수 있다.
2. 특정 사용자의 SQL 실행 시간 분석
특정 사용자가 실행한 SQL 중 평균 실행 시간이 긴 SQL을 감지하려면 아래와 같이 ASH 데이터를 조회할 수 있다.
| SELECT sql_id, COUNT(*) AS execution_count, AVG(elapsed_time) AS avg_execution_time FROM v$active_session_history WHERE sample_time >= SYSDATE - (1) -- 최근 24시간 조회 AND user_id = ( SELECT user_id FROM dba_users WHERE username = '사용자명' ) GROUP BY sql_id ORDER BY avg_execution_time DESC; |
- avg_execution_time → 해당 SQL의 평균 실행 시간.
- execution_count → 해당 SQL이 실행된 횟수.
이 데이터를 활용하면 특정 사용자가 실행한 SQL 중 실행 시간이 길어 성능 문제를 유발하는 SQL을 빠르게 감지할 수 있다.
3. 특정 사용자의 대기 이벤트 분석
SQL 실행 중 대기 시간이 긴 경우, ASH 데이터를 활용하여 어떤 대기 이벤트(WAIT EVENT)가 발생했는지 분석할 수 있다.
| SELECT event, COUNT(*) AS wait_count FROM v$active_session_history WHERE sample_time >= SYSDATE - (1) -- 최근 24시간 조회 AND user_id = ( SELECT user_id FROM dba_users WHERE username = '사용자명' ) GROUP BY event ORDER BY wait_count DESC; |
- event → 해당 사용자가 실행한 SQL에서 발생한 대기 이벤트.
- wait_count → 특정 대기 이벤트가 발생한 횟수.
이 데이터를 활용하면 특정 사용자가 실행한 SQL이 CPU, I/O, 네트워크 대기 등의 원인으로 인해 지연되는지를 분석하고, 해당 문제를 해결할 수 있다.
특정 사용자의 SQL 최적화를 위한 해결 방법
1. 실행 빈도가 높은 SQL 최적화
- 자주 실행되는 SQL을 애플리케이션에서 캐싱(Cache)하여 불필요한 실행을 줄인다.
- 바인드 변수를 활용하여 SQL 실행 횟수를 줄이고, 커서 공유를 최적화한다.
2. 실행 시간이 긴 SQL 튜닝
- 인덱스를 추가하여 Full Table Scan을 방지하고, 실행 계획을 최적화한다.
- 조인 방식 변경(Nested Loop Join 활용), 병렬 처리(PARALLEL 힌트) 등을 적용하여 실행 속도를 향상한다.
3. 특정 사용자의 대기 이벤트 최적화
- I/O 대기 시간이 높은 경우, 인덱스 최적화 및 테이블 파티셔닝을 고려한다.
- CPU 대기 시간이 높은 경우, SQL 실행 계획을 최적화하여 불필요한 계산을 줄인다.
ASH 데이터를 활용하면 특정 사용자가 실행한 SQL을 실시간으로 추적하고 분석할 수 있다. 이를 통해 SQL 실행 빈도, 실행 시간, 대기 이벤트 등의 요소를 효과적으로 모니터링할 수 있으며, 특정 사용자가 성능 저하를 유발하는 SQL을 실행하는지를 감지할 수 있다.
특히, 자주 실행되는 SQL을 최적화하고, 실행 시간이 긴 SQL을 튜닝하며, 대기 이벤트를 분석하면 데이터베이스 성능을 크게 개선할 수 있다.
