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