데이터 베이스 성능 튜닝 첫번째 라이브러리 캐시 최적화

1. SQL 처리 과정

A.SQL PARSING -> B.OPTIMIZER  -> C. ROW-SOURCE GENERATION  -> D.EXECUTION

A : 문법검사(문맥오류) -> 의미검사(오브젝트 존재 유무)

B : OPTIMIZER 3가지 SUB 엔진

1. QUERY TRANSFORMATION : 쿼리 변환(MERGE, UNEST 등)

2. PLAN GENERATOR : 실행계획 생성

3. ESTIMATOR : 비용 계산(선택도 카디널리 계산 및 총 비용 계산)

C : 오라클 내부만의 코드 생성

* 라이브러리 캐시 안에 SQL을 찾기 위해 사용되는 SQLID는 SQL문장 그 자체로 들어가 있슴 (대소문자,공백,주석등 달라지면 다른 SQL)

* 소프트 파싱 : A를 거친 후 메모리(LIBRARY CACHE)에서 같은 SQL을 찾아 D로 바로 실행

* 하드 파싱 : A를 거친 후 메모리(LIBRARY CACHE)에서 같은 SQL을 찾지 못해 B-C 과정을 거침 (하드란 말처럼 OPTIMIZER의 단계는 그만큼 무겁다)

커서 (다음의 3가지 모두 커서라고 호칭)

공유커서 : 라이브러리 캐시에 공유되 잇는 Shared SQL Area  (클래스 상태)

세션커서 : Private Sql Area 에 저장된 커서 , 

공유커서를 실행하기 위해 PGA에 메모리(Private Sql Area)를 할당 (인스턴스상태)하고 공유커서에 대한 포인터를 유지 = 커서를 오픈한다고 표현

에플리케이션커서 : 세션 커서를 가리키는 핸들 (java, vb같은 애플리케이션에 메모리 할당)

** 하드 파싱을 줄이기 위해선 바인드 변수의 사용이 중요하다.

바인드변수 사용의 단점

실행단계에서 변수가 바인드 되기 때문에 SQL 최적화 시점에 컬럼의 히스토그램을 사용하지 못한다. -> 분포도에 따라 인덱스 스캔 OR 풀스캔이 유리한데 균등분포를 사용하기 때문에 실행계획이 고정된다. 그렇더라도 OLTP 환경에선 동시접속자가 많으므로 하드파싱으로 인한 부하를 줄이는게 효과적이다.

** DW 나 OLAP 환경에선 상수가 오히려 유리

바인드 변수 PEEKING : SQL이 첫 번째 수행되면서 하드파싱할때 함께 딸려온 바인드 변수 값을 훔쳐보고 그 값에 대한 컬럼 분포를 이용해 실행 계획을 결정

-> 분포도에 따라 최악의 실행 계획 결정 가능성이 있슴

적응적 커서 공유 : 바인드변수에 따라 커서를 분기해서 실행계획을 결정, 선택도가 비슷한 변수는 같은 커서를 공유 (조건절 컬럼에 히스토그램이 생성되어 있어야 한다.)

V$SQL 에서 IS_BIND_AWARE = 'Y' 로 나타남

쿼리에서 라이브러리 캐시 최적화를 구현

DW

SELECT /*+FULL */

FROM    테이블 A

WHERE   선택도 높은 컬럼 조건

UNION ALL

SELECT /*+INDEX A(IDX01) */

FROM    테이블 A

WHERE   선택도 낮은 컬럼 조건

ON-LINE 

ONLINE 에선 위처럼 작성하면 하드파싱의 부하도 높이고 실행단계의 CPU 사용량도 높아짐

1. 변수에 따른 UNION ALL로 분리

SELECT /*+FULL */

FROM    테이블 A

WHERE   :bind is null

AND    ~~

UNION ALL

SELECT /*+INDEX A(IDX01) */

FROM    테이블 A

WHERE   :bind is not null

AND       A.COL1 = :bind

2.IF ELSE 로 분기

조건절에 따른 쿼리기준

1. NOT NULL 컬럼일때는 NVL, DECODE 사용

2. NULL 컬럼일때는 위와 같이 UNION ALL 로 분기

3. 인덱스 ACCESS 조건이 아닐때는 :C IS NULL OR COL=:C  또는 C LIKE :C || '%' 사용

STATIC SQL 구현을 위한 쿼리

1. IN 조건절이 가변적이면서 갯수가 많지 않을때

   최대 조건 만큼 IN (:A, :B, :C~~)

2. IN 조건절이 가변적이면서 갯수가 많을때 (코드길이가 일정할때)

방법 1 

:inList = '01,02,03,04,55,99'

INSTR(:inList, 분류코드) > 0   -> 분류코드가 인덱스 선두컬럼일때 비효율

방법2

:inList = '010203045599'

select /*+ ordered use_nl(B) */ B.*

from  (select substr(:inList, (level-1)*2+1, 2) 분류코드

         from  dual

         connect by level <= length(:inList) /2 ) A

        ,수시공시내역 B

where B.분류코드 = A.분류코드

3. Exists 쿼리안의 조건이 가변적일경우 

   (exits의 첫번째 만족 조건시 리턴 이용)

and exists (

select 'x' from dual where :chk = 'N'

union all

select 'x'

from  tab1

where tab1.종목 = main.종목

and    otherCond = 'Y'

and    :chk = 'Y'

)

4. select가 동적일때

decode(gbn,1,sum(col1), avg(col1))

sum(decode(gbn,1,col1, col2)) => decode함수를 집계대상 건수만큼 반복하므로 비효율

5. 콤보박스의 이상,이상,미만 등의 조건처럼 가변적일때

where col1 between :min1 and max1

and    col2 between :min2 and max2

      and    col3 between :min3 and max3

세션커서캐싱  

자주 수행하는 SQL에 대한 세션 커서를 세션 커서 캐시에 저장

SQL 텍스트와 공유 커서를 가리키는 포인터를 저장

커서는 닫혀도 포인터는 유지하기때문에 다음 수행시 커서를 빨리 오픈

애플리케이션 커서캐싱

세션커서를 캐싱해도 공유 커서 힙을 PIN하고 실행에 필요한 메모리 공간을 PGA에 할당하는 작업은 하는데 이과정을 생략하는것이 애플리케이션 커서 캐싱

PRO-C

for(;;) {

EXEC ORACLE OPTION (HOLD_CURSOR = YES);

EXEC ORACLE OPTION (RELEASE_CURSOR = NO);

EXE SQL........

EXEC ORACLE OPTION (RELEASE_CURSOR = YES);

}

-> EXECUTE는 다수일지라도 PARSE CALL 은 1번

JAVA 1 : statement를 loop 밖에 선언, 해제

PreparedStatement stmt = con.preparedStatement(~~);

Resultset rs;

for(;;) {

stmt.setInt(1,i);

stmt.setString(2,"abc");

rs = stmt.executeQuery();

rs.close();

}

stmt.close();

JAVA 2 : 묵시적 캐싱 사용

((OracleConnection)conn).setStatementCacheSize(1);    //캐시사이즈 지정

((OracleConnection)conn).setImplicitCachingEnabled(true);        //묵시적 캐싱 기능 활성화

for(;;) {

PreparedStatement stmt = con.preparedStatement(~~);

stmt.setInt(1,i);

stmt.setString(2,"abc");

rs = stmt.executeQuery();

rs.close();

stmt.close();    //커서는 닫지만 내부적으로 닫히지 않은 채 캐시에 보관

}

오라클 성능고도화