바인드 변수의 부작용과 해법

• 바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고,
  실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 된다.
  이는 변수를 바인딩하는 시점이(최적화 시점보다 나중인) 실행시점이라는 사실에 주목할 필요가
  있으며, 이를 해석하면 바인드 변수를 사용함으로 인해 최적의 실행계획을 만드는 것은 한계가
  있다는 것을 알 수 있다.
• 다르게 해석한다면 바인드 변수를 사용함으로 인해 항상 고정적인 PLAN을 보장받을 수 있으며
  Literal을 사용함으로 인해 실행계획이 유동적으로 변경되는것을 막는 혜택 또한 볼 수 있다.

View Source

• 바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고,
  실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 됨
  이는 변수를 바인딩하는 시점이(최적화 시점보다 나중인) 실행시점이라는 사실에 주목할 필요가
  있으며, 이를 해석하면 바인드 변수를 사용함으로 인해 최적의 실행계획을 만드는 것은 한계가
  있다는 것을 알 수 있다.
  다르게 해석한다면 바인드 변수를 사용함으로 인해 항상 고정적인 PLAN을 보장받을 수 있으며
  Literal을 사용함으로 인해 실행계획이 유동적으로 변경되는것을 막는 혜택 또한 볼 수 있다.

• 바인딩 참조 시점 확인 테스트

-- 1. 테이블 생성
DROP TABLE T;

CREATE TABLE T AS
SELECT *
FROM   ALL_OBJECTS
;

-- 2. 테이블 UPDATE
UPDATE T
SET    OBJECT_ID = ROWNUM
;

COMMIT;

-- 3. 인덱스 생성
CREATE UNIQUE INDEX T_U1 ON T(OBJECT_ID);

-- 4. 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'T',
                                  CASCADE => TRUE);
END;
/

-- 5. 통계정보 확인
@STAT

COLUMN_NAME                    DATA_TYPE  DATA_LEN Null DISTINCT_KEY      DENSITY NUM_NULLS      NUM_BUCKETS SAMPLE_SIZE    LAST Analyzed
------------------------------ ---------- -------- ---- -------------- ---------- -------------- ----------- -------------- -------------------
CREATED                        DATE              7 N            28,849 .000034663              0           1         52,908 2009-12-01 21:39:13
DATA_OBJECT_ID                 NUMBER           22 Y           231,041 4.3282E-06        640,896           1        231,079 2009-12-01 21:39:13
GENERATED                      VARCHAR2          1 Y                 2         .5              0           1          5,340 2009-12-01 21:39:13
LAST_DDL_TIME                  DATE              7 N            29,401 .000034012              0           1         52,908 2009-12-01 21:39:13
OBJECT_ID                      NUMBER           22 N           871,975 1.1468E-06              0           1          5,340 2009-12-01 21:39:13
OBJECT_NAME                    VARCHAR2         30 N           275,980 3.6235E-06              0           1        871,975 2009-12-01 21:39:13
OBJECT_TYPE                    VARCHAR2         19 Y                22 .045454545              0           1          5,340 2009-12-01 21:39:13
OWNER                          VARCHAR2         30 N               197 .005076142              0           1          5,340 2009-12-01 21:39:13
SECONDARY                      VARCHAR2          1 Y                 2         .5              0           1          5,340 2009-12-01 21:39:13
STATUS                         VARCHAR2          7 Y                 1          1              0           1          5,340 2009-12-01 21:39:13
SUBOBJECT_NAME                 VARCHAR2         30 Y             3,958 .000252653        776,936           1          5,874 2009-12-01 21:39:13
TEMPORARY                      VARCHAR2          1 Y                 2         .5              0           1          5,340 2009-12-01 21:39:13
TIMESTAMP                      VARCHAR2         19 Y            30,744 .000032527              0           1         52,908 2009-12-01 21:39:13

                                                                NUMBER_ROWS     CLUSTERING     BLEVEL       AvgLfBlperKEY             INITRAN Init/Next/    FLst/
OWNER.INDEX_NAME                         TABLESPACE             DISTINCT_KEY    FACTOR         LEAF_BLOKCS  AvgDtBlperKEY DEG  LOGGIN MAXTRAN MIN/MAX       FGrp  LAS
---------------------------------------- ---------------------- --------------- -------------- ------------ ------------- ---- ------ ------- ------------- ----- ---
USER.T_U1                                USER_DATA                     871,975          12,325         [2]             1    1  YES    2/255   128k/128k     /     200
                                                                       871,975                       1,820             1                      1/unlimit

OWNER.INDEX_NAME                         Uniq Type   Status Drop COLUMN LIST
---------------------------------------- ---- ------ ------ ---- ----------------------------------------------------------------------------------------------------
USER.T_U1                                UNIQ NORMAL VALID  NO   OBJECT_ID

-- 6. Trace 실행
@mysess

MY_SESSION_INFO
--------------------------------------------------
 Sid, Serial#  : 9964, 50
   OS Process  : 2452:4440 (CPID), 627066 (SPID)

ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';

VAR VAL NUMBER
EXEC :VAL := 1000;

SELECT OBJECT_NAME
FROM   T
WHERE  OBJECT_ID = :VAL
;

OBJECT_NAME
-----------------
V_$ENQUEUE_STAT

ALTER SESSION SET SQL_TRACE=FALSE;

-- 7. TRC 파일 직접 확인
-- 바인드 변수를 실제 실행하는 시점이 CURSOR를 PARSING 하고 난 이후임을 알 수 있음
PARSING IN CURSOR #3 len=52 dep=0 uid=44 oct=3 lid=44 tim=9138570012406 hv=4215685038 ad='6148aef0'
SELECT OBJECT_NAME
FROM   T
WHERE  OBJECT_ID = :VAL
END OF STMT
PARSE #3:c=0,e=602,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=9138570012400
BINDS #3:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=11049ac98  bln=22  avl=02  flg=05
  value=1000
EXEC #3:c=0,e=3008,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=9138570015551
FETCH #3:c=0,e=139,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=9138570015811
FETCH #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=9138570017748

• 바인드 변수를 사용할 때는 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 높다.
• 바인드 변수를 사용하면 컬럼 히스토그램을 제대로 활용하지 못할 뿐만 아니라 파티션 테이블을 쿼리할 때 파티션 레벨 통계 정보를 이용하지 못하게 되는 것도 바인드 변수의 대표적인 부작용 중 하다.
• 파티션 레벨 통계보다 다소 부정확한 테이블 레벨 통계를 이용함으로써 옵티마이저가 가끔 악성 실행계획을 수립한다.

(1) 바인드 변수 Peeking

• 바인드 변수의 부작용을 극복하려고 오라클은 9i부터 바인드 변수 Peeking 기능을 도입함
• 'Peeking'이란 바인드 변수를 가진 CURSOR를 최초 수행할 때 해당 바인드 변수의 Literal 값을 한번 보고 실행계획을 작성한 뒤 해당 CURSOR가 재사용될 때 이전에 살펴본 Literal 값을 참조하여 실행계획을 작성하는 방식
• 이 방식의 문제는 분포도가 심하게 차이가 나는 경우(예를들어 A=90%, B=9%, C=1%) 최초 수행시 값이 A라면
  FULL SCAN이 좋겠지만, 그 이후 C가 들어옴에도 불구하고 최초 수행됐던 A를 참조하여 계속 FULL SCAN을 하는 단점이 있음
• 더욱 큰 문제는 10g부터 DBMS_STAT 기본 설정이 히스토그램을 생성할지 여부를 오라클이 판단하게 되어이에 대한 통제를 하지 않게 되면 9i보다 더욱 많은 히스토그램이 생성되게 되고 이로 인해 바인드 Peeking에 의한 폐해가 더욱 커지게 된다.
• 이로 인해 대부분의 사이트에서는 이 Peeking 기능을 FALSE로 설정하고 사용함

SELECT A.KSPPINM  NAME,
       B.KSPPSTVL VALUE,
       B.KSPPSTDF DEF_YN,
       A.KSPPDESC DESCRIPTION
FROM   X$KSPPI  A,
       X$KSPPSV B
WHERE  A.INDX = B.INDX
AND    LOWER(A.KSPPINM) LIKE '%' || TRIM(LOWER('_optim_peek_user_binds')) || '%'
ORDER  BY 1
;

NAME                   VALUE DEF_YN DESCRIPTION
---------------------- ----- ------ ------------------------------
_optim_peek_user_binds FALSE FALSE  enable peeking of user binds

(2) 적응적 커서 공유

• 11g에서 개선된 기능
• 이 기능이 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성되 있어야 한다.
• v$sql을 조회해 보면 is_bind_sensitive 커럼이 'Y'로 표시되어있어야함
• 하지만, Bind Sensitive 모드에서는 아직 바인드 값에 따라 실행계획이 바뀌지는 않으며, 우선 Bind Aware 모드로의 전환이 필요하다. 오라클은 Bind Sensitive 커서에 재해 내부적으로 별도의 히스토그램과 수행 통계를 관리하며, 특정 값으로 실행했ㅎ을 때에 비해 많은 일량을 처리한 것으로 판단되는 순간 해당 커서를 Bind Aware 모드로 전환한다.
• 'Bind Aware' 모드에서 생성된 커서를 Bind Aware 커서라고 부르며, v$sql에서 is_bind_aware 컬럼이 'Y'로 표시된다.
• v$sql 에서 is_shareable 컬럼이 'N'로 설정되며, 이후 라이브러리 캐시에 공간이 필요할 때 가장 먼저 밀려난다.
• 새로 생성된 커서의 실행계획이 기존에 캐싱돼 있던 커서의 실행계획과 결과적으로 같다면, 그중 하나만 사용하고 나머지는 버린다. 그럼으로써 같은 실행계획을 가진 커서가 불필요하게 많이 만들어지는 것을 방지한다.

(3) 입력 값에 따라 SQL 분리

• 바인드 변수는 Literal 값처럼 Cardinarity를 옵티마이저가 정확히 구할 수 없으므로 값의 분포도에 따라 최적의 실행계획을 만들기가 힘듬
• 이를 해결하기 위해 값의 분포도를 미리 알고 있다면 해당 값에 따라 SQL을 분기하여 최적의 실행계획을 유도할 수 있음
• 아래는 분포도가 큰 '서울시', '경기도' 값은 FULL SCAN, 분포도가 작은 다른 지역은 INDEX RANGE SCAN을 UNION ALL을 사용하여 유도한 내용

SELECT /*+ FULL(A) */
*
FROM 아파트매물 A
WHERE :CITY IN ('서울시', '경기도')
UNION ALL
SELECT /*+ INDEX(A IDX01) */
*
FROM 아파트매물 B
WHERE :CITY NOT IN ('서울시', '경기도')
AND 도시 = :CITY;

• 하지만 모든 분기마다 UNION ALL을 사용하게 되면 Parsing 시간이 더 커져서 수행속도 및 리소스를 증가시킬 수 있으므로
  IF ELSE 분기로 하나의 SQL만 액세스하도록 유도

IF :CITY IN ('서울시', '경기도') THEN
SELECT /*+ FULL(A) */
*
FROM 아파트매물 A;
ELSE
SELECT /*+ INDEX(A IDX01) */
*
FROM 아파트매물 B
WHERE 도시 = :CITY;
END IF;

4. 예외적으로, Literal 상수값 사용

• Literal 값보다 바인드 변수를 사용하는 것이 대부분 CURSOR 재활용 측면에서 좋긴 하지만 일부 측면에서는
  Literal로 사용하는 것이 더 좋은 경우가 있다.
  이는 특정 컬럼의 갑 종류가 얼마 없는 경우인데, 이 경우에는 하드파싱의 부하가 미미하게 되며 옵티마이저가
  Literal 값을 미리 알고 있기 때문에 더 좋은 실행계획을 생성할 수 있다.
• 그러므로 이를 잘 활용하여 적재적소에 Literal을 적게만 활용하도록 하자

문서에 대하여

• 최초작성자 : ~xsoft
• 최초작성일 : 2009년 12월 05일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*

Powered by a free Atlassian Confluence Open Source Project License granted to phoca. Evaluate Confluence today.

Powered by Atlassian Confluence 2.10.4, the Enterprise Wiki. Bug/feature request - Atlassian news - Contact administrators