SQL 튜닝 및 문제 해결을 위한 권고사항
=====================================



PURPOSE
-------
이 문서는, SQL 문장을 튜닝하는데 도움이 될 수 있는 정보들을 포함하고
있다. SQL 튜닝은 그 자체로 방대한 주제이며, 이 문서에서는 그 중
주요 부분에 대한 일부 도움이 되도록 구성하는데 촛점을 두었다.

Explanation
-----------
차례 : SQL 성능 문제의 원인
===========================

1. 튜닝이 부족한 SQL.
2. 디스크 성능상의 문제, 디스크에서 발생하는 경합.
3. 불필요한 소팅 작업.
4. SQL 처리 초반의 조건 적용. (late row elimination)
5. 지나치게 잦은 파싱 작업.
6. 적절한 인덱스의 부재 / 잘못된 인덱스 선택.
7. 부적절한 실행 계획 또는 부적절한 join 순서 선택.
8. import시 테이블에 대한 통계 정보가 부정확하여 export 전에
비해 성능 저하
9. CBO 사용 시, 지나치게 많은 sample rate.
10. 컬럼 데이터의 비균등성. (data skew)
11. 새로운 기능이 CBO에서만 사용 가능함.
12. ITL에 대한 경합


진단 / 해결 방안
================

1. 튜닝이 부족한 SQL
흔히, 문제의 원인 가운데 SQL 문제 자체가 적절하게 구성되지 않은
것이 한 요인인 경우가 있다. 만약 시스템상의 문제를 감지하게 된다면,
먼저 statspack (또는 utlbstat/utlestat) 등을 사용하여 데이터베이스
환경 구성과 관련된 문제를 먼저 살펴보는 것이 바람직하다.
시스템 구성 및 성능과 관련 문서는 다음 문서들을 참조할 것.

SGA TUNING SCRIPT
CLIENT/SERVER 환경에서의 PERFORMANCE TUNING
UTLBSTAT 과 UTLESTAT 사용법
STATSPACK 개관 및 설치, 구성 방법
STATSPACK>수행방법


데이터베이스 구성상의 문제점에 대한 튜닝을 진행하여, 어느정도
수준까지 맞추어 지면, 자원을 가장 많이 사용하는 select 문장을
다음과 같은 방식으로 찾아 낼 수 있다.

( 비슷한 리포트를 OEM의 Tuning Pack을 통해서도 얻을 수 있다 )

SELECT address, SUBSTR(sql_text,1,20) Text, buffer_gets, executions,

buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 100000
ORDER BY 5;

위에서 'buffer_gets' 값을 > 100000 으로 비교한 것은 일반적인 지침이
아니며, 시스템 특성에 따라 비교 해야 하는 값을 지정하여야 한다. 일부
시스템에서 100000 block buffer 이상을 사용하는 SQL 문장이 나타나지
않는 다고 해서, 튜닝을 해야 하는 SQL 문장이 없다는 것을 의미하지는
않는다. 이 값을 통해, select 문장을 수행하는 과정에서 얼마나 많은 row가
처리되는지를 알 수 있다.

위에서 ADDRESS 값으로 v$sqltext 뷰와 join을 시켜 전체 SQL 문장을 알아
볼 수 있다.

SELECT sql_text FROM v$sqltext WHERE address = '...' ORDER BY piece;

SQL 전체 문장을 판별한 후에는, 그 SQL을 튜닝하여, 처리 과정에서
자원을 덜 사용하는 방향으로 접근할 수 있다.

만약 문제 상황이 CPU 자원을 많이 사용하는 것과 연관되어 있다면,
각 세션에 대한 CPU 자원 활용 정보를 조사하여, 해결책을 도모해야 한다.
v$sesstat 뷰를 이용하여, CPU 자원을 많이 사용하는 세션 및 해당
세션에서 실행되고 있는 SQL 문장을 다음과 같은 방법을 통해
판별할 수 있다.

1) 'CPU used by this session'이라는 통계 정보의 refernece number를
확인한다.

SELECT name ,statistic#
FROM v$statname
WHERE name LIKE '%CPU%session';

NAME STATISTIC#
----------------------------------- ----------
CPU used by this session 12

2) 그 다음에, 어느 세션이 CPU를 가장 많이 사용하는지를 조사한다.

SELECT * FROM v$sesstat WHERE statistic# = 12;

SID STATISTIC# VALUE
---------- ---------- ----------
1 12 0
2 12 0
3 12 0
4 12 0
5 12 0
6 12 0
7 12 0
8 12 0
9 12 0
10 12 0
11 12 0
12 12 0
16 12 1930

3) 세션에 대한 자세한 정보를 조사한다.

SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG
FROM v$sqlarea a, v$session s
WHERE sid = 16
AND s.sql_address = a.address
AND executions > 0
ORDER BY 5;

4) v$sqltext에 대한 조회를 통해, 해당 세션의 SQL 문장 전체를
조사한다.

5) 질의에 대한 실행 계획을 조사한다. Autotrace는 내부 처리
과정을 조사하는데 유용한 툴이다. 참고



2. 디스크 성능 상의 문제, 디스크에서 발생하는 경합

statspack 또는 BSTAT/ESTAT을 사용하거나, O/S의 I/O 관련 자료를
분석하는 것이 문제 해결에 도움이 된다. 어떤 SQL문장 실행을 통해
발생하는 I/O의 양은, 시스템에 다른 작업이 없는 상태에서 실행을
시켜야 정확한 값을 알 수 있다.
I/O 관련 사항을 모니터 하는데 도움이 되는 또다른 접근 방법은
오라클에 10046 Level 8 trace 를 걸어, 특정 세션에 대한 모든 wait
상황을 추출해 내는 것이다. 특정 세션에 대한 10046 이벤트는,
다음과 같은 방법으로 걸 수 있다.

alter session set events '10046 trace name context forever, level 8';

I/O에 대한 상황은 트레이스 파일의 결과 가운데 I/O와 관련된
wait 상황을 분석하여 알 수 있다. 다음은 그 예이다.

'db file sequential read' (Single-Block i/o - Index, Rollback Segment, Sort)
'db file scattered read' (Multi-Block i/o - Full table Scan).

시간과 관련된 정확한 통계치를 얻기 위해서는 TIMED_STATISTICS = TRUE
으로 지정되어 있어야 한다. 그렇지 않으면, 비교값이 의미가 없게 된다.

만약, bind variable 값도 조사를 하려면, level 12 trace를 사용하면
알 수 있다.




3. 불필요한 소팅 작업

첫번째로 확인하여야 할 사항은, 처리 결과를 얻기 위해 정말 소트 작업이
필요한지 여부를 확인하는 것이다. 만약 소트 작업이 반드시 필요하다면,
소트 작업이 디스크에서 발생하지 않도록 충분한 메모리를 할당하여
I/O 문제를 유발 시키지 않도록 조치 하는 것이 바람직하다.

소트 작업은 다음과 같은 측면에서 자원을 매우 많이 사용하는 작업이다.

- 높은 CPU 사용
- 많은 량의 디스크 사용 가능성

SQL 문장 처리시, 소트 작업이 가능한 후반부에 처리 될 수 있도록
조정할 필요가 있다. 그 이유는, 최대한 적은 row에 대해서만
소트 작업이 수행되도록 하는 데 있다.

다음 사항에 유념할 것 :

- 인덱스를 사용하여, 이미 소트가 된 순서의 데이터를 액세스
할 수 있음

- Sort merge join 수행에는 반드시 소트 작업이 수반됨.

- 실제 소트를 피할 수 있는 경우가 있다. 이 경우 explain plan
에 NOSORT라고 나타남.

결과적으로:

- 소트 작업을 메모리에서 수행 할 수 있도록 sort area size를
증가시킬 것.

- 질의 처리에서 좀더 적은 수의 row가 처리 되도록 하여 소트
작업시 처리되는 row 갯수를 줄일 것.

- 소트 작업을 피하기 위해, 인덱스를 사용하여 row를 읽을 경우
인덱스에 정렬된 순서대로 데이터를 액세스 할 수 있다.

- 버퍼캐쉬를 sort block으로 채우지 않도록 하기 위해
sort_direct_writes 를 사용한다.

- PRO*C를 사용한다면, release_cursor=yes 를 사용한다. 이것은
점유하고 있는 temporary sement를 release 시키는 역할을
한다.

4. SQL 처리 초반의 조건 적용 (Late row elimination)

질의 처리는, 많은 양의 row가 SQL 처리 초반에 많이 제거 될 경우
처리 속도가 빨라지게 되나. 이것은, 처리 중간 과정에서 필요하지
않은 row의 데이터에 대한 불필요한 비교 처리를 피할 수 있기
때문이다. 속도 상의 개선 효과가 가시적이지 않더라도 CPU 자원
활용 측면에서 훨씬 유리하다.

만약, SQL 처리 초반에 결과와 관련된 row를 일찍 제외 시킬 수 있
는 조건을 적용 시킨다면, 후속 처리 시간이 현격하게 단축된다.



5. 지나치게 잦은 파싱 작업

지나치게 많은 파싱 작업 (over parsing)은, 커서가 공유 되지
않는 다는 것을 의미한다. 만약 SQL 문장이 여러번 참조 된다면
SQL을 파싱시킨 결과가 공유되는 것이, 매번 파싱을 수행하는
것보다 훨씬 유리하다.
관련 문서:



6. 적절한 인덱스의 부재 / 잘못된 인덱스 선택

인덱스가 키 컬럼에 없을 경우, 데이터를 가져오기 위해서는 full
table scan 작업을 수행하여야 한다. 일반적으로, 인덱스는
질의 처리를 위해 선별성이 높은 조건을 효과적으로 지원하기 위해
생성시킨다.

만약, 선별성이 적은 인덱스가 선택된다면, 다음과 같은 조치를
취하는 것을 고려 하여야 한다.

RBO
- 인덱스의 우선 순위가 동일할 경우, row cache 순서에 따라
인덱스를 선택한다.

CBO
- 테이블을 보다 높은 sample size로 analyze 시킨다.
- 컬럼 데이터가 균등하게 분포되어 있지 않다면, 히스토그램을
추가 시킨다.
- 힌트를 사용하여 사용하고자 하는 인덱스를 사용하도록 지정한다.

질의 처리 시 인덱스의 유용성은 join의 종류 및 join 순서에
따라 다르다. 참고



7. 부적절한 실행 계획 또는 부적절한 join 순서 선택

잘못된 실행 계획이 채택 된 경우, 적절한 조정 작업을 수행하여야
한다.

만약 문제 원인이 부적절한 join 순서에 있다면, join의 순서를
한눈에 알아 볼 수 있는 그림을 그려 보는 것이 유용하다.

(예)
A-B-C-D
|
E-F

위 그림은 시각적으로 join 순서를 알 수 있게 해 주며, 누락된
join을 확인하는데 도움을 준다. 실행 계획을 튜닝하는데 있어,
join 순서별로, 처리되는 row의 갯수를 조사하여, join 순서를
조정하는 것이 바람직 하다.


8. import 시 테이블에 대한 통계 정보가 부정확하여 export 전에
비해 성능 저하

Oracle 8i 전 버젼에서는, 테이블이 export 될 당시 analyze 된
테이블에 대해서는 import시 자동으로 analyze estimate statistics
를 수행하였다. 이것은 export 한 데이터를 import 할 경우 동일한
질의에 대한 처리 속도가 다를 수 있는 원인이었다.

Oracle 8i부터는, export할 때, 테이블 관련 통계 정보를 동시에
export 할 수 있는 기능 뿐 아니라, sampling frequence를 지정
할 수 있는 옵션이 추가 되었다.

9. CBO 사용 시, 지나치게 많은 sample rate

만약 CBO에서 정확한 통계 정보를 가지고 있지 않다면, 적절한
실행 계획을 수립할 수 없게 된다. 일반적으로 5% 정도의 sample
size로도 충분한 통계 정보를 얻을 수 있으나, 일부 경우 좀 더
세밀한 sample size를 지정하여야 할 경우가 있다.
관련된 권고안은 에 기술되어 있다.


10. 컬럼 데이터의 비균등성.

만약 컬럼 데이터의 분산이 균등하지 않다면, 히스토그램
형태의 통계 정보를 사용하는 것을 고려하여야 한다. 히스토그램
통계 정보는 균등하게 분포된 데이터에 대해서는 도움이 되지
않으며, 컬럼과 비교할 조건이 정해져 있지 않을 경우 (binding
variable) 에도 도움이 되지 않는다. 참고



11. 새로운 기능이 CBO에서만 사용 가능함.

새로운 기능 대부분이 RBO에서는 사용할 수 없기 때문에 CBO를
사용하여야 하는 경우가 있다.

- 질의 처리 시 테이블 별 degree of parallelism 지정

- Index-only table (IOT)

- Partition Table

- Materialised view

관련 정보는 에 자세히 기술되어 있다.


12. ITL에 대한 경합

ITL에 대한 경합은, 각 블럭에 충분한 량의 Interested
Transaction List가 실제 발생하는 update를 지원할 수 있을
정도로 충분하지 않을 경우 발생할 수 있다. 이와 같은
문제는 export 와 import 작업을 수행한 이후 나타날 수 있는데,
이것은 블럭에 대해 update할 수 있는 공간이 충분히 남아 있지
않고 ITL 값이 증가되지 않은 경우 흔히 발생한다.


출처 : Technical Bulletin (Korean)