DATOR


튜닝-OLTP주요파라메터 확인

OLTP튜닝을 하다보면 아래 2개의 파라메터에 의해서
튜닝의 큰 골격을 잡기가 어려워 질수 있다.

 

OLTP만 사용하는 시스팀이라면 아래 2개의 파라메터는
해당 권고안으로 처리되어야 된다.

 

슬라이드1.GIF 슬라이드2.GIF 슬라이드3.GIF 슬라이드4.GIF 슬라이드5.GIF 슬라이드6.GIF 슬라이드7.GIF

 

 

 

참조자료)

 

 

1. 개요

   [ CURSOR ]

- 모든 SQL문은 Parsing이 이루어지면 Library Cache내에 자신만의 공간을 가지는데 이 영역을 Cursor라 한다.

- Cursor에는 The Parsed Statement, Execution Plan, 참조하는 Object의 목록이 저장되어 있다.

- Cursor를 공유함으로써 Parsing Overhead를 줄일 수 있으며 메모리를 보다 효율적으로 사용할 수 있다.

 

[ CURSOR 공유 ]

- Cursor를 공유하기 위해서는 동일한 SQL문을 사용하여야 하며 다음과 같은 규칙을 따라야 한다.

  . SQL문은 대/소문자, 공백, 콤마를 포함해서 동일하게 작성되어야 한다.

. SQL문에서 참조하는 Object가 동일해야 한다

- Bind Variables를 사용한다.

. Bind변수를 사용하면 Oracle SQL을 동일한 것으로 판단하며 Execution Plan을 변경하지 않는다. 따라서 SQL호출 시 Bind 변수 값에 상관없이 Shared Pool에 저장된 동일한 SQL문을 공유한다.

- Dynamic(Literal) SQL 패턴을 사용함으로 SQL을 공유하지 못하여 PARSING 부하가 발생하게 된다.

 

2. CURSOR_SHARING PARAMETER 수정을 통한 HARD PARSE 감소

2.1.  위에서 거론한 바와 같이 단시간에 모든 프로그램의 구조를 변경할 수 없는 상황에서 SHARED_SQL AREA상에서 SQL HIT RATIO를 높일 수 있는 CURSOR_SHARING PARAMETER를 설정하여

동적으로 변하는 사용자 입력 값이 포함된 SQL이라 하더라도 동일한 SQL로 간주하여 재사용이 가능하도록 할 수 있다.

-  CURSOR_SHARING = EXACT

: SQL구문이 모두 똑같을 경우에만 REPARSE하지 않음

  (alter session set cursor_sharing=EXACT)

: SELECT COUNT(*)

      FROM TAB WHERE B1 = 10;  --------- )

    SELECT COUNT(*)

      FROM TAB WHERE B1 = 20;  --------- )

 

SQL은 기본적으로 동일한 값이 입력되어 파싱된다. 파싱 단계에서 동일한 값이 없다

단 동일한 값이 입력될 경우에는 Hard Parsing하지 않고 soft parsing(SQL Area에 있는지 확인만 함)하게 된다.

 

select hid, count(*), sum(amt1)

from  tli_1219

where msg_type='0000' and txntype in  ('M','I')

group by hid

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.00       2.51       6919       6921          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.00       2.51       6919       6921          0           0

 

 

select hid, count(*), sum(amt1)

from  tli_1219

where msg_type='0001' and txntype in  ('M','I')

group by hid

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        2      0.00       0.00          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        2      0.00       4.88      13838      13842          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        6      0.00       4.89      13838      13842          0           0

 

Misses in library cache during parse: 1

 

-  CURSOR_SHARING = SIMILAR

: SQL구문이 비록 다르지만 , 상수 값만이 다른 경우 해당 컬럼의 분포도를 참조하여 optimizer는 동일 SQL문으로 판단 될 경우 바인딩 처리하여 Hard Parse를 줄이고 Soft Parse를 하도록 유도하고 있다.

 

select hid, count(*), sum(amt1)

from  tli_1219

where msg_type='0110' and txntype in  ('M','I')

group by hid

 

select hid, count(*), sum(amt1)

from  tli_1219

where msg_type='0410' and txntype in  ('M','I')

group by hid

 

select hid, count(*), sum(amt1)

from  tli_1219

where msg_type='0420' and txntype in  ('M','I')

group by hid

 

è 변경

 

select hid, count(*), sum(amt1)

from  tli_1219

where msg_type=:"SYS_B_0" and txntype in  (:"SYS_B_1",:"SYS_B_2")

group by hid

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      3      0.00       0.00          0          0          0           0

Fetch        7      0.00       7.56      20720      20763          0          38

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       13      0.00       7.57      20720      20763          0          38

 

-  CURSOR_SHARING = FORCE

: SQL구문이 다르지만 단순히 LITERAL만 다를 경우 모두 동일한 SQL이라 판단하여 시스템의 바인딩 변수로 처리하여 파싱한다.

물론 상수값의 분포도가 일정하다면 큰 무리는 없지만, 상수값의 분포도가 어느 한쪽으로 치우쳐 인덱스 사용이 불 필요할 경우에도 옵티마이져는 계속 인덱스를 사용하는 실행계획으로 풀도록 고정이 될 수 있다.

 

3. 결론

- MVS INSTANCE LEVEL로 설정된 CURSOR_SHARING = SIMILAR INSTANCE LEVEL이 아니라 꼭 필요한 경우에 해당 SESSION LEVEL에서 설정하도록 하도록 하고, INSTANCE LEVEL에서는 CURSOR_SHARING = EXACT로 변경하도록 한다.

- 변경 : INSTANCE - CURSOR_SHARING = SIMILAR è CURSOR_SHARING = EXACT

- 추가 : SESSION  - CURSOR_SHARING = SIMILAR

        ALTER SESSION SET CURSOR_SHARING = SIMILAR ;

 

 

Tag

Leave Comments