05주차 정리내용 - 이은규

[이은규]

03. 오라클성능관리

SQL 튜닝을 예로 들자면, 쿼리 성능에 문제가 생기면 Explain Plan을 통해 실행계획을 먼저 확인한다. 그것만으로 문제점을 정확히 파악할 수 없을 때는 AutoTrace를 걸어 수행 시 실제 일량을 측정하거나 SQL 트레이스를 걸어 내부 수행 절차상 어느 단계에서 부하를 일으키는지 눈으로 확인한다. 문제점이 파악되면 쿼리를 변환하거나 옵티마이저 힌트를 사용해 튜닝을 실시하고, 그것만으로 부족하다고 판단될 때는 인덱스 조정을 병행한다. 그래도 고객이 성능에 만족하지 못할 때는 어쩔 수 없이 반정규화를 실시하거나 집계 테이블을 생성 하도록 권고하지만 이것은 구조적 문제에 기인할 때만 사용하는 최후의 수단이다. SQL 외에 다른 데이터베이스 요소를 튜닝할 때도 비슷한 절차로 진행된다.

01 Explain plan

-SQL을 수행하기 전에 실행계획을 확인하고자 할 때, 먼저 plan_table을 생성해 explain plan 명령를 사용한다.

-오라클 10g 부터는 설치 시 기본적으로 sys. plan_table$ 테이블이 만들어지고 사용자가 별도로 plan_table 생성하지 않아도 된다.

02 AutoTrace

-옵션 조합에 따라 필요한 부분만 출력해 볼 수 있다.

1. set autotrace on

-SQL을 실제 수행하고 그 결과와 함께 실행계획 및 실행통계를 출력한다.

2. set autotrace on explain

-SQL을 실제 수행하고 그 결과와 함께 실행계획을 출력한다.

3. set autotrace on statistics

-SQL을 실제 수행하고 그 결과와 함께 실행통계를 출력한다.

4. set autotrace traceonly

-SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행계획과 통계만을 출력한다.

5. set autotrace traceonly explain

-SQL을 실제 수행하지 않고 실행계획만을 출력한다.

6. set autotrace traceonly statistics

-SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행통계만을 출력한다.

1~3은 수행결과를 출력해야 하므로 쿼리를 실제 수행한다.

4, 6는 실행통계를 보여줘야 하므로 쿼리를 실제 수행한다.

5는 실행계획만 출력하면 되므로 쿼리를 실제 수행하지 않는다. SQL*Plus에서 실행계획을 가장 쉽고 빠르게 확인해 볼 수 있는 방법이다.

-AutoTrace 기능을 실행계획 확인 용도로만 사용한다면 plan_table만 생성

-실행통계까지 함께 확인하려면 v _$sesstat, v _$statname, v _$mystat 뷰에 대한 읽기 권한이 필요

- dba, select_catalog]ole 등의 롤1R<이e)을 부여받지 않은 일반사용자들에게는 별도의 권한 설정이 펼요

-statistics 모드로 AutoTrace를 활성화시키면 새로운 세션이 하나 열리면서 현재 세션의 통계정보를 대신 쿼리해서 보여준다.

-statistics 모드로 AutoTrace를 활성화하니까 새로운 세션이 하나 추가되었음을 확인

-아까 새롭게 열렸던 세션이 사라진 것을 확인

03 SQL 트레이스

-사전 실행계획과 AutoTrace 결과만으로 부하원인을 찾을 수 없을 때， SQL 트레이스를 통해 쉽게 찾아낼 수 있다.

(1) 자기 세션에 트레이스 걸기

- 현재 자신이 접속해 있는 세션에만 트레이스를 설정하는 방법

TKProf 유틸리티 : TKProf 유틸리티를사용하면 트레이스파일을보기 쉽게 포맷팅해 준다.

트레이스결과분석

이벤트트레이스

alter session set events ’ 10046 trace name context forever, level 1 ’ ;

 a1ter session set events ’ 10046 trace name context off ’ ; 
 설정할 수 있는 레벨 값은 1, 4, 8, 12

 레벨 1은 지금까지 살펴본 일반적인 SQL 트레이스와같다.

Elapsed time = CPU time + Wait time

• Elapsed time = CPU time + Wait time = Response시 점 - Call시 점

• SELECT문 = Parse Call + Execute Call + Fetch Call( - 1 회 이상)
• DML문 = Parse Call + Execute Call

(2) 다른 세션에 트레이스 걸기

-튜닝 대상 SQL이 수집되지 않은 상황이라면 커넥션 Pool에 놓인 세션 또는 시스템 레벨로 트레이스를 걸어 SQL 수행 정보를 수집해야 한다.또는 특정 세션에 심한 성능 부하를 일으키고 있다면, 트레이스를 걸어야 하는데 그럴때 사용 할 수 있는 방법이 존재하며, 버젼마다 다르다. 

-트레이스를 해제할 때는 레벨을 0으로 설정하면 된다.

오라클 10g 이후부터는 dbms_monitor 패키지를 사용

트레이스 해제 시 session_trace_disable

문제가 발생한 세션에 트레이스를 걸 때 버전에 상관없이 오래 전부터 사용하던 명령어는 oradebug다. 자세한 사용법은 ‘oradebug help’를 입력하면 알수 있다.

기본 사용 패턴의 간단한 예시

(3) Service, Module, Action 단위로 트레이스 걸기

-10g 부터 Service, Module, Action 별로 트레이스를 설정 및 해제 가능한 dbms_monitor 패키지가 존재하며, 현재 접속해있는 세션 뿐만 아니라 새로 커넥션을 맺는 세션도 자동으로 트레이스가 설정 됨

-v$session을 통해 Service, Module, Action을 확인 할 수 있음 

-Action은 dbms_application_info.set_action('action_name')을 통해서 설정 변경 가능

-지금까지 설명한 기능을 효과적으로 사용하려면 모든 프로그램 모율 수행 전에 dbms_applicationjnfo.set_ffiodule 프로시저를 한번씩 호출하도록 프로그램을 변경해야 한다.

- 데이터베이스 Call이 한번씩 더 발생하기는 하지만 I/O 작업이 전혀 없고 세션 Global 변수 값만 살짝 바꿔주기 때문에 부하가 거의 없다고 할 수 있다. SQL 단위로 set action을 자주 수행하는 것은 시스템에 다소 부하를 줄 수 있으므로 SQL 수행이 빈 번한 OLTP성 프로그램보다는 배치 프로그램에만 적용하는 게 좋겠다

 -set_module 호출히는 부분을 개발자가 모률마다 일일이 삽입하려면 귀찮거니와 빠뜨리기 쉬우므로 WAS에서 커넥션을 얻을 때마다 호출되는 공통 모률 내에 추가시켜 주는 것이 좋다.

- v$session 뷰에서 client identifier 컬럼이 특정 값으로 설정된 세션에만 트레이스를 걸 수도 있다.

04 DBMS_XPLAI\l 패키지

(1) 예상 실행계획 출력

-첫 번째 인자에는 실행계획이 저장된 plan table명을 입력하고， 두 번째 인자에는 statementjd를 입력하면 된다.

- 두 번째 옵션이 NULL일 때는 가장 마지막 explain plan 명령에 사용했던 쿼리의 실행계획을 보여준다.

-병렬 쿼리에 대한 실행계획을 수집 했다면 @?/rdbms/admin/utlxplp 스크립트를 수행함으로써 병 렬 항목에 대한 정보까 지볼수있다.

(2) 캐싱된 커서의 실제 실행계획 출력

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

-오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 γ$sql 뷰를 제공한다

-활용도가 가장 높은 것 이 v$sql_plan과 v$sql_plan_statistics이다. 그리고 이두뷰를 합쳐서 보여주는 것 이 v$sqtplan_statistics_all이다.

v$sqtplan 뷰의 활용방법

- 캐싱된 커서 정보를 v$sql에서 조회할 수 있고，v$sql_plan을 통해 실제 수행하면서 사용했던 실행계획까지 확인해 볼 수 있다.

(3) 캐싱된 커서의 Row Source별 수행 통계 출력

-SQL문에 gather J)lan_statistics 힌트를 시용하거나， 시스템 또는 세션 레벨에서 statisticsJevel 파라미터를 all로 설정하면, 오라클은 실제 SQL을 수행히는 동안의 실행계획 각 오퍼레이션 단계 (Row Source)별로 수행 통계를 수집한다.

-v$sqtplan_statistics 뷰에는 모든 통계항목에 대해 마지막 수행 통계치와 누적 통계 치를조회할수 있도록 컬럼이 두 개씩 제공된다.

• outputJOWS, last_outputJOWS
• cr_b띠‘fer--Eets， last_cu_buffer--Eets
• disk reads. last disk reads

- 누적 값을 출력

-dbms_xplan. display _cursor 함수는 우리를 대신해 위 뷰를 읽어 깔끔하게 포뱃팅해주는 기능을 제공한다.

- dbms_xplan. display 함수에는 없던 iostats, memstats, allstats 옵션을 사용하면 실제 수행 시 Row Source별 수행통계를 보여준다. 여기서도 가장 최근 수행한 SQL을 찾을 때는 첫 번째， 두 번째 인자에 NULL 값을 입력하면된다.

-결과 샘플 :  E-Rows는 SQL을 수행하기 전 옵티마이저가 각 Row Source별로 예상했던 로우 수로서 v$sqtplan에서 읽어온 값이다. A-Rows는 실제 수행 시 읽었던 로우 수로서， v$sqtplan_statistics에서 읽어 온 항목이다.

05 V$SYSSTAT

-오라클은 성능 측정 지표로서 활용 가능한 항목들을 선정하고, SQL이 수행되는 동안 지속적으로 그 항목들에 대한 누적 통계치를 저장

-인스턴스 기동 후 현재까지 누적된 수행 통계치를 시스템 레벨로 확인하고자 할 때 사용히는 뷰가 v$sysstat

-개별 세션별로 확인할 때 사용하는 뷰가 v$sesstat

-현재 접속해 있는 본인 세션에 대한 수행통계는 v$mystat

(1) 시스템 수행 통계 수집 및 분석

-v$sysstat에 나타나는 값들은 인스턴스 기동 후 또는 세션 수립 후 현재까지 누적된 값. 값의 크고 작음만으로 의미있는 정보를 얻기는 힘듬

-두 구간 사이의 변화량을 구해 내부적으로 어떤 일이 있었는지 판단

-수집된 수행통계를 쿼리해 두 구간 사이의 증분을 구해보면 작업 수행 도중 오라클 내부적으로 어떤 일들이 일어났는지 알 수 있다.

(2) Ratio 기반 성능 분석

-수집된 수행 통계 자료를 이용해 DB의 전반적 인 건강상태를 체크할 수 있는 의미 있는 Ratio 값들을 구할 수 있다.

- 자주 분석되는 항목들

• Buffer Nowait %
• Redo No Wait %
• Buffer Hit %
• Latch Hit %

• In-memory Sort %

• Library Hit %

• 80ft Parse %
• Execute to Parse %
• Parse CPU to Parse Elapsd %:
• % Non-Parse CPU
• Memory Usage %
• % 8QL with executions) 1
• % Memory for 8QL w/exec)l

-Buffer Nowuit % : 버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에성공한비율이다

Redo NoWait % : Redo로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율이 낮으면 로그 스위칭이 느리거나 너무 자주 발생함을 의미

Buffer Hit % : 디스크 읽기를 수반하지 않고 버퍼캐시에서 블록찾기에 성공한 비율

Latch Hit % : 래치 경합없이 첫번째 시도에서 곧바로 래치를 획득한 비율

Librarv Hit % : 라이브러리 캐시에 이미 적재된 SQL커서를 생행하거나 오브젝트정보를 읽으려할 때 커서 또는 오브젝트정보가 Heap영역에서 찾아진다면 Hit에 성공 비율.  Get hit율과 Pin hit율로 나누어짐.  Get hit율은 Parse 단계와 관련이 있으며 이 수치가 낮다면 하드파싱 또는 최초 로드가 발생한 경우임

Soft Parse % 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율

(전체 Parse Call 횟수 - 하드파싱 횟수) / (전체 Parse Call 횟수) * 100

이 비율이 낮다면 바인드 변수를 사용하도록 애플리케이션을 개선해야 한다.

Execute to Parse % : Parse Call없이 곧바로 SQL을 수행한 비율. 즉, 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율
n-Tier에서 이 값이 일반적으로 값이 낮게 나타남

Parse CPU to Parse Elapsd % : 파싱 총 소요 시간 중 CPU time이 차지한 비율. 파싱에 소요된 시간 중 실제 일을 수행한 시간비율
이값이 낮으면 대기시간이 많았다는 의미로서 Shared Pool과 라이브러리 캐시 경합이 많았다는 것을 의미하며 대개 하드파싱 부하때문임
초당 하드파싱 횟수가 거의 나타나지 않는데 이 Ratio가 낮은 수치를 기록한다면 Parse Call 자체가 많아 발생한는 경합임

% Non-Parse CPU : SQL을 수행하면서 사용한 전체 CPU time중 파싱 이외의 작업이 차지한 비율. 이 비율이 낮으면 파싱에 소비되는 CPU Time이 많은거며, 파싱부하를 줄이도록 애플리케이션을 개선해야함

In-memory Sort  % :  전체 소트 수행횟수에서 In-Memory방식으로 소트한 비율 

Memory Usage % : Shared Pool내에서 현재 사용중인 메모리 비중

% SQL with executions>1 : 전체 SQL 개수에서 두번이상 수행된 SQL이 차지하는 비중. 이 비율이 낮으면 Literal 상수값을 이용하는 쿼리수행빈도가 높다는 것을 의미

% Memory for SQL w/exec>1 : 전체 SQL이 차지하는 메모리 중 두번이상 수행된 SQL이 차지하는 메모리 비중. 이 비율이 낮으면 Literal 상수값을 사용하는 쿼리에 의해 Shared Pool이 낭비되고 있음을 의미