SQL 과실행계획을이용한튜팅 엔코아컨설팅 컨설팅사업본부본부장 김동훈이사
CONTENTS SQL 의개념실행계획패턴실행계획의최적화
SQL 의개념 - 수행단계 SQL 은데이터처리방법을기술한것이아니라단지필요한데이터를요구한것임 SQL Parser Parsed Query Query Transfrmer Transfrmed Query Estimatr Plan Generatr Rw Surce Generatr Query + Estimates Query Plan Schema Objects Statistics 사용자가실행한 SQL은데이터딕셔너리를참조하여파싱을수행. 옵티마이져는파싱결과를이용해논리적으로적용가능한실행계획형태를선택하고, 힌트를감안하여일차적으로잠정적인실행계획들을생성데이터딕셔너리의통계정보 ( 데이터의분포도, 테이블저장구조, 인덱스구조, 파티션형태, 비교연산자 ) 등을감안하여각실행계획의비용을계산실행계획들의산출된비용을비교하여가장최소의비용을가진실행계획을선택 ( 최저가입찰방식이므로항상최적의결정이라고만할수는없음 )
SQL 의개념 데이터처리영향요소 SQL 작성시데이터처리효율에영향을미치는요소를항상생각 데이터모델 데이터저장형태 인덱스형태및구조 분리형테이블 일체형테이블 <= Frd > Frd <= Clark > Clark <= Hunt > Hunt Nn Key Clumn Key Clumn Rw Header Allen 0007 Clarke 0003 Davis 0002 Frd 0005 Green 0006 Hunt 0008 Jnes 0004 King 0001 SQL 의형태 SELECT * FROM table1 WHERE COL1 = 123 AND COL2 <> ABC OR COL3 < 100; Bitmap Inde (COL1=123) Bitmap MINUS 실행계획 Bitmap MINUS Bitmap Inde (COL2= ABC ) TABLE ACCESS Bitmap Cnversin (ROWID) Bitmap OR Bitmap Inde (COL2 IS NULL) Bitmap MERGE Bitmap Inde (COL3 < 100) 0.350 0.300 0.250 0.200 0.150 0.100 0.050 0.000 통계정보 계급계급구간도수계급값 1-0.5~4.5 8 2.0 1252.8 2 4.5~9.5 3 7.0 169.4 3 9.5~14.5 6 12.0 37.9 0 1 4 2 14.5~19.5 3 4 7 5 17.0 6 43.3 5 19.5~24.5 5 22.0 280.2 6 24.5~29.5 5 27.0 779.5 7 29.5~34.5 0 32.0 0.0 8 34.5~39.5 1 37.0 505.6 합 35 3068.7 옵티마이져 메모리의활용 클러스터링팩터 CBO First_rws All_rws Shared Pl Shared SQL Area SGA Database Buffer Cache Red Lg Buffer RBO Versin
SQL 의개념 집합개념필수 SQL 은집합을처리하기위한도구 원집합 번호시작일시종료일시고객 123 20070112 20070130 이순신 123 20070130 20070204 김유신 123 20070204 20070228 김유신 123 20070228 20070501 이순신 123 20070501 99991231 을지문득 125 20070301 20070317 강감찬 125 20070317 20070412 강감찬 125 20070412 20070510 계백 125 20070510 99991231 홍길동.... 중간필요집합 번호시작일시종료일시 SW 고객 123 20070112 20070130 1 이순신 123 20070130 20070204 2 김유신 123 20070204 20070228 2 김유신 123 20070228 20070501 3 이순신 123 20070501 99991231 4 을지문득 125 20070301 20070317 1 강감찬 125 20070317 20070412 1 강감찬 125 20070412 20070510 2 계백 125 20070510 99991231 3 홍길동.... 원집합에서결과집합을얻기위해서는반드시중간집합이필요함중간집합은 Analytic functin READ, SUM을사용하여구할수있음. 결과집합 전화번호시작일시종료일시고객 123 20070112 20070130 이순신 123 20070130 20070228 김유신 123 20070228 20070501 이순신 123 20070501 99991231 을지문득 125 20070301 20070412 강감찬 125 20070412 20070510 계백 125 20070510 99991231 홍길동....
SQL 의개념 집합연결방법 두개이상의집합을연결하는방법은 4 가지형태만존재 조인의활용 운반단위 저장형함수및 Scalar Subquery 활용 CREATE FUNCTION BEGIN (@v_empn RETURN FUNC1 varchar(10), varchar2 is declare V_avg_amt int SELECT avg( 급여총액 ) int v_avg_amt FROM 급여 WHERE 사번 =v_empn and 년월 like 199803% ; RETURN v_avg_amt; END FUNC1 ; SELECT 사번, 성명, FUNC1(empn) FROM 직급, 직책, 사원, WHERE 부서 = 1100 ; CD SQ AT A 1 150 A 2 200 B 1 100 B 2 120 CD SQ BT A 10 300 A 11 100 B 11 150 B 15 100 서브쿼리의활용 SELECT. 자재코드,. 자재명,. 안전재고, y. 재고수량 FROM 자재, 자재일일재고 y WHERE y. 자재코드 =. 자재코드 UNION, GROUP BY UNION ALL Ł CD SQ AT BT A 1 150 A 2 200 B 1 100 B 2 120 and y. 년월일 = cnvert(varchar(8), getdate, 112) and. 자재코드 IN (SELECT 자재코드 FROM A 10 300 A 11 100 B 11 150 B 15 100 구매의뢰 GROUP BY Ł WHERE 진행상태 = 발주중 and 출고일자 like 199807% ) CD AT BT A 350 400 B 220 250...
실행계획패턴 실행계획수립에영향을미치는요소 SQL 형태 인덱스, 테이블구조 사용컬럼, 연산자형태 =, LIKE, A = 상수, A = : 변수 SELECT FROM TAB WHERE... 통계및 Parameter 정보 힌트사용 SELECT /*+ FULL */ FROM... 시스템및네트워크상태 OPTIMIZER DBMS, 버전 옵티마이져모드 RULE All_rws First_rws DB2 분산 DB
실행계획패턴 테이블및조인액세스 옵티마이져는대부분두개의패턴을통해실행계획을작성 Inde Range Scan & Table Scan Full Table Scan TABLE 액세스 실행계획패턴 ( 두가지방법만존재 ). 운반단위. 운반단위 INDEX TABLE TAB Nested Lp Srt Merge Hash 조인 FLD1= 111' INDEX (FLD1)... TABLE ACCESS BY ROWID KEY2= KEY1... FLD2 LIKE AB% INDEX TAB1 (KEY2) TABLE ACCESS BY ROWID... COL1 = 10 TAB2 운반단위 FLD1= 111' INDEX (FLD1). TABLE ACCESS BY ROWID S O R. T FLD2 LIKE AB% TAB1 KEY2 = KEY1 는머지조건으로... Merge 운반단위 S O R... T TABLE ACCESS BY ROWID. COL1 = 10 TAB2 INDEX (KEY2) TAB1 Hash fn() Hash area Partitin Table Hash Table Bitmap vectr P C111 P C41 4 P C212 Hash fn() P C51 5 P C31 3 U GA TAB2
실행계획패턴 실행계획패턴 옵티마이져의거의대부분은다음의실행계획패턴으로수행 Nested Lp 조인패턴 NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF 'SDAMASTER.SAM_ORG' INDEX (UNIQUE SCAN) OF 'SDAMASTER.PK1_SAM_ORG' TABLE ACCESS (BY INDEX ROWID) OF 'SDAMASTER.SAM_STF' INDEX (RANGE SCAN) OF 'SDAMASTER.I01_SAM_STF' TABLE ACCESS (BY INDEX ROWID) OF 'SDAMASTER.INS_INS_PL' INDEX (SKIP SCAN) OF 'SDAMASTER.I02_INS_INS_PL
실행계획패턴 실행계획패턴 Srt Merge 조인패턴 MERGE JOIN SORT(JOIN) TABLE ACCESS FULL OF 사원 SORT(JOIN) VIEW SORT(UINQUE) TABLE ACCESS BY ROWID OF 근태 INDEX RANGE SCAN OF 유형 _ 일자 _IDX Hash 조인패턴 HASH JOIN TABLE ACCESS (BY ROWID) OF ORDER INDEX (RANGE SCAN) OF ORDDATE_INDEX (NON-UNIQUE) TABLE ACCESS (FULL) OF DEPT 조인은반드시성공한결과가다음조인에참여 조인테이블이무수히많아도테이블을액세스하는방법은 2 가지만존재
실행계획패턴 복잡한실행계획패턴 복잡하지만단위별로분석하면앞에설명한기본적인패턴을벗어나지못함 Rws Eecutin Plan ------- --------------------------------------------------- 0 SELECT STATEMENT 1181 SORT (GROUP BY) 1181 NESTED LOOPS 1514 VIEW 256854 SORT (GROUP BY) 256854 HASH JOIN (OUTER) 256854 VIEW 256854 UNION-ALL 256852 NESTED LOOPS 0 INLIST ITERATOR (CONCATENATED) 256856 TABLE ACCESS (BY INDEX ROWID) OF DPACCB' 256908 INDEX (RANGE SCAN) OF 'DPACCB_IDX1' (NON-UNIQUE) 24915032 TABLE ACCESS (BY INDEX ROWID) OF 'BR_INFO' 40840104 INDEX (RANGE SCAN) OF BR_INFO_IDX2' (NON-UNIQUE) 0 INLIST ITERATOR (CONCATENATED) 422116 TABLE ACCESS (BY INDEX ROWID) OF 'DPACCB' 422118 INDEX (RANGE SCAN) OF 'DPACCB_IDX2' (NON-UNIQUE) 15 VIEW 15 SORT (GROUP BY) 15 TABLE ACCESS (BY INDEX ROWID) OF DPDDBS' 16080201 INDEX (RANGE SCAN) OF DPDDBS_IDX1' (NON-UNIQUE) 1688 TABLE ACCESS (BY INDEX ROWID) OF 'DPGDPF' 3202 INDEX (RANGE SCAN) OF 'PK_DPGDPF' (UNIQUE)
실행계획의최적화 최적화대상 실행계획의최적화란비효율을제거하는것임 최초입력이 1000건이고출력이1000건이면비효율은없음입력이 1000인데출력이 100이면 900건의비효율이발생한것임비효율이발생하는경우항상비효율을발생시킨원인이존재이원인을분석하여해결하는것이실행계획최적화 SELECT 가입계약번호, 전화번호, Y. 상품 FROM 가입계약이력 X, 가입계약별상품 Y WHERE X. 가입계약 = Y. 가입계약 AND Y. 상품코드 = FF001 1000 1000 1000 1000 1000 SELECT 가입계약번호, 전화번호, Y. 상품 FROM 가입계약이력 X, 가입계약별상품 Y WHERE X. 가입계약 = Y. 가입계약 AND Y. 상품코드 = FF001 AND X. 상태 = 정지 1000 1000 1000 100 100 가입계약별상품 가입계약이력 가입계약별상품 가입계약이력
실행계획의최적화 테이블특성 테이블특성에따라서전략을수립하여실행계획을최적화 1 적은데이터를가진소형테이블 -DB_FILE_MULTIBLOCK_READ_COUNT 값이하블록크기의테이블 - 한번의멀티블럭 I/O 에의해인덱스없이전체테이블스캔가능 - 다른테이블들과연결될경우인덱스유무는옵티마이져에영향 - 특히조인에서내측루프로수행되면작은테이블이라도인덱스없으면큰영향 2 주로참조되는 (Referenced) 역할을하는중대형테이블 - 트랜잭션데이터의행위주체, 목적이되는개체들로구성된테이블 ( 키엔티티집합, 고객 ) - 데이터증감없고, 검색위주액세스발생, 검색조건형태고정 - 블록내에최대한조밀한인덱스공간을위해 PCTFREE 를 0 에가깝게부여
실행계획의최적화 테이블특성 3 업무적구체적인행위를관리하는중대형테이블 - 매출정보 와같은업무의구체적인수행내용을담고있는트랜잭션테이블 - 결합인덱스, B-Tree, 결합인덱스컬럼구성순서에따른효율성차이 - 최소의인덱스, 최대의액세스유형, 기존인덱스형태및예상감안한인덱스구성전략필요 4 저장용 (Lg 性 ) 대형테이블 - 로그성데이터관리목적의테이블 - 저장우선, 갱신거의없으므로 PCTFREE 여유공간불필요, PRIMARY KEY 제약조건불필요 - 식별자키필요하면 UNIQUE INDEX 생성, 파티션사용고려
실행계획의최적화 대용량데이터처리최적화 실행계획제어를통한조인의횟수감소를통한최적화 0.01 초차이만나더라도 0.01 * 10000 = 100 초 0.01 * 1 억 = 277 시간 = 12 일 소량의균은 치료약이되지만 대량의균은 생명을위태롭게한다. 반복!! Ł 대량데이터처리를위한대책이필요 병렬처리 (Parallel Prcessing) 파티션 (Static Partitin) Merge(Array Prcessing) 전략적인인덱스구성고급 SQL 구사실행계획최적화해쉬조인 (Hash Jin) 대량의 INSERT 가발생한다면 분리형이가장적당 적당한파티션전략이필요함
실행계획의최적화 조인횟수감소 실행계획제어를통한조인의횟수감소를통한최적화 A22_SVER_TRAN_DTL 4 천만건 TABLE FULL SCAN A12_TRAN 100건 A14_GL_ACNT 27건 A16_LOG_CHNL 120건 A08_WORK_VOL 9 건 SELECT... FROM A22_SVER_TRAN_DTL A22 X, (SELECT..., ROWNUM FROM A12_TRAN A12, A14_GL_ACNT A14, A16_LOG_CHNL A16, (SELECT DISTINCT WORK_BR_CD ACNT_BR_CD FROM A08_WORK_VOL WHERE ASIN_SBU_CD = '30' AND BASE_YM = :IN_DATE ) A99) A12 WHERE A22.SVER_TYPE_CD = A12.SVER_TYPE_CD AND A22.UPMU_CATE_CD = A12.UPMU_CATE_CD AND A22.GL_ACCOUNT_ID = A12.GL_ACCOUNT_ID AND A22.ACNT_BR_CD ACNT_BR_CD = A12.WORK_BR_CD HASH OUTER JOIN HASH BUILDING 부하 핵심은조인횟수의감소 기존의조인방식보다훨씬빠른속도보장
실행계획의최적화 비절차형처리 입력, 변경을동시에처리하여대부분의절차형데이터처리를 SQL 하나로처리 MERGE /*+ USE_HASH(Y X) PARALLEL(Y 20) FULL(X) PARALLEL(X 20) */ INTO DW.KCF_CONT_MASTER X USING INFRA.ECT_KCF_CONT_MASTER_02 Y ON (X.I_NCN=Y.I_NCN AND X.I_YYYYMM='999912' ) WHEN MATCHED THEN UPDATE SET X.I_CAN_GUBUN=Y.I_CAN_GUBUN, X.I_RESALE = NVL(Y.I_RESALE,X.I_RESALE), X.I_PREFIX = NVL(DECODE(Y.I_PREFIX,'X',X.I_PREFIX,Y.I_PREFIX),X.I_PREFIX), X.I_AGE = DECODE(SUBSTR(Y.I_AGE,1,1), '-', '*', 'X', 'X', '*', '*', LPAD(SUBSTR(Y.I_AGE,1,3),3,'0')), X.I_CUST_GRADE = NVL(Y.I_CUST_GRADE,X.I_CUST_GRADE) WHEN NOT MATCHED THEN INSERT VALUES ( Y.I_NCN, '999912', Y.I_CAN_GUBUN, Y.I_RESALE, Y.I_PREFIX, Y.I_CN, Y.I_BAN, Y.I_CUSTOMER, Y.I_CTN, Y.MODEL_SERIAL_NO, DECODE(SUBSTR(Y.I_AGE,1,1), '-', '*', 'X', 'X', '*', '*', LPAD(SUBSTR(Y.I_AGE,1,3),3,'0')), Y.I_GENDER, Y.I_FORMER, Y.I_S_PRICEPLAN, Y.I_ZIPCODE, Y.I_MODEL, Y.I_DEALER, Y.I_JATASA_GUBUN,.. );
퀴즈 번호별상태 번호 상태 123 사용 124 사용 125 미사용 126 사용 127 사용 128 미사용 129 사용.. 번호별단말기상태 번호 단말기 상태 123 D01 사용 124 D02 미사용 125 D03 미사용 126 D04 사용 127 D05 미사용 128 D06 사용 129 D07 사용... 번호별상품상태 번호 단말기 상태 123 S01 미사용 124 S02 사용 125 S03 미사용 126 S04 사용 127 S05 사용 128 S06 미사용 129 S07 사용... SELECT 번호, COUNT(DECODE(SW,1,1)) 번호별상태, COUNT(DECODE(SW,2,1)) 번호별상품상태, COUNT(DECODE(SW,3,1)) 번호별단말기상태 FROM ( SELECT 번호, SW, COUNT(*) OVER (PARTITION BY 번호 ) CNT WHERE CNT <> 3 GROUP BY 번호 FROM ( SELECT 번호,1 SW FROM 번호별상태 WHERE 상태 = 사용 UNION ALL SELECT 번호, 2 SW FROM 번호별상품상태 WHERE 상태 = 사용 UNION ALL SELECT 번호, 3 SW FROM 번호별단말기상태 WHERE 상태 = 사용 ) X ) A
Q & A 감사합니다.
ESI 교육센타 7월교육안내 (www.en-cre.cm) Oracle 사용자를위한 SQL 활용 기간 : 2007 년 07 월 02 일 ~ 2007 년 07 월 04 일 새로쓴대용량데이터베이스솔루션 I 기간 : 2007년 07월 09일 ~ 2007년 07월 13일관계형데이터베이스모델링 I ( 개론 ) 기간 : 2007년 07월 18일 ~ 2007년 07월 20일 새로쓴대용량데이터베이스솔루션 fr MS-SQL 기간 : 2007 년 07 월 23 일 ~ 2007 년 07 월 26 일