ALTIBASE HDB TM Administration Performance Tuning Guide Release 6.5.1 (January 2, 2017)
------------------ ALTIBASE Administration Performance Tunning Guide Release 6.5.1 Copyright c 2001~2017 ALTIBASE Corp. All Rights Reserved. 본문서의저작권은 알티베이스에있습니다. 이문서에대하여당사의동의없이무단으로복제또는전용할수없습니다. 알티베이스 152-790 서울시구로구구로동 182-13 대륭포스트타워 Ⅱ 10 층전화 : 02-2082-1114 팩스 : 02-2082-1099 고객서비스포털 : http://support.altibase.com homepage: http://www.altibase.com -----------------------------------------------------------
목차 서문... 7 이매뉴얼에대하여... 8 대상사용자... 8 소프트웨어환경... 8 이매뉴얼의구성... 8 문서화규칙... 9 관련자료... 11 온라인매뉴얼... 11 Altibase 는여러분의의견을환영합니다.... 11 1. 성능튜닝소개... 13 성능튜닝개요... 14 데이터베이스서버튜닝... 15 성능관련프로퍼티... 15 성능관련통계정보... 18 운영체제리소스관리... 18 SQL 튜닝... 20 성능관련이슈... 20 SQL 튜닝방법론... 22 질의처리 (Query Processing) 개요... 24 2. HDB 서버튜닝... 29 로그파일... 30 체크포인트... 31 버퍼... 32 서비스쓰레드... 33 가비지콜렉터... 34 SQL Plan Cache... 36 CPU 사용률... 37 3. 쿼리옵티마이저... 39 쿼리옵티마이저개요... 40 옵티마이저의작업... 40 목차 3
옵티마이저에영향을미치는요소... 41 쿼리변환... 42 공통표현식제거 (Common Subexpression Elimination)... 42 Constant Filter 우선처리... 42 View Merging... 43 Subquery Unnesting... 44 조건절 Pushdown (Predicate Pushdown)... 44 조건절이행 (Transitive Predicate Generation)... 47 View Materialization 기법... 47 논리적실행계획생성... 49 정규화 (Normalization)... 49 비용계산 (Cost Estimation)... 51 액세스방법... 51 조인순서... 63 조인방법... 66 물리적실행계획생성... 71 저장노드의특징... 72 옵티마이저관련프로퍼티... 75 4. EXPLAIN PLAN 사용하기... 77 EXPLAIN PLAN 의개요... 78 Plan Tree 의이해... 78 Plan Tree 출력... 79 Plan Tree 읽기... 82 실행계획트리의해석... 83 Plan Tree 활용... 85 실행노드... 86 AGGREGATION... 86 ANTI-OUTER-JOIN... 88 BUG-UNION... 89 CONCATENATION... 91 CONNECT BY... 92 COUNT... 93 DISTINCT... 94 FILTER... 97 FULL-OUTER-JOIN... 99 GROUP-AGGREGATION... 101 GROUPING... 102 HASH... 105 JOIN... 107 LEFT-OUTER-JOIN... 112 4 Performance Tuning Guide
LIMIT-SORT... 114 MATERIALIZATION... 116 MERGE-JOIN... 117 PARALLEL-QUEUE... 119 PARALLEL-SCAN-COORDINATOR... 122 PARTITION-COORDINATOR... 124 PROJECT... 126 SCAN... 127 VIEW... 133 VIEW-SCAN... 136 SET-DIFFERENCE... 138 SET-INTERSECT... 140 SORT... 142 STORE... 145 5. 옵티마이저와통계정보... 147 통계정보의개요... 148 통계정보관리... 149 통계정보조회... 149 주의사항... 149 6. SQL 힌트... 151 힌트의개요... 152 힌트처리정책... 152 힌트사용법... 152 힌트의종류... 154 최적화적용기준... 154 정규화형태... 154 조인순서... 155 조인방법... 155 중간결과저장매체... 156 해시버킷크기... 157 그룹처리방법... 157 중복제거처리방법... 157 뷰최적화방법... 158 액세스방법... 158 쿼리변환... 159 플랜캐시관련힌트... 160 Direct-Path INSERT 관련힌트... 161 단순쿼리처리방법... 161 병렬질의처리... 161 목차 5
산술연산관련힌트... 161 7. SQL Plan Cache... 163 SQL Plan Cache 의개요... 164 ALTIBASE HDB SQL Plan Cache 구조... 164 SQL Plan Cache 의특징... 165 SQL Plan Cache 관리... 166 SQL Plan Cache 정책... 166 SQL Plan Cache 제어문... 166 SQL Plan Cache 사용구문... 167 SQL Plan Cache 관련프로퍼티... 167 SQL Plan Cache 관련성능뷰... 168 제약사항... 168 예제... 168 찾아보기... 171 6 Performance Tuning Guide
서문 서문 7
이매뉴얼에대하여 이매뉴얼은 ALTIBASE HDB 를효과적으로사용하기위한튜닝 방법에대하여설명한다. 대상사용자 이매뉴얼은다음과같은 ALTIBASE HDB 사용자를대상으로작성되었다. 데이터베이스관리자 시스템관리자 성능관리자다음과같은배경지식을가지고이매뉴얼을읽는것이좋다. 컴퓨터, 운영체제및운영체제유틸리티운용에필요한기본지식 관계형데이터베이스사용경험또는데이터베이스개념에대한이해 데이터베이스서버관리, 운영체제관리또는네트워크관리경험 소프트웨어환경 이매뉴얼은데이터베이스서버로 ALTIBASE HDB 버전 6.5.1 을 사용한다는가정하에작성되었다. 이매뉴얼의구성 이매뉴얼은다음과같이구성되어있다. 제 1 장성능튜닝소개이장은 ALTIBASE HDB 성능튜닝에대한개략적인정보를제공한다. 제 2 장 HDB 서버튜닝이장은 HDB 서버를운영할때고려해야하는요소를설명한다. 제 3 장쿼리옵티마이저이장은옵티마이저의구조를살펴보고, 질의문이최적화되기 8 Performance Tuning Guide
위해어떤과정을거치는지설명한다. 제 4 장 Explain Plan 사용하기이장은 ALTIBASE HDB 서버가최적화된질의를실행하기위해수행하는접근경로를나타내는 EXPLAIN PLAN 에대해설명한다. 제 5 장옵티마이저와통계정보이장은쿼리를최적화하는데있어통계정보가왜중요한지를알아보고, 사용자가통계정보를수집하고설정하는방법을설명한다. 제 6 장 SQL 힌트이장은사용자가직접 SQL 문의실행계획을변경할수있는 SQL 힌트에대해설명한다. 제 7 장 SQL Plan Cache 이장은 ALTIBASE HDB 의 SQL Plan Cache 기능에대한개념및특징에대해설명한다. 문서화규칙 이절에서는이매뉴얼에서사용하는규칙에대해설명한다. 이규칙을이해하면이매뉴얼과설명서세트의다른매뉴얼에서정보를쉽게찾을수있다. 여기서설명하는규칙은다음과같다. 구문다이어그램 샘플코드규칙 구문다이어그램 이매뉴얼에서는다음구성요소로구축된다이어그램을사용하여, 명령문의구문을설명한다. 구성요소 예약어 SELECT 의미명령문이시작한다. 완전한명령문이아닌구문요소는화살표로시작한다. 명령문이다음라인에계속된다. 완전한명령문이아닌구문요소는이기호로종료한다. 명령문이이전라인으로부터계속된다. 완전한명령문이아닌구문요소는이기호로시작한다. ; 명령문이종료한다. 필수항목 서문 9
선택적항목 NOT ADD 선택사항이있는필수항목. 한항목만제공해야한다. DROP 선택사항이있는선택적항목. ASC DESC ASC 선택적항목. 여러항목이허용된다. 각반복앞부분에 콤마가와야한다. DESC, 샘플코드규칙 코드예제는 SQL, Stored Procedure, isql, 또는다른명령라인 구문들을예를들어설명한다. 아래테이블은코드예제에서사용된인쇄규칙에대해설명한다. 규칙 의미 예제 [ ] 선택항목을표시 VARCHAR [(size)] [[FIXED ] VARIABLE] { } 필수항목표시. 반드시하나이상을선택해야되는표시 { ENABLE DISABLE COMPILE } 선택또는필수항목표시의인자구분표시 { ENABLE DISABLE COMPILE } [ ENABLE DISABLE COMPILE ]... 그이전인자의반복표시예제코드들의생략되는것을표시 SQL> SELECT ename FROM employee; ENAME ------------------------ SWNO HJNO HSCHOI.. 10 Performance Tuning Guide
20 rows selected. 그밖의기호 위에서보여진기호이외에기호들 EXEC :p1 := 1; acc NUMBER(11,2); 기울임꼴 구문요소에서사용자가지정해야하는변수, 특수한값을제공해야만하는위치지정자 SELECT * FROM table_name; CONNECT userid/password; 소문자 사용자가제공하는프로그램의요소들, 예를들어테이블이름, 칼럼이름, 파일이 SELECT ename FROM employee; 름등 대문자 시스템에서제공하는요소들또는구문에나타나는키워드 DESC SYSTEM_.SYS_INDICES_; 관련자료 자세한정보를위하여다음문서목록을참조하기바란다. Installation Guide Getting Started Guide SQL Reference Stored Procedures Manual isql User s Manual Utilities Manual Error Message Reference 온라인매뉴얼 Altibase 고객서비스포털 (http://support.altibase.com) 에서국문및영문매뉴얼 (PDF, HTML) 을받을수있다. Altibase 는여러분의의견을환영합니다. 이매뉴얼에대한여러분의의견을보내주시기바랍니다. 사용자의의견은다음버전의매뉴얼을작성하는데많은도움이됩니다. 보내실때에는아래내용과함께고객서비스포털 (http://support.altibase.com/kr/) 로보내주시기바랍니다. 서문 11
사용중인매뉴얼의이름과버전 매뉴얼에대한의견 사용자의성함, 주소, 전화번호이외에도 Altibase 기술지원설명서의오류와누락된부분및기타기술적인문제들에대해서이주소로보내주시면정성껏처리하겠습니다. 또한, 기술적인부분과관련하여즉각적인도움이필요한경우에도고객서비스포털을통해서비스를요청하시기바랍니다. 여러분의의견에항상감사드립니다. 12 Performance Tuning Guide
1. 성능튜닝소개 이장은 ALTIBASE HDB 성능튜닝에대한개략적인정보를제공한다. 성능튜닝개요 데이터베이스서버튜닝 SQL 튜닝 성능튜닝소개 13
성능튜닝개요 데이터베이스시스템튜닝이란성능향상을목표로수행하는일련의작업을의미한다. 성능향상을좀더구체적으로표현하면아래와같다. 데이터베이스서버의전체처리량 (throughput) 최대화 쿼리의응답시간최소화 리소스사용율최소화이문서는성능향상을위해사용자가취할수있는 ALTIBASE HDB 의튜닝에관련된정보를아래와같이분류하여제공한다. 데이터베이스서버튜닝 SQL 튜닝 14 Performance Tuning Guide
데이터베이스서버튜닝 아래는 ALTIBASE HDB 서버튜닝에관련된주요소들이다. 성능관련프로퍼티 성능관련통계정보 운영체제리소스관리 메모리구성과사용 I/O 구성및설계사용자는성능문제가발생할수있는병목현상을방지하기위해데이터베이스의초기설계에주의를기울여야한다. 또한서버운영에있어아래의사항도함께고려해야한다. 체크포인트 I/O 로그파일쓰기 (Logfile Writing) 서비스쓰레드병목 메모리에이저 버퍼 SQL Plan Cache 서버운영에관련된튜닝요소는다음장에서자세히설명한다. 성능관련프로퍼티 성능최적화를위해 ALTIBASE HDB 프로퍼티파일을사용목적에맞게수정해야한다. 다음은성능과관련된프로퍼티의목록이다. 각프로퍼티에대한상세한설명은 General Reference 2 장의 " 성능관련프로퍼티 " 절을참조한다. AGER_WAIT_MAXIMUM AGER_WAIT_MINIMUM BUFFER_VICTIM_SEARCH_INTERVAL BUFFER_VICTIM_SEARCH_PCT BULKIO_PAGE_COUNT_FOR_DIRECT_PATH_INSERT CHECKPOINT_BULK_SYNC_PAGE_COUNT CHECKPOINT_BULK_WRITE_PAGE_COUNT CHECKPOINT_BULK_WRITE_SLEEP_SEC CHECKPOINT_BULK_WRITE_SLEEP_USEC CHECKPOINT_FLUSH_COUNT CHECKPOINT_FLUSH_MAX_GAP CHECKPOINT_FLUSH_MAX_WAIT_SEC 성능튜닝소개 15
CM_BUFFER_MAX_PENDING_LIST CM_DISPATCHER_SOCK_POLL_TYPE DATABASE_IO_TYPE DATAFILE_WRITE_UNIT_SIZE DB_FILE_MULTIPAGE_READ_COUNT DEDICATED_THREAD_CHECK_INTERVAL DEDICATED_THREAD_INIT_COUNT DEDICATED_THREAD_MAX_COUNT DEDICATED_THREAD_MODE DEFAULT_FLUSHER_WAIT_SEC DIRECT_IO_ENABLED DISK_INDEX_BUILD_MERGE_PAGE_COUNT EXECUTE_STMT_MEMORY_MAXIMUM EXECUTOR_FAST_SIMPLE_QUERY FAST_START_IO_TARGET FAST_START_LOGFILE_TARGET HASH_AREA_SIZE HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE HASH_JOIN_MEM_TEMP_AUTO_BUCKET_COUNT_DISABLE HIGH_FLUSH_PCT HOT_LIST_PCT HOT_TOUCH_CNT INDEX_BUILD_THREAD_COUNT INDEX_INITRANS INDEX_MAXTRANS INSPECTION_LARGE_HEAP_THRESHOLD LFG_GROUP_COMMIT_INTERVAL_USEC LFG_GROUP_COMMIT_RETRY_USEC LFG_GROUP_COMMIT_UPDATE_TX_COUNT LOB_CACHE_THRESHOLD LOCK_ESCALATION_MEMORY_SIZE LOG_IO_TYPE LOW_FLUSH_PCT LOW_PREPARE_PCT MAX_FLUSHER_WAIT_SEC MAX_THREAD_COUNT MULTIPLEXING_CHECK_INTERVAL MULTIPLEXING_MAX_THREAD_COUNT MULTIPLEXING_THREAD_COUNT NORMALFORM_MAXIMUM OPTIMIZER_FEATURE_ENABLE 16 Performance Tuning Guide
OPTIMIZER_MODE OPTIMIZER_UNNEST_AGGREGATION_SUBQUERY OPTIMIZER_UNNEST_COMPLEX_SUBQUERY OPTIMIZER_UNNEST_SUBQUERY OUTER_JOIN_OPERATOR_TRANSFORM_ENABLE PARALLEL_LOAD_FACTOR PARALLEL_QUERY_THREAD_MAX PARALLEL_QUERY_QUEUE_SIZE PREPARE_STMT_MEMORY_MAXIMUM QUERY_REWRITE_ENABLE REFINE_PAGE_COUNT SHM_PAGE_COUNT_PER_KEY SECONDARY_BUFFER_ENABLE SECONDARY_BUFFER_FILE_DIRECTORY SECONDARY_BUFFER_FLUSHER_CNT SECONDARY_BUFFER_SIZE SECONDARY_BUFFER_TYPE SORT_AREA_SIZE SQL_PLAN_CACHE_BUCKET_CNT SQL_PLAN_CACHE_HOT_REGION_LRU_RATIO SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT SQL_PLAN_CACHE_SIZE STATEMENT_LIST_PARTIAL_SCAN_COUNT TABLE_INITRANS TABLE_LOCK_ENABLE TABLE_MAXTRANS TABLESPACE_LOCK_ENABLE TEMP_MAX_PAGE_COUNT TEMP_STATS_WATCH_TIME THREAD_CPU_AFFINITY TIMED_STATISTICS TIMER_RUNNING_LEVEL TIMER_THREAD_RESOLUTION TOTAL_WA_SIZE TOUCH_TIME_INTERVAL TRANSACTION_SEGMENT_COUNT TRX_UPDATE_MAX_LOGSIZE 성능튜닝소개 17
성능관련통계정보 서버의쓰레드들이작업을계속하기위해서어떤이벤트에대해대기해야할때대기이벤트관련통계치의값이증가된다. 특정대기이벤트에대한통계값이높다면해당대기이벤트가성능에영향을미치고있는원인일수도있다. 대기이벤트에관련된성능뷰는아래와같다. 각성능뷰에대한상세한설명은 General Reference 3 장을참고한다. V$SESSION_EVENT V$SESSION_WAIT V$SESSION_WAIT_CLASS V$SYSTEM_EVENT V$SYSTEM_WAIT_CLASS 아래는 ALTIBASE HDB 의여러가지통계정보 ( 메모리사용량, 프로세스상태, 세션이나버퍼풀관련통계정보등 ) 를저장하는성능뷰이다. 각성능뷰에대한상세한설명은 General Reference 3 장을참고한다. V$BUFFPAGEINFO V$BUFFPOOL_STAT V$DBMS_STATS V$FLUSHER V$FLUSHINFO V$LATCH V$LOCK_WAIT V$MEMSTAT V$SERVICE_THREAD V$SESSTAT V$SYSSTAT V$UNDO_BUFF_STAT 운영체제리소스관리 아래와같이운영체제가제공하는명령어와모니터링툴을사용해서 ALTIBASE HDB 서버의리소스사용량을확인할수있다. AIX HPUX LINUX Performance Monitor top top top topas glance 18 Performance Tuning Guide
nmon System Activity Reporter sar sar sar Virtual Memory Statistics vmstat vmstat vmstat I/O Statistics iostat iostat iostat Error Log errpt dmesg dmesg 성능튜닝소개 19
SQL 튜닝 최적화되지않은쿼리는불필요한데이터에대한액세스나리소스사용을증가시켜데이터베이스서버의성능을전반적으로떨어뜨린다. SQL 튜닝은이러한요인을제거하여쿼리를최적화하는일련의작업을의미하며, 다음의작업들이포함된다. SQL 문재작성 데이터베이스스키마재설계 ALTIBASE HDB 프로퍼티조정 운영체제커널파라미터조정 SQL 튜닝방법중 SQL 문의재작성과데이터베이스스키마재설계는 ALTIBASE HDB 의쿼리처리방법과제한사항등에의해쿼리성능이영향을받기때문에 ALTIBASE HDB 의쿼리처리방법에대한심도깊은이해가요구된다. 또한, 메모리테이블과디스크테이블을모두사용할수있기때문에저장매체의특성에따라서쿼리처리에어떻게영향을미치는지에대한이해가필요하다. 본절에서는간략하게성능관련이슈를살펴보고 SQL 튜닝을위해필요한기본개념에대하여설명한다. 성능관련이슈 실행계획 클라이언트에서 SQL 문의수행을요구하면질의처리기는구문검사, 정당성검사, 최적화과정을거쳐실행절차를정의한실행계획을생성한다. 실행계획은트리구조로구축되기때문에본매뉴얼에서실행계획트리또는 plan tree 라고호칭되기도한다. 쿼리옵티마이저가가장효율적이라는판단에의해결정된실행계획은실제로 SQL 문의실행시간에큰영향을끼친다. SQL Plan Cache ALTIBASE HDB 에서는세션간에 SQL 구문의실행계획이공유될수있다. 이는 SQL 구문수행시마다매번실행계획을만드는비용을줄인다. 이기능을이용하려면 SQL Plan Cache 와관련된 SQL_PLAN_CACHE_SIZE 프로퍼티의값을적절히설정해야한다. 20 Performance Tuning Guide
자세한설명은 SQL Plan Cache 장을참고하기바란다. 데이터베이스스키마와데이터용량 우선적으로응용프로그램의특성및자원활용의효율에따라메모리테이블및디스크테이블의사용을적절히고려하여야한다. 일반적으로자주사용되는데이터는메모리테이블에, 접근빈도가낮은데이터는디스크테이블에저장하여관리하는것이유리하다. 응용프로그램에서사용되는 SQL 문의종류를고려해각 SQL 문별로테이블에접근하는회수, 접근하는레코드수및디스크페이지수가최소비용이되도록테이블스키마의구성과인덱스구성에유의해야한다. 또한, 단순한 SQL 문또는레코드건수가많은테이블에대해서는술어 (predicate) 내칼럼값을비교하는데발생하는비용이성능에큰영향을미친다. 그러므로데이터변환과비교비용을최소화할수있는적합한데이터타입을선정하는것이중요하다. 따라서가능한적은수의레코드에접근하도록 SQL 문을작성하여비교하는회수를줄이고, 비교연산시데이터변환이일어나지않도록칼럼의데이터타입과비교되는값의데이터타입을잘선정해야한다. 응용프로그램로직 ( 테이블접근순서 ) 만약여러클라이언트가 ALTIBASE HDB 데이터베이스에연결된경우, 각클라이언트의테이블접근순서가성능에영향을미칠수도있다. 한트랜잭션내에서 DML 문을여러개사용하여여러테이블에접근하는경우, 응용프로그램에서테이블에접근하는순서등을잘못설계했다면 lock 을획득하기위해대기하는시간 (lock waiting time) 으로인해전체응용프로그램성능이저하될수도있다. 따라서클라이언트응용프로그램의로직설계에유의해야한다. 시스템리소스한 SQL문의처리시성능에영향을미치는시스템리소스는가용한메모리의크기, 메모리버퍼의크기, 디스크성능과 CPU 성능이다. 검색질의의성능은물론메모리테이블만을사용할경우디스크성능에영향을받지않는다. 그러나, 디스크테이블을검색할경우디스크성능및가용한메모리버퍼의크기에따라질의성능이영향을받게된다. 이러한점은메모리테이블을검색할경우일정한질의응답시간을기대할수있는반면, 디스크테이블을검색할경우질 성능튜닝소개 21
의수행시점의환경에따라많은성능차이를보이게되는원인이된다. ORDER BY 절또는 GROUP BY 절등이포함된질의를처리할때, 질의처리기는 sorting 기법이나 hashing 기법을사용하는데이처리의중간결과를저장하기위해메모리임시공간또는디스크임시공간을사용하게된다. 중간결과가메모리임시공간에저장되는경우, 사용되는메모리는데이터베이스영역에잡혀있는메모리가아니다. 그러므로메모리테이블의크기가크다면많은메모리가필요할수있으므로질의처리시메모리스와핑으로인한성능저하가생기지않도록주의해야한다. 중간결과가디스크임시공간에저장되는경우, 사용되는자원은디스크임시테이블스페이스의디스크영역과메모리버퍼영역이다. 가용한메모리버퍼영역의크기에따라성능에커다란영향을미치게된다. 또한질의처리작업은연산자를처리하기위한작업이많아 CPU 를많이사용한다. 그러므로 CPU 점유율과 CPU 성능도질의성능에영향을많이미친다. SQL 튜닝방법론 쿼리의응답시간을최소화하기위해 isql 을사용해서사용자가수행할수있는기초적인튜닝절차는아래와같다. 1. Profiling 기능을사용해서응답시간이오래걸리는질의문을찾는다. 2. isql 에서 Explain Plan 을볼수있도록설정한다. 3. isql 에서쿼리수행에걸리는시간을볼수있도록 Set Timing On 을설정한다. 4. 쿼리를실행해서실행계획을확인한다. 5. SQL 문변경, 인덱스설정, 힌트등을사용하여쿼리의응답시간을감소시킨다. 이절에서는 SQL 튜닝을하기위해사용할수있는도구와방법에대해간단히설명한다. 프로파일링 (Profiling) 프로파일링은사용자가요청한 SQL 문에대해아래와같은정보를 22 Performance Tuning Guide
수집하는기능이다. 쿼리를요청한클라이언트 실행시각 쿼리문 수행소요시간 사용된인덱스 버퍼 / 디스크접근비용 실행계획프로파일링기능은 'ALTER SYSTEM SET..' 구문으로프로퍼티 QUERY_PROF_FLAG 의값을설정하여사용할수있다. 이프로퍼티에설정하는값에따라수집되는정보가다르다. 자세한정보는 General Reference 의 2 장 ALTIBASE HDB 프로퍼티를참조하기바란다. SQL 성능측정방법 APRE, ODBC, 또는 JDBC 등을사용한응용프로그램개발시, 응용프로그램내에서시간을구하는함수를이용하여쿼리처리에소요되는시간을측정할수있다. 또한 isql 에서다음 isql 명령어를이용해쿼리수행시간을측정할수도있다. SET TIMING ON; 메모리테이블의경우 isql 로질의를반복수행시거의유사한성능을얻을수있다. 하지만디스크테이블의경우는반복수행시버퍼교체가적게발생하여최초수행때보다다음수행시보다나은성능을얻게된다. 이는디스크테이블에대하여동시수행되는질의들이많을경우버퍼교체로인해성능의일정함을보장할수없음을의미한다. 그러나일반적으로 isql 상에서 SQL 문을튜닝하여성능을향상시킨 SQL 문의경우실제응용프로그램에서도같은비율의성능향상을볼수있다. 실행계획분석 SELECT, INSERT, UPDATE, DELETE 등의 SQL DML 문의실행계획은 isql 상에서만확인이가능하다. DELETE 문, UPDATE 문, 및 MOVE 문은 SELECT 문처리와같은최적화과정을거치므로내부적으로동일한실행계획이생성된다. INSERT 문의경우 INSERT INTO SELECT 의 SELECT 문부분에대한 성능튜닝소개 23
실행계획만확인할수있다. 일반적으로실행계획에서확인해야할내용은다음과같다. 자세한내용은다음절에서살펴본다. 액세스방법이효율적인가? 조인순서및방법은바람직한가? 적절한데이터타입및연산을사용하는가? 불필요한 hashing 및 sorting 이수행되지않는가? 실행계획을분석해서성능저하원인을파악한후, 이에대한적절한조치를취함으로서성능을개선할수있다. SQL 튜닝방법 다음은 SQL 튜닝의기본적인방법이다. 각각의방법에대해서는본매뉴얼의 " 쿼리옵티마이저 " 장과 "SQL 힌트 " 장을참고한다. 효율적인인덱스를사용하는지확인하라. 필요하다면인덱스를추가하라. 조인순서와조인방법을최적화하라. sorting 과 hashing 작업을최소화하라. 힌트를활용하라. 질의처리 (Query Processing) 개요 SQL 문의튜닝시항상모든응용프로그램에적용해최적의성능을발휘하는절대적인규칙을제시하기란쉽지않다. 데이터베이스의설계또는응용프로그램의비지니스로직에따라 SQL 튜닝방법은매우다양하다. 최적의 SQL 문을작성하기위해서는 ALTIBASE HDB 의질의처리에대한이해가필수적이다. 이절에서는하나의 SQL 문이어떠한과정을거쳐처리되는지, 성능에영향을미치는요소는어떠한것들이있는지에대해설명한다. 질의처리과정 DBMS 에서 SQL 문처리를담당하는모듈을질의처리기 (Query Processor) 라한다. 질의처리기는사용자가수행을요구한 SQL 문에대해정당성을검사한후데이터베이스에접근하는최적의접근순서와방법을결정하고, 이에따라조건에맞는레코드를검색한후필요한연산을수행하여마지막으로클라이언트에게 24 Performance Tuning Guide
처리한값을돌려준다. 질의처리수행과정을개략적으로도식화하면다음과같다. Client Prepare Server Parsing Validation Optimization Bind Binding Execute Fetch Execution Database [ 그림 1-1] Query Processing 순서 각단계별수행작업은다음과같다. 1. 구문분석 (Parsing): SQL 문의문법을검사 (syntax 검사 ) 하고구문분석정보를저장하는파스트리 (Parse Tree) 를생성한다. 2. 정당성검사 (Validation): SQL 문의의미상정당성을검사 (semantic 검사 ) 하고메타테이블을검색하여파스트리를확장한다. 3. 최적화 (Optimization): 주어진파스트리에기반하여다양한통계정보와접근비용계산을통해최적화된실행계획을생성한다. 4. 바인딩 (Binding): 호스트변수값 (host variable value) 을생성된실행계획에연결한다. 5. 실행 (Execution): 실행계획트리에따라 SQL 문을실행한다. 질의를튜닝하기위해 ALTIBASE HDB 의질의최적화와질의실행과정을이해하는것은매우중요하다. 본문서에서는이를기반으로한질의튜닝방법에대하여자세히알아본다. 성능튜닝소개 25
메모리테이블과디스크테이블 ALTIBASE HDB (Hybrid Database) 는메모리테이블과디스크테이블을모두지원하는데이터베이스관리시스템이다. 따라서, 메모리테이블과디스크테이블에대한질의처리방법의차이를이해하는것은질의튜닝에있어큰도움이된다. 메모리테이블과디스크테이블을위한질의처리기의기본적인차이는다음과같다. Query Processor Executor Optimizer 참조 Item Memory Table Disk Table Object identifier Pointer OID(RID) Buffer management N/A Limited buffer Join methods One-pass algorithms Multi-pass algorithms Main cost CPU Disk Index selection Minimize record access Minimize disk I/O Cost factor T(R), V(R.a), etc + B(R), M T(R): Table R의레코드개수 V(R.a): R.a 칼럼에서서로다른값의개수 (Cardinality) B(R): Table R의디스크페이지개수 M: 가용한메모리버퍼개수 질의처리기는크게옵티마이저 (Optimizer) 와실행기 (Executor) 로구분된다. 옵티마이저는비용계산을통해실행계획트리를생성한다. 실행기는실행계획트리의각노드에따라실제로구문을실행한다. ALTIBASE HDB의실행기와옵티마이저는저장매체의차이를충분히반영하는반면, 이에대한별도의구분없이실행계획을생성하고, 질의처리시에그특성만을반영하여수행할수있도록설계되었다. 실행기 (Executor) 실행기는테이블의저장매체에따라위의표에서와같이기본적인개념및그동작에있어차이점이있다. 먼저레코드를구별하는 객체식별자 (Object identifier) 는메모리테이블의경우포인터이며디스크테이블의경우 OID(RID) 와같은특정디스크주소로변환할수있는식별자이다. 이러한차이는레코드접근에있어메모리테이블은직접접근이가능한반면디스크테이블은주소변환에의한접근이필요함을의미하다. 26 Performance Tuning Guide
메모리테이블에대한질의처리시에는버퍼를사용하지않아이에대한고려가필요없다. 반면, 디스크테이블에대한질의처리는제한된메모리버퍼내에서이루어지기때문에버퍼에원하는레코드가없을경우 (Buffer miss) 디스크 I/O 를유발하는버퍼교체 (Buffer replacement) 가수반된다. 조인연산을수행하기위해서필요에따라중간결과가저장된다. 이때제한된메모리에이결과를모두적재할수있는가에따라 onepass 또는 multi-pass 알고리즘으로처리된다. One-pass 알고리즘의경우가용메모리임시공간에중간결과를모두적재할수있을때사용되는반면, multi-pass 알고리즘은중간결과를메모리상에모두적재할수없을때버퍼교체를최소화하기위해사용된다. 메모리테이블에대한조인의경우버퍼제한이없어 one-pass 알고리즘이사용될수있는반면, 디스크테이블의경우버퍼제한을고려하여 one-pass 또는 two-pass 알고리즘이사용된다. 위와같이실행기의처리방식은저장매체에따라근본적으로다르며, 성능에있어서도큰차이를보이게된다. 옵티마이저 (Optimizer) 쿼리옵티마이저는테이블이저장된저장매체에따라위의표와같이기본적인개념및그동작에있어차이점이있다. 옵티마이저는메모리테이블을조회할경우 CPU 비용을최소화할수있도록실행계획을생성한다. 반면, 디스크테이블을조회할경우디스크 I/O 를최소화할수있도록실행계획을생성한다. 즉, 저장매체에따라질의성능에가장큰영향을미치는자원사용을최소화할수있는실행계획을생성한다. 옵티마이저는테이블에접근하는방법 (Access method) 을선택할때, 메모리테이블의경우읽을레코드수를최소화할수있는인덱스를선택한다. 그러나디스크테이블은디스크 I/O 를최소화할수있는접근방법을선택한다. 이러한차이는메모리테이블의경우대부분인덱스를사용하는것이전체테이블스캔보다나은성능을보장하지만, 디스크테이블의경우데이터의분포에따라인덱스를사용하는것보다전체테이블스캔이오히려적은디스크 I/O 를발생시키기때문이다. 옵티마이저는비용계산을위한인자로다양한통계정보를사용한다. 메모리테이블에대한비용계산시에는테이블의레코드개수 [T(R)], 칼럼내의서로다른값의개수 [V(R.a)], 칼럼의최소값과최대값등의통계정보가사용된다. 그리고디스크테이블에대한비용계산시에는메모리테이블에대한비용계산방법외에도 성능튜닝소개 27
테이블이사용하고있는디스크페이지개수 [B(R)], 가용한메모리 버퍼페이지개수 [M] 등의통계정보가추가적으로이용된다. 28 Performance Tuning Guide
2. HDB 서버튜닝 이장은 HDB 서버를운영할때고려해야하는요소를설명한다. 로그파일 체크포인트 버퍼 서비스쓰레드 가비지콜렉터 SQL Plan Cache CPU 사용률 HDB 서버튜닝 29
로그파일 ALTIBASE HDB 는새로운로그파일이생성될때, 트랜잭션의응답시간이늦어지는것을방지하기위해로그파일을미리생성해둔다. 그러나여분의로그파일이부족한경우, 트랜잭션들이대기하는상황이발생하므로데이터베이스성능이전반적으로떨어진다. 아래는미리만들어둔로그파일의여분이부족해서로그파일이생성되기를기다린횟수를확인하는쿼리이다. SELECT lf_prepare_wait_count FROM v$lfg; 이값이크다면 PREPARE_LOG_FILE_COUNT 프로퍼티의값을더큰값으로설정하여로그파일매니저가충분한개수의로그파일을미리만들도록한다. 단, 이값이클수록서버가사용하는메모리량이증가하므로무조건크게하는것은바람직하지않다. PREPARE_LOG_FILE_COUNT 프로퍼티에대한설명은 General Reference 를참조한다. 30 Performance Tuning Guide
체크포인트 ALTIBASE HDB 는체크포인트를수행중디스크 I/O 부하로성능이 저하될수있다. $ALTIBASE_HOME/trc/altibase_sm.log 에 기록되는체크포인트트레이스메시지중아래메시지를출력하는 3, 4 단계에소요되는시간이길다면디스크 I/O 를모니터링할필요가 있다. [CHECKPOINT-step3] Flush Dirty Page(s) [CHECKPOINT-step4] sync Database File sar, iostat 등의커맨드를사용해서디스크 I/O 의병목을확인할수있다. $ sar 1 3 02:32:26 PM CPU %user %nice %system %iowait %idle 02:32:30 PM all 0.25 0.00 2.87 1.87 95.01 02:32:31 PM all 0.12 0.00 6.24 6.99 86.64 02:32:32 PM all 0.25 0.00 8.61 3.75 87.39 $ iostat 1 avg-cpu: %user %nice %sys %iowait %idle 0.13 0.00 8.76 3.88 87.23 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sdb1 2821.78 63.37 608388.12 64 614472 만약디스크 I/O 의병목이성능저하의원인이라면로그파일과데이터파일이저장되는디스크를분리해서문제를해결할수있다. 또는아래의체크포인트관련프로퍼티값을조절해서디스크 I/O 분산효과를기대할수도있다. 단이방법으로는로그파일의개수가증가하는부작용이생길수도있다. CHECKPOINT_BULK_WRITE_PAGE_COUNT 체크포인트시메모리의더티페이지들을여러번에나눠서디스크로저장할수있다. 이때이프로퍼티는한번에디스크로기록하는더티페이지의개수를설정한다. CHECKPOINT_BULK_WRITE_SLEEP_SEC CHECKPOINT_BULK_WRITE_SLEEP_USEC CHECKPOINT_BULK_WRITE_PAGE_COUNT 의값이 0 이아닐때더티페이지들을디스크로저장후대기하는시간 ( 초, 마이크로초 ) 을설정한다. CHECKPOINT_BULK_SYNC_PAGE_COUNT 체크포인트를할때메모리와디스크의데이터를몇개의페이지단위로일치시킬것인지를설정한다. 체크포인트에대한설명은 Administrator's Manual 을참고하기바란다. HDB 서버튜닝 31
버퍼 HDB 서버는디스크테이블의경우한정된메모리버퍼에데이터를 적재한후액세스한다. 따라서최적화되지않은쿼리로인해잦은 디스크 I/O 가발생하는경우성능저하를유발할수있다. 버퍼관련정보는 V$BUFFPOOL_STAT 성능뷰를조회하여파악할 수있다. SELECT hit_ratio 'HIT_RATIO(%)', victim_search_warp FROM v$buffpool_stat; V$BUFFPOOL_STAT 성능뷰의 HIT_RAIO 칼럼값이작으면 메모리버퍼대신에디스크로부터읽기 (read page) 횟수가많음을 나타낸다. 즉이값이작으면, HDB 서버가빠른질의처리를못하고 있음을보여준다. 또한 V$BUFFPOOL_STAT 성능뷰의 VICTIM_SEARCH_WARP 칼럼값이계속증가하고있다면, 플러셔의페이지플러쉬작업이 밀리고있음을나타낸다. 디스크테이블에서많은페이지를 액세스하는쿼리를튜닝하거나, BUFFER_AREA_SIZE 프로퍼티값을 늘려서이러한문제를해결할수있다. 통계정보는서버가시작한이후부터누적된값이므로특정기간 동안의값을알기위해서는모든칼럼에대해다음의방법으로 계산해야한다 : ( 현재의값 - 측정시작시점의값 ). 32 Performance Tuning Guide
서비스쓰레드 서버에서클라이언트의요청을받아질의를수행하는쓰레드를 서비스쓰레드라한다. ALTIBASE HDB 서버는아래의두가지 모드로서비스쓰레드를생성하고운용한다. 전용쓰레드모드 (Dedicated Thread Mode): 서버에다수의클라이언트가접속하여질의를수행하는경우, 서버는각클라이언트세션별로하나의서비스쓰레드를 생성하여질의를수행한다. 멀티플렉싱쓰레드모드 (Multiplexing Thread Mode): ALTIBASE HDB 서버는서버에최적화된개수의서비스쓰레드만 생성하고, 클라이언트세션들이이를공유한다. ALTIBASE HDB 는항상최적화된개수의서비스쓰레드를유지하기 위해동적으로서비스쓰레드를생성하거나삭제하도록설계되어 있다. 단, DEDICATED_THREAD_INIT_COUNT 또는 MULTIPLEXING_THREAD_COUNT 프로퍼티에서지정한최소 개수만큼의서비스쓰레드는항상유지한다. 클라이언트의동시연결수가아주많을경우새로운서비스쓰레드생성으로인해서버의성능이저하될수있다. V$SERVICE_THREAD 성능뷰를조회해서서비스쓰레드관련부하를확인할수있다. isql> SELECT rpad(type, 30), count(*) FROM V$SERVICE_THREAD GROUP BY type UNION ALL SELECT rpad(name, 30), value1 FROM V$PROPERTY WHERE name LIKE 'MULTIPLEXING%_THREAD_COUNT'; RPAD(TYPE, 30) COUNT ----------------------------------------------- SOCKET 44 IPC 10 MULTIPLEXING_THREAD_COUNT 8 MULTIPLEXING_MAX_THREAD_COUNT 1024 위결과와같이 SOCKET 항목의수치가 MULTIPLEXING_THREAD_COUNT 항목의수치보다크다면아래의조치를취할수있다. MULTIPLEXING_THREAD_COUNT 프로퍼티의값을더크게설정한다. HDB 서버에서처리시간이오래걸리는쿼리문을튜닝한다. HDB 서버튜닝 33
가비지콜렉터 다중버전동시성제어 (Multi-Version Concurrency Control, MVCC) 기법에서는한데이터에대해필요없는오래된데이터가생성될수있다. 가비지콜렉터 (garbage collector 또는 ager) 는불필요한오래된버전의데이터가차지하는메모리공간을회수하여재사용될수있도록조치를취함으로써메모리사용의효율성을높인다. 그러나 MVCC 동작은아래와같은문제를유발할수있으므로데이터베이스운영시주의가필요하다. 장시간수행되는트랜잭션에의한데이터베이스크기증가특정트랜잭션이너무오랫동안커밋되지않고수행되고있으면, 다른트랜잭션들의이전이미지들을읽을가능성이있다. 따라서가비지콜렉터가다른트랜잭션들의이전이미지정보들 ( 메모리테이블은이전버전, 디스크테이블은언두로그레코드정보 ) 과해당레코드의인덱스키들을삭제할수없게된다. 이에따라메모리테이블의크기가증가되고, 디스크의언두테이블스페이스크기가증가하게된다. 또한, 해당트랜잭션이롤백할때를대비해서로그파일도삭제하지못하므로, 로그파일이존재하는파일시스템이꽉찰가능성이있다. 동시수행트랜잭션과다로인한데이터베이스크기증가 ALTIBASE HDB 는 MVCC 로인해생성된이전이미지정보들의해제를가비지콜렉터에게맡기고있다. 만일동시에수행되는트랜잭션의수가해당시스템의 CPU 개수보다현저히많을경우에는가비지콜렉터가이전이미지정보들을삭제할여유를가지지못해데이터베이스크기가계속늘어날수있다. 대량의갱신연산으로인한데이터베이스의크기증가한번에대량의이전정보를생성해야하는연산 (bulk update) 들이자주수행되면, 메모리테이블은그크기가커지며, 디스크테이블은언두테이블스페이스가커질수있다. 이전이미지정보과다로인한성능저하위에열거한내용들로인하여이전이미지정보가데이터베이스내에너무많이남아있으면트랜잭션이실제로목적하는레코드를찾는데더많은비용이들어갈수있어서전체적으로성능이느려질소지가있다. 아래와같이 V$MEMGC 성능뷰를조회하여가비지콜렉터의메모리회수능력을확인할수있다. GCGAP 값은가비지콜렉터가삭제해야할오래된버전의양을의미한다. 34 Performance Tuning Guide
isql> SELECT gc_name, add_oid_cnt, gc_oid_cnt, add_oid_cnt - gc_oid_cnt gcgap FROM v$memgc; ADD_OID_CNT GC_OID_CNT GCGAP ---------------------------------------------- 113 113 0 아래의쿼리는가비지콜렉터가메모리회수를위해커밋하기를대기하고있는트랜잭션을조회한다. 이렇게조회되는트랜잭션에대해서는트랜잭션이수행하는쿼리문을튜닝할필요가있다. SELECT session_id, total_time, execute_time, tx_id, query FROM v$statement WHERE tx_id IN (SELECT id FROM v$transaction WHERE memory_view_scn = (SELECT minmemscnintxs FROM v$memgc LIMIT 1)) AND execute_flag = 1 ORDER BY 2 DESC; 가비지콜렉터가메모리회수작업을자주수행하도록 AGER_WAIT_MUNIMUM, AGER_WAIT_MAXIMUM 프로퍼티값을조절하는것도 MVCC 동작으로인해메모리사용량이과다하게되는것을방지하는하나의방법이다. HDB 서버튜닝 35
SQL Plan Cache 7 장 SQL Plan Cache 를참조한다. 36 Performance Tuning Guide
CPU 사용률 ALTIBASE HDB 서버내부의쓰레드별 CPU 사용률과 CPU 사용률이 높은쓰레드가어떤작업을하는지확인할수있다. OS 별로쓰레드의 CPU 사용률을확인하는명령어는다음과같다. OS Command AIX ps -mo THREAD -p altibase_pid HPUX glance +s +G LINUX 예제 ) ps -Lfm -p altibase_pid $ ps -mo THREAD -p 1003630 USER PID PPID TID ST CP PRI SC WCHAN F TT BND COMMAND snkim 1003630 1 - A 92 60 25 * 40001 - - /home2.. - - - 1405123 S 0 60 1-410400 - - - - - - 1687749 S 0 60 1 f100070f10019c40 8410400 - - - - - 3907689 S 92 106 1 f10001004af1b2c0 400000 - - CPU 사용률이높은쓰레드가어떤작업을하고있는지확인하는명령어는다음과같다. HP-UX, Linux pstack altibase_pid AIX procstack altibase_pid HDB 서버튜닝 37
3. 쿼리옵티마이저 이장은옵티마이저의구조를살펴보고질의문이최적화되기위해 어떤과정을거치는지설명한다. 쿼리옵티마이저 39
쿼리옵티마이저개요 쿼리옵티마이저 (Query optimizer) 는주어진 SQL 을분석하여가능한실행계획을작성하고, 이에대한비용을평가하여가장효율적인실행계획을선택한다. 질의성능의대부분이이과정에서결정되며, 복잡한질의일수록질의최적화과정의정확성에의해서성능이좌우된다. 옵티마이저의작업 질의문은최적화이전단계에서구문분석 (parsing) 과의미분석 (validation) 과정을거친다. 이과정에서파싱트리가생성되는데, 옵티마이저는이파싱트리로부터각종비용을평가하여효율적인실행계획트리 (plan tree) 을생성한다. 이러한과정을수행하는옵티마이저의구조는다음과같다. Parse Tree (Parsed Query) Query Rewriter Transformed Query statistics Logical Plan Generator (Cost Estimation) Logical Plan Physical Plan Generator Execution Plan [ 그림 3-1] 옵티마이저구조 옵티마이저는크게 Query Rewriter, Logical Plan Generator, Physical Plan Generator 로구성된다. 각구성요소의역할은다음과같다. Query Rewriter: 주어진파스트리를이용하여동일한결과를가지면서최적화에더유리하도록쿼리를변환한다. ALTIBASE HDB 가사용하는쿼리변환기법에대해서는아래의 " 쿼리변환 " 40 Performance Tuning Guide
절에서상세히설명한다. Logical Plan Generator: 변형된쿼리와통계정보를이용하여실행비용을계산함으로써최적화된논리적쿼리플랜을생성한다. Physical Plan Generator: 최적화된쿼리플랜를이용하여물리적실행계획트리를생성한다. 쿼리옵티마이저의각요소에의해수행되는최적화작업은크게아래와같이분류할수있다. 쿼리변환 논리적실행계획생성 물리적실행계획생성 옵티마이저에영향을미치는요소 ALTIBASE HDB 쿼리옵티마이저의동작은아래와같은요소들에의해영향을받고결과적으로실행계획에차이가생긴다. SQL 문과조건절 (predicates) 의형태 인덱스와제약조건 (constratints) 통계정보 SQL 힌트 옵티마이저관련프로퍼티통계정보와 SQL 힌트에대해서는아래의장에서설명한다. 쿼리옵티마이저 41
쿼리변환 쿼리처리과정에서옵티마이저는파싱이완료된파스트리를의미적으로동일하면서최적화에유리한형태로 SQL 문을재작성하는데, 이를쿼리변환이라고한다. ALTIBASE HDB 쿼리옵티마이저는아래와같은쿼리변환기법을사용한다. 이중일부기법은 Query Rewriter 가쿼리를변환하는단계에서사용하지만, 일부는 Logical Plan Generator 가쿼리플랜을최적화하는단계에서사용한다. 공통표현식제거 (Common Subexpression Elimination) Constant Filter 우선처리 View Merging Subquery Unnesting 조건절 Pushdown(Predicate Pushdown) 조건절이행 (Transitive Predicate Generation) 공통표현식제거 (Common Subexpression Elimination) 동일한조건식이 WHERE 절에중복되어존재하는경우, 옵티마이저가중복된연산식을하나로합친다. 아래의예제에서 OR 뒷부분의 ( department_id=60 ) 조건은앞선 조건에포함되어있으므로옵티마이저가해당조건을삭제하게된다. SELECT department_id, salary FROM employee WHERE ( department_id = 60 AND salary = 4200 ) OR ( department_id = 60 ); 아래의예제에서는 salary = 4200 조건이무의미하므로옵티마이저가해당조건을삭제하게된다. SELECT department_id, salary FROM employee WHERE ( department_id = 60 OR salary = 4200 ) AND ( department_id = 60 ); Constant Filter 우선처리 1 = 1 또는 1 <> 1 과같이테이블이소유한값에관계없이항상 TRUE 또는 FALSE 를결정하는조건을 constant filter 라고한다. Constant filter 는항상같은논리값을갖기때문에질의수행과정에서한번만비교하고추가적인비교연산비용이발생하지 42 Performance Tuning Guide
않는다. 즉, constant filter 의논리값이 FALSE 인경우에는어떠한 검사도추가적으로발생하지않으며, 테이블에접근할필요도없다. 무의미해보이는 constant filter 도다음과같이매우다양한용도로 활용할수있다. 그예로스키마만구성하고데이터는구축하지않는경우에 constant filter 를활용할수있다. 다음과같은질의가이에 해당하는예이다. CREATE TABLE T3 AS SELECT * FROM T1, T2 WHERE 1 <> 1; 위와같이 T1 과 T2 테이블의데이터는가지지않으면서모든칼럼 정보를가진 T3 테이블을만들고싶을경우 constant filter 를 사용하면원하는작업을수행할수있다. 또한, 다음예와같이검색권한을제한하는용도로활용할수있다. SELECT * FROM T1, T2 WHERE T1.i1 = T2.a1 AND? > 20; 위와같이나이값에해당하는호스트변수를지정하여조건에 부합하지않는사용자가질의를수행하거나결과값이없는질의의 수행으로인한부하등을방지할수있다. 이외에도옵티마이저는다음과같이 subquery 를포함하고있는 조건절도 constant filter 로처리하여한번만수행하고 subquery 가 반복적으로수행되지않게할수있다. SELECT * FROM T1 WHERE EXISTS ( SELECT * FROM T2 WHERE T2.date = SYSDATE ); 위의예제에서 EXISTS 조건은 T1 의데이터와전혀관계없는 constant filter 이다. View Merging 메인쿼리에포함된뷰를메인쿼리와머지하는기법이다. 조건절과조인만을포함하는단순뷰 (Simple View) 의경우, 사용자가 NO_MERGE 힌트를사용하지않는한옵티마이저가 View Merging 을수행한다. 아래는 emp_engineer 뷰와 departments 테이블을조인하는쿼리문이뷰 Merging 에의해변환되는예제이다. CREATE OR REPLACE VIEW emp_engineer AS SELECT eno, e_lastname, emp_job, salary, dno FROM employees WHERE emp_job='engineer'; SELECT e.eno, e.e_lastname, e.salary, d.dname, d.mgr_no FROM emp_engineer e, departments d 쿼리옵티마이저 43
WHERE d.dno=e.dno AND e.salary>=1500; => SELECT e.eno, e.e_lastname, e.salary, d.dname, d.mgr_no FROM employees e, departments d WHERE d.dno=e.dno AND e.emp_job='engineer' AND e.salary>=1500; * 관련힌트 : NO_MERGE Subquery Unnesting WHERE 절에포함된부질의를중첩된부질의 (Nested Subquery) 이라고한다. 부질의는결과집합을한정하기위해주로메인쿼리 (Main Query, 외부질의 ) 에있는칼럼을참조하는형태를보인다. 이렇게중첩된부질의가포함된쿼리를중첩되지않은조인형태의쿼리로변환하는것을 "Subquery Unnesting" 이라고한다. 아래는중첩된부질의를포함하는쿼리가 Subquery unnesting 에의해변환되는예제이다. SELECT * FROM employees WHERE dno IN (SELECT dno FROM departments) => SELECT * FROM (SELECT dno FROM departments) d, employees e WHERE d.dno=e.dno; * 관련힌트 : UNNEST, NO_UNNEST 조건절 Pushdown (Predicate Pushdown) 옵티마이저는실행비용및수학적일치성등을고려하여다양한형태의조건절 Pushdown 을고려한다. 옵티마이저가사용하는대표적인조건절 Pushdown 기법은다음과같다. 뷰에대한조건절 Pushdown Outer 조인에대한조건절 Pushdown 조인조건 Pushdown 뷰에대한조건절 Pushdown 뷰에대한조건절 Pushdown 은사용자정의뷰에대하여질의를수행시메인쿼리의 WHERE 절에기술된조건을뷰의내부로밀어넣는기법이다. 예를들어, 다음과같이정의된뷰와이에대한질의가있다고하자. 44 Performance Tuning Guide
CREATE VIEW V1(a1, a2) AS SELECT i1, i2 FROM T1 WHERE i2 > 20; SELECT * FROM V1 WHERE a1 = 1; 옵티마이저는해당질의의최적화과정에서뷰에대한조건절 Pushdown 을사용하는것이최적화에유리하다고판단하면, WHERE 절의조건이내부적으로다음과같은형태로처리되도록결정한다. 이를개념적으로표현한질의는다음과같다. SELECT * FROM ( SELECT i1, i2 FROM T1 WHERE i2 > 20 AND i1 =1 ) V1; 즉, 위와같이질의를변형시켜 T1.i1 칼럼의인덱스를활용할수있도록한다. 그러나옵티마이저가항상이런종류의 Pushdown 을사용하도록결정하지는않는다. 사용자가뷰내부의구조를파악하고있다면적절한조건절을사용하여, 옵티마이저가뷰에대한 Pushdown 기법을선택하도록할수있다. 또한사용자는힌트를사용하여옵티마이저가명시적으로뷰에대한조건절 Pushdown 기법을선택하도록할수있다. 그러나뷰에대한조건절 Pushdown 을사용하는것이원래질의보다더나은성능을반드시보장하지는않는다. * 관련힌트 : NO_PUSH_SELECT_VIEW, PUSH_SELECT_VIEW Outer Join 에대한조건절 Pushdown FROM 절에 outer join 이사용된경우다양한형태의조건절 Pushdown 이가능하다. 이들기법의공통된점은 WHERE 절에기술된조건을 outer join 의조인처리이전에수행되게한다는것이다. 예를들어다음과같은질의를살펴보자 SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.i1 = T2.a1 WHERE T1.i1 = 1; 위질의에조건절 Pushdown 기법이적용될경우개념적으로다음과같은질의형태로처리된다. SELECT * FROM (SELECT * FROM T1 WHERE T1.i1 = 1) T1 LEFT OUTER JOIN T2 ON T1.i1 = T2.a1; 즉, left outer join 에대한조인조건을처리하기전에 T1.i1 = 1 조건을먼저처리하여 T1 의결과집합을줄인다. 이러한기법역시수학적동치여부및비용평가를통해적용여부가결정된다. 예를들어다음의세질의는경우에따라서로다른결과를생성하며, 동일한질의가아니다. SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.i1 = T2.a1 WHERE T2.a1 = 1; SELECT * FROM T1 LEFT OUTER JOIN (SELECT * FROM T2 WHERE T2.a1 = 1) T2 ON T1.i1 = T2.a1; 쿼리옵티마이저 45
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.i1 = T2.a1 AND T2.a1 = 1; 옵티마이저는수학적동치인상태에서동일한결과를얻기위해서 위의질의를다음과같은형태로조건절 Pushdown 을적용한다. SELECT * FROM T1 LEFT OUTER JOIN (SELECT * FROM T2 WHERE T2.a1 = 1) T2 ON T1.i1 = T2.a1 WHERE T2.a1 = 1; Left outer join 의경우 WHERE 조건을 ON 절에옮겨처리하고싶다면다음과같이 WHERE 절에반드시남겨두어야동일한결과를보장받을수있다. SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.i1 = T2.a1 AND T2.a1 = 1 WHERE T2.a1 = 1; 위의예제에서알수있듯이, 사용자가임의로질의조건을추가하여조건절 Pushdown 과유사한효과를기대할수있다. 하지만조건을추가하여동일한결과를얻음과동시에성능까지향상시킬수있는지여부를판단하는것이매우중요하다. 집합연산자를포함한뷰에대한조건절 Pushdown 조건절 Pushdown 은특히집합연산을통해생성된뷰를처리할때매우효과적이다. 예를들어다음과같은뷰와이를이용한질의를살펴보자. CREATE VIEW V1(a1, a2) AS ( SELECT m1, m2 FROM T2 UNION ALL SELECT x1, y1 FROM T3 ); SELECT * FROM T1, V1 WHERE T1.i1 = V1.a1 AND T1.i1 = 1; 위의뷰정의에서 T2.m1 과 T3.x1 칼럼에모두인덱스가있다고해도해당질의를수행할때어떤인덱스도사용되지않는다. 이때먼저아래의절에서설명하는 ' 조건절이행 ' 을통해아래와같이 V1.a2=1 조건절이내부적으로생성된다. SELECT * FROM T1, V1 WHERE T1.i1 = V1.a1 AND T1.i1 = 1 AND V1.a1 = 1; 이상태에서조건절 Pushdown 을통해쿼리가다음과같이변환되어 T2.m1 과 T3.x1 칼럼의인덱스를모두사용할수있게된다. SELECT * FROM T1, ( SELECT m1, m2 FROM T2 WHERE T2.m1 = 1 UNION ALL SELECT x1, y1 FROM T3 WHERE T3.x1 = 1 ) V1 WHERE T1.i1 = V1.a1 AND T1.i1 = 1; 위와같이조건절의적절한기술은옵티마이저가보다효율적인실행계획을작성하는데도움을주며, 사용자의명시적인조건절의변경을통해성능향상을꾀할수있다. 그러나이러한조건절의 46 Performance Tuning Guide
조인조건 Pushdown 추가가반드시좋은성능을보장하지는못하며, 경우에따라비교 연산비용만증가시킬수있음을유념하여야한다. 메인쿼리의조건절에서뷰와관련된조인조건절을뷰안으로 밀어넣는 (pushing) 기법이다. * 관련힌트 : PUSH_PRED 조건절이행 (Transitive Predicate Generation) 조건절이행기법은조인조건과단일테이블조건이존재할때 유사한다른단일테이블조건을추가하여성능을향상시키는 기법이다. 예를들어다음과같은질의를살펴보자 SELECT * FROM T1, T2 WHERE T1.i1 = T2.a1 AND T1.i1 = 1; 해당질의를처리하기위해어떠한인덱스도사용할수없을경우 다음과같이유사한단일테이블조건을추가하면, 성능향상에 도움이된다. SELECT * FROM T1, T2 WHERE T1.i1 = T2.a1 AND T1.i1 = 1 AND T2.a1 = 1; 즉, T2 의결과집합의크기를줄임으로서성능을향상시킬수있다. View Materialization 기법 뷰에대한조건절 Pushdown 과상충되는최적화기법이바로 view materialization 기법이다. 이는뷰가하나의질의에서반복해서사용될때질의처리과정중에뷰의결과를임시로저장하여처리하는기법이다. 예를들어다음과같은뷰의정의와관련질의를살펴보자 CREATE VIEW V1(a1, a2) AS SELECT i1, SUM(i2) FROM T1 GROUP BY i1; SELECT * FROM V1 WHERE V1.a2 > (SELECT AVG(a2) FROM V1 WHERE a1 > 10 ); 위와같은질의에대하여 view materialization 기법이사용되면, V1 뷰의결과는임시로저장되고최상위질의와 subquery 는이를사용하게된다. 즉, V1 의결과를얻기위하여뷰를정의한질의를반복하여수행하지않아도되는효과를얻게된다. 쿼리옵티마이저 47
이러한질의에대하여뷰에대한조건절 Pushdown 기법을 사용하도록힌트를주면오히려느린성능을얻을수도있으므로 사용자의올바른판단이필요하다. 48 Performance Tuning Guide
논리적실행계획생성 옵티마이저는쿼리변환과정을거쳐변형된쿼리와통계정보를이용하여논리적실행계획을생성한다. 옵티마이저는 SQL 구문이접근하는테이블, 인덱스, 파티션들의저장특성같은통계정보를활용하여비용을계산한다. 비용이란특정플랜으로쿼리를수행하는데필요할것으로예상되는시간을옵티마이저가추정하여하나의수치로표현한것이다. 옵티마이저는비용이가장낮은액세스방법, 조인순서를선택하여논리적실행계획을생성한다. 정규화 (Normalization) 정규화의개념 사용자가작성한 WHERE 조건절은매우다양한형태로표현된다. 옵티마이저는다양한조건처리를일관되고효율적인방법으로처리하기위해사용자가정의한 WHERE 절을정형화된형태로변경한다. 이러한과정을정규화라고한다. 정규화는 AND, OR, NOT 등의논리식을사용해서조건절을확장하고변형하는과정이다. AND 연산자를최상위논리식으로표현하는방법을 CNF (Conjunctive Normal Form) 이라하며, OR 연산자를최상위논리식으로표현하는방법을 DNF (Disjunctive Normal Form) 이라한다. CNF 정규화는주어진조건절을 AND 연산자를최상위로하여하위에 OR 논리연산자가구성되도록재배치하는것이다. 다음예는조건절이 CNF 정규화를사용해서변환될때결과로나타나는구조를보여준다. WHERE (i1 = 1 AND i2 = 1) OR i3 = 1 CNF: (i1 = 1 OR i3 = 1) AND (i2 = 1 OR i3 = 1) DNF 정규화는주어진조건절을 OR 연산자를최상위로하여하위에 AND 논리연산자로만구성되도록재배치하는과정이다. 다음예는조건절이 DNF 정규화를사용해서변환될때결과로나타나는구조를보여준다. WHERE (i1 = 1 OR i2 = 1) AND i3 = 1 DNF: (i1 = 1 AND i3 = 1) OR (i2 = 1 AND i3 = 1) 즉, 옵티마이저는주어진조건절을 CNF 로변환했을때의실행 쿼리옵티마이저 49
비용과, DNF 로변환했을때의실행비용을비교하여보다나은정규화형태를선택하게된다. 일반적으로옵티마이저는 CNF 기반의실행계획을선택하며, 일부의경우 DNF 기반의실행계획을선택한다. 예를들어, 다음과같은질의를살펴보자. SELECT * FROM T1 WHERE i1 = 1 OR i2 = 1; CNF 정규화 : AND (i1 = 1 OR i2 = 1) DNF 정규화 : (i1 = 1 AND ) OR (i2 = 1 AND ) 위의조건절은 T1 테이블에인덱스가없거나하나의칼럼에만인덱스가있는경우는 CNF 로처리된다. 즉, T1 테이블을전체스캔하여질의를처리하는것이가장효율적이다. 그러나 i1 과 i2 칼럼에각각인덱스가있는경우, DNF 로정규화하여각각의인덱스를사용하여 (i1 = 1) 의결과와 (i2 = 1) 의결과를따로얻어합치는것이보다효율적이다. 이와같이동일한조건식이더라도인덱스의유무에따라다른타입의정규화가선택된다. 이는옵티마이저가각경우의실행비용을비교하여결정하기때문이다. 물론, WHERE 절에논리연산자가없거나 OR 연산자가존재하지않는경우라면옵티마이저는 CNF 정규화만을사용하게되며, OR 연산자가존재하는경우에는 CNF 와 DNF 를모두구성하고각각의비용을비교하여실행계획을생성한다. 사용자튜닝정규화과정은조건절의확장을불가피하게한다. 따라서조건절기술시정규화된형태로구성하는것은조건절의확장을방지하여불필요한비교연산을제거할수있다. 예를들어, 다음조건절은 CNF 또는 DNF 로수행이가능하지만, 이는질의변경을통해 CNF 로만동작하게할수있다. WHERE i1 = 1 OR i1 = 2 OR i1 = 3 CNF 정규화 : i1 IN (1, 2, 3) 유사한예로다음과같은질의는 CNF 또는 DNF 로의질의변형을통해불필요한정규화과정을제거하고동일한조건비교를방지하여성능을향상할수있다. WHERE (i1 = 1 AND i2 = 1) OR (i1 = 2 AND i2 = 2) CNF 정규화 : (i1, i2) IN ( (1,1), (2,2) ) 50 Performance Tuning Guide
WHERE (i1 = 1 AND i2 = 1) OR ( i3 = 1 AND i4 = 1) DNF 정규화 : (i1, i2) = (1, 1) OR (i3, i4) = (1, 1) 물론정규화형태의기술시테이블의인덱스정보등을반드시고려하여야한다. 사용자가정규화형태로기술하여도, 옵티마이저가사용자가원하지않는정규화형태를선택할수있다. 이러한경우힌트를사용하여제어할수있는데, 이는힌트를설명한절에서자세히설명다. 비용계산 (Cost Estimation) 옵티마이저는통계정보를활용한아래의세가지측정치를고려하여최적의실행계획을수립한다. 선택도 (Selectivity) 전체대상로우중에서특정조건절 (predicate) 에의해선택될것으로예상되는로우의비율이다. 옵티마이저는선택도를사용해서카디널리티를구하고또비용을구함으로써인덱스사용여부, 조인순서와방법등을결정한다. 따라서선택도는최적의실행계획을수립하기위한기본요소라고할수있다. 카디널리티 (Cardinality) 전체대상로우중에서선택될것으로예상되는로우의개수이다. ( 총로우수 * 선택도 ) 로계산된다. 비용 (Cost) 접근비용 (access cost) 과디스크 I/O 비용의합으로계산된다. 접근비용은인덱스스캔또는풀스캔같은액세스방법에의해결정된다. 액세스방법에대한자세한설명은아래의 ' 액세스방법 ' 을참고한다. 액세스방법 액세스방법이란데이터베이스에서데이터를가져오는방법을말한다. 일반적으로테이블에서작은수의레코드를가져오는경우에는인덱스를사용하는것이더효율적이고, 많은수의레코드에접근하는경우에는전체테이블스캔이더효율적이다. ALTIBASE HDB 는아래의액세스방법을사용한다. 전체테이블스캔 (Full Table Scans) 인덱스스캔 쿼리옵티마이저 51
전체테이블스캔 테이블의모든로우를읽어서선택기준을만족하지않는로우를 걸러낸다. 전체테이블스캔은아래와같은경우에사용된다. 인덱스가없을때 테이블에서많은양의데이터에접근할때 * 관련힌트 : FULL SCAN(table) 아래예제는전체테이블스캔이사용되는쿼리와실행계획을 보여준다. isql> SELECT /*+ FULL SCAN(employees) */ eno, e_firstname, e_lastname, emp_job FROM employees WHERE sex = 'F';... ------------------------------------------------ PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65, COST: 0.18 ) SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 ) ------------------------------------------------ 인덱스스캔 인덱스스캔은아래의유형으로구분된다. 인덱스범위스캔 (Index Range Scan) 인덱스전체스캔 (Index Full Scan) * 관련힌트 : INDEX, INDEX ASC, INDEX DESC, NO INDEX 인덱스범위스캔인덱스범위스캔은인덱스트리의루트에서리프까지수직적으로탐색한후에리프들에서필요한범위만스캔하는방식이다. ALTIBASE HDB 에서는이것을 'Key range 처리방법 ' 이라고도표현한다. 인덱스범위스캔은인덱스를이용하여조건에부합하는범위내의데이터를검색하는방법이다. 즉, 조건을만족하는최소값의위치와최대값의위치만을결정하고해당조건에대한별도의비교없이그범위내의모든데이터를스캔한다. 따라서이방법은범위내의데이터에대한별도의비교연산이필요없어매우우수한성능을보장한다. 인덱스를구성하는선두칼럼이아래와같은조건절에사용된경우 52 Performance Tuning Guide
인덱스범위스캔이사용될수있다. c1 = value c1 < value c1 > value 데이터는인덱스칼럼순으로정렬되어반환된다. 인덱스를구성하는 칼럼들이 ORDER BY/GROUP BY 절에있는경우, 옵티마이저가 불필요한정렬을피할것이다. 아래예제는인덱스범위스캔이사용되는쿼리와실행계획을 보여준다. isql> SELECT /*+ INDEX(employees, EMP_IDX1) */ eno, e_firstname, e_lastname, emp_job FROM employees WHERE dno = 1003;... ---------------------------------------------------------- PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65, COST: 0.03 ) SCAN ( TABLE: EMPLOYEES, INDEX: EMP_IDX1, RANGE SCAN, ACCESS: 4, COST: 0.00 ) ---------------------------------------------------------- 인덱스전체스캔 특정범위만탐색하는인덱스범위스캔과달리리프노드를처음부터끝까지탐색하는방식이다. 보통은데이터검색을위한최적의인덱스가없을때차선으로선택된다. 데이터가인덱스키에의해정렬되므로이방식을사용할경우옵티마이저가별도의정렬작업을하지않아도된다. ALTIBASE HDB 에서는이것을 'Key filter 처리방법 ' 이라고도표현한다. Key filter 처리방법은인덱스를이용하여범위검색은할수없으나, 인덱스의리프노드를차례로스캔하면서저장되어있는키값으로비교연산을수행하는방법이다. 이방법은인덱스를저장하고있는페이지만을접근하여비교함으로써데이터페이지에대한접근횟수를줄여성능이향상될수있다. 하지만이러한성능향상효과는디스크테이블에만한정된다. 메모리테이블의인덱스는키값을저장하지않기때문에 filter 처리방법과비교하여성능향상의효과가없다. Filter 처리방법은인덱스를사용할수없는조건절에대한처리방법으로데이터를직접읽어비교연산을수행한다. WHERE 절의조건절을처리하기위해다수의 filter 를사용해야하는경우, 옵티마이저는각 filter 에대하여예상되는처리비용을비교하여가장비용이적은 filter 를우선적으로처리하여비교비용이 쿼리옵티마이저 53
최소화되도록 filter 의처리순서를결정한다. 아래예제는인덱스전체스캔이사용되는쿼리와실행계획을 보여준다. CREATE TABLE t1(c1 INT, c2 CHAR(10)) TABLESPACE sys_tbs_disk_data; CREATE INDEX t1_idx ON t1(c1); INSERT INTO t1 VALUES(1,'a'); INSERT INTO t1 VALUES(2,'b'); INSERT INTO t1 VALUES(3,'c'); isql> SELECT * FROM t1 ORDER BY c1;... ---------------------------------------------------------- PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 16, COST: 14.02 ) SCAN ( TABLE: T1, INDEX: T1_IDX, FULL SCAN, ACCESS: 3, DISK_PAGE_COUNT: 64, COST: 14.00 ) ---------------------------------------------------------- 인덱스생성시고려사항 주어진조건절을처리하는데가장효율적인 key range 검색을하기위해서는인덱스가있어야한다. 그러나인덱스를생성하기위해서는다음과같은사항을반드시고려하여야한다. 인덱스의생성이검색연산의성능을향상시킬수는있으나, 인덱스를관리하기위한저장공간이필요하여시스템자원을추가로소모할수있다. 또한과도한인덱스의생성은데이터를삽입, 삭제, 또는갱신할때관련인덱스의정보를유지하기위한비용발생으로성능저하를유발할수도있다. 메모리테이블은거의모든질의처리에있어인덱스를이용한검색방법이전체테이블스캔보다우수한성능을보인다. 디스크테이블의경우인덱스스캔 (index scan) 이전체테이블스캔 (full table scan) 보다반드시효과적인것은아니다. 전체테이블스캔이데이터페이지에접근하는패턴이일정한반면, 인덱스스캔은그패턴이일정하지않아경우에따라과도한 Disk I/O 를유발할수있기때문이다. 따라서디스크테이블은인덱스스캔을이용한검색이테이블의전체레코드중 1/10 이하또는아주작은수의레코드만을선택하는경우에한해인덱스를생성할것을권고한다. 그러므로질의수행의빈도수, 인덱스사용에따른성능향상, 갱신질의 (INSERT, DELETE, UPDATE) 와전체시스템성능간의영향등을고려해서인덱스를생성해야한다. 54 Performance Tuning Guide
옵티마이저의인덱스선택 옵티마이저는주어진조건과해당테이블의인덱스를이용하여가장효율적인액세스방법을결정한다. 이과정에서옵티마이저는다양한통계정보를이용하여각인덱스의사용에따른비용을평가하며, 이중가장효율적인액세스방법을선택한다. 액세스방법이결정되면옵티마이저는각조건들에대하여처리방법 (key range 처리, filter 처리등 ) 을결정한다. 옵티마이저가각액세스방법의비용을계산하기위해사용하는개념적인공식은다음과같다. Access cost + Disk I/O cost 개념적인 Access cost 계산 Full scan : T(R) Index Scan : log(t(r)) + T(R) * MAX(1/V(Index), selectivity) 개념적인 Disk I/O cost 계산 Full scan : B(R) Index Scan : Buffer Miss : [T(R) / V(R.a)] * ( 1- M/B(R) ) No Buffer Miss : B(R) * ( 1 - (log V(R.a)/logT(R)) ) 옵티마이저는접근비용 (access cost) 과디스크 I/O 비용 (disk I/O cost) 을모두통합하여계산한다. 메모리테이블의경우디스크 페이지가존재하지않아디스크 I/O 비용은수식에의하여자동으로 계산되지않는다. 옵티마이저는각인덱스에대한비용계산시인덱스를이용하여 처리할수있는조건을선택하고이를기반으로비용을계산한다. 이 때, 비용에가장큰영향을미치는것이인덱스에관련된조건의 효율성이다. 이를조건의선택도 (selectivity) 라고한다. 조건의선택도는전체레코드중조건에의해선택되는레코드들의 비율을의미한다. 즉, 조건의선택도가낮을수록결과레코드의수가 줄어들어성능을향상시킬수있다. 예를들어다음과같은조건을살펴보자. WHERE i1 = 1 AND i2 = 1 위의조건에서 i1 과 i2 칼럼에각각인덱스가존재한다면어떠한 인덱스를선택할것인가를결정할때가장중요한요소는해당 조건의선택도이다. 예를들어, i1 컬럼에서로다른값의종류가 쿼리옵티마이저 55
100 [V(i1) = 100] 개가있고, i2 칼럼에는서로다른값의종류가 1000 [V(i2) = 1000] 이라면각조건의선택도는다음과같이 계산된다. Composite 인덱스의활용 (i1 = 1) 의 selectivity = 1/V(i1) = 1/100 (i2 = 1) 의 selectivity = 1/V(i2) = 1/1000 즉, 해당질의를처리하기위해 i2 칼럼의인덱스를사용하는것이 보다효율적인액세스방법이다. 위와같은개념의액세스선택방법이보편적으로올바른실행 계획을작성한다. 그러나이역시비용계산에의한선택이기때문에 모든상황에있어항상좋은액세스방법이선택되는것은아니다. 예를들어, 다음과같은질의를살펴보자. SELECT * FROM soldier WHERE gender = 'female' AND rank = 'lieutenant'; 위의질의에서 gender 와 rank 칼럼에모두인덱스가있다고할때, 옵티마이저는비용계산을통해 rank 칼럼에대한조건을처리하기 위한인덱스를사용하는것이바람직하다고판단할것이다. 그러나, 사용자가 (gender = 'female') 의조건을만족하는결과집합이 아주작다는것을알고있다면, 힌트등을사용하여인덱스선택을 달리하는것이바람직하다. 옵티마이저는 composite 인덱스를사용할때조건절을비교하여 최대한많은조건절을 key range 검색할수있도록선택한다. 이때 key range 검색으로처리될수있는조건이많을수록보다나은 성능을기대할수있다. 정의된인덱스를참조하는조건절을사용자가어떻게기술하는가에 따라인덱스를이용한성능은매우차이가나므로, composite 인덱스의동작원리를이해하는것은 SQL 튜닝에매우큰도움이 된다. 예를들어, 다음과같은 composite 인덱스가존재할때다양한 조건들이어떻게처리되는지를살펴보자. Composite index on T1(i1, i2, i3, i4) WHERE i1 = 1 AND i2 = 1 AND i3 = 1 AND i4 = 1 위의 WHERE 절에포함되어있는모든조건은 composite 인덱스를이용하여모두 key range 처리가가능하다. WHERE i1 = 1 AND i2 > 0 AND i3 = 1 AND i4 = 1 Key Range : i1 = 1, i2 > 0 Filter(or Key filter) : i3 = 1, i4 = 1 위의예에서 key range 처리방법이적용가능한조건절은두개로 56 Performance Tuning Guide
인덱스와비교연산자 결정되고나머지는 filter 로처리된다. 이는 key range 처리는 최소값과최대값을결정할수있는영역내에서만가능하기때문에 부등호연산이후의조건절은 key range 처리를할수없다. WHERE i1 = 1 AND i3 = 1 AND i4 = 1 Key Range : i1 = 1 Filter : i3 = 1, i4 = 1 위의예에서는모두등호연산을사용하고있지만, key range 처리가가능한조건은하나뿐이다. 이는 composite 인덱스를구성하고있는칼럼들의순서에모두부합하는조건이있을경우에만 key range 처리가가능하기때문이다. 즉, i2 칼럼에대한조건이없어 i3, i4 에대한조건은 key range 처리를할수없다. 위와같이 composite 인덱스의경우에는조건들이칼럼의누락없이키칼럼의순서에부합할때와해당조건이등호연산으로이루어져있을때 key range 처리를사용하여평가될수있다. 예를들어다음과같은질의가빈번하게사용되어인덱스를추가하려고한다면인덱스를최대한활용할수있도록생성하는것이바람직하다. WHERE i1 > 0 AND i2 = 1 바람직한인덱스 : Index on T1(i2, i1) i2 와 i1 칼럼을참조하는조건들을평가하기위해 filer 없이 key range 처리가사용될수있다. 부적절한인덱스 : Index on T1(i1, i2) i1 칼럼을참조하는조건을평가하는데는 key range 처리가사용될수있으나, i2 칼럼을참조하는조건을평가하기위해서는 filter 처리가사용된다. 그러므로이인덱스가더비효율적이다. 인덱스가존재하고해당칼럼에대한조건이존재한다고해서반드시인덱스를사용될수있는것은아니다. 즉, 조건절의기술형태와비교연산자의종류에따라인덱스의사용가능여부가결정되므로이에대한주의가필요하다. 비교연산자의종류와인덱스사용가능여부는다음과같다. 종류 단순비교 인덱스비교연산자사용가능여부 = O!= O < O 비고 쿼리옵티마이저 57
<= O > O >= O BETWEEN O 범위비교 NOT BETWEEN O 멤버비교패턴비교 NULL 비교존재비교 Quantify ANY Quantify ALL IN O NOT IN O LIKE O 가능 : T1.i1 LIKE abc% 불가 : T1.i1 LIKE %abc NOT LIKE X IS NULL O IS NOT NUL O EXISTS X NOT EXISTS X UNIQUE X NOT UNIQUE X =ANY O!=ANY O <ANY O <=ANY O >ANY O >=ANY O =ALL O!= ALL O < ALL O <= ALL O > ALL O >= ALL O [ 표 3-1] 비교연산자에따른인덱스사용여부 Geometry 데이터타입관련비교연산자는다음과같다. R-Tree 인덱스가존재하는경우에만 geometry 데이터타입칼럼에정의된 인덱스를사용할수있다. 종류비교연산자인덱스사용가능여부 비고 Geometry CONTAINS O R-Tree index 비교 CROSSES O only DISJOINT O 58 Performance Tuning Guide
DISTANCE O EQUALS O INTERSECTS O ISEMPTY X ISSIMPLE X NOT CONTAINS X NOT CROSSES X NOT EQUALS X NOT OVERLAPS X NOT RELATE X NOT TOUCHES X NOT WITHIN X OVERLAPS O RELATE X TOUCHES O WITHIN O [ 표 3-2] Geometry 비교연산자사용여부 위와같이인덱스가존재하고인덱스를사용할수있는비교 연산자라하더라도항상인덱스가사용될수있는것은아니다. 즉, 조건절의형태에따라인덱스의사용가능여부가결정된다. 인덱스가사용될수있는조건절의형태는다음과같다. ( 단, 조건절의예제에서해당칼럼이 INTEGER 타입임을가정한다.) 조건절의형태 가능한예 불가능한예 인덱스사용이가능한비교연산자여야한다. T1.i1 = 1 T1.i1 NOT LIKE a 칼럼이있어야한다. T1.i1 = 1 1 = 3 칼럼에대한연산이없어야한다. T1.i1 = 1 + 1 T1.i1 + 1 = 3 연산자의한쪽에만칼럼이있어야한다. (T1.i1, T1.i2) = (1, 1) T1.i1 = T2.i1 (T1.i1, 1) = (1, T1.i2) T1.i1 = T1.i2 칼럼의타입 ( 값 ) 이변경되지않아야한다. T1.i1 = SMALLINT'1' T1.i1 = 1.0 [ 표 3-3] 조건절에따른인덱스사용여부 위와같이인덱스를사용하기위해서는조건절의기술에주의를 기울여야한다. 특히칼럼의값에대한연산이나타입변환이 발생하지않도록주의하여야한다. 인덱스사용을고려한데이터타입과데이터변환에대한설명은 다음절 ( 인덱스와데이터타입 ) 에서자세히설명한다. 쿼리옵티마이저 59
인덱스와데이터타입 WHERE 절에서참조하는칼럼에인덱스가생성되어있다고해서항상인덱스스캔이가능한것은아니다. 데이터타입과데이터변환에따라인덱스스캔이가능한경우도있고, 그렇지않은경우도있다. SELECT * FROM T1 WHERE T1.i1 =? 위의질의에서 i1 칼럼이 VARCHAR 타입이고 PRIMARY KEY 인경우, isql 로이쿼리의실행계획을확인하면인덱스스캔이가능한것으로나타난다. 하지만실제변수를바인딩할때 INTEGER 등의숫자형으로바인딩을하는경우칼럼값을숫자형으로변환하여비교해야하므로인덱스스캔이불가능하다. 따라서, isql 에서인덱스스캔으로수행되는질의인지확인하고, 실행계획이인덱스스캔이가능한것으로표시되는경우에도실제응용프로그램에서바인딩하는값과칼럼의데이터타입을확인해야한다. 그렇지않으면응용프로그램에서는전체테이블을스캔 (full table scan) 하여 isql 상에서의실행속도에비해현격한속도차이가날수도있다. 데이터타입과인덱스사용가능여부는다음과같다. 검은부분으로표시되는부분은비교연산수행시, 키칼럼에타입변환이발생하게된다. VALUE KEY CHAR VARCHAR SMALLINT INTEGER BIGINT NUMERIC FLOAT REAL DOUBLE DATE BLOB NIBBLE BYTE GEOMETRY CHAR O O X X X X X X X X - - - - VARCHAR O O X X X X X X X X - - - - SMALLINT X X O O O O O O O - - - - - INTEGER X X O O O O O O O - - - - - BIGINT X X O O O O O O O - - - - - NUMERIC O O O O O O O O O - - - - - FLOAT O O O O O O O O O - - - - - REAL X X O O O O O O O - - - - - DOUBLE O O O O O O O O O - - - - - DATE O O - - - - - - - O - - - - BLOB - - - - - - - - - - O - - - NIBBLE - - - - - - - - - - - O - - 60 Performance Tuning Guide
BYTE - - - - - - - - - - - - O - GEOMETRY - - - - - - - - - - - - - O [ 표 3-4] 데이터타입에따른인덱스사용여부 데이터타입은다음과같이크게문자형계열과숫자형계열로 구분할수있으며, 각계열에속하는데이터타입들간의비교는모두 인덱스를사용할수있다. 숫자형 계열 문자형계열 CHAR, VARCHAR Native 정수형 BIGINT, INTEGER, SMALLINT 실수형 DOUBLE, REAL Non-Native 고정소수점형 NUMERIC, ( 지수형 ) DECIMAL, NUMBER(p), NUMBER(p,s) 부동소수점형 FLOAT, NUMBER 즉, 문자형계열에속하는 CHAR, VARCHAR 간의비교또는숫자형계열에속하는정수형, 실수형, 지수형간의비교시에는모두인덱스사용이가능하다. 문자형계열 char_column = VARCHAR abc varchar_column = CHAR abc 숫자형계열 integer_column = DOUBLE 1 number_column = DOUBLE 1 integer_column = NUMBER 1 숫자형계열의서로다른데이터타입간비교연산에대해서는다음의변환표를기준으로변환하고비교한다. 정수형실수형지수형정수형정수형실수형지수형실수형실수형실수형실수형지수형지수형실수형지수형인덱스칼럼에변환이발생하는경우가이에해당되는데, 인덱스칼럼의값에변환이발생한다할지라도, 이비교기준에의해내부적으로인덱스칼럼의변환된값으로비교연산을수행하게된다. 그러므로, bigint_col = NUMERIC 1 과같이칼럼에변환이발생하는인덱스스캔은 bigint_col = BIGINT 1 과같이칼럼에변환이발생하지않는인덱스스캔보다수행속도가느리게된다. 쿼리옵티마이저 61
이외에인덱스스캔과별도로두값을비교하는비교연산자의수행성능은비교대상데이터의타입과밀접한관계가있다. 같은데이터타입간의비교일경우에는데이터변환비용이없으므로최적의성능을발휘하겠지만, 서로다른데이터타입간의비교라면데이터변환이최소화되도록해야한다. 예를들어숫자형타입과문자형타입을비교할경우데이터변환비용만고려한다면 FLOAT 과 VARCHAR 인경우가장짧은변환경로를가진다. 그러나데이터타입별로데이터저장크기와형식에차이가있으므로이를종합적으로고려해데이터타입을결정해야한다. 다음은데이터타입변환경로를도식화한그림이다. REAL <0+0+0> <G+E+L> INTERVAL SMALLINT <0+0+0> <0+E+0> <0+E+0> INTEGER BIGINT <0+E+L> <0+0+0> <G+0+0> <0+0+L> <0+0+0> <0+E+L> <0+0+L> DOUBLE NUMERIC <G+0+0> CHAR <0+0+L> <0+E+0> <0+0+0> <0+0+0> <0+0+0> <0+0+L> <G+0+0> <0+E+0> FLOAT <G+0+0> VARCHAR <G+0+0> <G+0+0> <G+E+0> <0+E+L> NULL <G+0+0> ALL DATE G : Group Penalty E : Error Penalty L : Loss Penalty G >> E >> L A B A 타입에서 B 타입으로데이터타입변환가능 A B C A와 C를비교하는경우항상 A가 C로변환되거나 C가 A로변환되는것은아니다. 연산자에따라 A가 C로변할수도있고, 그반대인경우도있으며, A가 B로변환되고 C가 B로변환되어연산을수행하는경우도있다. [ 그림 3-2] 데이터변환타입경로 62 Performance Tuning Guide
위와같이인덱스가사용될수있도록조건절을기술하는것은매우중요하다. 조건절의유형에따라성능에영향을미칠수있으므로 WHERE 절의기술및응용프로그램의작성시주의를기울여야한다. 옵티마이저는개별테이블에대한액세스방법을결정하면, 조인에대한처리를수행한다. 이때개별테이블에대하여결정된액세스방법은절대적인것이아니며, 조인처리과정에서재조정될수도있다. 조인순서 복잡한질의의경우조인의순서및조인의처리방법은질의성능에가장큰영향을미친다. 따라서적절한조인순서와조인방법이선택되었는지를판단하고조정하는것은질의성능향상에큰도움이된다. 옵티마이저는조인을처리하기위하여다음과같은단계를따른다. 1. 조인조건을이용한조인의그룹화 2. 각그룹의조인관계표현 3. 각그룹의조인순서결정 4. 각조인의조인방법결정 5. 그룹간의조인순서및조인방법결정아래와같이힌트를사용해서조인순서를지시할수있다. ORDERED 힌트 액세스방법관련힌트에조인순서를파라미터로기입 조인방법관련힌트에조인순서를파라미터로기입이절에서는옵티마이저가조인순서를결정하는과정에대하여알아본다. 조인순서는조인선택도를이용한 Join greedy algorithm 를기반으로결정된다. 조인그룹의분류 조인관계가없는테이블까지모두고려하여조인순서를결정하는것은일반적으로부하만증가시킬뿐올바른조인순서를결정하는데큰도움이되지않는다. 즉, 조인관계가있는테이블끼리하나의그룹으로묶어처리하고이후각그룹에대한조인순서를결정하는것이효율적이다. 예를들어, 다음과같은질의를살펴보자. 쿼리옵티마이저 63
SELECT * FROM T1, T2, T3, T4, T5 WHERE T1.i1 = T2.a1 AND T2.a2 = T3.m1 AND T4.x1 = T5.z1; 조인그룹의분류 : (T1, T2, T3), (T4, T5) 이와같이그룹간에조인관계가전혀없도록조인그룹을분류한후, 각조인그룹에대하여다음과같은과정을통하여조인순서를결정한다. 조인관계의구성 각조인그룹에대하여조인관계를구성하는것은조인순서결정시직접적인조인관계가없는테이블간의비교비용을줄여보다효율적인조인순서를결정하기위함이다. 예를들어다음과같은질의를살펴보자 SELECT * FROM T1, T2, T3, T4 WHERE T1.i1 = T2.a1 AND T2.a2 = T3.m2 AND T3.m3 = T4.x3; 위의질의에서조인관계는다음과같이표현될수있다. 조인관계 T1.i1 = T2.a1 T2.a2 = T3.m2 T3.m3 = T4.x3 T1 T2 T3 T4 옵티마이저는위와같이조인순서결정시조인관계만을고려하여비용을평가함으로써 (T1, T4) 와같이직접적인조인관계가없는테이블간의조인이우선적으로결정되는것을방지한다. 조인관계를고려하는것이일반적으로효율적인조인순서를결정하는데도움이되지만, 반드시올바른조인순서를결정하는것은아니다. 따라서필요한경우사용자가힌트를지정하여조인순서를제어할수있다. 조인관계의순서결정 옵티마이저는위에서생성한조인관계를이용하여각조인관계들중가장효율적인조인관계순서를결정하게된다. 조인관계의순서를결정하는방법은조인관계들중조인선택도 (selectivity) 가가장효율적인조인관계를우선선택한다. 그리고다시선택된관계로부터관련된조인관계들중에효율적인조인관계를선택하는방식으로결정된다. 그러나조인관계의순서가실제조인순서는아니며, 실제조인 64 Performance Tuning Guide
순서는조인방법의결정을통해완성된다. JOIN JOIN JOIN T4 JOIN T4 JOIN T3 T3 JOIN T1 T2 T1 T2 조인관계의순서 [ 그림 3-3] 조인관계의순서결정 조인방법결정후의실제조인순서 위의그림에서보듯이이과정에서의조인관계의순서는조인 관계의깊이만을결정할뿐이다. 조인관계순서의가장중요한 요소는조인의선택도 (selectivity) 이며, 두테이블의조합을통해 생성되는결과집합의원래테이블크기에대한비율을의미한다. 즉, 조인관계의순서결정시조인을통한결과집합의대소를비교하는 것이아니라, 결과집합의크기가원래테이블의레코드개수에 비해서얼마나많이줄어드는가를기준으로판단하게된다. 옵티마이저는조인의선택도를아래처럼계산한다. 이공식의자세한 설명은이문서의범위를벗어난다. 개념적인 Join selectivity 계산 [T(R) * T(S) / MAX[V(R.a), V(S.a)]/ [T(R) + T(S)] 이와같은조인관계순서의결정은조인의선택도에따라보다 효율적인비율로결과집합을줄일수있는조인관계를우선적으로 선택하게된다. 그리고조인방법결정에의하여비교적정확한조인 순서가결정된다. 예를들어, 하나의질의에포함된각테이블의레코드개수와 조인으로생성되는결과의개수가다음과같을때를살펴보자. T(R) = 10, T(S) = 10, T(R JOIN S) = 10 T(T) = 1000, T(U) = 1000, T(T JOIN U) = 100 위의예에서테이블 R 과 S 를조인한결과의개수가 T 와 U 를조인한결과의개수보다적지만, 오히려결과집합을아주큰비율로줄일수있는 T 와 U 의관계가더중요한조인관계가된다. 옵티마이저가조인관계를이용하여생성한조인순서가모든 쿼리옵티마이저 65
경우에있어항상바람직함을보장할수는없다. 이를위해조인순서힌트를사용하여조인순서를제어할수있다. 옵티마이저는조인관계의순서를결정한후에두테이블간의조인방법을결정하고최종적으로조인순서를결정한다. 조인방법 조인관계의순서가결정되면, 옵티마이저는두테이블의각조인관계에대하여조인방법을결정한다. 이때, 각조인방법의비용비교를통해조인의순서, 방향, 및조인방법을결정한다. ALTIBASE HDB 가지원하는조인방법은다음과같이크게네가지계열에속한다. Nested loop 조인계열 Sort-based 조인계열 Hash-based 조인계열 Merge 조인계열각계열별조인방법및사용가능한조인의종류는다음과같다. 조인계열 Nested Loop Sort-based Hash-based Merge-based 조인방법 조인방향 Left=>Right Right=>Left Full nested loop C, I, S, A, L C, I, R Full store nested loop C, I, S, A, L, F C, I, R, F Index nested loop I, S, A, L I, R Anti outer nested loop F F Inverse index nested loop S One pass sort join I, S, A, L, F I, R, F Two pass sort join I, S, A, L, F I, R, F Inverse sort join S, A One pass hash join I, S, A, L, F I, R, F Two pass hash join I, S, A, L, F I, R, F Inverse hash join R S, A, L Index merge join I, S, A I Sort merge join I, S, A I 66 Performance Tuning Guide
사용가능한조인의종류 C (Cartesian Product): 조인관계를갖지않는두테이블의조합 I (Inner Join): 조인관계를갖는두테이블의일반적인조인 S (Semi Join): Semi 조인관계를갖는두테이블의조인 A (Anti Join): Anti 조인관계를갖는두테이블의조인 L (Left outer join): Left outer 조인관계를갖는두테이블의조인 R (Right outer join): Right outer 조인관계를갖는두테이블의조인 F (Full outer join): Full outer 조인관계를갖는두테이블의조인각조인에대한자세한설명은 SQL Reference에서 SELECT 구문의설명을참고한다. [ 표 3-5] 조인방법에따른사용가능한조인의종류옵티마이저는위와같은다양한조인방법들중적용가능한조인방법에대한비용평가를통해가장효과적인조인방법을선택하고조인의방향을결정한다. 조인방법이결정되면기준테이블 (driving table, outer table) 은왼쪽에위치시키고, 반복테이블 (driven table, inner table) 은오른쪽에위치시킨다. 사용자는실행계획을통해어떠한조인방법이선택되었는지확인할수있으며, 힌트를사용하여조인방법을제어할수있다. 이절에서는각조인계열의조인방법에대해살펴본다. Nested Loop 조인계열 nested loop 조인계열에는다음과같은조인방법들이있다. Full nested loop join Full store nested loop join Index nested loop join Anti outer nested loop join Inverse index nested loop join Full nested loop join 은한테이블의모든레코드를다른테이블의모든레코드와조인하는방법이다. 이방법은일반적으로아래쿼리와같이조인관계가존재하지않는두테이블간의조인시사용될가능성이높다. SELECT * FROM T1, T2; Full store nested loop join 은반복테이블 (inner table) 의결과를저장한후 full nested loop join 을적용하는방법이다. 이방법은조인조건외의조건처리에의하여결과집합이매우줄어드는경우적용될가능성이높으며, 일반적으로조인그룹간의 Cartesian product ( 교차조인 ) 에의하여사용될가능성이높다. SELECT * FROM T1, T2 WHERE T1.i1 = 1 AND T2.i1 = 1; 쿼리옵티마이저 67
Index nested loop join 은인덱스를이용하여조인조건을처리하는 방법이다. 기준테이블 (outer table) 의레코드수가적고반복 테이블 (inner table) 에인덱스가존재하는경우에사용될가능성이 높다. Index on T2(i1) SELECT * FROM T1, T2 WHERE T1.i1 = T2.i1 AND T1.i1 = 1; Anti outer nested loop join 방법은 FULL OUTER JOIN 의처리를위해서만사용된다. 기준테이블 (outer table) 과반복테이블 (inner table) 모두조인조건에해당하는칼럼에인덱스가정의되어있을때다른조인방법에비해이방법이선택될가능성이높다. Index on T1(i1), Index on T2(i1) SELELCT * FROM T1 FULL OUTER JOIN T2 ON T1.i1 = T2.i1; Inverse index nested loop join 방법은 SEMI JOIN 의처리를위해서만사용된다. 기준테이블 (outer table) 에인덱스가있고반복테이블 (inner table) 에는인덱스가없는경우에사용될가능성이높다. 특히기준테이블의레코드수가반복테이블보다상대적으로많을때더욱유리하다. 하지만반복테이블에인덱스가존재한다면 Index nested loop join 이선택될가능성이높다. Index on T1(i1) SELECT * FROM T1 WHERE T1.i1 IN ( SELECT i1 FROM T2 ); 각조인의수행비용은개략적으로 Access cost + Disk I/O cost 로계산된다. * 관련힌트 : USE_NL Sort-based 조인계열 Sort-based 조인방법은반복테이블 (inner table) 을정렬된순서로저장하고조인조건을이용하여범위검색을하는방법이다. 일반적으로이방법은아래와같이쿼리에부등호조인조건이사용되고인덱스가없을때선택될가능성이높다. SELECT * FROM T1, T2 WHERE T1.i1 > T2.i1; sort-based 조인계열에는다음과같은조인방법들이있다. One-pass sort-based join Two-pass sort-based join Inverse sort-based join One-pass sort-based 조인방법은반복테이블 (inner table) 의데이터양이적어임시공간내에서관리가가능할때사용된다. 이방법은메모리테이블이반복테이블 (inner table) 로사용될경우항상사용된다. 68 Performance Tuning Guide
Hash-based 조인계열 Two-pass sort-based 조인방법은반복테이블 (inner table) 의 데이터양이방대하여임시공간의범위내에서관리할수없을때 사용된다. 이방법은디스크 I/O 를줄이기위해사용된다. 이방법은 기준테이블 (outer table) 과반복테이블 (inner table) 을모두 정렬하여임시공간에저장한다. 그런다음기준테이블 (outer table) 의데이터정렬순서로조인조건을검사함으로써반복 테이블 (inner table) 의동일한디스크페이지접근확률을높인다. 결국디스크 I/O 비용이줄어든다. Inverse sort-based join 은 SEMI JOIN 또는 ANTI JOIN 의처리를 위해서만사용된다. 기준테이블 (outer table) 보다반복테이블 (inner table) 이상대적으로클경우 Inverse sort-based join 을사용하는 것이실질적으로유리할수도있지만, ALTIBASE HDB 의 옵티마이저는대부분 Inverse hash-based join 을선택한다. Inverse sort-based join 은조인결과를정렬해서반환하기때문에추가 정렬을하지않아도된다는장점이있다. Inverse sort-based join 을 강제로사용하고자한다면아래와같이힌트를사용해야한다. SELECT * FROM T1 WHERE T1.i1 IN ( SELECT /*+ SORT_SJ */ i1 FROM T2 ); 각조인의수행비용은개략적으로 Access cost + Disk I/O cost 로계산된다. * 관련힌트 : USE_SORT, USE_ONE_PASS_SORT, USE_TWO_PASS_SORT Hash-based 조인방법은반복테이블 (inner table) 을 hash 구조로저장하고, 조인조건을이용하여범위검색을하는방법이다. 이방법은아래와같이쿼리의조인조건에등호연산자가사용되고인덱스가없을때선택될가능성이높다. SELECT * FROM T1, T2 WHERE T1.i1 = T2.i1; hash-based 조인계열에는다음과같은조인방법들이있다. One-pass hash-based join Two-pass hash-based join Inverse hash-based join One-pass hash-based 조인방법은반복테이블 (inner table) 의데이터양이적어임시공간내에서관리가가능할때사용된다. 메모리테이블이반복테이블 (inner table) 로사용될경우에는항상이방법이사용된다. 쿼리옵티마이저 69
Two-pass hash-based 조인방법은반복테이블 (inner table) 의데이터양이방대하여임시공간의범위내에서관리할수없을때사용된다. 기준테이블 (outer table) 과반복테이블은모두동일한 hash 함수를사용하여분할되어임시공간의여러테이블에저장된다. 그런다음, 각임시테이블끼리조인조건을검사함으로써반복테이블 (inner table) 의동일한디스크페이지접근확률을높인다. Inverse hash-based join 은 SEMI JOIN, ANTI JOIN 또는 LEFT OUTER JOIN 의처리를위해서만사용되며, 기준테이블 (outer table) 보다반복테이블 (inner table) 이상대적으로클경우선택될확률이높다. 각조인의수행비용은개략적으로 Access cost + Disk I/O cost 로계산된다. * 관련힌트 : USE_HASH, USE_ONE_PASS_HASH, USE_TWO_PASS_HASH Merge 조인계열 Merge 조인방법은두테이블의데이터가정렬될경우매우 효율적인방법이다. 이방법에는기준테이블과반복테이블의 개념이없으며양쪽테이블을순차적으로진행하면서조인조건을 검사한다. Merge 조인이사용되기위해서는양쪽테이블이모두조인키를 기준으로정렬되어야한다. 따라서아래와같이각각의테이블이 인덱스를통해정렬되어있는경우에선택될확률이높다. Index on T1(i1), Index on T2(a1) SELECT * FROM T1, T2 WHERE T1.i1 = T2.a1; Merge 조인의수행비용은개략적으로 Access cost + Disk I/O cost 로계산된다. * 관련힌트 : USE_MERGE 70 Performance Tuning Guide
물리적실행계획생성 마지막으로옵티마이저는물리적실행계획트리를생성한다. 물리적실행계획트리는쿼리프로세서의실행기 (Executor) 가쿼리를수행하는단위작업인실행노드들로구성되며, 실행기는실행계획트리의실행노드들을따라가며쿼리를수행한다. 실행노드는자식노드의개수와중간결과의저장여부에따라다음과같이구분된다. 단일비저장노드 (Unary Non-materialization Node): 하나이하의자식노드를가지며, 해당기능을수행하기위해별도의저장공간을필요로하지않고하나의레코드만을관리하는노드이다. 단일저장노드 (Unary Materialization Node) : 하나이하의자식노드를가지며, 해당기능을수행하기위해별도의저장공간을필요로하는노드이다. 이진비저장노드 (Binary Non-materialization Node) : 두개의자식노드를가지며, 해당기능을수행하기위해별도의저장공간이필요하지않다. 이진저장노드 (Binary Materialization Node) : 두개의자식노드를가지며, 해당기능을수행하기위해별도의저장공간을필요로한다. 다중비저장노드 (Multiple Non-materialization Node) : 두개이상의자식노드를가지며, 해당기능을수행하기위해별도의저장공간을필요로하지않는다. 이러한구분에따라 ALTIBASE HDB 에는다음표와같은물리연산자가존재한다. 각실행노드에대한상세한설명은 EXPLAIN PLAN 장에서설명한다. 구분노드이름기능다양한액세스방법을사용해서테이블에서데이 SCAN 터검색액세스방법으로 filtering 되지않는데이터의 FILTER filtering 처리단일 PROJECT 프로젝션처리비저장 GROUPING 그룹처리노드 AGGREGATION Aggregation 연산수행 VIEW 뷰레코드구성 VIEW-SCAN 임시저장뷰에대한검색 COUNT 특수 COUNT(*) 의처리 쿼리옵티마이저 71