★★
Oracle의 Hint
→ Optimizer에게 작업방법을 알려줄 수 있는 수단임.
따라서 힌트를 사용하게 되면 대부분 힌트에 의해서
optimizer는 작업을 하게 됨. (SQL 튜닝이 쉬워짐)
한편, 힌트를 갖고 있는 SQL의 실행계획은 고정됨.
(힌트의 문제점)
㈜ 사용법
- Hint의 영향력은 hint를 갖고 있는 SQL에 한정됨
- 하나의 SQL은 배타적인 관계에 해당하는 hint가 아니라면
개수에 제한없이 hint를 사용할 수 있음
- Hint는 syntax 에러가 없음
- /* + hint */ 또는 -- + 와 같이 "*" 와 "+" 사이에
또는 "-" 와 "+" 사이에 빈칸이 있어선 안됨
- Hint에 의해서 테이블을 지칭할 때, 테이블에 대해서
alias를 사용 중이라면 hint도 반드시 alias를 사용해야 함
Part 2. 부분범위 처리
부분 범위 처리(Partial Range Scan)는 Sort 없이, 조건에 의해
일치하는 데이터를 한 번에 모두 처리하기 보다는 일부분의 데이
터만을 처리토록 함으로써 사용자의 요구사항을 만족시킬 수 있
기 위한 데이터 처리방법이라고 할 수 있다.
특히, 이러한 개념의 활용은 대용량 데이터를 다루는 데 있어서
무엇보다 중요하다고 할 수 있다.
따라서 Part 2 에서는 주어진 사례를 통해서 부분 범위 처리에
대해 구체적으로 알아보고자 한다.
★★★
부분범위 처리(Partial Range Scan)란?
→ Sort 없이 조건에 부합하는 전체 데이터 중에서 일부분만을
사용자에게 즉시 보여주고 나머지 데이터에 대한 처리를 잠시
수행하지 않도록 하는 데이터 처리 방법을 뜻한다.
ex. 게시판 관련 "Paging" 처리
★ 개인별 과제 실습
- 부분범위 처리(Partial Range Scan) 활용을 통한 최적화
====================================================
데이터 존재여부를 확인하고자 할 때, 테이블로부터 조건에
해당되는 모든 데이터를 찾아서 사용할 필요는 사실상 없다.
만약에 조건에 해당되는 데이터가 매우 큰 데이터가 된다고
하면 불필요하게 데이터를 읽는 경우가 되는데, 이럴 때는
무엇 보다도 조건에 해당되는 데이터를 정확하게 한 건만
읽도록 해야 한다. ===============
더군다나, 이러한 목적에서 사용 중인 count(*)하는 SQL이
Loop 내에서 반복적으로 사용되고 있다면 더욱 그렇다.
이와 함께 Loop 내에서 사용되고 있는 MIN/MAX 처리를 위한
SQL도 튜닝이 되어야 한다.
★★★
개인별 과제#01 실습
- 부분범위 처리(Partial Range Scan) 활용을 통한 최적화
====================================================
① Loop내에 있는 COUNT(*)하는 부분
데이터 존재여부를 확인하고자 할 때, 테이블로부터 조건에
해당되는 모든 데이터를 찾아서 사용할 필요는 사실상 없다.
만약에 조건에 해당되는 데이터가 매우 큰 데이터가 된다고
하면 불필요하게 데이터를 읽는 경우가 되는데, 이럴 때는
무엇 보다도 조건에 해당되는 데이터를 정확하게 한 건만
읽도록 해야 한다. ===============
더군다나, 이러한 목적에서 사용 중인 count(*)하는 SQL이
Loop 내에서 반복적으로 사용되고 있다면 더욱 그렇다.
② Loop내에 있는 MIN/MAX 처리 부분
MIN과 MAX 값을 구하고자 할 때, 테이블로부터 조건에 해당
되는 모든 데이터를 찾아서 사용할 필요가 없다.
분명히 MIN과 MAX는 각각 한 건씩 밖에는 없기 때문이다.
===========================================================
* COUNT(*)부분에 대한 최적화
SELECT COUNT(*) INTO H_CNT
FROM EC_COURSE_SQ
WHERE COURSE_CODE = H_COURSE_CODE
AND YEAR||'' = '2002'
AND ROWNUM = 1;
또는
SELECT /*+ INDEX(B EC_COURSE_SQ_PK) */
COUNT(*) INTO H_CNT
FROM EC_COURSE_SQ B
WHERE COURSE_CODE = H_COURSE_CODE
AND YEAR = '2002'
AND ROWNUM = 1;
* MIN과 MAX 부분에 대한 최적화
특히, 부분범위 처리가 되기 위해서는 MIN과 MAX를 분리해야 함.
SELECT /*+ INDEX_ASC(B EC_COURSE_SQ_PK) */
COURSE_SQ_NO
INTO H_MIN_SQ_NO
FROM EC_COURSE_SQ B
WHERE COURSE_CODE = H_COURSE_CODE
AND YEAR = '2002'
AND ROWNUM = 1;
SELECT /*+ INDEX_DESC(B EC_COURSE_SQ_PK) */
COURSE_SQ_NO
INTO H_MAX_SQ_NO
FROM EC_COURSE_SQ B
WHERE COURSE_CODE = H_COURSE_CODE
AND YEAR = '2002'
AND ROWNUM = 1;
→ 부분범위 처리 적용됨으로 인해서 퍼포먼스 개선은 될 수 있었으나,
Loop 내에서 SQL을 여러 번 실행시켜야 하는 문제가 발생함.
참고) 조인 활용
→ Loop 내의 SQL은 제거할 수는 있지만, MIN과 MAX에 대한
부분범위 처리 적용이 불가능하다.
SELECT COURSE_CODE, COURSE_NAME,
MIN_SQ,
DECODE(CHK,'E00', DECODE(MIN_SQ,MAX_SQ,99), MAX_SQ) AS MAX_SQ,
CHK
FROM (SELECT COURSE_CODE, COURSE_NAME,
MIN_SQ, MAX_SQ,
DECODE(MIN_SQ,0,'N00','E00') AS CHK
FROM (SELECT A.COURSE_CODE, A.COURSE_NAME,
NVL(MIN(B.COURSE_SQ_NO),0) AS MIN_SQ,
NVL(MAX(B.COURSE_SQ_NO),0) AS MAX_SQ
FROM EC_COURSE_SQ B, EC_COURSE A
WHERE A.COURSE_CODE = B.COURSE_CODE(+)
AND B.YEAR(+)||'' = '2002'
GROUP BY A.COURSE_CODE, A.COURSE_NAME));
[사례연구 #05] MIN과 MAX 처리
부분범위 처리가 되지 않을 경우에, MIN과 MAX를 처리함에 있어서
발생할 수 있는 문제점을 짚어 보고, 부분범위 처리를 하고자 할 때
필요한 것이 무엇이며 또한 이를 적용함으로써 MIN과 MAX에 대한 처
리가 어떻게 최적화 되는지를 확인함.
※ 특히, 부분범위 처리가 가능하려면, MIN 또는 MAX 값을 구하고자
하는 컬럼의 데이터를 갖고 있는 인덱스가 존재해야 함.
→ INDEX_ASC, INDEX_DESC 사용을 통해서 최적화 된 SQL을 작성이 가능
㈜ MS의 SQL*Server인 경우 - MAX의 최적화
SELECT TOP(1) B.COURSE_SQ_NO
FROM EC_COURSE_SQ B
WHERE B.COURSE_CODE = 14 "Suppressing" 적용
AND B.YEAR+'' = '2000'
ORDER BY B.COURSE_CODE DESC, B.YEAR DESC, B.COURSE_SQ_NO DESC;
㈜ IBM의 DB2인 경우 - MAX의 최적화
SELECT B.COURSE_SQ_NO
FROM EC_COURSE_SQ B
WHERE B.COURSE_CODE = 14 "Suppressing" 적용
AND B.YEAR||'' = '2000'
ORDER BY B.COURSE_CODE DESC, B.YEAR DESC, B.COURSE_SQ_NO DESC
FETCH FIRST 1 ROW ONLY;
* SQL*Server와 DB2의 경우, Optimizer는 Cost base이며, 위에 작성된
SQL에서의 ORDER BY로 인해서 추가로 발생하는 Sort는 없다.
★★★
Suppressing
→ 인덱스 컬럼에 변형을 가함으로써 해당 컬럼을 첫 번째로하는
결합 인덱스 및 해당 컬럼만 갖는 단일컬럼 인덱스를 사용하지
못하게 하는 것을 의미한다.
이러한 개념을 활용할 경우, 힌트가 아니어도 사용하기를
원하는 인덱스를 사용할 수 있게 할 수 있다.
모든 DBMS에서 사용할 수 있는 방법이다.
[사례연구 #06] 인덱스에 의한 Sort 대체
★★★
SORT의 문제
SQL에 의해 처리하려는 데이터를 Sort하려고 할 때,
먼저 SORT_AREA_SIZE에 지정된 만큼의 메모리를 사용하는 중에
Sort를 수행하게 된다. 그런데 만약에 SQL에 처리하려는
데이터가 메모리를 초과하게 된ㅋ다면 Disk(Temporary tablespace)
를 사용하는 중에 Sort를 수행하게 되므로써 이때 Disk I/O가
추가로 발생하게 된다.
즉, Sort에 의한 문제점으로는 대표적으로 메모리 사용에 의한 것과
Disk I/O의 추가적인 발생이라는 문제가 지적될 수 있다.
※ Temporary Tablespace가 사용되는 분야
① Sort 수행시
② HASH 조인 수행시
③ SORT/MERGE 조인 수행시
④ CREATE INDEX 시
Q. 이러한 SORT를 제거할 수 있는 수단이 무엇일까?
→ 인덱스가 그러한 수단이 충분히 될 수 있다 .
Part 3. 조인(Join)
조인 방식으로는 Nested Loops, Sort Merge, Hash 등이 있으며,
이들 조인 방식은 나름대로의 장점과 단점이 각각 있다.
그러므로 조인을 튜닝하고자 할 때, 무엇보다도 각 조인 방식 별로
튜닝 포인트를 구분하는 가운데 튜닝이 진행될 수 있어야 한다.
Part 3 에서는 인덱스의 설계 상 문제로 인한 조인의 퍼포먼스 문제
를 해결하는 것과 다양한 서브쿼리 활용을 통해서 조인의 문제를 해
결하는 사례를 다루고자 한다
[사례연구 #07] 조인 시 사용된 결합인덱스의 문제
테이블 간 조인의 연결고리(조건)로 자주 사용되는 컬럼 들을 대상으로
결합 인덱스를 구성했을 때, 결합인덱스의 설계상 문제(컬럼 순서의 문제)
로 말미암아 발생하는 퍼포먼스 문제에 대한 해결 방법을 찾고자 함.
★★★
튜닝 시 View 사용 목적
① 테이블 간 조인회수를 개선(감소)
② 테이블 간 조인순서를 제어
③ 함수의 사용회수(빈도수)를 개선(감소)
④ Update 작업의 최적화 등
★★★
View Merge
~ 종종 뷰를 갖는 SQL을 실행하는 중에 뷰가 사라지는 경우가 있다.
이러한 현상을 "view merge"라고 한다. 이러한 경우엔 뷰를 통해
얻고자 했던 퍼포먼스 향상을 얻기가 어렵다.
이 때, 실행계획 상에는 "VIEW"라는 작업(용어)가 보이질 않는다.
이와 같은 View merge를 방지하기 위해서는 다음과 같은 Keywords
가 View가 갖는 SQL에서 사용되어야 한다.
① ORDER BY, DISTINCT, GROUP BY
② Set Operators : UNION ALL, UNION, MINUS, INTERSECT
③ 그룹함수 : SUM, COUNT, AVG, MAX, MIN
④ ROWNUM
⑤ CONNECT BY
★★★
Index Scan vs. Full Table Scan
|-------------------------------------|
(0%) ↑ (100%)
(10~15%)
일반적으로, 0% 에서 많게는 15% 까지 데이터를 처리하게 될 때는
인덱스를 사용하는 것이 좋고, 그 이상의 데이터를 처리하게 될 때
는 인덱스를 사용하지 않는 것이 훨씬 좋다.
○ 조인을 위한 연결고리 컬럼을 소유하는 결합인덱스의 설계 상 문제로
인해 조인의 퍼포먼스에 문제가 있음을 나타내는 사례연구인데, 특히
연결고리로써 사용되는 컬럼(들) 일수록 결합인덱스 설계시 컬럼의 순
서상 앞에 위치할 수 있도록 하는 것이 무엇보다 중요하다.
Q. 이와같이 컬럼의 순서상 앞에 위치할 때와 그렇지 않을 경우,
차이점은 무엇인가?
A. 각 컬럼들에 대해서 '='을 사용한다면 다음과 같은 차이가
존재하게 된다.
→ "검색조건" vs. "체크조건"
★ '=' 에 대한 해석 → "검색조건" VS. "체크조건"
→ '='에 대해서 효율성이 있을 때와 효율성이 없을 때를 구분하는데
효율성이 있을 때 "검색조건"이라 하며 효율성이 없을 때 "체크조건"
이라 부른다.
1st 2nd 3rd
(예) 결합인덱스 : ( 급여년월 + 급여코드 + 사원번호 )
① WHERE 급여년월 LIKE '2002%'
AND 급여코드 = '정기급여'; <- 체크조건
② WHERE 급여년월 BETWEEN '200201' AND '200212'
AND 급여코드 = '정기급여'; <- 체크조건
③ WHERE 급여년월 >= '200201'
AND 급여년원 <= '200212'
AND 급여코드 = '정기급여'; <- 체크조건
☆ 튜닝포인트 : 체크조건 → 검색조건
WHERE 급여년월 IN ('200212','200211','200210',
'200209','200208','200207',
'200206','200205','200204',
'200203','200202','200201')
AND 급여코드 = '정기급여';
㈜ IN으로 조건을 표현할 때, 직접 값을 나열하는 경우엔 1,000개로
제한되어 있다. 이러한 문제를 해결 위해서는 날짜정보를 갖고
있는 테이블이 있다고 할 때, 서브쿼리를 통해서 해결하면 된다.
테이블 간 조인을 위해 사용되는 컬럼의 경우 주로 '=' 조건으로
조인을 수행하게 된다. 이 때 조인 조건으로 사용되는 컬럼이 결합
인덱스를 구성하고 있는 컬럼이라고 한다면 이것은 앞에 위치해 있어
야 한다. 그렇지 않으면 "검색조건"이 아닌 "체크조건"으로 사용됨으로
인해 퍼포먼스 향상을 얻기가 어렵게 된다.
★★★
결합인덱스 설계시 컬럼 순서에 대한 결정
- 다음과 같은 기준을 순서대로 적용하는 가운데 컬럼 순서를
결정하게 된다.
① Where절에서 항상(가장 많이) 사용되는 컬럼을 우선적으로 찾는다.
② 항상(가장 많이) Equal('=')로 비교되는 컬럼을 우선적으로 찾는다.
③ 분포도가 가장 좋은 컬럼을 우선적으로 찾는다.
④ 자주 이용되는 Sort의 순서로 결정한다.
⑤ 향후에 추가하게 될 컬럼을 포함할 수 있도록 한다.
㈜ 첫 번째 칼럼 부터 마지막 칼럼에 대한 순서가 결정될 때까지
① → ② → ③ → ④ → ⑤를 순차적으로 적용토록 함.
㈜ 때에 따라 ③과 ④ 기준은 적용순서를 바꾸기도 한다.
★★★
v10g 경우, GROUP BY Mechanism → Hashing 사용함.
한편, v9i 까지는 Hashing을 사용하지 않음.
따라서 v10g에서의 GROUP BY에 의한 SORT결과와 v9i까지의
SORT결과가 서로 같지 않게 된다.
이를 같게 하려면, v10g에서 다음과 같이 정의한다.
_GBY_HASH_AGGREGATION_ENABLED = FALSE
㈜ SQL 차원에서 정의하려면, /*+ NO_USE_HASH_AGGREGATION */을
사용한다.
(example)
① v9i
SQL> SELECT DEPTNO, SUM(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
DEPTNO SUM(SAL)
---------- ----------
10 7450
20 12175
30 9400
② v10g
SQL> SELECT DEPTNO, SUM(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 12175
10 7450
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 15 | 12 (9)| 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 15 | 12 (9)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 70 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> SELECT /*+ NO_USE_HASH_AGGREGATION */
2 DEPTNO, SUM(SAL)
3 FROM EMP
4 GROUP BY DEPTNO;
DEPTNO SUM(SAL)
---------- ----------
10 7450
20 12175
30 9400
★★★
Cost 계산시, Oracle 9i에서는 I/O Cost만을 사용하는데,
10g 환경에서 (I/O cost + CPU cost)를 사용한다.
만약에 CPU Cost를 비용 계산시 참조하지 않도록 하려면, 다음과
같이 한다:
* Parameter : _optimizer_cost_model = IO
참고) 힌트 : NO_CPU_COSTING
★ Hidden Parameter "_optimizer_cost_model" 값 확인
SELECT A.KSPPINM Parameter,
A.KSPPDESC Description,
B.KSPPSTVL Session_Value,
C.KSPPSTVL Instance_Value
FROM X$KSPPI A,
X$KSPPCV B,
X$KSPPSV C
WHERE A.INDX = B.INDX
AND A.INDX = C.INDX
AND A.KSPPINM = '_optimizer_cost_model';
'04번. IT 힌트얻기 > ▶ DB Tuning' 카테고리의 다른 글
| [Tuning] perfect_study_04 (0) | 2011.10.14 |
|---|---|
| [Tuning] perfect_study_03 (0) | 2011.10.13 |
| [Tuning] 분석함수의 활용 (셀프조인의 해결) (0) | 2011.10.12 |
| [Tuning] 분석함수의 활용 (셀프조인의 해결) (0) | 2011.10.12 |
| [Tuning] Scalar 서브쿼리 활용 (0) | 2011.10.12 |