SQL) 오라클 성능 관리 Ⅲ

jinsung·2025년 12월 8일
SQLPoraclesql데이터자격증

SQL

목록 보기
18/46

7. Response Time Analysis 방법론과 OWI

대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 해결하는 방법, 과정을 '대기 이벤트 기반' 또는 'Response Time Analysis기반' 성능 관리 방법론이라고 한다.

Response Time Analysis 방법론

  • Response Time = Service Time + Wait Time
  • Response Time = CPU Time + Queue Time
    Service Time = Cpu Time
    Wait Time = Queue Time

Response Time을 위처럼 정의하고, CPU time과 Wait time을 각각 break down하면서 서버의 일량과 대기 시간을 분석해 나간다.
CPU time은 파싱 작업에 소요된 시간인지 오퍼레이션 수행에 소요된 시간인지를 분석한다.
Wait time은 각각 발생한 대기 이벤트들을 분석해 가장 시간을 많이 빼앗긴 이벤트 중심으로 해결방안을 모색한다.

OWI 란?

OWI란 Oracle Wait Interface의 준말으로, Response Time Analysis 방법론을 지원하려고 오라클이 제공하는 기능과 인터페이스를 통칭하는 말이다.
Response Time Analysis 방법론에 기반한 튜닝은 병목해소 과정이라고 요약할 수 있다.
또한, OWI에 기반한 튜닝은 모니터링과 튜닝을 반복하면서 병목을 해소해 나가는 방법론이다.

8. Statspack / AWR

8i부터 사용하던 Statspack과 10g 이후 사용하게 된 AWR(Auto Workload Repository)은 표준화된 방식으로 성능관리를 지원하려고 오라클이 제공하는 패키지이다.
이들은 Ratio 기반 성능진단과 Wait Event 기반 성능진단 방법론을 둘 다 가지고 있다.
동적 성능 뷰를 주기적으로 특정 Repository에 저장하고, 이를 분석해 오라클 데이터베이스의 병목원인과 튜닝 대상을 식별해 낸다.

Statspack과 AWR은 거의 같은 내용을 담고 있으며, 다른 점은 정보를 수집하는 방법이다.

Statspack --> 8i

  • SQL을 이용한 딕셔너리 조회 방식
  • PERFSTAT 계정 밑에 'stat$'로 시작하는 뷰를 이용

AWR --> 10g

  • DMA(Direct Memory Access) 방식으로 SGA가 직접 액세스하는 방식
    -> Statspack보다 더 빠르고 부하가 더 적어서 더 많은 정보를 수집하고 제공

  • Automatic이 의미하는 것 처럼 자동으로 성능 자료를 수집해 일정기간 보관

  • SYS 계정 밑에 'dba_hist_'로 시작하는 뷰를 이용

AWR 리포트 첫 장 내용들

1. Cache Sizes 항목들

버퍼 캐시, Shared Pool, 로그 버프 등 SGA를 이루는 주요 캐시 영역에 대한 크기 정보를 참고삼아 보여주는 것

2. Load Profile

Redo sizes, Logical reads, Pr, Pw 등 각 측정 지표 값들을 측정 시간으로 나눈 것과 트랜잭션 개수로 나눈 값들을 보여준다

3. Load Profile 바로 아래 항목들

  • % Blocks changed per Read : 읽은 블록 중 갱신이 발생하는 비중

  • Rollback per transaction % : 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중

  • Recursive Call % : 전체 Call 발생 횟수에서 Recursive Call이 차지하는 비중

  • Rows per Sort : 소트 수행 시 평균 몇 건씩 처리했는지를 나타낸다

4. 인스턴스 효율성에 관한 리포트

Ratio 기반 성능 분석 값들을 나타낸다

5. Shared pool 사용통계

Ratio 기반 성능 분석 값 중 AWR 리포트 구간 시작 시점의 Shared Pool 메모리 상황과 종료 시점에서의 메모리 상황을 보여준다

6. Top 5 Timed Events

AWR 리포트 구간 동안 누적 대기 시간이 가장 컸던 대기 이벤트 5개를 보여준다
CPU time 비중이 top1 이다 -> DB상태는 정상이다
CPU time 비중이 아래로 갈 수록 비정상이다

래치 이벤트는 CPU time과 같이 보면서 분석해야 된다

I/O 대기 이벤트가 높게 나타나는 것도 대게 정상이지만, CPU time보다 높은 점유율을 차지하면 I/O 튜닝이 필요하다

트랜잭션 처리 위주의 시스템이라면 log file sync 대기 이벤트가 상위에 있다고 무조건 안 좋은 것이 아니다. 이벤트가 많이 발생한 것만으로 불필요한 커밋을 자주 날렸다고 판단해서는 안 된다.

9. ASH(Active Session History)

Ratio 기반 분석 방법론의 한계점은, 시스템에 문제가 있는 것으로 진단되었을 때 그 원인을 찾아 실제 문제를 해결하는 데까지 많은 시간이 걸리는 데 있다.
오라클 10g에서 ASH 기능이 생겼고, AWR 데이터 수집을 아주 빠르고, 더 많이 하면서 별도의 Third Party 모니터링 도구 없이 오라클 내에서 세션 레벨 실시간 모니터링을 가능케 하는 강력한 기능으로서, OWI의 활용성을 극대화해준다.

v$active_session_history 뷰를 이용해 ASH 버퍼에 저장된 세션 히스토리 정보를 조회할 수 있다.

v$active_session_history의 정보들

  • 샘플링이 일어난 시간과 샘플 ID
  • 세션 정보, User명, 트랜잭션 ID
  • 수행 중 SQL 정보
  • 현재 세션의 상태 정보, 'ON CPU' 또는 'WAITING'
  • 병렬 Slave 세션일 때, 쿼리 코디네이터(QC) 정보를 찾을 수 있게 함
  • 현재 세션의 진행을 막고 있는 세션 정보(블로킹 정보)
  • 현재 발생 중인 대기 이벤트 정보
  • 현재 발생 중인 대기 이벤트의 파라미터 정보
  • 해당 세션이 현재 참조하고 있는 오브젝트 정보, v$session 뷰에 있는 row_wait_obj#, row_wait_file#, row_wait_block# 컬럼을 가져온 것임
  • 애플리케이션 정보

ASH 버퍼는 너무 빠르기 때문에 래치를 사용한다면 더 많은 경합이 생길 수 있어,
오라클은 ASH 버퍼를 읽는 세션은 래치를 요구하지 않음
-> 대신 일관성 없는 잘못된 정보가 나타날 수도 있음

ASH 기능을 사용하면 현재뿐 아니라 과거시점에 발생한 장애 및 성능 저하 원인까지 세션 레벨로 분석할 수 있게 도와준다. v$active_session_history 정보를 AWR내에 보관하므로 과거치에 대한 세션 레벨 분석이 가능해진다.

v$active_session_history를 조회했을 때 정보가 찾아지지 않는다면 이미 AWR에 쓰여진 것이므로 dba_hist_active_sess_history 뷰를 조회하면 된다.

부하를 최소화하면서, 세션 레벨의 상세한 분석이 가능하도록 오라클이 성능자료를 수집해 주므로 이제 AWR과 ASH를 잘 이용하면 전문 성능관리 툴의 도움 없이도 효과적으로 성능 분석을 수행할 수 있게 되었다.

10. V$SQL

v$sql은 개별 SQL 커서의 수행 통계를 분석할 목적으로도 많이 활용되지만, 집중 튜닝이 필요한 대상 SQL을 선정하는 데 활용할 수 있는 매우 유용한 도구이다.
그뿐만 아니라 튜닝 전후 성능 향상도를 비교할 목적으로 통계를 내는 데도 활용할 수 있다.

v$sql은 라이브러리 캐시에 캐싱돼 있는 각 Child 커서에 대한 수행통계를 보여준다.
v$sqlarea는 Parent 커서에 대한 수행 통계를 나타내며, 많은 컬럼이 v$sql을 group by 해 구한 값이다.
v$sql은 쿼리가 수행을 마칠 때마다 갱신되며, 오랫동안 수행되는 쿼리는 5초마다 갱신이 이루어진다.

V$SQL에 있는 정보들

  • 라이브러리 캐시에 적재된 SQL 커서 자체에 대한 정보
  • SQL 커서에 의해 사용되는 메모리 사용량
  • 하드파싱 및 무효화 발생횟수, Parse, Execute, Fetch Call 발생 횟수, Execute 또는 Fetch Call 시점에 처리한 로우 건수 등
  • SQL을 수행하면서 사용된 CPU time과 소요시간
  • SQL을 수행하면서 발생한 논리적 블록 읽기와 디스크 읽기, 그리고 소트 발생 횟수
  • SQL 수행 도중 대기 이벤트 때문에 지연이 발생 시간
  • 커서가 라이브러리 캐시에 처음 적재된 시점, 가장 마지막에 수행된 시점

v$sql에 보이는 통계치들도 다른 동적 성능 뷰처럼 누적값이라서, 가공후 사용해야 의미있는 분석이 가능하다.

Colored SQL ?

Top SQL에 포함되지 않더라도 사용자가 명시적으로 지정한 커서의 수행통계가 AWR에 주기적으로 수집되도록 마크하는 기능이다
컬럼에 마크 표시를 해 두면 오라클이 AWR 정보를 수집할 때마다 Top SQL 선정기준과 상관없이 해당 SQL의 수행 통계를 저장한다.

11. End-To-End 성능관리

시스템이 점차 3-Tier 이상 n-Tier 환경으로 구축돼 가는 실정이어서 DB 구간에 대한 분석만으로는 문제를 신속하게 해결하기 어려워졌다. 시스템 전체를 관장하는 입장에서는 전체적인 시각에서 시스템을 바라볼 수 있는 도구가 필요해졌다.
이런 요구사항에 맞춰 End-To-End 방식의 애플리케이션 성능 관리 툴이 많이 도입되고 있는 추세이다.
이들 툴을 사용하면 Web, AP, DB Zone으로 나눠 어떤 구간에서 병목이 발생하는지를 실시간으로 모니터링할 수 있다.
데이터베이스 튜닝을 전문으로 하는 컨설턴트 입장에서도 이들 툴을 잘 활용해 업무 효과를 높일 수 있는데, 특히 튜닝 대상 서비스와 SQL 목록을 식별하는 데에 매우 유용하게 사용할 수 있다.

12. 데이터베이스 성능 고도화 정석 해법

데이터베이스 성능 튜닝의 3대 핵심 요소 (애플리케이션 튜닝 관점)

1. 라이브러리 캐시 최적화

2. 데이터베이스 Call 최소화

3. I/O 효율화 및 버퍼캐시 최적화
-> 인덱스와 조인 원리, 옵티마이저 원리를 기반으로 한 SQL 튜닝

profile
jinsung
Backend Engineer
이전 포스트

SQL) 오라클 성능 관리 Ⅱ

다음 포스트

SQL) 라이브러리 캐시 최적화 원리 Ⅰ

0개의 댓글