본 자료는 일본 @IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#tuneorasql) 에 株式会社アゲハ加藤 猛씨가 연재한 Oracle SQLチューニング講座를 번역 재구성한 것입니다.
전회에 이어서 SQL 튜닝을 실시하기 위해서 필요한 기초지식으로서 액세스 하는 데이터·블록수에 큰 영향을 주는 결합 방법의 종류와 옵티마이져의 실행 계획의 결정에 영향을 주는 통계 정보, 힌트에 대해 설명하겠습니다.
정규화된 데이타베이스에서는 필요한 데이터를 얻기 위해서 복수의 테이블로부터 데이터를 취득하여 결합하는 처리가 많이 발생합니다. 테이블을 결합하는 방법에는 몇개의 종류가 있으며 각각의 결합 방법의 특징을 이해하는 것이 SQL 튜닝의 중요한 포인트입니다. 레코드의 액세스 방법에서 부적절한 결합 방법이 선택되었을 경우에는 액세스 하는 데이터·블록수가 증가하므로 퍼포먼스에 영향을 미칠 가능성이 있습니다.
여기에서는 대표적인 결합 방법인, 「네스티드 루프 결합」 「소트/머지 결합」 「해시 결합」에 대해 설명합니다.
◆ 네스티드 루프 결합
네스티드 루프 결합은 그림 1과 같이 실행됩니다.
 |
| 그림 1 네스티드·루프 결합 |
- (그림 1에서 DEPARTMENT 테이블)을 기동(외부)테이블로 하여 외부테이블로부터 얻을 수 있는 결합 조건열의 데이터를 기본으로 색인 스캔을 하는 테이블을 내부 테이블 (그림 1에서는 EMPLOYEE 테이블)로 한다.
- 외부테이블의 레코드를 하나하나 돌면서 내부 테이블에 액세스 해, 결합 조건을 만족하는지 검사한다.
- 결합 조건을 만족하는 레코드를 결합하고 결과를 돌려준다.
네스티드·루프 결합은, 테이블의 일부분을 결합하는 경우에 유효한 결합 방법으로 내부테이블의 결합 조건열에 색인이 작성되고 있어 그 색인을 사용하는 것으로 데이터에 효율적으로 액세스 할 수 있는 경우에 사용합니다.
RBO에서는 결합 조건열에 대한 색인이 한쪽의 테이블 밖에 존재하지 않는 경우는 색인이 존재하는 테이블이 내부테이블이 되고 쌍방으로 색인이 존재하는 경우에는 FROM구의 지정 차례가 뒤의 테이블(그림 1에서는 DEPARTMENT 테이블)가 외부테이블이 됩니다.
CBO 경우에는, 코스트注1에 근거해 외부테이블이 결정됩니다.
어느 테이블을 외부테이블로 정할까에 따라서 액세스 하는 데이터·블록수는 크게 다르므로 결합 순서가 중요한 포인트가 됩니다. 네스티드·루프 결합을 효율화하기 위해서는 결합을 시도하는 레코드수가 보다 적은 쪽을 외부테이블, 레코드수에 큰 차이가 없는 경우에는, 결합 조건열의 색인 스캔이 보다 효율적인 쪽을 내부테이블로 할 필요가 있습니다.
| 注1：코스트 코스트란 그 처리를 실시하기 위해서 필요한 자원량을 수치화한 것으로 통계 정보를 기본으로 산출됩니다. 옵티마이져가 필요로 하는 통계 정보가 취득되어 있지 않은 경우 Oracle이 가지는 디폴트치가 사용됩니다. |
소트/머지 결합은, 그림 2와 같이 실행됩니다.
 |
| 그림 2 소트/머지 결합 |
- DEPARTMENT 테이블을 결합 조건열(DEPARTMENT_ID열)로 소트 한다.
- 다음에 EMPLOYEE 테이블을 결합 조건열(DEPARTMENT_ID열)로 소트 한다.
- 각각의 테이블의 결과가 동일한 레코드를 결합하고 결과를 돌려준다.
소트/머지 결합은 테이블의 대부분의 데이터를 결합하는 경우에 유효한 결합 방법으로 그림 2와 같이 쌍방의 테이블을 결합 조건열로 소트한후 결과를 머지하여 대상 레코드를 추출합니다.
결합 조건이 (=)의 경우 보다는 효율적인 해시 결합(후술 합니다)이 사용 가능해지기 때문에 결합 대상이 많고 결합 조건이 (=)조건이 아닌(＜,＜=,＞,＞=) 경우에 사용하게 됩니다.
즉 검색 조건열에 색인이 작성되고 있어 사전에 그 색인으로 좁혀지고 있는 경우에는 결합전의 소트 처리가 불필요(색인은 데이터를 소트 해
보관 유지하고 있기 때문에)가 되기 때문에 크게 결합 처리의 퍼포먼스를 개선할 수 있습니다.
◆ 해시 결합
해시 결합은, 그림 3과 같이 실행됩니다
 |
| 그림 3 해시 결합 |
- 레코드수가 적은 테이블(그림 3에서는 DEPARTMENT 테이블)의 결합 조건열(DEPARTMENT_ID열)을 해쉬 함수로 메모리상에 해시·테이블을 작성한다.
- 또한 다른 테이블 (그림 3에서는 EMPLOYEE 테이블)의 결합 조건열(DEPARTMENT_ID열)을 해쉬 함수로 결합할 수 있을까를 해시·테이블로 확인한다.
- 해시치가 동일한 레코드를 결합하고 결과를 돌려준다
해시 결합은 결합 조건이 (=)으로 지정되어, 대량의 레코드 혹은 테이블의 대부분을 결합하는 경우에 유효한 결합 방법으로 CBO경우만 사용 가능합니다.
소트/머지 결합에 비해 사전의 소트 처리가 불필요 하므로 효율적인 결합 방법입니다. 각각의 결합 방법의 특징을 이해해 각 테이블의 레코드 건수나 검색의 조건 대상 데이터의 히트율등의 정보를 기본으로 대상의 SQL에 있어서의 최적인 결합 방법을 판단할 수 있습니다
■ 통계 정보의 취득
전편으로 설명한 것처럼 Oracle9i까지는 초기화 파라미터 「OPTIMIZER_MODE」가 디폴트인 CHOOSE의 경우 통계 정보가 취득되어 있나 없나에 따라, 옵티마이져가 사용하는 어프로치 방법(CBO,RBO)이 다릅니다.
RBO의 경우 미리 Oracle내에서 결정된 액세스 순서에 따라 실행 계획을 생성합니다만, CBO는 통계 정보를 기본으로 코스트를 계산해 실행 계획을 생성합니다. 그 때문에 SQL 튜닝을 실시하려면 어떤 옵티마이져 모드인지 , CBO의 경우에는 통계 정보가 언제 취득된 것인지를 파악해 둘 필요가 있습니다.
취득되는 주된 통계 정보
통계 정보란 테이블, 색인등의 레코드 건수나, 사용하고 있는 영역, Cardinallity注2, 데이터 분포注3 등의 데이터 특성을 나타내는 정보이며, 테이블, 색인, 혹은 열단위로 취득할 수 있습니다.구체적으로는표 1과 같은 정보를 나타냅니다.
| 注2：Cardinallity 레코드중의 중복의 값의 개수를 가리킵니다.예를 들면, 성별 데이터(남자, 여자)와 같이 값의 종류가 적은 경우에는 cardinallity가 낮다고 합니다.반대로 사원 번호와 같이 대부분의 값이 중복인 경우는 cardinallity 높다고 합니다. 注3：데이터 분포 열단위로 취득했을 경우만 수집됩니다. CBO는 값이 평균적으로 분산되어 있다란 것을 전제로 동작하므로 특정 종류의 데이터가 극단적으로 많다 등, 데이터의 편향이 큰 경우에는 적절하지 못한 실행 계획을 선택해 버리는 경우가 있습니다. 이러한 경우 열단위로 보다 상세한 통계 정보(히스토그램)를 취득하는 것이 좋습니다. |
| 항목 | 딕셔너리 열명 | |
| 테이블 통계정보 | 행수 | NUM_ROWS |
| 사용블럭수 | BLOCKS | |
| 미사용 블럭수 | EMPTY_BLOCKS | |
| 빈 영역의 평균사이즈（bytes） | AVG_SPACE | |
| 행 연쇄, 행 이행의 행수 | CHAIN_CNT | |
| 행의 평균길이(bytes) | AVG_ROW_LEN | |
| 색인 통계 정보 | 행수 | NUM_ROWS |
| 리프·블록까지의 B*Tree의 깊이 | BLEVEL | |
| 리프·블록수 | LEAF_BLOCKS | |
| 중복 하고 있지 않는 행수 | DISTINCT_KEY | |
| 색인의 값 마다의 평균 리프·블록수 | AVG_LEAF_BLOCKS_PER_KEY | |
| 색인의 값 마다의 평균 데이터·블록수 | AVG_DATA_BLOCK_PER_KEY | |
| 클러스터 계수 | CLUSTER_FACTOR | |
| 열의 통계 정보 | 열내에서 중복 하지 않는 값의 수 | NUM_DISTINCT |
| 열내의 하한치 | LOW_VALUE | |
| 열내의 상한치 | HIGH_VALUE | |
| 열내의 NULL의 수 | NUM_NULLS | |
| 열의 밀도 | DENSITY | |
| 열의 히스토그램내의 버킷수 | NUM_BUCKETS | |
| 표 1 수집되는 통계 정보 일람 | ||
취득한 통계 정보는 SYS 유저가 소유하는 데이터·딕셔너리에 저장되어 아래와 같은 딕셔너리에서 (ALL_또는, DBA_, USER_로 시작되는 뷰, 이하에서는 USER_로 시작되는 뷰 일람)로 통계 정보를 확인할 수 있습니다.
- USER_TABLES
- USER_INDEXES
- USER_TAB_COLUMNS
- USER_TAB_PARTITIONS
- USER_IND_PARTITIONS등
통계 정보가 취득되어 있지 않은 경우는 TABLE_NAME 이외는 표시되지 않습니다.
SQL> select table_name,
num_rows, blocks, avg_row_len, sample_size, last_analyzed |
| 리스트 1 통계 정보의 취득 |
통계 정보의 취득 방법
통계 정보의 취득은 DBMS_STATS 패키지 혹은 ANALYZE 커멘드에 의해서 취득할 수 있습니다. DBMS_STATS 패키지에는 퍼포먼스 개선 기능이 추가되었습니다. 예를 들면 통계 정보의 취득시간 단축을 위한 패러렐 처리에 의한 실행, 1%이하의 랜덤인 데이터 샘플링에 의한 취득, 통계 정보가 낡은 오브젝트만 재취득 등의 기능.
또한 Oracle9i에서는 DBMS_STATS 패키지에서 시스템의 통계 정보(시스템의 I/O 및 CPU 성능등)를 취득하는 것도 가능하므로 보다 최적인 실행 계획을 선택할 수 있습니다.
정보 취득에 대한 주의점
옵티마이져는 사전에 취득된 통계 정보를 사용해 실행 계획을 결정합니다.그 때문에 다음과 같은 점에 주의할 필요가 있습니다.
- 통계 정보 취득 후에 큰폭으로 데이터 건수가 증감했다
- 복수의 테이블의 결합 처리를 실행하는 경우 일부의 테이블만 통계 정보가 오래됐다.
- 복수의 테이블의 결합 처리를 실행하는 경우 일부의 테이블만 통계 정보가 취득되지 않았다. 注4
| 注4：일부의
테이블만 통계 정보가 취득되지 않은 경우 CBO에서 통계 정보가 취득되어 있지 않은 오브젝트에 액세스 했을 경우 Oracle는 하드 코딩 되고 있는 디폴트치를 사용해 코스트 계산을 실시합니다.Oracle9iR2에서는 초기화 파라미터 optimizer_dynamic_sampling를 「1」(디폴트) 이상의 값으로 설정해서 SQL 실행시에 통계 정보를 동적으로 수집하는 것도 가능하지만 SQL 실행시의 여분의 오버헤드가 되므로 가능한 한 사전에 취득하는 것이 좋다. |
상기와 같은 경우에는 최적인 실행 계획을 생성하지 못하고 퍼포먼스에 영향을 미칠 가능성이 있습니다. 그러므로 빈번히 갱신되는 테이블이나, 큰폭으로 데이터 건수가 증감하는 테이블에 관해서는 정기적으로 관련하는 오브젝트의 통계 정보를 다시 취득하는 것이 중요합니다
■ 힌트의 사용
CBO를 사용하는 옵티마이져는 최적인 실행 계획을 선택해 줍니다만 때로는 바람직하지 않은 실행 계획이 선택되는 경우도 있습니다.
예를 들면 바인드 변수를 사용하는 SQL에서는 SQL가 해석(hard parse)된 시점에서 지정되어 있던 바인드 변수의 값을 기본으로 실행 계획이 결정됩니다(bind peek 기능).공유 SQL 영역에 해당의 SQL가 남아 있는 동안은 동일한 SQL는 바인드 변수의 값에 관계없이 기존에 결정된 실행 계획을 사용하기 때문에 다른 값을 세트 한 SQL에서는 적절하지 않은 실행 계획이 되어 버릴 가능성이 있습니다.
이러한 경우 SQL에 힌트를 지정하여 풀테이블 스캔이나 색인 스캔등의 액세스 방법이나, 결합 방법/순서등을 옵티마이져에게 명시적으로 지시할 수 있습니다.
 |
| 그림 4 옵티마이져의 제안 |
튜닝에 사용하는 것이 많은 대표적인 힌트로서 이하와 같은 것이 있습니다.
| 힌트의 종류 | 힌트의 의미 | |
| 최적화 어프로치에 관한 힌트 | ALL_ROWS | 최고의 throughput가 되도록 최적화된다(풀 테이블 스캔, 소트/머지 결합이 선택되기 쉬워진다) |
| FIRST_ROWS(n) | 응답 시간을 최단으로 하도록 최적화된다(색인 스캔과 네스티드·루프 결합이 선택되기 쉬워진다) | |
| RULE | 룰 베이스의 어프로치를 선택한다 | |
| 액세스·패스에 관한 힌트 | FULL | 풀테이블 스캔을 선택한다 |
| INDEX | 색인 스캔을 선택한다 | |
| HASH | 해시 스캔을 선택한다 | |
| 결합 순서에 관한 힌트 | ORDERED | FROM구로 지정된 순서로 테이블을 결합한다 |
| LEADING | 결합 순서의 처음의 테이블을 지정한다 | |
| STAR | 가능한 경우, 스타 문의를 선택한다 | |
| 결합 방법에 관한 힌트 | USE_NL | 네스티드·루프 결합을 선택한다 |
| USE_MERGE | 소트/머지 결합을 선택한다 | |
| USE_HASH | 해시 결합을 선택한다 | |
| 그 다른 힌트 | APPEND | 다이렉트·패스 INSERT를 선택한다 |
| CACHE | 취득된 블록이, 버퍼·캐쉬내에서 LRU 리스트의 마지막에 사용되었지던 위치에 배치된다 | |
| 표 2 힌트의 종류와 의미 | ||
아래와 같이는, 액세스·패스 및 결합 방법의 힌트를 지정한 예가 됩니다.
select /*+
INDEX(d i_department$department_id) USE_NL(e d)
*/ |
| 리스트 2 액세스·패스와 결합 방법의 힌트를 지정 (주：SQL로 테이블 별명을 지정한 경우는 힌트도 별명으로 지정해야 함) |
SQL의 튜닝을 실시하는데 힌트의 사용은 매우 유효합니다만 사용시 이하의 점에 주의할 필요가 있습니다.
- 힌트의 지정 방법이 잘못되어 있었을 경우 에러 출력은 되지 않는다
Oracle는 그 힌트를 무시할 뿐이므로 반드시 실행 계획을 확인할 필요가 있습니다.
- 힌트의 사용에 의해 실행 계획이 고정되어 버린다
최적인 실행 계획은 데이터의 건수 값에 의해서 바뀌므로 CBO서는 통계 정보를 다시 취득하는등 필요에 따라서 옵티마이져가 실행 계획을 변경해 줍니다만 힌트를 지정하는 것으로 실행 계획이 고정되어 버립니다.
- 힌트(RULE 힌트 이외)를 사용하면 강제적으로 CBO가 된다
힌트를 포함한 SQL에 통계 정보를 취득하고 있지 않는 오브젝트가 포함되어 있을 경우, 그 오브젝트의 통계 정보는, Oracle가 가지는 디폴트치가 사용됩니다.그 때문에 최적인 실행 계획을 선택할 수 없을 가능성이 있습니다.
◇
2회에 걸쳐서 SQL 튜닝을 실시하는데 전제가 되는 지식을 설명했습니다. SQL 튜닝의 목적인 한정된 시스템·자원 안에서 최대한의 퍼포먼스 효과를 내는 것을 염두에 튜닝 대상이 되는 SQL의 최적화된 실행 계획이 무엇일까를 생각해야 합니다.
다음 회에서는 일상에서 대량으로 실행되고 있는 SQL중에서 튜닝 대상의 SQL를 찾아내는 방법이나 지금까지 설명한 실행 계획의 확인 방법에 대해 설명합니다.
[출처] 【제3회】SQL 튜닝의 필수 지식을 총점검 (후편) |작성자 hirokorea