오라클 성능고도화 원리와 해법 Ⅰ (3장)

오라클 성능 관리

Explain Plan

  - SQL 수행하기 전에 실행계획을 확인하고자 할 때, explain plan 명령을 사용한다

 

AutoTrace

  - 명령어 : set autotrace on
  - 출력결과 : 쿼리수행결과, 실행계획, 실행통계

  - set autotrace traceonly explain : SQL을 실제 수행하지 않고 실행계획만을 출력한다

 

SQL 트레이스

  - SQL 튜닝할 때 가장 많이 사용되는 강력한 도구는 뭐니 뭐니 해도 SQL 트레이스다

  -  AutoTrace 결과 일치 항목 db block gets = current, consistent gets = query, physical reads = disk, SQL*Net roundtrips to/from client = fetch count, rows processed = fetch rows

  - Elapsed time = CPU time + Wait time

 - 가장 눈에 띄는 것은 SQL*Net message from client 이벤트다. 이 대기 이벤트는 Idle 이벤트로서 오라클 서버 프로세스가 사용자에게 결과를 전송하고 다음 Fetch Call이 올 때까지 대기한 시간을 더한 값이다

  - 오라클 서버는 애부적으로 SDU(Session Data Unit) 단위로 패킷을 나누어 전송한다. 하나의 SDU 단위 패킷을 전송했는데 잘 받았다는 신호가 정해진 시간보다 늦게 도착하면 대기가 발생하는데, 그때 발생하는 대기 이벤트가 SQL*Net more data to client이다

  - 시스템 레벨로 전체 세션에 트레이스 거는 방법 : alter system set sql_trace = true;

 

DBMS_XPLAN 패키지

  - 예상 실행계획 출력 : dbms_xplan.display

  - 커서 : 하드 파싱과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area

  - sql id 값으로 조인해서 사용할 수 있도록 제공되는 뷰 : v$sql_plan, v$sql_plan_statistics, v$sql_plan_statistics_all

  - 라이브러리 캐시에 현재 캐싱돼 있는 SQL 커서의 실제 실행계획과, 실행계획을 만들면서 예상했던 Rows, Bytes, Cost, Time 정보 보여줌 : dbms_xplan.display_cursor('[sql_id]', [child_no], '[format]'));

  - 오퍼레이션 별로 수행 통계 수집 : gather_plan_statistics 힌트, statistics_level 파라미터 all, _rowsource_execution_statics 파라미터 true로 설정, SQL 트레이스를 걸기

 

V$SYSSTAT

  - 인스턴스 기동 후 현재까지 누적된 수행 통계치를 시스템 레벨로 확인하고자 할 때 사용하는 뷰가 v$sysstat이고, 개별 세션별로 확인할 때 사용하는 뷰가 v$sesstat이다. 현재 접속해 있는 본인 세션에 대한 수행통계는 v$mystat을 통해 확인할 수 있다

  - Ratio 기반 성능 분석 : Buffer Nowait %, Redo NoWait %, Buffer Hit %, Latch Hit %, In-memory Sort %, Library Hit %, Soft Parse %, Execute to Parse %, Parse CPU to Parse Elapsed %:, % Non-Parse CPU, Memory Usage %, % SQL with executions > 1, % Memory for SQL w/exec > 1

 

V$SYSTEM_EVENT

  - 대기 이벤트는 v$system_event, v$session_event, v$session_wait 등 뷰를 통해 확인할 수 있을 뿐 아니라 각 세션별로 이벤트 발생 상황을 로깅하는 기능도 제공한다

 

Response Time Analysis 방법론과 OWI

  - 대기 이벤트 기반 성능관리 방법론 : 대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법. 과정

  - Response Time = Service Time + Wait Time = CPU Time + Queue Time

  - Response Time Analysis 방법론에 기반한 튜닝은 병목해소 과정

  - AWR, v$sql 관련 뷰들을 분석해서 손쉽게 문제점을 도출하고 튜닝대상을 선정

 

Statspack / AWR

  - Statspack은 SQL을 이용한 딕셔너리 조회 방식인데 반해 AWR은 DMA(Direct Memory Access) 방식으로 SGA를 직접 액세스 하기 때문에 좀 더 빠르게 정보를 수집할 수 있다. 부하가 적기 떄문에 AWR은 Statspack 보다 더 많은 정보를 수집하고 제공할 수 있게 되었다

  - sar 명령어를 통해 하루동안 수집한 CPU 사용량을 그래프로 그려본 것

  - Statspack과 AWR 리포트 맨 첫 장을 보면 오라클 데이터베이스의 건강상태를 한눈에 파악해 볼 수 있는 요약보고서 나옴

 

ASH(Active Session History)

  - Ratio 기반 분석 방법론의 한계점은, 시스템에 문제가 있는 것으로 진단되었을 때 그 원인을 찾아 실제 문제를 해결하는 데까지 많은 시간이 걸리는 데 있다

  - 오라클은 현재 접속해서 활동 중인 Active 세션 정보를 1초에 한 번씩 샘플링해서 ASH 버퍼에 저장한다. SGA Shared Pool에서 CPU당 2MB의 버퍼를 할당받아 세션 정보를 기록하며, 1시간 혹은 버퍼의 2/3가 찰 때마다 디스크로 기록한다. 즉, AWR에 저장하는 것이다.

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

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

 

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

  - 절차 : 1단계) 모니터링 자료수집, 2단계) 분석 진단, 3단계) 튜닝, 4단계) 평가

  - 데이터베이스 성능 튜닝의 3대 핵심 요소 1) 라이브러리 캐시 최적화 2) 데이터베이스 Call 최소화 3) I/O 효율화 및 버퍼캐시 최적화