튜닝 방법론

proDBA 출처

1.   튜닝 방법론 따르기

A.       성능관리

¨         설계단계부터 초기에 시작해야 한다.<?xml:namespace prefix = o />

¨         목표설정 : 5초이내와 같이 구체적인 시간과 처리양을 제시한다.

¨         계속적인 모니터링

¨         80/20 규칙 : 일반적으로 SQL문장 20%가 시스템 사용류의 80%를 차지한다.

B.       관리해야될 요소

¨         스키마 : 물리적인 데이터 설계와 인덱스

¨         응용프로그램 : SQL문장 à 여기서 주로 다루게 될 사항이다.

¨         인스턴스 : 메모리 + 백그라운드 프로세스

¨         하드웨어 : CPU, 메모리, I/O, 네트워크

C.       응답시간

¨         응답시간 = 서비스 시간 + 대기시간

¨         Response Time = Service Time + Wait Time

-서비스 시간 : SQL이 처리하는 시간

-대기 시간 : Lock과 같은 기타등등으로 인해 지연되는 시간 약 300가지존재한다 함

 

D.       Tuning 방법론 : 튜닝을 할 수 있는 개발단계 순서와 역할들이다.

가)     보통 한사람이 여러 단계를 걸쳐서 담당하는 것이 일반적이다.

1.       업무기능 튜닝	업무 분석가
2.       데이터 설계 튜닝	설계자
3.       프로세스 설계 튜닝
4.       SQL문 튜닝	프로그래머
5.       물리적 구조 튜닝
6.       메모리 할당 튜닝	데이터베이스 관리자
7.       I/O 튜닝
8.       메모리 경합 튜닝
9.       운영체제 튜닝	OS 관리자
10.    네트워킹 문제	네트워크 관리자

 

E.       SQL 문 튜닝 개요

가)     문제가 있는 SQL 식별

¨   해당프로그램 SQL확인 하거나, SQL_Trace와 TKPROF 사용할 수 있음

나)     Optimizer 통계 확인 (CBO : Cost Based Optimizer)

¨   주기적으로 통계를 생성해야 한다.

다)     실행계획 검토

¨   Driving 테이블이 최상의 필터를 가져야 된다. (처리량 작은 Table부터)

라)     SQL 문 재구성

¨   가능한 where = 을 많이 쓰도록

¨   where 절의 컬럼에 연산자 쓰지 말도록.. 인덱스 무시되는 않도록 : trim(col) = ‘A’

¨   때론 각 작업에 별도의 SQL문장 작성 è 보통은 한방SQL을 권장

¨   Sub-Query에 Exists사용

¨   힌트로 엑세스 경로 및 조인 순서를 제어 할 수 있다.

마)     인덱스 재구성

¨   사용하지 않는 인덱스들은 제거

¨   성능에 중요한 액세스 경로를 인덱스화 한다

¨   기존인덱스의 열 순서를 변경하거나 추가 할 수가 있다. à 다른 SQL에 영향 심각한 영향 줄수 있으니 매우 조심 해야 한다.

¨   Index-Organized Table을 고려한다.

바)     실행계획 유지관리

¨   버전업, 시스템이동 등 변경사항시에도 실행계획이 유지되도록 한다.

¨   Stored outlines,  Stored statistics à 현실성은 좀 없어 보인다. 뒷장에서 자세히.

 

F.        요약

¨   User 기대관리

¨   각단계의 결과분석

¨   물리적 스키마 Tuning

¨   SQL 사용시기 선택

¨   가급적 SQL문 재사용

¨   SQL 문 설계 및 Tuning

¨   Optimizer로 최대의 성능 달성

2.   SQL문 처리

 

A.       개요

¨         SGA (System Grobal Area)

¨         공유 SQL영역

¨         PGA (Program Frobal Area)

¨         SQL 처리단계

¨         SQL 코드 작성표준

¨         공유커서

¨         자동 PGA 메모리관리

 

B.       SGA : System Grobal Area

가)     데이터베이스 프로세스에 의해 공유되는 데이터베이스 정보를 저장하기 위해 사용되는 메모리 영역이다

¨   파라미터 : SHARED_POOL_SIZE 세팅

나)     구성

¨   Datbase Buffer cache

¨   Shared Pool

n         Library cache : 최근에 참조된 SQL및 Pl/SQl 코드의 실행가능형태를 저장

n         Data Dictionary cache : User이름,세그먼트정보,테이블스테이스정보 등 저장

¨   Redo log buffer

¨   Java pool

¨   Large pool

¨    

C.       공유SQL영역

¨   명령문의 구문분석된 표현이 Library cache에 존재하며 공유가능한 경우 기존 Executable 파일을 재사용한다.   공유가능한 SQl문장은 LRU알고리즘에 의하여 관리된다.

¨   커서(Cursor) : 공유SQL영역에서 각 SQL문은 컨텍스트영역 또는 커서라는 영역에서 구문분석된다.

¨   다음과 같은 정보 포함함 (text, p-code, plan)

n         구분분석된 명령문

n         실행계획

n         참조된 객체 리스트

 

D.       PGA

가)     단일 프로세스에 대한 데이터 및 제어 정보를 포함하는 메모리 영역이다. Dedicated  서버환경에서 User가 오라클에 접속하여 세션이 생성될 때  서버에 의해 생성된다.

나)     구성

¨   User session 정보

n         정렬영역 (sort area)

n         SQL 실행작업영역 (private SQL area)

¨   Cursor 정보

¨   해시조인영역

¨   비트맵병합영역

¨   Stack Space

다)     9i에서는 소팅메모리를 자동관리함을 권장한다

¨   파라미터 : PGA_AGGREGATE_TARGET

 

E.       SQL 처리단계

열기 --> 구문분석 --> 바인드 --> 실행 --> 패치(Fetch) --> 닫기

가)     구문분석

¨   Library Cache에서 명령문 검색 : 기존에 구문부석된 공유 SQL영역이 있는지 확인

¨   구문확인 : syntax check

¨   의미 및 권한 확인 : Semantics check, Privilege check

¨   뷰정의와 sub suery 병합 : 문장변형

¨   실행계획 결정 : Plan 생성, 즉 커서가 생성됨

나)     바인드

¨   바인드된 변수값을 알수 없는 상태로 Plan이 설정된다. 커서 공유가 의도되어 있고 서로 다른 호출이 동일한 실행 계획을 사용하는 것으로 간주한다.

¨   자바에서 preparestatement 를 사용하는 방식이 바인드 변수를 사용하는 것. 따라서 반복 실행 되는 문장은 바인드 변수를 사용하여야 구문분석을 다시 하는 비용을 줄일수 있다.

다)     실행

¨   실행 계획을 적용 : Buffer Cache에서 해당 블록을 Search 한다.

¨   DML문에 대한 Disk Search와 정렬을 실행한다.

라)     Fetch

¨   쿼리에 대한 행 검색하여 데이터를 가져온다.

¨   SQL-Plus에서 ARRAYSIZE의 파라미터값을 조정하여 fetch하는 배역의 크기를 조정할 수 있다. --> 즉 운반단위를 지정한다.

 

F.        SQL 코드 작성표준

가)     커서 공유 이점

¨   구문 분석이 줄고 시간이 절약된다.

¨   동적으로 메모리 튜닝된며 메모리 사용이 개선된다.

나)     요구사항

¨   동일한 SQL문장을 사용한다.  대소문자, 탭, 공백, 주석이 모두 동일해야만 한다.

¨   같은 스키마내의 객체이어야 한다. 동일한 유저의 객체들

¨   바인드 되는 변수의 유형이 동일해야 한다.

¨   where cust_id = :a 와 where cust_id = :b는 내부적으로 바인드 변수의 이름이 바뀌기 때문이 동일한 실행 계획이 성립된다.

¨   --> 구문 분석되는 단계만 생략되어도 성능효과를 가져온다.

¨   일반적인 공유 코드를 사용하도록 한다.  ( 당연한 말인거 같은데…)

n         내장 프로시져 및 패키지,  트리거 사용하기.

 

프로그램화 하면 어차피 대소문자, 탭, 공백이 바뀔 경우는 없는 것 같다. Toad툴과 같은 자료 발췌를 위한 연습용 SQL에서는 어차피 지켜질 수 없는 노릇이고, 프로그램시 바인드 변수 사용하는 것에 목표를 두어야 겠다.

 

G.       공유커서를 사용하기 설정

가)     파라미터

¨   CURSOR_SHARING

n         EXACT (기본값) : sql문장이 완전히 동일해야만 된다. OLTP / DW환경에서

n         SIMILAR : 상수가 달라도 실행계획이 (PLAN)같다면 공유커서 사용한다.

-변수 값만 다른 경우나  라이브러리 캐시실패가 많이 나타나면 사용 할 수있다. OLTP 환경에서

n         FORCE : 상수가 달라도 무조건 동일 실행계획이 성립된다.  CBD환경에서는 잘못 사용될 수 있다. 왜냐면 분포도를 따지지 않으니깐. (추천안함)

==> 최적의 솔루션은 이 파라미터에 의존하지 않고 공유 가능한 SQL을 사용하는 것이다.

 

나)     공유커서 모니터링

SQL문장이 두번이상 구문분석 되지 않도록 SHARED POOL이 충분히 커야 된다.

¨   V$LIBRARYCACHE

NAMESPACE	라이브러리 캐시 영역의 이름
GETS	총 요청 회수 : 파싱을 위한 커서 요청 회수
GETHITS	객체의 핸들이 메모리에서 발견된 횟수
GETHITRATIO	GETHITS/GETS : 1에 가까워야 함
PINS	라이브러리 캐시의 객체가 실행된 횟수
PINHITS	객체의 모든 조각이 메모리에서 발견된 횟수
PINHITRATION	PINHITS/PINS 비율 : 1에 가까워야함
RELOADS	라이브러리 캐시 실패하여 다시 로드된 횟수 ¨         오래된 커서가 AGEOUT 된 경우 ¨         TABLE 구조변경으로 기존 SQL이 무효화 되었을 때

 

라이브러리 캐쉬 적중율 > 95 % 보다 커야 된다.

SELECT SUM(PINS-RELOADS) / SUM(PINS)  *  100  “HIT RATIO”

FROM V$LIBRARYCACHE

 

RELOAD 율은 < 1 % 작아야 된다.

                          SELECT SUM(PIINS) “EXECUTION”,

                                       SUM(RELOADS) “MISS”

                                       SUM(RELOADS)  / SUM(PIINS) “비율”

                          FROM V$LIBRARYCACHE

 

                          ---> SHARED_POOL_SIZE를 늘여 주어야 한다.

¨   V$SQLAREA : 캐시내의 모든 공유 커서에 대한 정보. 1000BYTE만

¨   V$SQLTEXT : 캐시의 총길이 SQL 문장

SELECT SEL_TEXT,  --실행된 sql문장

VERSION_COUNT, -- 이 커서의 버전

LOADS,  -- 리로드 회수

INVALIDATIONS, -- 무효화 되었던 회수

PARSECALLS, -- 커서가 호출된 회수

SORT -– 수행된 정렬회수

FROM V$SQLAREA

WHERE PARSING_USER-ID > 0  -- SYS = 0

AND COMMAND_TYPE = 3

(1 CREATE TABLE, 2 INSERT, 3 SELECT, 6 UPDATE, 7 DELETE)

 

H.       자동 PGA 메모리관리 (9i 부터) : 전용서버에서

¨   PGA 메모리를 자동관리 할수 있어서, 튜닝이 개선된다.

¨   파라미터 : PGA_AGGREGATE_TARGET = 10M ~ 4000M

인스턴스 세션의 PGA영역에 할당되어야 하는 총 메모리양을 지정한다. 특히 로드양이 많은 경우에 처리량을 높여준다.

설정되면 SORT_AREA_SIZE는 무시된다.

¨   동적성능 뷰

n         V$SYSSTAT,  V$SESSTAT

n         V$PGASTAT

n         V$PROCESS

n         V$SQL_WORKAREA_ACTIVE

n         V$SQL_WORKAREA

n         PGA_TARGET_ADVICE

 

 

I.       요약 : 공유 SQL영역 사용을 최적화 하려면

¨   일반적인 코드를 작성한다.

¨   코드 작성 표준을 따른다.

¨   바인드 변수를 사용한다.

¨   CURSOR_SHARING 파라미터를 설정한다.

                        자동 PGA 메모리 관리를 사용한다