오라클 성능 고도화 원리와 해법 I (2016년-2)
바인드 변수의 부작용과 해법
- 바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고,
실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 된다.
이는 변수를 바인딩하는 시점이(최적화 시점보다 나중인) 실행시점이라는 사실에 주목할 필요가
있으며, 이를 해석하면 바인드 변수를 사용함으로 인해 최적의 실행계획을 만드는 것은 한계가
있다는 것을 알 수 있다. - 다르게 해석한다면 바인드 변수를 사용함으로 인해 항상 고정적인 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
"구루비 DB 스터디 모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3172
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.