소개

프로그램에서 관련된 데이터만 기술하고, 데이터를 얻는 데 필요한 알고리즘은 기술하지 않는다는 점에서 SQL은 선언형 언어라고 할 수 있다. 따라서 특정 SQL 문을 충족시키기 위한 방법은 다양할 수 있다. 이러한 다양한 방법을 액세스 경로 또는 액세스 계획이라고 한다. 설명을 간단히 하기 위해 이 기사에서는 SQL 문을 충족시키는 특정 방식을 액세스 경로라고 부르기로 한다. 동일한 SQL 문을 충족시키는 다양한 액세스 경로에서 동일한 결과 세트가 생긴다고 해도 동일한 성능 레벨로 작업이 수행될 가능성은 거의 없다. SQL 컴파일러는 쿼리 최적화를 사용하여 합리적인 시간 내에서 특정 SQL 문에 적합한 최상의 액세스 경로를 선택한다.

독자가 개발자나 DBA, 쿼리 성능 조정 전문가라면 액세스 경로에 대한 기본사항을 이해하여 쿼리와 쿼리 워크로드가 프로덕션 환경에서 문제를 일으키기 전에 이를 정밀하게 조정할 수 있어야 한다. IBM Optim 쿼리 성능 조정 솔루션에서 제공하는 시각화 기능 및 성능 조정 권고사항과 더불어 기본적인 사항을 이해하면 이러한 작업을 보다 효율적으로 수행할 수 있다.

이 기사에서는 액세스 경로와 쿼리 실행을 소개한 후에 가장 일반적인 액세스 방법을 설명하고 DB2® for z/OS에서 지원하는 방법을 결합한다. 그다음에는 DB2 for z/OS 최적화 프로그램에서 선택하는 액세스 경로를 설명하고 액세스 경로의 선택과 관련된 중요한 정보를 나타내기 위한 액세스 경로 그래프를 살펴본다. 이 기사의 마지막 부분에서는 SQL문에서 직접 중요한 통계 정보를 제공하여 쿼리 분석을 하는 데 도움을 주는 Optim Query Tuner의 기능과 쿼리 어노테이션을 사용하는 방법을 샘플 시나리오를 통해 살펴본다.

액세스 경로 다이어그램과 상호 작용할 기회를 얻고자 하는 경우, 이 기사의 다운로드 섹션에서 얻을 수 있는 샘플 프로젝트 파일에 삽입되어 있는 액세스 경로 다이어그램을 이용하기 바란다. 이 프로젝트 파일을 Data Studio(Fix Pack 1 이상을 사용하는 독립형 패키지)나 Optim Query Tuner 제품으로 가져올 수 있다. 분석 결과는 이 기사에 있는 그림의 번호와 일치하게 레이블이 지정되어 있으므로 데이터베이스에 연결하지 않고도 분석 결과를 확인할 수 있다.

샘플 프로젝트를 가져오려면 다음을 수행한다.

1. 독립형 Data Studio나 Optim Query Tuner 제품에서 Data Perspective를 연다.
2. File > Import...를 선택한다.
3. Import Wizard에서 Query Tuner > Projects를 탐색한 후, Next를 클릭한다.
4. Browse..를 클릭한다. 그리고 다운로드한 zip 파일이 있는 디렉토리를 선택한다. 이렇게 하면 Projects 창에 프로젝트 목록이 표시된다.
5. sampleaccesspathproject를 선택한 후, Finish를 클릭한다.
6. 그러면 샘플 프로젝트가 Project Explorer에 표시된다. Project Explorer 창이 표시되지 않으면 Data Perspective에 있는지 확인한 후, Window > Reset Perspective를 선택한다. 또한, Window > Show View > Project Explorer를 선택할 수도 있다.

Optim 쿼리 성능 조정 솔루션 정보

Optim 쿼리 성능 조정 솔루션은 사용자가 문제를 해결할 수 있도록 도움을 주는 어드바이저 및 도구와 더불어 성능이 좋지 않은 SQL 문을 확인하여 성능을 조정할 수 있는 환경을 제공한다. 쿼리 성능을 조정하는 기능은 다음과 같은 제품에서 사용할 수 있다.

• 기본적인 단일 쿼리 성능 조정 및 쿼리 형식화 기능은 Data Studio 2.2.0.1 독립형 패키지에서 사용 가능하다. DB2 for z/OS와 DB2 for Linux®, UNIX®, and Windows®에서는 이 제품을 무료로 사용할 수 있다. 이 시리즈 기사에 있는 정보에서 Data Studio를 사용하여 액세스 경로 그래프를 해석하는 방법을 설명하고 있다고 하더라도 설명한 모든 기능을 Data Studio에서 사용할 수 있는 것은 아니라는 점을 인식하기 바란다.
• 단일 쿼리 성능 조정과 쿼리 형식화뿐만 아니라 대규모의 어드바이저 세트를 Optim Query Tuner에서 사용할 수 있다. 이 제품은 DB2 for z/OS 및 DB2 for Linux, UNIX, and Windows에서 사용 가능하다.
• 쿼리 워크로드 성능 조정, 단일 쿼리 성능 조정 및 전체 어드바이저 세트는 Optim Query Workload Tuner에서 사용 가능하다. 이 기사가 작성된 현재, 이 제품은 DB2 for z/OS에서만 사용 가능하다.

이 시리즈 기사에서는 Optim 쿼리 성능 조정 솔루션을 제공하는 어드바이저와 도구 세트를 간단히 Optim Query Tuner라고 부른다. 위에 있는 모든 제품에서도 사용이 불가능한 기능을 설명하는 경우에는 가능한 특정 제품의 이름을 표시하였다.

위로

액세스 경로 그래프를 읽는 방법

액세스 경로 그래프에서 쿼리의 실행과 관련된 "세부적인 내용"과 데이터 흐름을 확인할 수 있다. 액세스 경로 그래프의 리프(leaf) 노드는 쿼리 실행 계획에서 데이터 소스를 표현하는 색인 노드이거나 워크파일 노드, 테이블 노드이다(그림 1에는 이러한 유형의 노드에 대한 예가 표시되어 있다). 그래프에 표시된 바와 같이 데이터는 밑에서부터 이동하여 액세스 경로 그래프의 조작 노드에서 처리된다. 표 1에는 Optim Query Tuner 액세스 경로 그래프에 일반적으로 표시되는 몇 가지 조작 노드의 입/출력과 기능이 표시되어 있다.

위로

액세스 경로와 쿼리 실행

액세스 경로를 가장 쉽게 설명하려면 예제를 사용해야 한다. Listing 1에 있는 SQL 문은 세 개의 테이블을 조인하여 영업 보고서를 생성한다. 판매 금액은 고객의 성별과 결혼 여부를 기반으로 집계된다.

				
select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
from cust_customer c, cust_order_header oh, cust_order_detail od
where c.cust_code = oh.cust_code
  and oh.cust_order_number = od.cust_order_number
  and c.cust_prov_state_code = 'CA'
  add od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by c.gender_code, c.marital_status_code

액세스 경로는 다음과 같은 세 가지 컴포넌트로 이루어진 쿼리 실행이 절차적으로 설명된 것이라는 점을 직관적으로 알 수 있다.

• 테이블의 조인 시퀀스
• 테이블을 스캔하는 알고리즘(액세스 방법)
• 조인 조작을 수행하는 알고리즘(조인 방법)

그림 1은 Listing 1에 표시된 쿼리를 대상으로 Optim Query Tuner에서 생성된 액세스 경로 그래프이다.

(그림 1의 확대 이미지 보기)

쿼리에서 참조된 세 가지 테이블은 각각 해당 테이블 이름과 함께 테이블 노드로 그래프에 표시된다.

조인 시퀀스에서 선행 테이블은 CUST_ORDER_DETAIL 테이블이며 이 테이블은 PRODUCT_NUMBER 컬럼 전체에 정의된 색인을 통해 스캔된다. 이 색인은 그래프에서 색인 이름이 SQT01_CUST_ORDER_DETAIL인 색인 노드로 표현된다.

SQT01_CUST_ORDER_DETAIL 색인 노드 바로 위에는 IXSCAN 노드가 있다. 따라서 규정 RID를 얻기 위해서는 색인이 순회되어야 한다는 것을 의미한다. 그러면 FETCH 노드에서 규정 RID를 가져와서 테이블스페이스에서 해당 레코드와 데이터 페이지를 DB2 버퍼 풀로 페치한다.

조인 시퀀스에서 두 번째 테이블은 CUST_ORDER_HEADER 테이블이며 이 테이블은 CUST_ORDER_NUMBER 조인 컬럼에 있는 색인을 통해 스캔된다. CUST_ORDER_HEADER 테이블을 액세스하는 방법인 색인 스캔은 CUST_ORDER_DETAIL 테이블과 같은 방식으로 표현된다.

조인 시퀀스에 있는 마지막 테이블은 CUST_CUSTOMER이며 이 테이블도 CUST_CODE 컬럼 전체에 정의된 색인을 통해 스캔된다.

이제까지 테이블의 개요와 정보를 액세스하는 방법(이 경우에는 색인을 통해)을 살펴보았으므로 이제는 사용되는 조인 방법을 자세히 살펴보도록 한다. DB2 최적화 프로그램이 중첩 루프 조인(NLJOIN)을 사용하여 CUST_ORDER_DETAIL과 CUST_ORDER_HEADER 간의 첫 번째 조인 조작을 수행한다는 사실을 액세스 경로 그래프의 조인 노드에서 알 수 있다. 조인은 첫 번째 테이블(CUST_ORDER_DETAIL)의 결과 세트와 두 번째 테이블(CUST_ORDER_HEADER)의 결과 세트를 입력으로 받는다. 이러한 조인 조작의 결과는 두 번째 조인 조작과 중첩 루프 조인의 첫 번째 입력이 된다. 또한, 이 조인 조작의 결과는 조인 시퀀스에 있는 마지막 테이블인 CUST_CUSTOMER의 결과 세트에 조인된다.

데이터 흐름을 이해하는 것이 중요한데, 이는 쿼리를 실행할 때의 쿼리 성능 특성이 데이터 흐름에 가장 영향을 많이 주기 때문이다. 이 섹션의 나머지 부분에서는 쿼리 실행의 성능 특성을 완전히 이해할 수 있도록 데이터 흐름 측면에서 액세스 경로 그래프를 한 번 더 살펴본다.

각 노드에 표시된 숫자는 DB2 최적화 프로그램에서 산출한 기본 데이터 소스나 조작 노드의 카디널리티 추정치이다. 예를 들면, SQT01_CUST_ORDER_DETAIL 색인 노드에 있는 숫자 273은 273개의 개별 색인 키가 있다는 것을 나타낸다. CUST_ORDER_HEADER_PK는 고유 색인이므로 색인 키 수가 해당 테이블에 있는 레코드 수(539,526)와 정확히 일치한다. 각각 31,284개의 색인 키와 레코드가 있는 IDX_CUST_CUSTOMER 색인과 CUST_CUSTOMER 테이블도 마찬가지이다.

고유하지 않은 색인의 경우에는 색인 키의 수가 레코드 수보다 작다. 또한, 평균적으로 레코드 수와 색인 키 수 간의 비율이 각 색인 키와 연관된 RID 수가 된다. 액세스 경로 그래프에 있는 세 개의 테이블 노드에 표시된 숫자는 테이블의 크기를 레코드 수로 나타낸 값이다.

중첩 루프 조인의 첫 번째 레그에서는 실행 시간에 다음과 같은 동작이 일어난다.

1. 조인 시퀀스의 첫 번째 테이블에서 가장 먼저 수행되는 조작은 색인 스캔이기 때문에 먼저, BQT01_CUST_ORDER_DETAIL 색인 전체에서 색인 스캔(IXSCAN)이 진행된다. DB2 최적화 프로그램의 추정치를 기반으로 했을 때 대상 색인 키 범위 내에는 8개의 규정 RID가 존재한다.
2. FETCH 노드에서는 이 8개의 규정 RID를 사용하여 560273개의 레코드가 있는 테이블스페이스에서 해당 레코드와 페이지를 찾는다. 페치 조작에 적용할 특별한 조건부가 없기 때문에 FETCH 노드의 출력 카디널리티도 8이 된다. 이는 페치 조작을 하면 규정 레코드가 8개 생성된다는 것을 의미한다.
3. 그다음에는 이 규정 레코드 8개가 FETCH 노드 바로 위에 있는 NLJOIN 노드의 첫 번째 입력으로 들어간다. 중첩 루프 조인의 작동 방식으로 인해 외부 테이블 레코드마다 두 번째 레그(4개의 노드가 있음)가 실행된다. 즉, 조인 조작의 내부 테이블은 8번 스캔된다(외부 입력의 각 8개 규정 레코드를 대상으로 한 번씩 스캔됨).

NLJOIN 노드의 두 번째 레그에서 수행되는 과정은 첫 번째 테이블에 수행되는 과정과 비슷하다.

1. DB2 최적화 프로그램은 고유 색인(CUST_ORDER_HEADER_PK)을 선택하여 CUST_ORDER_HEADER 테이블에 있는 레코드를 액세스한다.
2. IXSCAN 노드과 FETCH 노드에 숫자로 표시된 바와 같이 색인 스캔이 수행될 때마다 CUST_ORDER_HEADER 테이블에서 하나의 레코드를 찾는다. 따라서, NLJOIN 노드에서 총 8개의 레코드가 생성된다.

마찬가지로 액세스 경로 그래프에 있는 마지막 레그가 8번 평가되며 첫 번째 NLJOIN 노드에서 생성된 각 레코드를 대상으로 한 번씩 수행된다. IXSCAN 노드(IDX_CUST_CUSTOMER)에서는 고유 색인을 사용하기 때문에 CUST_CUSTOMER 테이블을 스캔할 때마다 하나의 레코드만 일치하게 된다. 그러므로 최종 결과 세트에서 총 8개의 레코드가 생성된다.

성능 면에서 보면 우수한 성능의 액세스 경로는 최소한의 데이터를 사용하여 최종 결과 세트를 생성해야 한다. 위에서 살펴본 예제에서는 각 테이블에서 페치된 레코드 수가 최소로 유지되었다. CUST_ORDER_DETAIL 테이블에서는 색인 스캔을 사용하여 테이블스페이스에서 8개의 레코드만 페치하였다. 또한, 모든 NLJOIN 조작의 내부 입력을 프로브할 때마다 하나의 레코드만 일치한다. 따라서 DB2 최적화 프로그램의 추정치가 정확하면 이 액세스 경로가 매우 효과적인 액세스 경로가 된다. (이 기사의 쿼리 어노테이션을 사용하는 케이스 연구 섹션에서는 추정치가 정확한지 여부를 판별하는 방법을 설명한다.)

위로

기타 액세스 방법과 조인 방법

이전 섹션에서는 한 가지 액세스 방법과 조인 방법으로 각각 색인 스캔(IXSCAN)과 중첩 루프 조인(NLJOIN)을 살펴보았다. 이번 섹션에서는 액세스 방법과 조인 방법, 정렬 조작을 몇 가지 더 살펴보기로 한다. 각 조작과 관련된 설명에는 조작 시맨틱을 설명하는 Query Tuner 액세스 경로 그래프가 포함되어 있다.

Stage-1과 Stage-2 조건부를 사용한 테이블스페이스 스캔(TBSCAN)

Listing 2에 있는 SQL 문을 실행하면 그림 2에 있는 Optim Query Tuner 스크린샷의 오른쪽에 표시된 액세스 경로 그래프가 작성된다.

				
select count(*)
from cust_order_header
where cust_total_quantity > 3
  and cust_sales_tax > cust_total * 0.03

(그림 2의 확대 이미지 보기)

그림 2에는 액세스 경로 그래프에서 두 가지 노드(CUST_ORDER_HEADER 테이블과 테이블 스캔(TBSCAN) 조작)에 대한 자세한 설명을 표시하는 방법을 확인할 수 있는 예제가 있다. 해당 노드를 마우스 오른쪽 단추로 클릭하고 컨텍스트 메뉴에서 Show Description을 선택하면 액세스 경로 그래프에 있는 모든 노드에서 자세한 설명을 확인할 수 있다. 그림 2에는 CUST_ORDER_HEADER 테이블의 노드 디스크립터와 TBSCAN 노드의 디스크립터가 액세스 경로 그래프의 왼쪽에 표시되어 있다. SQL 문은 다이어그램 밑에 표시된다.

액세스 경로를 통해 TBSCAN 조작이 CUST_ORDER_HEADER 테이블 전체에서 수행된다는 것을 알 수 있다. 이는 DB2가 테이블스페이스에 있는 모든 데이터 페이지를 순차적으로 페치하여 버퍼 풀로 보낸다는 것을 의미한다. 그런 다음, DB2는 버퍼 풀에 있는 각 레코드를 스캔하여 Stage-1 조건부를 적용한다. 규정 레코드(관련된 컬럼만 해당)는 사설 메모리 풀로 복사되어 Stage-2 조건부에 의해 필터링된다. 집계 함수 COUNT는 Stage-2 조건부로 필터링한 이후에 남아있는 레코드에 적용된다.

데이터 흐름에 대한 세부사항은 TBSCAN 디스크립터의 속성 섹션에서 확인할 수 있다. DB2가 버퍼 풀에서 스캔한 레코드 수는 539,526개(TBSCAN 디스크립터의 Input Cardinality 값)이다. DB2 최적화 프로그램은 473,785.44개의 레코드가 Stage-1 조건부(해당 디스크립터의 Stage 1 Returned Rows 속성 참조)를 통과할 것이며 4,737.9688개의 레코드가 Stage-2 조건부(해당 디스크립터의 Stage 2 Returned Rows 속성 참조)를 통과할 것으로 추정한다. COUNT 함수로 인해 하나의 레코드만 생성된다. 디스크립터의 Prefetch 속성에는 값 'S'가 포함되어 있으며 이 값은 테이블스페이스에서 데이터 페이지를 페치할 때, I/O 성능을 개선하기 위해 실행 시간에서 프리페치를 시작한다는 것을 나타낸다.

파티션 정리(pruning)를 사용한 테이블 스캔(TBSCAN)

Listing 3에 있는 SQL 문을 실행하면 그림 3과 같은 액세스 경로 그래프가 작성된다.

				
select count(*)
from cust_order_header
where cust_total_quantity > 3 
  and cust_sales_tax > cust_total * 0.03
  and cust_order_number between 100000 and 580000 

(그림 3의 확대 이미지 보기)

테이블 노드 디스크립터에 표시된 바와 같이 테이블스페이스에는 10개의 파티션이 있으며 9번째 파티션의 한계 키는 625,000이다. 파티션 분할된 컬럼에는 cust_order_number between 100000 and 580000 조건부가 있기 때문에 DB2 최적화 프로그램이 TBSCAN을 첫 번째 9개 파티션으로 제한할 수 있다. 이러한 최적화 과정은 TBSCAN 디스크립터에서 설명하기로 한다. Page_Ranges 속성에는 하나의 범위 range 1이 포함되어 있으며 range 1의 적용 범위는 파티션 1에서 파티션 9까지 이다. 성능 개선을 위해 파티션 10은 실행 시간에 완전히 건너뛰게 된다.

일치 조건부를 사용한 색인 스캔(IXSCAN)

Listing 4에 있는 SQL 문을 실행하면 그림 4와 같은 액세스 경로 그래프가 작성된다.

				
select crdt_method_code, cust_total_quantity, count(*)
from cust_order_header 
where cust_total_quantity - 2 > 1 
  and crdt_method_code > 20 
  and cust_order_date > '2007-01-01-01.24.58.017000' 
group by crdt_method_code, cust_total_quant

(그림 4의 확대 이미지 보기)

일반적으로 Query Tuner의 액세스 경로 그래프에서는 색인 스캔이 네 개의 노드로 구성된 그룹으로 표시된다. 그림 4에 표시된 바와 같이 CUST_ORDER_HEADER 테이블은 BQT01_CUST_ORDER_HEADER 색인을 사용한 일치 색인 스캔을 통해 액세스되며 이 색인은 CRDT_METHOD_CODE 컬럼에서 정의된다. (그림 4에 표시된 것보다 약간 더 넓게 IXSCAN 디스크립터를 확장하면 이를 확인할 수 있다.) IXSCAN 디스크립터 세부사항을 통해 7.32% 필터 인수를 사용하는 일치 조건부가 하나 있다는 것을 확인할 수 있으며 이로 인해 총 1,558개의 색인 페이지와 539,526개의 RID 중, 조건에 맞는 색인 페이지와 RID는 각각 114개와 3,9471개가 된다.

FETCH 노드에서는 이러한 RID를 사용하여 해당 데이터 페이지를 테이블스페이스에 배치한다. 이 페이지가 DB2 버퍼 풀로 페치되면 이 페이지에 있는 레코드가 스캔되어 Stage-1 조건부가 적용된다. FETCH 노드의 세부 설명에는 18,515.602개의 레코드가 Stage-1 조건부를 통과할 것으로 추정된다는 내용이 표시되어 있다. Stage-2 조건부는 이러한 레코드의 관련 컬럼이 DB2에 있는 사설 버퍼로 복사된 후에 적용된다. 결국, FETCH 노드에서 리턴되는 레코드는 6,172.508개가 된다.

비일치 색인 스캔(IXSCAN)

Listing 5에 있는 SQL 문을 실행하면 그림 5와 같은 액세스 경로 그래프가 작성된다.

				
select count(*) as count
from cust_order_header

(그림 5의 확대 이미지 보기)

색인 스캔은 비일치로 수행될 수 있으며 이는 기본 색인을 스캔하는 데는 제한이 없다는 것을 의미한다. 즉, DB2는 각 리프(leaf) 페이지를 모두 스캔한다. 최적화 프로그램은 일반적으로 기본 색인을 사용하면 색인만을 액세스할 수 있으며 정렬을 할 필요가 없는 등의 장점을 얻을 수 있는 경우에 비일치 색인 스캔을 사용한다.

그림 5에 있는 예제에서는 집계 함수 count(*)가 해당 색인만을 스캔하여 평가할 수 있기 때문에 최적화 프로그램에서 비일치 색인 스캔을 사용한다. 또한, 색인은 일반적으로 테이블보다 작기 때문에 보다 효율적으로 스캔할 수 있다. IXSCAN 노드의 세부 설명에는 일치 조건부가 없기 때문에 1,680개의 모든 리프 페이지가 스캔된다는 내용이 표시되어 있다. 여기서는 색인만을 스캔하기 때문에 액세스 경로 그래프에 테이블 노드나 FETCH 노드가 표시되지 않는다.

중첩 루프 조인(NLJOIN)

Listing 6에 있는 SQL 문을 실행하면 그림 6에 있는 액세스 경로 그래프가 작성된다.

				
select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
from cust_order_header oh, cust_order_detail od
where oh.cust_order_number = od.cust_order_number
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by oh.cust_code

(그림 6의 확대 이미지 보기)

중첩 루프 조인(NLJOIN)은 두 개의 데이터 소스를 입력으로 받아서 조인 조건부를 통과하는 내부의 일치 레코드(오른쪽)와 외부의 레코드(왼쪽)를 조인한 후, 조인된 레코드를 출력으로 리턴한다. 중첩 루프 조인에 관한 자세한 정보는 참고자료 섹션에 있는 해당 DB2 플랫폼의 Information Center 링크를 참조하기 바란다.

그림 6에 있는 샘플 쿼리의 액세스 경로 그래프에 표시된 바와 같이 NLJOIN과 EQUAL 조인 조건부를 사용하여 외부 테이블(CUST_ORDER_DETAIL)의 규정 레코드와 내부 테이블(CUST_ORDER_HEADER)의 규정 레코드를 조인한다. 실행 시간에 해당 조인의 바깥쪽에 있는 FETCH 노드에서 이동하는 각 레코드는 CUST_ORDER_HEADER_PK 색인에서 색인 스캔(IXSCAN)을 사용하여 내부 테이블을 스캔한다.

그림 6에 있는 NLJOIN 디스크립터의 세부 정보에서 최적화 프로그램이 다음과 같이 추정한다는 사실을 알 수 있다.

• 로컬 조건부(Outer Input Cardinality 값은 8)를 적용한 후에는 규정 레코드가 8개 된다.
• 내부 테이블에 있는 하나의 레코드는 외부 테이블의 각 규정 레코드에 일치한다(Inner Input Cardinality 값은 1).
• 결과적으로 NLJOIN 노드에서는 8개의 조인 레코드가 출력으로 생성된다(Output Cardinality 값은 8).

내부 테이블을 스캔하는 데 들어가는 비용이 고유 색인을 사용하는 고유 액세스를 통해 최적화되기 때문에 이는 성능 면에서 매우 효과적인 조인 조작이라고 할 수 있다.

컴포지트 정렬을 사용한 중첩 루프 조인(NLJOIN)

Listing 7에 있는 SQL 문은 이전 섹션의 Listing 6에 있는 SQL 문과 동일하다. 그러나 다음 예제에서는 그림 7과 같이 다른 액세스 경로를 선택한다.

				
select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
from cust_order_header oh, cust_order_detail od
where oh.cust_order_number = od.cust_order_number
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by oh.cust_code

(그림 7의 확대 이미지 보기)

그림 6과 그림 7 간의 차이점은 그림 7에는 조인의 바깥쪽에 SORT 노드가 추가되었다는 점이다. 이러한 액세스 경로를 컴포지트 정렬을 사용한 중첩 루프 조인이라고 한다.

최적화 프로그램에서 SORT 노드를 추가하는 이유를 이제부터 설명한다. 그림 7에 있는 조인의 바깥쪽에 있는 FETCH 노드에 표시된 카디널리티 간에는 중요한 차이가 있다. 그림 6의 예제에는 레코드가 8개 뿐이었지만 그림 7에서는 최적화 프로그램이 규정 레코드 수를 8,708.988개로 추정하였다. 규정(외부) 레코드당 한 번 프로브를 하기 때문에 카디널리티가 높아질수록 내부 테이블을 프로브하는 횟수가 증가한다. 내부 테이블을 탐색하기 위해 이용하는 색인은 클러스터된 색인이다. 따라서, 내부 테이블을 연속해서 프로브하는 순서가 내부 테이블의 I/O 성능에 중요한 영향을 줄 수 있다. 보다 정확히 말하자면 CUST_ORDER_NUMBER 순으로 내부 테이블을 연속해서 프로브하면 내부 테이블을 순차적으로 스캔한 결과를 차례로 렌더링하는 다양한 프로브 과정 전체에서 내부 색인이 순차적으로 스캔된다. I/O 성능을 보다 효율화하는 것이 약 8,708개의 레코드를 정렬하는 편보다 더 낫다. 그러므로, 그림 7에서는 최적화 프로그램에서 중첩 루프 조인을 처리하기 위해 컴포지트 정렬을 사용한다.

내부 색인이 제대로 클러스터되어 있고 내부 테이블이 여러 번 프로브될 것으로 예상되는 경우에는 일반적으로 최적화 프로그램에서 컴포지트 정렬을 사용한 중첩 루프 조인을 선호한다.

외부 및 내부 정렬을 사용한 병합 스캔 조인(MSJOIN)

Listing 8에 있는 SQL 문을 실행하면 그림 8과 같은 액세스 경로 그래프가 작성된다.

				
select *
from cust_order_header oh, cust_order_detail od
where oh.cust_total_quantity = od.cust_quantity
  and oh.cust_total = od.cust_unit_price
  and oh.cust_order_date > '2009-01-16-01.00.00.000000'
  and od.product_number > 150000

(그림 8의 확대 이미지 보기)

병합 스캔 조인을 정렬된 병합 조인이라고도 한다. Optim Query Tuner에서는 MSJOIN 약자를 사용하여 이러한 조인 알고리즘을 표시한다. 병합 스캔 조인에 관한 자세한 정보는 참고자료 섹션에 있는 해당 DB2 플랫폼의 Information Center 링크를 참조하기 바란다.

병합 스캔 조인에는 언제나 하나 이상의 Equal 조인 조건부가 포함되어 있다. 그림 8은 샘플 쿼리의 병합 스캔 조인에 활용된 Equal 조인 조건부가 2개라는 사실을 나타낸다. 병렬 스캔 조인을 제대로 수행하려면 두 입력이 모두 조인 컬럼순으로 되어 있어야 한다. 보다 정확히 말해서 조인(CUST_ORDER_DETAIL)의 외부 테이블에서는 규정 레코드가 CUST_QUANTITY와 CUST_UNIT_PRICE의 순서로 되어 있어야 한다. 마찬가지로 내부 테이블의 규정 레코드는 CUST_TOTAL_QUANTITY와 CUST_TOTAL의 순서로 되어 있어야 한다. 내부 테이블은 TBSCAN을 사용하여 스캔하고 따라서 MSJOIN의 예상 순서를 확신할 수 없기 때문에 SORT 노드를 추가하여 순서를 강제로 조정한다.

그림 8에 있는 IXSCAN 디스크립터에 표시된 바와 같이 최적화 프로그램에서 활용하는 색인의 첫 번째 컬럼은 PRODUCT_NUMBER이며 이 컬럼에는 병합 스캔 조인을 수행하는 데 필요한 순서가 없다. 따라서, 조인의 외부에도 SORT 노드를 추가하여 순서를 강제로 조정한다.

앞서 설명한 바와 같이 병합 스캔 조인을 수행하려면 입력을 정렬해야 하지만 중첩 루프 조인의 경우에는 입력을 정렬할 필요가 없다. 따라서 중첩 루프 조인과 비교해서 병합 스캔 조인은 입력을 정렬해야 하는 수고가 필요하다. 그러나 병합 스캔 조인은 중첩 루프 조인에서와 같이 내부 테이블을 반복해서 스캔하지 않아도 된다는 장점이 있다. 그러므로 조인 방법을 선택할 때 최적화 프로그램이 내부 테이블 스캔의 성능상의 장점과 정렬에 들어가는 비용을 비교 평가하여 의사결정을 내릴 수 있게 도움을 준다. 병합 스캔 조인과 관련하여 한 가지 주목할 만한 점은 최적화 프로그램이 데이터의 "정렬 특성"을 추적하여 SORT 노드를 추가하지 않아도 되게 한다는 점이다. 예를 들어, 첫 번째 컬럼인 CUST_TOTAL 및 CUST_TOTAL_QUANTITY과 색인을 사용하여 내부 테이블을 스캔하면 내부 테이블에 SORT 노드를 추가하지 않아도 된다. 외부 입력의 카디널리티가 명확히 크고 내부 테이블을 효과적으로 액세스할 수 있는 방법이 없는 경우, 최적화 프로그램은 일반적으로 병합 스캔 조인을 선택한다.

위로

쿼리 어노테이션을 사용하는 사례 연구

이번 섹션에서는 Query Tuner 쿼리 어노테이션과 액세스 경로 그래프를 활용하여 액세스 경로와 SQL 성능을 분석하는 방법을 살펴본다. Listing 9에는 액세스 경로와 쿼리 실행의 개념을 소개하기 위해 Listing 1에서 사용한 것과 같은 세 방향 조인 SQL 문이 있다.

				
select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
from cust_customer c, cust_order_header oh, cust_order_detail od
where c.cust_code = oh.cust_code
  and oh.cust_order_number = od.cust_order_number
  and c.cust_prov_state_code = 'CA'
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by c.gender_code, c.marital_status_code

그림 9에는 WHERE 절에 있는 각 조건부와 FROM 절에 있는 각 테이블 참조가 줄바꿈이 되도록 SQL 문을 형식화하는 Optim Query Tuner의 쿼리 어노테이션 기능이 표시되어 있다. 조건부는 조건부의 종류(로컬 또는 조인)와 테이블 참조를 기반으로 다시 정렬되고 그룹화된다.

(그림 9의 확대 이미지 보기)

형식화와 어노테이션 기능 덕택에 기본 명령문을 이해하기가 보다 쉽다는 것을 그림 9를 통해 알 수 있다. 세 개의 테이블(CUST_ORDER_DETAIL, CUST_CUSTOMER 및 CUST_ORDER_HEADER) 참조는 서로 다른 세 개의 행에 표시된다. 또한, 조건부 네 개도 각각 다른 행에 표시된다(로컬 조건부 두 개가 두 개의 조인 조건부 앞에 표시됨).

읽기 쉽게 형식화할 수 있는 것은 물론이고 SQL 성능을 쉽게 분석할 수 있는 중요한 통계 정보를 제공하는 어노테이션에 액세스할 수 있다. 각 테이블 참조의 오른쪽에는 레코드 수와 페이지 수로 표시된 테이블 카디널리티와 규정된 열의 수(추정치)가 있다.

QUALIFIED_ROWS와 연관된 숫자는 기본 테이블 참조의 로컬 조건부를 모두 통과하는 레코드 수(추정치)를 나타낸다. 예를 들면, 로컬 조건부를 적용한 후에는 CUST_ORDER_DETAIL 테이블에서 560,273개의 레코드 중 8.000001개가 리턴될 것으로 추정된다. 마찬가지로 CUST_CUSTOMER 테이블에서는 31,284개의 레코드 중 729.99976개만 로컬 조건부를 통과하게 된다. CUST_ORDER_HEADER 테이블에는 로컬 조건부가 없기 때문에 539,526개의 모든 열이 통과하게 된다. 이 정보를 통해 조인 시퀀스에서 선행 테이블을 선택하는 방법을 이해할 수 있다. 즉, 규정된 열의 수가 적을 수록 최적화 프로그램이 해당 테이블을 조인 시퀀스의 선행 테이블로 선택할 가능성이 높아진다.

그림 1에 표시된 바와 같이 로컬 조건부를 적용한 후에는 이 테이블에서 8개의 레코드만 생성될 것으로 추정되기 때문에 최적화 프로그램은 CUST_ORDER_DETAIL 테이블을 조인 시퀀스의 선행 테이블로 선택한다. 액세스 경로에서는 조인 시퀀스의 두 번째 테이블 참조(CUST_ORDER_HEADER)와 세 번째 테이블 참조(CUST_CUSTOMER)를 완전히 일치된 고유 색인을 통해 액세스한다. 즉, 두 번째 테이블 참조와 세 번째 테이블 참조를 프로브할 때마다 조인 조건에 일치하는 레코드만을 효과적으로 찾는다. 따라서, 첫 번째 테이블 참조(CUST_ORDER_DETAIL)의 QUALIFIED_ROWS의 추정치가 정확하기만 하면 실제로는 이 액세스 경로가 성능이 우수한 액세스 경로가 될 수 있다.

최적화 프로그램에서 QUALIFIED_ROWS를 어떻게 계산하는지 그리고 그 값이 얼마나 정확하지 확인하려면 그림 9를 다시 참조한다. CUST_ORDER_DETAIL에는 로컬 조건부가 하나만 있으며 이 조건부는 PRODUCT_NUMBER 컬럼에 있는 IN 목록 조건부로 이 목록에는 6개의 요소(od.product_number 154110, 129170, 129150, 129110, 129140, 130130)가 있다.

이 조건부의 쿼리 어노테이션에서 조건부 필터 인수(FF)가 0.000014 또는 0.0014%라는 것을 확인할 수 있다. 다시 말해서 이 조건부를 통과하는 열의 백분율(추정치)은 0.0014%이다. QUALIFIED_ROWS의 추정치는 테이블 카디널리티(레코드 560,273개)와 조건부 필터 인수(0.0014%)를 곱하여 산출된다.

최적화 프로그램이 어떻게 필터 인수를 0.0014%로 결정하는지 이해하려면 먼저, IN 목록 조건부에 있는 6개의 요소를 살펴보아야 한다. 이 조건부의 쿼리 어노테이션에서 PRODUCT_NUMBER 컬럼에 273개의 개별 값이 있다는 것을 알 수 있다. 균일하게 분배된다고 가정하면 최적화 프로그램은 일반적으로 레코드의 2.197%(6/273 = 0.02197)가 이 조건부를 통과한다고 결론을 내린다. 그러나 최적화 프로그램은 이 값 대신 0.0014%를 사용한다. 이렇게 하면 보기보다 조건부의 선택성이 훨씬 더 좋아진다.

이러한 내용을 이해하려면 이 조건부의 쿼리 어노테이션에 있는 MAXFREQ를 살펴보아야 한다. 필수 통계치가 수집되어 이용할 수 있게 되면 해당 컬럼에서 매우 빈번하게 표시되는 값의 빈도가 MAXFREQ에 표시된다. 이 예제에서는 MAXFREQ가 3.32%이며 이 수치는 개별 값에 대한 평균 백분율(1/273 = 0.366%)보다 훨씬 더 크다. 이러한 결과는 273개의 값 중 일부는 표시되는 빈도가 낮다는 것을 의미한다. 이 컬럼에서 사용 가능한 통계를 통해 실제로 어떠한 작동이 일어나고 있는지 확인할 수 있다. 기본 액세스 경로 그래프에 있는 테이블 노드의 테이블 디스크립터에서 통계를 확인할 수 있다.

테이블 디스크립터를 확인하려면 액세스 경로 그래프에서 테이블 노드를 마우스 오른쪽 단추로 클릭한다. 그림 10에는 CUST_ORDER_DETAIL의 테이블 디스크립터가 표시되어 있다.

(그림 10의 확대 이미지 보기)

테이블 디스크립터에서 해당 컬럼 통계를 드릴 다운하면 맨 밑에 있는 6개의 값이 IN 목록 조건부에 있는 6개 요소의 빈도 수에 해당한다는 것을 확인할 수 있다. 이러한 빈도 값을 모두 합하면 IN 목록 조건부의 필터 인수가 0.0014%와 정확히 같아진다(추정). 이러한 결과를 통해 최적화 프로그램에서 필터 인수가 어떻게 0.0014%로 결정되었는지 확인할 수 있다.

또한, 테이블 디스크립터의 맨 밑에 표시된 통계 콜렉션의 시간소인에는 이 쿼리가 설명된 시간에 비해 상대적으로 빠른 날짜가 표시되어 있다는 점을 알 수 있다. 마지막 RUNSTATS 이후 데이터 분배가 변경되지 않는 한 최적화 프로그램의 추정치는 실제 값에 매우 근접한다. 이러한 내용이 사실이라고 가정하면 기본 액세스 경로를 신뢰성이 높은 최적의 경로로 간주할 수 있다.

위로

결론

이 기사에서는 액세스 경로 그래프를 읽는 방법과 액세스 경로의 기본 개념을 살펴보았다. Optim Query Tuner의 액세스 경로 그래프와 쿼리 어노테이션 기능을 사용하여 쿼리를 실제로 컴토하였으며 DB2 최적화 프로그램에서 특정 액세스 경로를 선택하는 방법과 이유에 대해 설명했다. 이러한 정보를 통해 쿼리의 성능을 조정할 때 필요한 기본 원칙을 확인할 수 있다. 이 시리즈의 다음 기사에서는 쿼리의 성능을 조정하는 데 사용할 수 있는 방법론에 관한 자세한 정보를 살펴볼 예정이다.

위로

다운로드 하십시오

다운로드 방식에 대한 정보

참고자료

교육

• "Optim 성능 관리 솔루션" 데모(developerWorks, 2010년 4월)를 살펴보고 가상의 회사에서 DB2 for Linux, UNIX, and Windows용 Optim Query Tuner와 Optim 솔루션을 사용하여 비즈니스가 영향을 받기 전에 문제점을 어떻게 해결하는지 확인하자.
  
  
• 제품을 구매하는 방법이 포함된 자세한 정보는 Optim Query Workload Tuner for DB2 for z/OS 제품 웹 페이지 또는 Optim Query Tuner for DB2 for Linux, UNIX, and Windows 제품 웹 사이트를 참고한다.
  
  
• DB2 Information Center에서 조인과 관련된 DB2 for Linux, UNIX, and Windows 제품 정보를 읽어보자.
  
  
• DB2 Information Center에서 조건부 유형과 관련된 DB2 for Linux, UNIX, and Windows 제품 정보를 읽어보자.
  
  
• DB2 for z/OS Information Center에서 중첩 루프 조인과 관련된 DB2 for z/OS 제품 정보를 읽어보자.
  
  
• DB2 for z/OS Information Center에서 병합 스캔 조인과 관련된 DB2 for z/OS 제품 정보를 읽어보자.
  
  
• DB2 for z/OS Information Center에서 Stage 1과 Stage 2 조건부와 관련된 DB2 for z/OS 제품 정보를 읽어보자.
  
  
• "Best practices when using Data Studio and Optim Development Studio with DB2 for z/OS"(developerWorks, 2010년 6월)에는 DB2 for z/OS에서 쿼리의 성능을 조정할 수 있도록 Data Studio를 구성하는 데 도움을 주는 정보가 있다.
  
  
• developerWorks Optim 제품군 페이지를 참조하여 Optim 솔루션에 관해 자세히 알아보자. 기술 자료, 사용법 기사, 교육, 다운로드, 제품 정보 등을 찾아볼 수 있다.
  
  
• developerWorks Information Management 영역에서는 Information Management에 대한 정보를 제공한다. 기술 자료, 사용법 기사, 교육, 다운로드, 제품 정보 등을 찾아볼 수 있다.
  
  
• developerWorks 기술 행사 및 웹 캐스트를 통해 최신 정보를 얻을 수 있다. Optim 통합 데이터 관리 포트폴리오와 관련된 가상의 기술 브리핑에 대한 스케줄은 이 페이지를 참조한다.
  
  

제품 및 기술 얻기

• Data Studio 무료 소프트웨어를 다운로드하자.
  
  

토론

• 포럼에 참여하기.
  
  
• Integrated Data Management 전문가 블로그를 확인하고 포괄적인 참고자료와 다운로드 목록이 있는 Integrated Data Management 커뮤니티 공간에 참여하자.
  
  
• developerWorks 포럼 & 블로그를 통해 developerWorks 커뮤니티에 참여하자.
  
  

필자소개

Gene Fuh has been working in database technologies for IBM since 1994. He joined the DB2 for z/OS organization in 2000 after he had worked in the DB2 LUW organization for six and a half years. In 2004, Gene started up a team for the development of DB2 Optimization Expert and Optimization Service Center (DB2 OE/OSC). He was chief architect and overseer of the project until 2007, when the product became available simultaneously with DB2 9 for z/OS. In 2008, Gene initiated the transition of DB2 OE/OSC technology into the Optim tuning solutions, which is now known as Optim Query Tuner and Optim Query Workload Tuner. During his 17 years with IBM, Gene filed 48 patent applications and published more than 20 technical papers in both academic and IBM conferences.

Kendrick Ren is the technical lead of the IBM Optim Query Tuner and Optim Query Workload Tuner products, working out of the IBM Toronto lab. He has worked with these products in their previous incarnations as the DB2 Optimization Expert and Optimization Service Center products since the team was founded in 2004. Kendrick works closely with the customers and business partners who are using these products, assisting them in the area of query optimization. Before joining the Optimization Expert team, Kendrick worked two years on the IBM WebSphere Commerce Server product.

Kathy Zeidenstein has worked at IBM for a bazillion years. She currently works on the IBM Optim Solutions technical enablement team and is responsible for community development and communications. Before taking on this role, she was a product marketing manager in text search and analytics technologies.

아티클 순위

의견

위로

목차

• 소개
• Optim 쿼리 성능 조정 솔루션 정보
• 액세스 경로 그래프를 읽는 방법
• 액세스 경로와 쿼리 실행
• 기타 액세스 방법과 조인 방법
• 쿼리 어노테이션을 사용하는 사례 연구
• 결론
• 다운로드 하십시오
• 참고자료
• 필자소개
• 의견