by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




  • 바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고,
    실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 됨
    이는 변수를 바인딩하는 시점이(최적화 시점보다 나중인) 실행시점이라는 사실에 주목할 필요가
    있으며, 이를 해석하면 바인드 변수를 사용함으로 인해 최적의 실행계획을 만드는 것은 한계가
    있다는 것을 알 수 있다.
    다르게 해석한다면 바인드 변수를 사용함으로 인해 항상 고정적인 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. Adaptive Cursor Sharing

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을 적게만 활용하도록 하자

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.