1. SQL문장 응답속도 향상을 위한 고려사항
2. SQL문장의 효율향상 방안
응답속도 향상을 위한 튜닝(TUNING)방법론에 있어서 SQL문장 튜닝을
가장먼저 고려해야 한다.
정보시스템 성능저하의 가장큰 요인이 개발자의 잘못된 SQL문장 때문이라고
해도 과언은 아니다. 같은량의 DATA를 가지고 같은일을 하는 SQL문장이
최적화되면 최소 10배에서 100배까지 응답속도의 차이를 나타내고 있기 때문이다.
어떻게 하면 효율적으로 SQL문장을 사용할 수 있고, 잘못사용하고 있는
SQL문장들은 어떻게 하면 고효율을 내도록 바꿀수 있는지에 대한 방법론을
기술하겠다.
1. SQL문장 응답속도 향상을 위한 고려사항
1.1 SQL문장 사용시 고려사항
APPLICATION PROGRAM을 개발할때 SQL문장을 활용함에 있어 개발자는
다음과 같은 사항을 숙지 하여야 한다.
1)SQL은 그자체가 하나의 APPLICATION
2)SQL은 사용방법에 따라 크게 확장
3)SQL은 집합개념을 바탕으로한 비절차형 언어
4)동일한 실행결과를 추출하는 다양한 형태의 SQL이 존재
5)ACCESS PATH에 따라 PERFORMANCE차이가 매우 크다
6)실행결과보다 처리과정에 유의해서 사용
1.2 SQL문장의 고효율을 위한 고려사항
SQL문장이 고효율을 발휘하기 위해서는 다음과 같은 항목을
점검하여야 한다.
1)INDEX를 활용한 성능향상
2)JOIN방법개선을 통한 성능향상
3)실행경로 최적화로 인한 성능향상
4)부분처리를 활용한 성능향상
2. SQL문장의 효율향상 방안
2.1 INDEX를 활용한 성능향상
INDEX는 TABLE내에서 ROW를 신속하게 찾아내기 위해 사용된다.
INDEX는 KEY와 ROWID로 구성된다. KEY는 ROW에 있는 한개의 COLUMN이거나
혹은 COLUMN의 조합이다. RDBMS의 INDEX 엔트리는 B*-TREE메카니즘을 사용해
저장되며 이는 KEY값에 가장짧은 접근경로를 보장한다. KEY값을 찾아내기
위해 필요한 I/O는 최소이고, 일단 발견되면 ROWID는 직접TABLE에 있는 ROW를
찾는데 사용된다.
2.1.1 INDEX의 적용기준
INDEX의 적용기준은 다음과 같은 기준으로 선정한다.
1)6 BLOCK이상의 TABLE에 적용(6 BLOCK이하는 연결고리만)
2)COLUMN의 분포도가 10~15% 이내인 경우 적용
3)분포도가 범위이내이더라도 절대량이 많은 경우에는 단일테이블 클러스터링을
검토할것
4)분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용
5)INDEX만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도
적용할수 있슴(손익분기점 : APPLICATION TUNING)
1 컬럼값의 평균 ROW수
분포도 = --------------- * 100 = --------------------- * 100
컬럼값의 종류 테이블의 총 ROW수
2.1.2 INDEX의 선정기준
INDEX를 선정하고자 할때에는 다음과 같은 기준을 따를때 최상의 효율을
발휘할 수 있다.
1)분포도가 좋은 COLUMN은 단독적으로 생성하여 활용도 향상
2)자주 조합되어 사용되는 경우는 결합INDEX 생성
3)각종 ACCESS 경우의 수를 만족할 수 있도록 INDEX간의 역활분담
4)가능한 수정이 빈번하지 않는 COLUMN
5)기본키 및 외부키(조인의 연결고리가 되는 COLUMN)
6)결합INDEX의 COLUMN순서 선정에 주의
6.1)항상 사용하는가?
6.2)항상 '='로 사용되는가?
6.3)분포도가 좋은 COLUMN우선
6.4)SORT순서는?
6.5)어떤 COLUMN을 추가? (후보선수)
7)반복수행(LOOP 내)되는 조건은 가장빠른 수행속도를 내게 할 것
8)실제 조사된 ACCESS 종류를 토대로 선정 및 검증
2.1.3 INDEX의 선정시 고려사항
INDEX를 선정하고자 할때 고려사항은 다음과 같다.
1)새로 추가된 INDEX는 기존 ACCESS경로에 영향을 미칠수 있음
2)지나치게 많은 INDEX는 오버헤드를 발생
3)넓은 범위를 INDEX로 처리시 많은 오버헤드 발생
4)옵티마이저를 위한 통계데이타를 주기적으로 갱신
5)INDEX의 개수는 테이블의 사용현태에 따라 적절히 생성
6)분포도가 양호한 COLUMN도 처리범위에 따라 분포도가 나빠질수 있음
7)INDEX의 사용원칙을 준수해야 INDEX가 사용되어짐
8)조인(JOIN)시에 INDEX의 사용여부에 주의
2.1.4 INDEX의 선정 정차
INDEX를 선정하고자 할때는 다음과 같은 절차를 따라야 한다.
1)해당 테이블의 ACCESS 유형조사
2)대상 COLUMN의 선정 및 분포도 분석
3)반복 수행되는 CRITAICAL ACCESS PATH의 해결
4)클러스터링 검토
5)INDEX COLUMN의 조합 및 순서의 결정
6)시험생성 및 테스트
7)수정이 필요한 APPLICATION 조사 및 수정
8)일괄적용
2.1.5 EQUAL이 INDEX에 미치는 영향
검색조건절에 EQUAL(=)이 있을때 결합INDEX를 생성하면 TABLE을 직접
검색하는 횟수가 줄어든다.
2.1.6 추가된 INDEX COLUMN의 역활
기존에 생성된 INDEX에 많이 사용하는 COLUMN을 추가함으로써 성능향상을
가져올 수도 있고, 오히려 성능저하를 시킬수도 있다.
2.1.7 INDEX를 사용하지 않는 경우
기존에 INDEX를 효율적으로 만들었음에도 불구하고, 개발자의 의도와는
달리 INDEX를 사용하지 않는 경우가 있다.
1)INDEX COLUMN의 변형
SELECT *
FROM DEPT
WHERE SUBSTR(DNAME, 1,3) = 'ABC';
<수정후>
SELECT *
FROM DEPT
WHERE DNAME LIKE 'ABC%';
2)INDEX COLUMN의 변형(INTERNAL)
SELECT *
FROM DEPT
WHERE DNAME = 10;
<수정후>
SELECT *
FROM DEPT
WHERE DNAME = '10';
2)NOT OPERATOR 사용
SELECT *
FROM DEPT
WHERE DNAME <> 'ABC';
<수정후>
SELECT *
FROM DEPT
WHERE NOT EXISTS (SELECT DNAME
FROM DEPT
WHERE DNAME = 'ABC');
3)NULL, NOT NULL
SELECT *
FROM DEPT
WHERE DNAME IS NOT NULL;
SELECT *
FROM DEPT
WHERE DCODE IS NOT NULL;
<수정후>
SELECT *
FROM DEPT
WHERE DNAME > ' ';
SELECT *
FROM DEPT
WHERE DCODE > 0;
2.1.8 COST_BASED와 RULE_BASED
OPTIMIZER에는 COST_BASED와 RULE_BASED의 두가지 종류가 있으며,
RULE_BASED에서는 INDEX가 지정되어 있으면 INDEX를 사용하지만,
COST_BASED에서는 TABLE내의DATA의 분포도에 따라 INDEX를 사용하기도
하고, 사용하지 않는등 OPTIMIZER에 의해 취사 선택된다.
2.1.9 OPTIMIZER의 취사 선택
OPTIMIZER에서 COST_BASED로 사용할 경우 수집된 통계정보에 따라 COST가
작은쪽을 선택하게 되며 이에 따라서 INDEX가 사용되지 않기도 하고
일부만 사용하기도 한다.
2.2 JOIN 방법개선을 통한 성능향상
두개이상의 TABLE을 JOIN해서 사용할때 JOIN방법에 따라서 수행속도의
차이는 현저히 나타난다.
즉, 처리결과는 같지만 연결하기 위하여 처리하는 일량에서 차이가 난다.
2.3 실행경로 최적화로 인한 성능향상
개발자가 작성한 SQL문장이 아무리 효율적으로 작성하였다고 하더라도
OPTIMIZER에 의해 실행경로가 변경될 경우 고효율을 발휘할수 없게되므로
실행경로를 확인해볼 필요가 있다.
=> 응답속도가 느린 SQL문장의 경우 실행경로의 확인이 필수항목
2.4 부분처리
부분적으로 가공하여 즉시 처리한 후, 다음처리를 위하여 준비하는 방식
2.4.1 부분처리의 사용 기준
1)조건을 만족하는 전체집합이 아닌 일부분만을 ACCESS
2)DATA량이 많아도 PERFORMANCE에 지장이 없고, 오히려 향상
3)INDEX나 CLUSTER를 적절히 활용하여 부분범위 처리방식으로 유도
4)QUERY를 이원화하여 일부분씩 SCAN하도록 유도
5)TABLE은 ACCESS하지 않고 INDEX만 사용하도록 유도
6)ROWNUM을 활용
2.4.2 INDEX를 활용한 부분처리
RDBMS를 사용하는데에 있어서 INDEX의 활용은 필수적인 요소이지만,
특히 부분범위 처리를 하고자 할때에는 더욱 그효과를 발휘할 수 있다.
우리가 어떤 작업의 결과를 특정한 값에 의한 정렬된 값의 순의로 얻기를
희망한다고 가정해보자
RDBMS를 사용하여 개발된 APPLICATION에서 ORDER BY 구문을 사용하면,
작업한 결과를 한번 더 SORT를 통하여 정렬작업이 수행되므로,
응답속도는 정렬작업에 소용되는 시간만큼 더욱 느려질 뿐만 아니라,
전체가 완전히 처리될때까지 기다릴수 밖에 없다.
그러나 INDEX를 활용하면 부분 범위 처리를 할수 있을뿐만 아니라,
INDEX에 의해 미리 정렬된 값들을 보유하고 있기 때문에 정렬에 소요되는
시간도 줄일수 있게되어 응답속도는 훨신 빨라질것이다.
2.4.3 INDEX만으로 부분처리
어떤 처리결과를 GROUP화 하여 계산된 결과치를 얻기를 원하는 경우에
RDBMS를 이용하는 처리에서 사용되는 문장이 GROUP BY구문이다.
이때 INDEX만 처리하여 결과를 얻는 경우와 TABLE을 ACCESS하여
원하는 결과를 얻는 경우와는 응답속도에서 상당한 차이가 있다.
즉, INDEX만 처리하여 결과를 얻는경우는 부분범위를 처리할 수 있고,
TABLE을 ACCESS하여 결과를 얻는 경우는 전체 범위를 처리할 수 밖에 없다.
2.4.4 MAX 이용으로 부분처리
한 TABLE의 특정한 COLUMN이 MAX인 값을 얻고자 할때, 역순으로 정렬된
INDEX를 이용하면 보다 효율적인 응답속도를 얻을수 있다.
즉, TABLE을 ACCESS하여 MAX값을 얻고자 할때에는 TABLE을 검색후
처리된 결과를 SORT하여 얻어진다.
그러나 역순으로 정렬된 INDEX를 이용하면 검색된 첫번째 ROW가
최대값이므로 다른 ROW들은 검색할 필요가 없어지며, 부분범위를
처리할 수 있게된다.
2.4.5 EXISTS를 이용한 부분처리
PL/SQL에서 어떤 SQL문장을 처리하기전에 TABLE을 검색하여
조건에 해당하는 ROW가 있으면 갱신을 수행하고, 해당하는
조건의 ROW가 없으면 입력하는 처리를 하는 경우가 있다.
이때 TABLE에 해당하는 조건을 COUNT하여 이용하는 방법과
EXISTS문장을 이용하는 방법이 있는데, EXIST문장을 이용하면 보다
효율적인 응답속도를 얻을 수 있다.
즉, COUNT를 이용하는 방법은 TABLE에 해당하는 조건을 전부 검색 하지만
EXISTS의 처리는 해당하는 조건이 한건만 검색되면 처리를 종료한다.
2.4.6 ROWNUM을 이용한 부분처리
PL/SQL에서 어떤 SQL문장을 처리하기전에 TABLE을 검색하여
조건에 해당하는 ROW가 있으면 갱신을 수행하고, 해당하는
조건의 ROW가 없으면 입력하는 처리를 하는 경우가 있다.
이때 TABLE에 해당하는 조건을 COUNT하여 이용하는 방법과
앞서 설명한 EXISTS문장과 동일한 방법으로 ROWNUM문장을 이용하는 방법이
있는데, ROWNUM문장을 이용하면 보다 효율적인 응답속도를 얻을 수 있다.
즉, COUNT를 이용하는 방법은 TABLE에 해당하는 조건을 전부 검색 하지만
EXISTS의 처리는 해당하는 조건이 한건만 검색되면 처리를 종료한다.
출처 : http://cafe.naver.com/target 카페명 : Are you Ready ??