PTIMIZER & SQL 엔코아정보컨설팅박상용수석컨설턴트
PTIMIZING & PTIMIZER 최적화 (ptimizing) Resource Usage Response Time 최적화의대상 물적최적화 Hardware Software Network Application SQL Database 인적최적화 풍부한경험 인식의전환 도전의식 소프트최적화 적절한방법론및솔루션, 운영방식적용 프로젝트성공의 3 요소 1
RDB, 왜쉽고도어려운가? SQL PTIMIZER select col1, col2*10,.. from account x, custommer y, transection z where x.acct = z.acct and y.cust = z.cust and jdate = 970609 ; SQL 해석 실행계획 작성 실행 률ㄷㄱ34346 ㅓㅏㄴ아ㅓㅗㄴㅇㅓㅜㄴ야ㄷㅈㄷㅂ저ㅊ리아ㅡㄹㅏㅡ치ㅏㅜㅓ투 93 ㅑㅇ너ㅓㅇㄹ너ㅐㅇ러ㅐㄿㄹㅇㄹㅓㅜㄴㅑㅑㅕㅜㅑㅜ랸웅ㄴ결ㅑ어ㅐ우ㅐㅇ눈애ㅓ래ㅓㅐ앵래 8ㅈ9ㅗ9 ㅗㅑ야ㅏㄴ어ㅐ B BB JHBJB M M J ㅐㅜㅜㄹ울애ㅣㅓ애럴애ㅓㄹ애ㅐㄹ앙ㄹㄹ이ㅏㅡㅈ냐ㅈㄷㅂ989ㅈ돌ㅍㄴㅇㄴㄴ어ㅐㅑㅓ내ㅑㄴㅍㄴㅇㄹㅇㄹㄴ률ㄷㄱ34346 과ㅓㅏㄴ아ㅓㅗㄴㅇㅓㅜㄴ야ㄷㅈㄷㅂ저ㅊ리아ㅡㄹㅏㅡ치ㅏㅜㅓ투 93 ㅑㅇ너ㅓㅇㄹ너ㅐㅇ러ㅐㄿㄹㅇㄹㅓㅜㄴㅑㅕㅇㅑㅕㅜㅑㅜ랸웅ㄴㅑ어ㅐ우ㅐㅇ눈애ㅓ래ㅓㅐ앵래 8ㅈ9ㅗ9 ㅗㅑ야ㅏㄴ어ㅐㅑ퍼ㅐㅜㅜㄹ울애ㅣㅓ애럴애ㅓㄹ애ㅐㄹ앙ㄹㄹ이ㅏㅡㅈ냐ㅈㄷㅂ 사용자는요구만하고 ptimizer 가실행계획수립 참조출 사용자는요구만하고 수립된실행계획에따라엄청난수행속도차이발생 실행계획제어가어렵다. ptimizer 가최적의실행계획을수립할수있도록종합적이고전략적인 Factor 를부여를 비절차형으로기술해야함 CL$ TAB$ BJ$ IND$ VIEW$ customer account transaction 집합적으로접근해야함 SQL 이어떤역할을담당하도록구현할것인가? Data Dictionary Data 2 작성참참조조ㄴㅍㄴㅇㄹㅇㄹㄴ 추출
SQL 실행계획을정복하라! 1 ptimizer & ptimizer Mode ptimizer SQL 로요구된결과를최적의성능을보장하면서최소의비용으로처리할수있도록처리경로를결정하는 DBMS 의일부분 CB vs. RB 1) CB : 통계정보로부터모든 Access Path 고려하여실행계획수립 2) RB : 미리정해진 Rule 에따라실행계획수립 ptimizer Mode 1) RULE 2) CHSE 3) ALL_RWS 4) FIRST_RWS 3
SQL 실행계획을정복하라! RB (Rule Based ptimizer) 인덱스구조나사용연산자에부여된순위로써최적경로결정 통계정보를전혀가지지않음 경우에따라비현실적인처리경로수립 수립될처리경로예측가능 사용자가원하는처리경로로유도하기가용이 ( 수동카메라 ) 일반적인보편타당성있음 ( 생각보다높은신뢰성 ) racle 10g 부터지원되지않음 RB 의적용 Specified in INIT.RA file PTIMIZER_MDE = RULE PTIMIZER_MDE = CHSE & no statistics Specified in Session Level ALTER SESSIN SET PTIMIZER_MDE = RULE ALTER SESSIN SET PTIMIZER_MDE = CHSE & no statistics Specified in SQL Level Rule Hint (e.g., SELECT /*+ RULE */...) 4
SQL 실행계획을정복하라! RB Condition Ranking 1 RWID = constant 2 Cluster join with unique or primary key = constant 3 Hash cluster key with unique or primary key = constant 4 Entire Unique concatenated index = constant 5 Unique indexed column = constant 6 Entire cluster key = corresponding cluster key of another table in the same cluster 7 Hash cluster key = constant 8 Entire cluster key = constant 9 Entire non-unique CNCATENATED index = constant 10 Non-UNIQUE index merge 11 Entire concatenated index = lower bound 12 Most leading column(s) ) of concatenated index = constant 13 Indexed column between range or indexed column LIKE % 14 Non-UNIQUE indexed column between range or indexed column like %' 15 UNIQUE indexed column or constant (unbounded range) 16 Non-UNIQUE indexed column or constant (unbounded range) 17 Equality on non-indexed = column or constant (sort/merge join) 18 MAX or MIN of single indexed columns 19 RDER BY entire index 20 Full table scans 5
SQL 실행계획을정복하라! CB (Cost Based ptimizer) 통계정보를이용해실질적인비용을계산하여최소비용선택 실제데이터의구성상태에따른현실적인처리경로수립 이론적으로는규칙기준에비해훨씬진보된형태 전문지식이부족하더라도극단적인악성실행계획은피해갈수있음 수립될처리경로예측이곤란 ( 럭비공 ) 원하는처리경로로유도하기가곤란 ( 자동카메라 ) 생각만큼완벽한처리경로를얻을수는없음 어쩔수없는논리적한계성존재 CB 의적용 Specified in INIT.RA file PTIMIZER_MDE = CHSE/FIRST_RWS/ALL_RWS Statistics exist Specified in Session Level ALTER SESSIN SET PTIMIZER_MDE = CHSE/FIRST_RWS/ALL_RWS Statistics exist Specified in SQL Level Hint (e.g., SELECT /*+ FIRST_RWS or ALL_RWS */...) 6
SQL 실행계획을정복하라! CB Architecture Parsed Query (from Parser) Query Transformer Transformed Query 더좋은쿼리플랜을생성 할수있도록쿼리의형태를 변형하는것이유리한가? 를판단 Estimator Statistics Dictionary Query + Estimates Plan Generator Query Plan (to Row Source Generator) 쿼리변형의 4 가지테크닉 View Merging Predicate Pushing Subquery Unnesting Query Rewrite with Materialized Views 7
SQL 실행계획을정복하라! 2 ptimizer & SQL SQL 은저리절차를기술한것이아니라결과에대한요구일뿐임 처리절차는 ptimizer 가생성, 즉진정한프로그래머는옵티마이저 없는길을생성해주는것이아니라이미존재하는길을단지찾아줄뿐임 사용자가부여한영향요소에따라논리적으로존재하는최적은달라짐 ( 책임은사용자에게있음 ) 최적이란주어진상황에따라달라지는것임 : 딜레마? 동일한결과를얻을수있는경로는많으나효율성의차이는큼 옵티마이저는절대전지전능하지않다. 8
SQL 실행계획을정복하라! 3 ptimizer 에영향을미치는요소 연산자형태 =, LIKE, A = 상수, A = : 변수 SQL 형태 SELECT FRM TAB WHERE... A B CB 인덱스, 클러스터링 통계정보 로우수 분포도 최대값... SELECT /*+ FULL */ FRM... PTIMIZER RULE ALL_rows First_rows 힌트사용 분산 DB racle Infomix.. V7.4... DBMS, 버전 옵티마이져모드 9
SQL 실행계획을정복하라! 4 ptimizer 의한계 (1) 현재의정보만으로미래를예측해야함 아무리많은정보를가지고있더라도미래를예측할수있는가? 정보는 CST, 얼마나많은정보를가질수있는가? 분포도산정의어려움 컬럼의사용연산자별로정확한분포도를얻을수있는가? 컬럼의결합에따른정확한분포도를얻을수있는가? 모든결합형태에대한통계정보를보유할수있겠는가? 단위컬럼분포도들의계산에의해산정한분포도는정확할수있는가? 컬럼간의결합분포도는궁합에따라크게달라짐 10
SQL 실행계획을정복하라! 4 ptimizer 의한계 (2) 논리적으로이미존재하고있는길을찾아줄뿐임 없는길을만들어낼수는없음 SQL 사용능력과전략적인인덱스구성은결국사람이해야하는것임 콩심은데콩나고팥심은데팥난다. 대부분현실에서사용되는조건은변수형태로부여 설사컬럼값에따라정확한분포도를판단할수있다하더라도변수상태로 파싱할때는무용지물 변수상태로파싱할때는평균값에의존할수밖에없음 그렇다고해서모든 SQL 을 Dynamic SQL 로사용할수는없음 11
SQL 실행계획을정복하라! 5 집합적사고와비절차적 SQL 의사용 절차형으로작성된프로그램 SQL 위주프로그램 LP_1 SELECT_1 조건 1 조인 SELECT_3 SELECT_2 SELECT_1 다중처리 데이터처리 _1 SELECT_2 LP_2 데이터처리 _2 옵티마이져의최적화단위는 SQL SUB RUTINE CALL 옵티마이져의최적화단위는 SQL 조건 2 SELECT_3 Array INSERT 절차형처리를최적화하는옵티마이져는있을있을수없음없음 Array UPDATE SQL 에의해최적화 INSERT_1 UPDATE_1 각각의최적이전체의최적일수는없음 12
SQL 실행계획을정복하라! 6 SQL 실행계획의이해 NESTED LPS FILTER 1 2 6 SELECT ename, job, sal, dname FRM emp, dept WHERE emp.deptno = dept.deptno AND not exists (SELECT * FRM salgrade TABLE ACCESS WHERE emp.sal BETWEEN losal AND hisal) (FULL) SALGRADE 3 4 TABLE ACCESS (FULL) EMP 5 TABLE ACCESS (BY RWID) DEPT INDEX (UNIQUE SCAN) PK_DEPT ID PERATIN PTINS BJECT_NAME 1 FILTER 2 NESTED LPS 3 TABLE ACCESS FULL EMP 4 TABLE ACCESS BY RWID DEPT 5 INDEX UNIQUE SCAN PK_DEPT 6 TABLE ACCESS FULL SALGRADE 13
Dynamic SQL 과 Static SQL 을구별하라! Dynamic SQL 실행절차 Static SQL 문의실행절차 입력 입력 Binding SQL SQL Parsing Parsing Execute Binding Execute Dynamic SQL 의특징 Static SQL 문의특징 입력사항에따라최적화처리변동 Tool 에따라처리방법이상이. SQL*Forms : Execute_Query, Enter_Query. SQL*Plus : '&' 또는 '&&'. Precompiler : Dynamic SQL(4 Method) Tool 에서제공되지않을경우구현이복잡 입력된값이 Binding 되기전에처리경로를확정 처리경로로확정된필드가입력되지않았을때, Performance 저하 간단명료하게작성가능 처리경로가되는필드는필수입력필드로설정 하거나실행계획을분리하는방안을검토 Union all 혹은 Static SQL 을로직내에서 IF..THEN..ELSE 로분기처리 14
Dynamic SQL 사례 PL/SQL DECLARE x NUMBER := 10; y NUMBER; BEGIN WHILE x <= 500000 LP EXECUTE IMMEDIATE 'SELECT 100 FRM customers WHERE cust_id = ' x into y ; x := x + 10; END LP; END; PL/SQL WHILE 문실행시 SQL 문이완성되고, 완성된 SQL 문이실행 1 회의 PL/SQL 문실행시 50000 번의새로운 SQL 문이파싱되고실행 SGA SQL 영역에 50000 개의 SQL 문이순차적으로적재 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 50000 65.57 63.82 0 34 0 0 Execute 50000 2.67 2.29 0 0 0 0 Fetch 50000 2.02 1.64 0 100221 0 50000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 150000 70.26 67.76 0 100255 0 50000 15
Static SQL 사례 PL/SQL DECLARE x NUMBER := 10; y NUMBER; BEGIN WHILE x <= 500000 LP SELECT 100 into y FRM SH.CUSTMERS WHERE CUST_ID = x; x := x + 10; END LP; END; PL/SQL WHILE 문실행시변수바인딩되면서 SQL 문이반복실행 1 회의 PL/SQL 문실행시 1 번의새로운 SQL 문이파싱되고실행 SGA SQL 영역에 1 개의 SQL 문이적재 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 50000 1.13 0.78 0 0 0 0 Fetch 50000 0.58 0.51 0 100002 0 50000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 100001 1.71 1.30 0 100002 0 50000 16
비용기준최적화 (HINT) SYNTAX DELETE SELECT UPDATE INSERT /*+ hint */ text EXAMPLE SELECT /*+ INDEX(CN STATUS_IDX) 미결된건은극히일부이므로, STATUS_INDEX 를사용함 */ * FRM CNTRACTS CN WHERE STATUS= 미결 ' ; 17
비용기준최적화 (HINT) 기 능 설 명 사용예 RULE CHSE, RULE BASED PTIMIZER 를사용 /*+ RULE */ FIRST_RWS 첫째레코드의추출시간을최소화할목적으로최적화 (Response Time Goal) /*+ FIRST_RWS */ ALL_RWS FULL RWID 모든레코드를모두처리하는시간을최소화할목적으로최적화 (Throughput) 지정된테이블에대한전체스캔 지정된테이블에대한 RWID 에의한테이블스캔 /*+ ALL_RWS */ SELECT /*+ FULL(A) */ * FRM EMP A /*+ RWID(table) ) */ CLUSTER 지정된테이블에대한클러스터스캔 /*+ CLUSTER(table) ) */ HASH 지정된테이블에대한해쉬스캔 /*+ HASH(table) ) */ INDEX_ASC 내림차순 ( 순차적 ) 으로인덱스를스캔 /*+ INDEX_ASC(table index) */ INDEX_DESC 오름차순 ( 역순 ) 으로인덱스를스캔 /*+ INDEX_DESC(table index) */ 18
비용기준최적화 (HINT) 기 능 설 명 사용예 INDEX_FFS 주어진인덱스를 FAST FULL SCAN /*+ INDEX_FFS(table index) */ AND_EQUALS RDERED 여러개의인덱스를머지하여사용 (2~5 개 ) FRM 절에기술된순서대로테이블을조인 /*+ AND_EQUALS (table index1,..., index5) */ /*+ RDERED(table1 table2 table3) */ USE_NL 먼저특정테이블의행을액세스하여그값에해당하는다른테이블의행을찾는작업을해당범위까지실행하는조인 /*+ USE_NL(table1 table2) */ USE_MERGE 먼저각각의테이블의처리범위를스캔하여정열한후서로머지하면서조인 /*+ USE_MERGE(table1 table2) */ USE_HASH CACHE 주어진테이블에대하여 HASH JIN 하도록함 NCACHE, Full table scan 시 block 을 LRU 의 MRU 위치에둔다 /*+ USE_HASH(table1 table2) */ /*+ USE_CACHE(table) ) */ 19
버전별 HINT 비교 H I N T 7.x 8.0 8i 9i ptimization Approaches and Goals ALL_RWS FIRST_RWS(n) CHSE RULE Join rders RDERED STAR Parallel Execution PARALLEL NPARALLEL PQ_DISTRIBUTE PARALLEL_INDEX NPARALLEL_INDEX 20
버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Access Methods FULL RWID CLUSTER HASH INDEX INDEX_ASC INDEX_CMBINE INDEX_JIN INDEX_DESC INDEX_FFS N_INDEX AND_EQUAL 21
버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Query Transformations USE_CNCAT N_EXPAND REWRITE NREWRITE MERGE N_MERGE STAR_TRANSFRMATIN FACT N_FACT 22
버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Join perations USE_NL USE_MERGE USE_HASH DRIVING_SITE LEADING MERGE_AJ MERGE_SJ HASH_AJ HASH_SJ NL_AJ NL_SJ 23
버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Additional Hints APPEND NAPPEND CACHE NCACHE UNNEST N_UNNEST PUSH_(JIN_)PRED N_PUSH_(JIN_)PRED PUSH_SUBQ RDERED_PREDICATES CURSR_SHARING_EXACT 24
다양한 PTIMIZING 전략 SQL 실행계획 부분범위처리 인덱스의활용및액세스효율의향상 클러스터링의활용 IT, Bitmap, FBI(Function Based Index) JIN 의최적화 다중처리 (ARRAY PRCESSING) I/ 의분산및 SQL AREA 튜닝 적절한데이터타입의사용 Dynamic vs. Static SQL 의실행절차 HINT 병렬처리 파티셔닝 3-Tier 환경에서의성능개선이슈의이해 25
SQL 보다적절한설계가먼저다! 복잡한관계 단순한관계 무엇을어떻게이용할것인가? ERD 을을그릴수있는사람은수 정보의단절을어떻게막을것인가? 많이있지만데이터를모델링할수있는사람은 융통성과통합성을어떻게유지할것인가? 많지않다!!!!!! 관계형데이터베이스특성을어떻게반영할것인가? 단순명료하면서도원하는수행속도를보장받을수있는가? 26
데이터모델링의중요성 데이터는기업의소중한자산이며모든정보의기반 무엇이올바른선택인가? 모호한개체집합및관계정의 Process 중심의개체집합데이터중복및일관성문제완벽하지않은이력관리 구체적, 입체적인개체집합정의 Data 중심의시스템기반구축미래를위한정제된데이터축적완벽한이력관리 업무요구를 PG 변경으로수용유지보수비용증가시스템이재개발되어도문제해결불가정보가축적될수록쓰레기만증가 업무요구를 Data 변경으로수용유지보수비용감소시스템이재개발되어도 PG 만변경정보가축적될수록가치가증가 27
개발자의인식전환이관건 절차형처리방식 설계는대충해도프로그램에서특별처리하면된다. 모름지기모든처리과정은내가직접만들어야한다. 로직구사를잘하는사람이우수한사람이다. 데이터는한건씩처리할수밖에없다. 데이터는출력해서보는것이다. 100 만건이면많은데이터다. 같은데이터를동시에사용하면경합이생긴다. 경력이많아지면관리자가되어야한다. 비절차형처리방식 ( 집합개념 ) 잘못된설계는시스템에엄청난영향을미친다. 요구만잘하면좋은처리과정은저절로만들어진다. DBMS 가좋은역할을하도록전략적인 FACTR 를부여해야... 데이터는여러건을동시에처리할수있다. 가능한온라인프로그램이되도록해야한다. 1000 만건이하라면결코많은데이터가아니다. 같은데이터를동시에사용하면효율이더좋아진다. 경력이많은관리자는많아도경력이많은전문가는흔치않다. 28
3-Tier 환경에서의성능개선이슈 누가어떤부분을처리하도록할것인가? 호출횟수를어떻게줄일것인가? 세션을계속유지할것인가? 3-Tier Architecture Service Call DBMS Call Finance Human Resources Sales User Presentation Layer Business Layer AP Server or WAS Data Layer Customer Database 29