소개

이 시리즈의 첫 번째 기사인 Optim Query Tuner를 사용하여 SQL 성능 조정하기, Part 1: 액세스 경로 이해하기에서 액세스 경로의 개념을 소개했다. 주어진 SQL 문에 대해 보통 복수의 액세스 경로 선택이 있고, 다른 액세스 경로는 대개 다른 성능 특성을 가진다. SQL이 실행되기 전에, DB2 최적화 프로그램은 후보 액세스 경로의 비용을 추정하고 최소 비용(추정치)의 경로를 선택한다. 이 프로세스는 동적 SQL 문에 대한 PREPARE 단계에 포함되고 정적 SQL 문에 대한 BIND 단계에 포함된다.

DB2 최적화 프로그램은 최선의 액세스 경로를 선택하기 위해 유용하지만, 해당 결과는 입력 데이터에 의존하며, 이는 보통 최적화 프로그램이 액세스하거나 제어하지 않는다. 독자가 개발자 또는 DBA라면 쿼리를 성능 조정하는 방법을 이해하는 것이 유용하므로 DB2 최적화 프로그램에 최고의 입력을 제공할 수 있다.

이 기사에서는 세계적인 수준의 최적화 프로그램이 DB2에 존재할 때 조차도 쿼리를 성능 조정하는 방법을 이해하는 것이 왜 중요한 지에 대한 이유를 비롯하여 단일 쿼리를 성능 조정하기 위한 방법론을 소개한다. 그러면 Optim Query Tuner의 관련 기능을 사용하여 쿼리를 성능 조정하기 위한 방법론을 설명하기 위해 샘플 쿼리를 사용한다. 이는 단일 쿼리를 이해하고 분석하며 성능 조정하는 데 도움을 주는 측면에서 이점이 많이 있을 수 있다.

이 기사는 주로 DB2 for z/OS에서 SQL 성능 조정을 위해 설계되었지만, 이 기사에서 대부분의 쿼리 최적화 개념과 SQL 성능 조정 방법론이 DB2 for Linux®, UNIX® 및 Windows®에도 적용 가능하다.

이 기사에서 독자 스스로 샘플 쿼리를 시도하려는 경우 이 기사의 다운로드 섹션에서 샘플 프로젝트 파일을 다운로드한 다음에 프로젝트 파일을 Data Studio(수정팩 1 이상을 갖춘 IDE 패키지 또는 독립형) 또는 어느 Optim Query Tuner 제품에나 가져올 수 있다.

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

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

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

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

• 기본적인 단일 쿼리 성능 조정 및 쿼리 형식화 기능은 Data Studio 2.2.1(독립형 및 IDE 모두)에서 사용 가능하다. DB2 for z/OS 및 DB2 for Linux, UNIX 및 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라고 부른다. 위에 있는 모든 제품에서도 사용이 불가능한 기능을 설명하는 경우에는 가능한 특정 제품의 이름을 표시했다.

이 기사는 주로 쿼리 성능 조정 방법론에 주목하고, 요점을 설명하기 위해 Optim Query Tune에서 화면 캡처를 사용하는 것을 참고하자. 이 기사는 Query Tuner를 사용하는 면에서 "방법" 정보를 제공하는 것이 목적이 아니다. 다양한 제품 기능으로 탐색하는 것에 대한 자세한 정보와 OQT에서 다양한 기능을 실행하는 방법에 대한 자세한 소개글을 확인하려면 참고자료 섹션을 참조하기 바란다.

쿼리 최적화의 개요

그림 1과 같이 DB2 최적화 프로그램은 최선의 액세스 경로를 선택한다.

최적화 프로그램은 복수의 입력으로부터 나온 정보에 따라 각 후보 액세스 경로의 비용을 비교한다. 예를 들어, 다음을 확인하자.

• 카탈로그 통계
  DB2 최적화 프로그램은 비용 기반 최적화 프로그램이다. 비용 기반 최적화의 토대는 최적화 프로그램이 모든 후보 액세스 경로의 비용을 정확하게 추정하고 비효율적인 액세스 경로와 효율적인 액세스 경로를 차별화하기 위해 사용하는 통계 세트이다. DB2 카탈로그 테이블에서 통계는 액세스 경로의 비용을 추정하는 데 사용된다. 예를 들어, 카탈로그 테이블 SYSTABLES 및 SYSTABLESPACE의 정보는 테이블에서 얼마나 많은 행과 페이지가 데이터를 보유하는지 알려준다.
• 실제 데이터베이스 설계
  실제 데이터베이스 설계는 테이블 설계, 색인 설계, 구체화된 쿼리 테이블 설계 및 다른 실제 데이터베이스 오브젝트의 설계를 포함한다. 색인 설계는 액세스 경로 선택에 중대한 영향을 준다. 이전 기사에서 언급한 것처럼 하나의 테이블 액세스에 대해 액세스 메소드의 두 가지 유형인 테이블스페이스 스캔(TBSCAN) 및 색인 스캔(IXSCAN)이 있다. 색인 스캔은 특히 테이블이 대규모이지만 규정된 행 수가 적을 때, 대개 데이터에 액세스하는 가장 효율적인 방법이다.
• SQL 문
  SQL 문 자체도 액세스 경로 선택에 영향을 준다. 예를 들어, 잘못 코드된 조건부는 최적화 프로그램이 색인을 사용 가능할 때 조차도 색인 스캔을 사용하지 않도록 방지할 수 있다. 또한, 액세스 경로를 선택하기 전에 최적화 프로그램은 먼저 쿼리 변환 시리즈를 수행하여 사용 가능한 액세스 경로의 수를 늘린다. SQL 문이 잘못 코드되면, 최적화 프로그램이 쿼리를 변환하기에 어렵게 되어, 최적 액세스 경로를 선택하기 위한 옵션이 더 적어진다.
• 액세스 경로 선택을 위한 기타 고려사항
  카탈로그 통계, 실제 데이터베이스 설계 및 SQL 문 자체를 고려하는 것 외에도 DB2 최적화 프로그램은 중앙 프로세서 모델, 중앙 프로세서의 수, 버퍼 풀 크기, RID 풀 크기 및 기타 시스템 자원 설정도 고려한다. 예를 들어, 액세스 경로가 다른 버퍼 풀 크기를 보유하는 경우, 모든 카탈로그 통계가 동일한 경우조차도 하나의 시스템에서 다른 시스템으로 변경할 수 있다.

DB2 최적화 프로그램은 포괄적이며 매우 강력하다. 그러면 DB2 최적화 프로그램이 작업하는 동안 쿼리 성능 조정이 왜 필요한가? 다음 두 가지 이유로 여전히 쿼리 성능 조정이 필요할 수 있다.

• DB2 최적화 프로그램은 전부 알지 못한다.
  DB2 최적화 프로그램은 계획을 기반으로 하는 정보가 많지만, 이는 아직 존재하지 않는 것을 알 수는 없다. 예를 들어, 관련된 통계로 카탈로그를 입력하기 위해 RUNSTATS를 실행하지 않는 한, 최적화 프로그램은 데이터의 특성을 알지 못한다. 이외에도 일부 항목은 런타임까지 인식할 수 없다. 예를 들어, 최적화 프로그램은 해당 쿼리가 실행되어야 호스트 변수 또는 매개변수 마커(쿼리에 포함된 경우)의 값을 인식한다.
• DB2 최적화 프로그램은 전부 제어하지 않는다.
  이전에 언급한 것처럼, 실제 데이터베이스 설계, SQL 문 및 시스템 자원 설정은 최적화 프로그램이 최선의 액세스 경로를 선택하는 방법에 영향을 미치지만, 데이터베이스와 쿼리 설계 모두 DB2 최적화 프로그램이 제어할 수 없는 태스크이다. 이는 DBA 및 개발자가 SQL 성능을 돕거나 해를 끼치는 역할을 하는 곳이다.

쿼리 성능 조정의 목적은 최적화 프로그램에 가능한 최선의 입력을 제공하는 것이므로, 최적화 프로그램은 최선의 액세스 경로를 선택할 수 있다. 이는 애플리케이션 개발자들과 DBA 둘 다의 노력과 연관된다.

애플리케이션 개발자의 경우 다음을 수행한다.

• SQL 코딩 표준 및 가이드라인을 따른다.
  SQL 문을 쓸 때 SQL 코딩 표준 및 가이드라인을 준수해야 한다. 예를 들어, 색인 가능 또는 Stage 1 조건부를 쓰고 조인 조건부(카테시안 조인이라고도 함) 없이 쿼리 쓰기를 방지한다.
• REOPT 바인드 옵션을 적절히 사용한다.
  변수가 있는 SQL 문의 경우, 최적화 프로그램은 기본 필터 인수를 사용하여 바인드 시간에 최선의 액세스 경로를 판별한다. 일부 경우에 액세스 경로는 명령문에 호스트 변수, 매개변수 마커 또는 특수 레지스터가 포함된 경우 런타임 시 원활하게 수행하지 않는다. 바인드 시간 또는 실행 시간에 액세스 경로를 다시 최적화하기 위한 REOPT 바인드 옵션을 사용할 수 있다.

DBA의 경우 다음을 수행한다.

• 충분하고 정확한 통계를 수집한다.
  충분하지 않거나 정확하지 않은 통계는 후보 액세스 경로에 정확하지 않은 비용 추정을 초래하고, 이는 비효율적인 액세스 경로의 선택을 위한 가장 일반적인 이유이다. 반면, 모든 통계의 콜렉션과 갱신은 필요하지 않은 자원을 너무 많이 소모할 것이다. 데이터 분배에서 INSERT, UPDATE 및 DELETE 조작과 변경을 고려하면, 최소한의 자원 소모를 통해 올바른 통계를 정기적으로 수집해야 한다.
• 색인 설계를 최적화한다.
  로컬 조건부와 조인 조건부로 효율적인 액세스를 지원하기 위한 색인을 설계해야 한다. 데이터 정렬을 방지하고 색인 전용 액세스를 제공하기 위해 색인을 설계해야 할 수도 있다.
• 전체적으로 애플리케이션을 성능 조정한다.
  애플리케이션의 우수한 성능을 보장하려면 애플리케이션을 전체적으로 성능 조정하는 것이 필요하다. 하나의 명령문을 각각 평가하여 전체 애플리케이션을 성능 조정하는 데 필요한 노력은 엄청나다. 또한, 하나의 명령문에서 성능 개선이 애플리케이션에서 다른 명령문의 성능을 퇴보하게 할 수도 있다. 그러므로, 전체적으로 애플리케이션을 성능 조정하는 것이 중요하며, 이는 또한 워크로드 성능 조정이라고도 한다. 이 기사는 단일 쿼리 성능 조정에 주목할 것이다. 이 시리즈의 다음 기사에서는 워크로드 성능 조정을 자세히 소개하기 위해 이 기사의 방법론을 확장할 것이다.

이 기사는 잠재적인 쿼리 성능 문제에 대한 방법론과 이러한 잠재적인 문제를 다루는 방법을 설명한다. Optim Query Tuner를 사용하면 프로세스가 더 간단해진다.

쿼리 성능 조정 방법론

쿼리 성능 조정 방법론의 개요

쿼리 성능 조정을 수행하기 위해 먼저 성능 조정하려는 것을 이해해야 한다. 이 경우에 이는 해당 쿼리 자체와 최적화 프로그램이 선택한 현재 액세스 계획이며, 그 다음에 이를 성능 조정하는 방법을 알아야 한다.

이 개념을 기반으로 쿼리를 완전히 성능 조정하기 위해 다음 태스크를 수행해야 하며, 이는 다음과 같이 Query Tuner에서 수행할 수 있다.

• 문제점 쿼리를 형식화하여 해당 쿼리의 논리를 읽고 이해하기에 더 간편하게 만든다.
• 관련된 통계로 문제점 쿼리에 어노테이션을 작성하여 DB2 최적화 프로그램이 추정을 위해 사용하고 있는 내용을 더 잘 이해한다.
• 쿼리 액세스 계획을 분석하여 데이터를 액세스할 때 최적화 프로그램이 내리는 선택을 시각화한다.
• 통계 분석을 수행하여 DB2 최적화 프로그램이 항상 가장 최신이고 가장 필요한 통계를 보유하도록 보장한다.
• 조건부 분석을 수행하여 조건부가 가능한 한 선택적이 되는지 확인한다.
• 색인 분석을 수행하여 필요하지 않은 테이블 스캔을 방지하는 데 도움을 주도록 올바른 색인이 존재하도록 보장한다.

다음 섹션에서 목록 1의 SQL 문은 쿼리 성능 조정의 개별 태스크를 각각 자세히 설명하기 위한 샘플로 사용된다. 예상 가능한 대로 이러한 태스크들 사이에 상호의존성이 매우 높다. 예를 들어, 수집된 통계를 변경하는 것은 조건부 분석의 결과에 영향을 미칠 가능성이 매우 높다. 또한, 특정 성능 문제가 해결될 때까지 하나 이상의 이러한 태스크를 여러 번 반복해야 할 수 있다.

SELECT CCUS.CUST_FIRST_NAME
     , CCUS.CUST_LAST_NAME
     , CINT.CUST_INTEREST_RANK
     , CILO.CUST_INTERST
FROM CUST_CUSTOMER AS CCUS
     , CUST_INTEREST_LOOKUP AS CILO
     , CUST_INTEREST AS CINT
WHERE ( CCUS.CUST_CITY = 'Singapore' 
        AND CCUS.CUST_PROV_STATE = 'Singapore'            
        AND CCUS.CUST_CODE IN ( 
                                SELECT COHE.CUST_CODE
                                FROM CUST_ORDER_HEADER AS COHE
                                     , CUST_ORDER_STATUS AS COST
                                WHERE ( COHE.CUST_ORDER_DATE 
                                            >='2009-01-01 00:00:00.001'            
                                        AND COST.CUST_ORDER_STATUS IN ( 'Shipped', 
                                            'Back-ordered', 'In-process' ) 
                                        AND COHE.CUST_ORDER_STATUS_CODE 
                                            = COST.CUST_ORDER_STATUS_CODE
                                      )
                              )
        AND CCUS.CUST_CODE = CINT.CUST_CODE
        AND CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE
      )
ORDER BY CCUS.CUST_LAST_NAME ASC 
         , CCUS.CUST_FIRST_NAME ASC 
         , CINT.CUST_INTEREST_RANK ASC

문제점 쿼리 형식화

쿼리를 성능 조정하기 전에 문제점 쿼리의 다음 측면을 이해해야 한다.

• 쿼리의 시맨틱: 쿼리에서 어느 테이블이 액세스되는가? 각 참조된 테이블에서 어느 종류의 조건부가 사용되는가? 어느 종류의 조건부가 참조된 테이블을 조인하는 데 사용되는가?
• 쿼리의 액세스 경로: 어떻게 테이블이 액세스되는가? 전체 테이블이 스캔되는가 아니면 색인별로 액세스되는가? 색인인 경우 어느 색인인가? 조인 시퀀스와 조인 메소드는 무엇인가?

그림 2에서 알 수 있듯이 원래 형식화되지 않은 문제점 쿼리는 읽고 이해하기에 어렵다.

Optim Query Tuner는 문제점 쿼리를 형식화할 수 있으며, 이는 분석의 훌륭한 시작점을 제공한다. 형식화된 쿼리에서 각 테이블 참조, SELECT 절 아래 각 컬럼 참조 및 각 조건부가 자체 행에 표시된다. 이 기사에서 샘플 쿼리의 경우, 형식화된 쿼리가 그림 3에 표시된다.

독자가 예상 가능한 대로, 길고 복잡한 SQL에 대해 쿼리를 간단하게 형식화하면 DBA 시간을 절약할 수 있다. 이제 어느 테이블에 액세스되는지, 쿼리에 얼마나 많이 있는지, 그리고 이러한 테이블이 어떻게 조인되는지를 알아보기에 간편하다. 형식화된 쿼리는 다음 측면에 기능을 제공한다.

• 복잡한 SQL의 섹션을 펼치고 접어서 참조된 뷰 및 서브쿼리와 같이 쿼리의 부분으로 더 자세히 파고들어보자.
• 특정 테이블이 SQL에서 액세스되는 방법을 간편하게 확인한다. 형식화된 쿼리에서 어느 행이나 클릭하면, 동일한 테이블에서부터 나온 컬럼 또는 테이블 참조가 들어있는 쿼리의 다른 행들도 강조표시된다.
• 로컬 조건부 또는 조인 조건부 및 테이블 참조와 같이 다양한 기준에 따라 조건부의 형식화 정렬을 사용자 정의한다.

그림 3과 같이 형식화된 쿼리로 다시 돌아가서 다음을 확인할 수 있다.

• 쿼리는 다음 세 가지 테이블에 액세스한다. 즉, 규정된 고객 이름과 관심사 정보를 얻기 위한 CUST_CUSTOMER, CUST_INTEREST 및 CUST_INTEREST_LOOKUP이다.
• 세 가지 테이블은 동등(equal) 조인 조건부로 조인된다.
• CUST_CUSTOMER에서 세 가지 조건부가 있다. 처음 두 개의 조건부는 단순한 동등(equal) 조건부이다(CCUS.CUST_CITY = 'Singapore', CCUS.CUST_PROV_STATE = 'Singapore'). 세 번째 조건부는 IN 목록 조건부이며, 이는 다음과 같이 관련없는 서브쿼리가 들어있다.
  • 서브쿼리는 CUST_ORDER_HEADER 및 CUST_ORDER_STATUS 테이블에 액세스하여 규정된 고객 코드를 얻고, 이러한 두 개의 테이블은 동등 조인 조건부로 조인된다.
  • CUST_ORDER_HEADER에 범위 로컬 조건부가 있고, CUST_ORDER_STATUS에 IN 목록 로컬 조건부가 있다.
• 다른 두 개의 테이블에 로컬 조건부가 없다(CUST_INTEREST 및 CUST_INTEREST_LOOKUP).
• 결과는 고객 이름과 관심사로 정렬된다.

또한 Query Tuner는 DB2 최적화 프로그램이 쿼리를 변환한 위치를 간단하게 포착하게 된다. 상기시키기 위해 변환은 쿼리의 성능을 개선하도록 노력하기 위해 DB2 최적화 프로그램이 쿼리를 조정하는 것이다. 예를 들어, 이는 조인 시퀀스 평가를 가능하게 하기 위해 이행 종결에 대한 조건부를 추가할 수 있다.

설명으로서 A.CUSTNO BETWEEN ? AND ? AND C.CUSTNO = A.CUSTNO와 같은 조건부를 고려하면, DB2는 조건부 C.CUSTNO BETWEEN ? AND ?도 사실이어야 함을 이끌어낼 수 있으므로, DB2 쿼리 변환은 또 다른 색인을 고려하기 위해 이를 사용하는 조건부를 추가할 수 있다.

이 기사에서 샘플의 경우 변환된 쿼리는 그림 4에 표시된다.

확인 가능한 대로, 최적화 프로그램은 IN 목록 서브쿼리를 처리하는 가상 테이블을 작성한다. 게다가 관련없는 서브쿼리는 상관된 서브쿼리로 변환되었다. 이는 DB2 for z/OS V9.1에 도입된 최적화이며, 이는 몇 가지 독립적인 쿼리 블록이라기 보다는 전체적으로 쿼리를 최적화하는 데 DB2를 사용한다. 쿼리가 전체적으로 최적화될 때, DB2는 단일 쿼리 블록에서 다른 블록으로 영향을 고려할 수 있고, 최적 쿼리 경로를 판별하기 위해 쿼리 블록을 다시 정렬하는 것을 고려할 수 있다.

문제점 쿼리 어노테이션 작성하기

형식화된 SQL 조건부 및 테이블 참조와 함께, Query Tuner는 관련된 카탈로그 통계의 어노테이션을 포함하고 카디널리티 및 추정된 규정된 행과 같이 비용 추정 정보를 포함한다. 이는 그림 5에 표시된다. 이 정보를 확인 가능하도록 준비하면 DBA가 분석을 가속화하고 긴급 상황에 대한 중단 시간을 줄이는 데 유용할 수 있다.

쿼리에서 각 테이블 참조의 경우, Query Tuner는 다음 어노테이션을 추가한다.

• CARDF(그림 5의 1번 참조): 테이블에서 총 행의 수를 선언하는 테이블 카디널리티. CUST_CUSTOMER는 가장 큰 테이블(31,284)인 반면, CUST_INTEREST_LOOKUP은 가장 작은 테이블이다(338).
• QUALIFIED_ROWS(그림 5의 2번 참조): 테이블에 로컬 조건부를 적용한 후 규정된 행의 수. CUST_CUSTOMER 및 CUST_INTEREST에 거의 동일한 테이블 카디널리티가 있지만, CUST_CUSTOMER의 규정된 행은 1을 넘는 것에 불과하며, 이는 테이블에 매우 선택적인 로컬 조건부가 있음을 의미한다. CUST_INTEREST와는 반대로, 규정된 행의 수는 카디널리티와 동일하다(31,255). 이는 테이블에 필터링이 없음을 표시한다. 테이블 CUST_CUSTOMER에 두 개의 로컬 조건부가 있는 반면 CUST_INTEREST에 로컬 조건부가 없기 때문에 이는 설명 가능하다.
• NPAGES(그림 5에서 3번 참조): 이 테이블의 행이 나타나는 총 페이지 수.

쿼리에서 각 조건부에 대해, Query Tuner는 다음과 같이 조건부에서 참조된 컬럼에 대한 통계 어노테이션을 추가하고, 조건부에 대해 비용 추정도 추가한다.

• COLCARDF(그림 5의 4번 참조): 컬럼에서 구별되는 값의 추정 숫자를 선언하는 컬럼 카디널리티이다. 조건부에 둘 이상의 컬럼이 있으면, 각 참조된 컬럼에 대한 컬럼 카디널리티는 조건부에서 컬럼이 나타나는 것과 동일한 순서로 백래시("/")로 구분된다.
• MAX_FREQ(그림 5에서 5번 참조): 모든 가능한 컬럼 값의 최대 빈도. 특정 컬럼 값의 빈도는 컬럼에 대한 특정 값이 들어있는 테이블에서 행의 백분율이다. 예를 들어, 하나의 컬럼(COLCARDF=5)에 대한 다섯 가지 구별된 값이 있고 데이터가 동일하게 분배된 경우, 각각의 다른 컬럼 값은 테이블 행의 20%를 차지하므로 MAX_FREQ는 약 20%이다. 컬럼 카디널리티가 5이지만 MAX_FREQ가 20%보다 훨씬 더 큰 경우, 이는 테이블의 데이터가 컬럼에 동일하게 분배되지 않음을 의미한다. 다시 말해서, 컬럼에 데이터 오차가 있다.
• FF(그림 5에서 6번 참조): 조건부에 대한 필터 인수. 필터 인수는 조건부가 참인 경우에 대해 테이블에서 행의 비율을 추정하는 0에서 1 사이의 수이다. 필터 인수는 조건부가 얼마나 선택적인지 표시된다. 조건부가 선택적이면 선택적일수록, 조건부가 더 빨리 적용되어야 한다.

쿼리 액세스 계획 분석

Query Tuner는 데이터 서버가 쿼리를 실행하기 위해 사용하는 처리의 시각화를 제공한다. 이 시각화는 액세스 계획 그래프라고 한다. 액세스 계획 그래프에서부터 쿼리가 어떻게 처리될 것인지에 관련하여 최적화 프로그램이 내리는 선택이 무엇인지 확인하고, 이러한 선택에 대한 이유를 확인할 수 있다. 다이어그램은 테이블, 색인, 조작 및 리턴된 데이터를 표현하는 노드를 구성한다. 노드는 프로세스의 플로우를 표시하는 링크로 정리되고 연결된다. 그래프는 왼쪽에서 오른쪽으로, 아래에서 위로 읽힌다. 그리고 각 노드는 통계, 비용 추정치, 선택 정보로 어노테이션이 작성되고 그리하여 이는 액세스 계획 플로우를 판별하는 데 사용된다.

액세스 계획을 이해하는 것은 성능을 이해하고 영향을 미치는 것 뿐만 아니라 성능을 안정화하는 데에도 중요하다. 액세스 경로를 읽고 이해하는 것에 대한 자세한 정보는 이 시리즈의 이전 문서를 참조하자.

그림 6은 이 기사에서 샘플 쿼리에 대한 Query Tuner로 생성된 액세스 경로 그래프이다.

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

그림 6의 액세스 계획 그래프에서부터 다음을 확인할 수 있다.

• 쿼리는 두 개의 쿼리 블록인 QB1(그림 6의 1번 참조) 및 QB2(그림 6의 2번 참조)가 들어있다. QB2가 IN 목록 서브쿼리를 표현하는 반면, QB1은 기본 서브쿼리이다.
• QB2는 외부 쿼리 블록 QB1에서 CUST_CUSTOMER 테이블로 조인되며, 이는 IN 목록 서브쿼리가 원본 쿼리에서 관련없는 서브쿼리일지라도 상관된 서브쿼리로 변환되었음을 의미한다.
• QB1에 대한 액세스 계획은 아래와 같이 요약될 수 있다. TBSCAN(CUST_CUSTOMER) NLJ ISCAN(CUST_INTEREST) NLJ ISCAN(CUST_INTEREST_LOOKUP)
  • QB1에서 3개 테이블은 중첩 루프 조인(NLJ)으로 조인된다
  • QB1에서 3개 테이블은 다음 조인 시퀀스로 조인된다. CUST_CUSTOMER -> CUST_INTEREST -> CUST_INTEREST_LOOKUP
  • CUST_CUSTOMER는 테이블 스캔으로 액세스되는 반면, CUST_INTEREST 및 CUST_INTEREST_LOOKUP은 색인 스캔으로 액세스된다.
• QB2에 대한 액세스 계획은 아래와 같이 요약될 수 있다. TBSCAN(CUST_ORDER_HEADER) NLJ ISCAN(CUST_ORDER_STATUS)
  • QB2에서 2개 테이블은 중첩 루프 조인(NLJ)으로 조인된다.
  • QB2에서 2개 테이블은 다음 조인 시퀀스로 조인된다. CUST_ORDER_HEADER -> CUST_ORDER_STATUS
  • CUST_ORDER_HEADER는 테이블 스캔으로 액세스되는 반면에, CUST_ORDER_STATUS는 색인 스캔으로 액세스된다.

그림 6의 액세스 계획 그래프에서부터 다음과 같이 일부 초기 성능 분석을 수행할 수 있다.

• 내부 테이블로의 액세스(CUST_INTEREST 및 CUST_INTEREST_LOOKUP)는 색인 스캔에 의해 이루어진다. 이는 합리적으로 효율적인 액세스 계획이다.
• 외부 서브쿼리(CUST_CUSTOMER) 및 내부 서브쿼리(CUST_ORDER_HEADER)의 선행 테이블로의 액세스는 테이블 스캔에 의해 이루어지며, 이는 다음과 같이 잠재적인 문제가 될 수 있다.
  • CUST_CUSTOMER에 대한 테이블 카디널리티는 약 30000이다. 하지만 이는 선행 테이블이고 테이블 스캔으로 한 번만 액세스될 것이므로, 이는 일부 성능 문제점을 야기할 수 있지만, 손상이 되지 않을 것이다.
  • 내부 서브쿼리는 상관된 서브쿼리이다. 얼마나 많은 규정된 행이 외부 테이블(CUST_CUSTOMER)로부터 리턴되는 지에 따라, 여러 번 액세스될 수 있다. 그림 5에서 어노테이션 또는 그림 6에서 액세스 계획 그래프로부터 CUST_CUSTOMER에 대한 추정된 규정된 행은 1임을 확인할 수 있다. 다시 말해서, 최적화 프로그램은 CUST_ORDER_HEADER가 한 번만 스캔될 것이라고 생각한다. 테이블 카디널리티가 약 50000이라고 고려하면, 이는 성능 손상이 되어서는 안 된다.

지금까지는 CUST_CUSTOMER가 외부 서브쿼리에서 선행 테이블이기 때문에 한 번만 스캔될 것이라고 확신한다. 하지만, CUST_CUSTOMER로부터 규정된 행이 기존 통계와 조건부로 계산되기 때문에 CUST_ORDER_HEADER가 한 번만 스캔될 것인지 확실하지 않다.

• CUST_CUSTOMER에 대한 테이블 카디널리티는 31284이다(그림 5의 7번 참조).
• 테이블에서 두 개의 로컬 조건부가 있으며, 이는 매우 선택적이다.
  • CCUS.CUST_CITY = 'Singapore', FF=0.00727(그림 5의 8번 참조)
  • CCUS.CUST_PROV_STATE = 'Singapore', FF=0.004(그림 5의 9번 참조)
• 그러므로 최적화 프로그램으로 추정된 규정된 행은 약 31284*0.00727*0.004 = 1이다.

CUST_CUSTOMER로부터 추정된 규정된 행이 정확하지 않으면 어떻게 되는가? 예를 들어, 두 개의 로컬 조건부가 최적화 프로그램이 추정하는 것만큼 선택적이지 않으면 어떻게 되는가? CUST_ORDER_HEADER가 여러 번 액세스될 수 있기 때문에 이는 심각한 성능 문제를 야기할 수 있다.

의심스러운 성능 병목현상을 유효성 검증하는 한 가지 방법은 쿼리의 런타임 통계로 살펴보는 것이며, 이는 IFCID 318에서 성능 추적을 켜서 확보할 수 있다. 또 다른 옵션은 Query Tuner를 사용하여 명령문 캐시에서부터 명령문을 캡처하고 명령문 런타임 정보를 보는 것이다. 이는 그림 7과 같다.

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

강조표시된 행(그림 7에서 "B"로 끝나는 것)은 이 기사에서 샘플 쿼리에 대한 런타임 정보를 보여준다. 확인 가능한 대로, 쿼리는 세 번 실행되었고, 평균 경과 시간은 약 307초이며, 이는 매우 느리다. 테이블 스캔(STAT_RSCN)의 총 수는 약 1764이며, 예를 들어, 실행 당 580(1764/3) 테이블 스캔 이상이다. 이는 액세스 계획 그래프로부터 추정할 수 있는 내용보다 훨씬 더 많다. 이는 약 2이다(하나는 CUST_CUSTOMER이고, 또 다른 하나는 CUST_ORDER_HEADER임). 이는 CUST_CUSTOMER로부터 추정된 규정된 행 수가 현실에서 멀어진 것이라는 의심을 추가적으로 확인한다.

이를 유효성 검증하는 또 다른 방법은 규정된 행의 실제 값을 계산하는 것보다 아래로 쿼리를 실행하는 것이다.

SELECT COUNT(*)
FROM CUST_CUSTOMER AS CCUS
WHERE CCUS.CUST_CITY = 'Singapore' AND CCUS.CUST_PROV_STATE = 'Singapore'

위의 쿼리 계수의 결과는 CUST_CUSTOMER로부터 약 588개의 규정된 행이 있음을 보여준다. 다시 말해서, 최적화 프로그램은 테이블에서 로컬 조건부의 선택을 높게 추정(overestimate)했다. 이 기사의 다음 섹션에서 통계, 조건부 및 색인 관점에서부터 문제점 쿼리를 분석할 것이며, 그 다음에 높은 추정이 발생한 이유 뿐만 아니라 이를 해결하는 방법을 보여준다.

통계 분석 수행

쿼리 어노테이션 정보로부터 어느 종류의 통계가 사용 가능하고 어느 종류의 통계가 누락되는지 간편하게 확인할 수 있다. 이 기사에서 쿼리의 경우, 참조된 테이블, 컬럼 및 색인의 기본 통계는 테이블 카디널리티, 컬럼 카디널리티 및 기타 등등과 같이 수집되었다.

한편, 컬럼 빈도와 같은 일부 분배 통계는 절대 수집되지 않았다. 예를 들어, 그림 8과 같이 조건부 CCUS.CUST_CITY = 'Singapore'에서 컬럼 CUST_CITY에 대한 MAX_FREQ는 누락된 것으로 표시된다. 통계를 누락하면 최적화 프로그램이 조건부의 선택을 높게 추정하거나 낮게 추정할 수 있고, 결과적으로 비효율적인 액세스 경로를 선택하도록 할 수 있다.

통계가 이전에 수집되었지만 오랜 기간 동안 새로 고쳐지지 않아서 이제는 사용되지 않는 경우에도 동일한 문제가 발생할 수 있다. 이는 데이터가 마지막 통계 수집 이후로 급격하게 변경된 경우에 특히 잘 적용된다. 마지막으로 통계가 수집된 시간을 표시하는 액세스 계획 그래프의 테이블 또는 색인 노드에서 속성 RUNSTATS TIMESTAMP가 있다. 참고로 통계가 사용되지 않는 것으로 고려되기 전에 얼마나 오래되었는지 정의하기 위해 Query Tuner 환경 설정에 임계값을 지정할 수 있다. 기본값으로 통계는 1년 이상된 경우 사용되지 않는 것으로 식별될 것이다.

다른 통계가 다른 시점에 수집되는 경우, 통계가 DB2 for z/OS에서 서로 일관되지 않는 상황이 발생할 수 있다. 예를 들어, 테이블 레벨 통계가 특정 테이블에서 수집되고 이후에 엄청난 행 수가 이 테이블에 삽입되면, 색인을 위해 통계를 수집하면 색인 컬럼이 테이블 카디널리티보다 더 큰 컬럼 카디널리티만 보유하게 될 수 있다. 일관적이지 않은 통계는 최적화 프로그램이 잘못된 액세스 경로를 선택하게 잘못 이끌 수도 있다.

조건부 분석 수행

한편, 그림 9와 같이 어노테이션 뷰에서 어느 잠재적인 문제가 있는지 찾기 위해 조건부 분석을 수행할 수도 있다.

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

그림의 어노테이션 정보에 따르면, 테이블 참조 CUST_CUSTOMER(CCUS)는 두 가지 로컬 조건부가 있으며, 이는 각 필터 인수 0.00727 및 0.004를 통해 매우 선택적이다.

빈도 통계가 수집되지 않았으므로(MAX_FREQ=(missing)), 각 조건부의 필터 인수는 데이터가 컬럼에서 동일하게 분배되었다고 가정하여 계산된다. 예를 들어, CUST_CITY의 컬럼 카디널리티는 1376이므로, 조건부의 필터 인수 CCUS.CUST_CITY='Singapore'가 1/1376=0.00727로 계산된다.

조건부 선택의 추정치에 얼마나 높은 신뢰도가 나타날 수 있는가? 데이터가 컬럼에 오차가 있으면, 예를 들어, 데이터의 다수가 Singapore의 도시 이름을 보유하면, 현재 선택은 엄청나게 높게 추정될 수 있다. 더 정확한 추정치를 얻기 위해 테이블 CUST_CUSTOMER의 컬럼 CUST_CITY 및 CUST_PROV_STATE에서 빈도 통계를 수집해야 한다.

또 다른 잠재적인 문제점은 내부 서브쿼리에서 테이블 참조 CUST_ORDER_HEADER(COHE)의 로컬 조건부이다(그림 9도 참조). 이는 범위 조건부이고 사용 가능한 빈도 및 히스토그램 통계가 없기 때문에, 필터 인수는 기본 통계 정보 HIGH2KEY 및 LOW2KEY로 계산된다. 추정은 컬럼에서 데이터 오차가 있으면 정확하지 않을 수 있다. 더 정확한 추정치를 얻기 위해 테이블 CUST_ORDER_STATUS_CODE의 컬럼 CUST_ORDER_DATE에서 히스토그램 통계를 수집해야 한다.

로컬 조건부의 선택을 분석했으니, 이제 테이블 선택을 살펴보자.

외부 서브쿼리의 세 개의 테이블에 대해 CUST_INTEREST_LOOKUP(CILO) 및 CUST_INTEREST(CINT)에서 로컬 조건부가 없다. 따라서 이러한 테이블 중 하나가 선행 테이블로 처리되면, 테이블 선택은 0이며, 이는 매우 잘못된 것이다.

CUST_CUSTOMER(CCUS)가 선행 테이블로 처리되면 두 개의 로컬 조건부를 고려할 때, CUST_CUSTOMER의 테이블 선택은 0.00727*0.004이고, CUST_CUSTOMER의 규정된 행은 약 1이다(31284*0.00727*0.004). 이는 매우 선택적으로 보이지만, 이 값은 두 개의 컬럼(CUST_CITY, CUST_PROV_STATE)이 서로 관련없다는 가정을 사용하여 계산되었으며, 이는 사실이 아니다. Singapore은 Singapore 주의 하나의 도시이기 때문에, 테이블 선택의 더 정확한 추정치를 얻기 위해 컬럼 그룹의 카디널리티와 빈도 통계를 수집해야 한다(CUST_CITY, CUST_PROV_STATE).

CUST_CUSTOMER가 처리된 이후, 조인 조건부 CCUS.CUST_CODE = CINT.CUST_CODE는 테이블 CUST_INTEREST(CINT)의 로컬 조건부로 고려될 수 있다. CUST_INTEREST의 테이블 카디널리티가 약 31255라고 하면, CUST_CUSTOMER로부터 각 규정된 행에 대해 CUST_INTEREST에서 일치하는 레코드가 하나만 있음을 확인할 수 있다. 즉, 테이블 CUST_INTEREST는 조인 조건부를 통해 매우 선택적이 될 수 있었다.

CUST_CUSTOMER 및 CUST_INTEREST가 처리된 이후에 조인 조건부 CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE는 테이블 CUST_INTEREST_LOOKUP(CILO)에서 로컬 조건부로 고려될 수 있었다. CUST_INTEREST_LOOKUP의 테이블 카디널리티가 약 338이라면, CUST_INTEREST로부터 각 규정된 행에 대해 CUST_INTEREST_LOOKUP에서 약 13(338/26)개의 일치하는 레코드가 있음을 확인할 수 있으며, 이는 합리적으로 훌륭한 선택 레벨이다.

위의 통계와 조건부 분석을 통해 어느 통계가 수집되어야 하는지에 대한 분명한 생각을 가지게 된다. 이러한 통계를 수집한 후에 그림 10과 같이 새 액세스 계획 그래프를 얻게 된다.

이 새 액세스 계획 그래프로부터 다음을 확인할 수 있다.

• 테이블 CUST_CUSTOMER에서 컬럼 그룹(CUST_CITY 및 CUST_PROV_STATE)의 통계가 수집되었으므로, DB2 최적화 프로그램은 이제 로컬 필터링이 1이 아니라 590이 된 후에 규정된 행의 올바른 숫자를 인식한다.
• 내부 서브쿼리의 조인 시퀀스가 변경되었다. 원본 시퀀스는 CUST_ORDER_HEADER-> CUST_ORDER_STATUS이고, 이제는 CUST_ORDER_STATUS -> CUST_ORDER_HEADER이다. CUST_ORDER_HEADER 테이블의 CUST_ORDER_DATE 컬럼에서 히스토그램 통계가 수집되었기 때문이다.
• CUST_CUSTOMER로부터의 올바른 규정된 행을 통해 내부 서브쿼리는 관련없는 서브쿼리로 처리되며, 이는 작업 파일로 구체화되면 메모리에서 최소 색인 스캔으로 액세스된다.

그림 11에서 "S"로 끝나는 행은 RUNSTATS 이후에 샘플 쿼리의 런타임 정보를 보여준다. 평균 경과 시간은 약 2.8초이며, RUNSTATS 이전(약 307초)과 비교하면 엄청난 성능 개선이다.

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

그림 11에서도 표시되는 것처럼 쿼리의 각 실행에 대한 두 개의 테이블 스캔이 여전히 있음을 확인할 수 있다. 그림 10의 액세스 계획 그래프에서부터 두 개의 테이블 스캔이 각각 CUST_CUSTOMER 및 CUST_ORDER_HEADER로부터 나오는 것을 간편하게 알아볼 수 있다. 다음 섹션에서 이는 색인 분석으로 어떻게 수정할 수 있는지 확인할 것이다.

색인 분석 수행

Optim Query Tuner는 HTML과 텍스트 형식으로 특정 SQL 문으로 수반되는 테이블, 색인 및 조건부에 대한 정보가 들어있는 보고서를 제공한다. 이 기사의 샘플 쿼리의 경우 그림 12는 색인 보고서를 보여주며, 이는 참조된 테이블의 기존 색인을 설명한다.

편리하게 논의하기 위해 기존 색인이 표 1에 요약되어 있다.

이제 다음과 같이 그림 3에서 형식화된 쿼리로부터 추출한 대로 테이블 CUST_CUSTOMER에 대한 조건부를 살펴보자.

• CCUS.CUST_CITY = 'Singapore'
• CCUS.CUST_PROV_STATE = 'Singapore'
• CCUS.CUST_CODE = CINT.CUST_CODE

표 1과 위의 조건부에서 기존 색인을 고려해보면, 조인 조건부(CCUST.CUST_CODE = CINT.CUST_CODE)만 기존 색인 IDX_CUST_CUSTOMER로부터 혜택을 누릴 수 있다. 두 개의 로컬 조건부는 어떠한 기존 색인에서부터나 색인 스캔의 일치 또는 스크리닝의 혜택을 누릴 수 없다.

색인 스캔으로 로컬 조건부 및 조인 조건부를 둘 다 지원하려면 컬럼 CUST_CITY, CUST_PROV_STATE 및 CUST_CODE에서 색인을 작성해야 한다.

테이블 CUST_INTEREST 및 CUST_INTEREST_LOOKUP에 대해 각각은 조인 조건부가 있고, 기존 색인은 아래와 같이 조인 조건부에서 일치하는 색인 스캔을 지원할 수 있다.

• CCUS.CUST_CODE = CINT.CUST_CODE
• CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE

테이블 CUST_ORDER_HEADER에 대해 기존 색인 모두는 아래와 같이 테이블에서 로컬 및 조인 조건부를 지원하지 않는다.

• COHE.CUST_ORDER_DATE >= '2009-01-01 00:00:00.001'
• COHE.CUST_ORDER_STATUS_CODE = COST.CUST_ORDER_STATUS_CODE

색인 스캔으로 로컬 조건부 및 조인 조건부를 둘 다 지원하기 위해 컬럼 CUST_ORDER_DATE 및 CUST_ORDER_STATUS_CODE에서 색인을 작성해야 한다. 또한, 색인 전용 액세스로 더 나은 성능을 얻기 위해 키 컬럼으로서 CUST_CODE도 추가할 수 있다. 이 테이블이 IN 목록 서브쿼리의 일부임을 고려하여, CUST_CODE가 첫 번째 키 컬럼이면, DB2 최적화 프로그램이 상관된 서브쿼리로 서브쿼리에 액세스하기 위해 글로벌 쿼리 최적화를 고려할 수 있다.

테이블 CUST_ORDER_STATUS의 경우, 첫 번째 색인 BQT01_CUST_ORDER_STATUS는 로컬 조건부에 대해 색인 액세스를 지원하지만 조인 조건부를 지원할 수 없다. 다음과 같이 두 번째 색인 CUST_ORDER_STAT_PK는 조인 조건부에 대해 색인 액세스를 지원할 수 있지만 로컬 조건부를 지원할 수 없다.

• COST.CUST_ORDER_STATUS IN ( 'Shipped', 'Back-ordered', 'In-process' )
• COHE.CUST_ORDER_STATUS_CODE = COST.CUST_ORDER_STATUS_CODE

테이블 CUST_ORDER_STATUS에서 더 우수한 성능을 달성하려면 컬럼 CUST_ORDER_STATUS_CODE 및 CUST_ORDER_STATUS 둘 다로 색인을 작성해야 한다.

이전 색인 분석을 고려해 볼 때, 더 우수한 성능을 달성하기 위해 다음 세 가지 색인을 제안한다.

이러한 세 가지 새 색인을 작성하면 그림 13과 같이 새 액세스 계획 그래프를 얻게 된다.

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

새롭게 작성된 색인을 통해 모든 테이블은 색인 스캔으로 액세스된다. 서브쿼리는 상관된 서브쿼리로 다시 변경되며, 서브쿼리에서 두 개의 테이블은 색인만 사용하여 액세스되며 이는 최고의 성능을 제공한다.

그림 14에서 "I"로 끝나는 행은 새 색인을 작성한 이후에 샘플 쿼리의 런타임 정보를 보여준다. 평균 경과 시간은 약 0.246초이며, RUNSTATS 이후 성능(약 2.8초)과 비교하면 개선이 추가된 것이다.

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

통계, 조건부 및 색인에 대한 단계별 분석을 통해 문제점 쿼리의 성능은 엄청나게 개선되었다. 표 2는 성능 개선을 요약한 것이다. 행 1은 성능 조정 이전의 런타임 통계를 보여준다. 행 2는 관련된 통계를 수집한 후의 통계를 보여주고, 행 3은 제안된 색인을 작성한 이후의 결과를 보여준다.

Optim Query Tuner로 쿼리 성능 조정 가속화하기

지금까지 쿼리 어노테이션, 액세스 계획 그래프 및 기타 등등의 Optim Query Tuner 기능의 지원을 통해 문제점 쿼리를 분석하고 성능 조정할 수 있는 방법을 살펴보았다. 도구가 매우 직관적이고 사용자에게 익숙하다고 해도 도구가 제공하는 강력한 기능을 완전히 활용하려면 여전히 DB2 최적화 프로그램 및 SQL 액세스 경로에 대한 충분한 배경 지식을 갖추어야 한다. 물론, 분석하는 데 시간을 어느 정도 쓰는 것도 필요하다.

하지만, 대부분의 경우에 쿼리 성능 조정을 수동으로 수행하지 않아도 된다. Optim Query Tuner는 권장사항을 제공하여 쿼리 성능 조정을 자동화하는 어드바이저 시리즈를 제공하며, 이는 성능 문제를 해결하기 위해 제품으로부터 직접 검토하고 구현할 수 있다.

• 통계 어드바이저: 액세스 경로 선택이 사용 가능하도록 하기 위해 필수 통계를 수집하기 위한 권장사항.
• 쿼리 어드바이저: 더 나은 효율성을 위해 쿼리를 다시 쓰기 위한 권장사항.
• 색인 어드바이저: 성능을 개선하는 데 유용할 수 있는 색인에 대한 권장사항.

권장사항: 이는 일반적으로 다음 순서로 어드바이저를 실행하도록 제안된다.

1. 통계 어드바이저를 실행하고 통계를 최신으로 만드는 조치를 취한다.
2. 쿼리 어드바이저를 실행하고, 필요하고 가능한 경우 SQL을 다시 쓴다.
3. 색인 어드바이저를 실행하고 새 색인을 작성하거나 기존 색인을 조정한다. 정확한 완료된 통계는 훌륭한 액세스 경로 조언을 추진하는 데 유용할 것이며, 색인 어드바이저는 당연히 훌륭한 통계가 있을 때에만 실행되어야 한다.

다음 섹션은 이전 어드바이저에 대한 자세한 세부사항을 다룬다.

통계 어드바이저를 실행하여 통계 품질 및 콜렉션 개선

이 기사의 시작부에서 언급한 것처럼 데이터베이스 통계는 최적화 프로그램이 액세스 계획에 대한 의사결정을 내리는 팩트이다. 결과적으로 통계가 정확하지 않고 오래되거나 충돌하는 경우 최적화 프로그램은 쿼리 계획에서 단계의 비용에 정확하지 않은 추정치를 작성할 것이며, 이로 인해 성능이 저하될 것이다.

DB2에서 명령 RUNSTATS TABLE ALL INDEX ALL은 모두 동일한 통계를 수집하며, 이 중 많은 수는 쿼리 성능을 개선하기 위해 필요하지 않을 수 있다. 동시에 유틸리티는 여러 컬럼 및 분배된 통계와 같이 특정 키 통계를 수집하지 않는다. 보통 컬럼 사이에 데이터 상관성이 있다. 예를 들어, 이 기사에서 샘플 쿼리에 대한 CUST_CITY와 CUST_PROV_STATE 컬럼 사이에 강력한 상관성이 있다. 개별 컬럼의 통계를 수집하는 것은 필요한 정보를 제공하는 데 충분하지 않을 수도 있으므로, 컬럼 그룹 통계를 수집해야 한다.

Query Tuner 통계 어드바이저는 다음과 같은 문제가 되는 통계 상태를 식별하기 위해 RUNSTATS 권장사항을 제공하여 필요한 통계를 더 간편하게 수집하게 해준다.

• 누락 통계: 통계가 누락될 때 최적화 프로그램은 비용을 판별하기 위해 기본 값을 가정하며, 이는 완전히 정확하지 않을 수 있다.
• 충돌하는 통계: 일관되지 않은 통계로 인해 최적화 프로그램이 잘못된 추정치 비용을 이끌어내고 액세스 계획 평가에 대한 잘못된 의사결정을 내리게 될 수 있다. 일관되지 않은 통계는 사용자가 부분적 통계를 수집할 때 나타날 수 있다. 예를 들어, 다른 시점에 테이블 및 색인 통계를 개별적으로 수집하는 것이다.
• 사용되지 않는 통계: 오래된 통계는 테이블의 현재 상태를 더 이상 표현하지 않을 수 있다.

통계 어드바이저도 조언의 관련 중요성을 평가하고 다음 두 가지 권장사항 유형을 제공한다.

• 복구: 이 유형의 권장사항은 중요한 통계가 누락되었거나 통계 사이에 충돌이 있음을 표시한다. 해당 권장사항은 관련 통계를 캡처하고 복구하기 위해 실행할 수 있는 RUNSTATS 명령을 포함한다.
• 완료: 이 유형의 권장사항은 복구 권장사항을 다룰 뿐만 아니라 유지보수 용도로 새로 고칠 수 있는 다른 통계도 다룬다. 권장사항은 관련 통계의 상태를 유지보수하기 위해 유지보수 주기에 정기적으로 포함하려 할 수 있는 RUNSTATS 작업을 제공한다.

그림 15와 같이 특정 권장사항에 대한 조언에 파고들면서, 통계 어드바이저가 통계를 수집하거나 복구하는 데 사용할 수 있는 RUNSTATS 명령을 생성하는 것을 확인할 것이다. 나중에 실행하기 위해 저장하거나 독자가 적절한 권한이 있으면 Query Tuner 클라이언트로부터 직접 이를 실행할 수도 있다. 참고로, Query Tuner로부터 RUNSTATS 권장사항을 실행하기 위해 SYSPROC.DSNUTILU 저장된 프로시저가 사용 가능해야 하며 서버 측에서 조작적이어야 한다.

또한 권장사항 배후의 설명을 확인한다. 그림 15에서 어드바이저가 통계가 충돌하거나 누락할 뿐만 아니라 왜 어드바이저가 충돌한다고 생각하는지에 대한 설명을 제공하는 몇 가지 인스턴스를 발견한 것을 확인할 수 있다.

독자는 이미 개선한 통계 품질이 액세스 계획 최적화에 대한 의사결정의 기반이 되는 정확한 데이터를 DB2 최적화 프로그램에 제공하여 잠재적으로 성능을 개선하고 CPU를 절감하는 것을 어떻게 의미하는지 확인했다. 하지만, 또 다른 이점이 있다. 필요한 통계만 수집하여, CPU 오버헤드를 추진하고 제한된 유지보수 창 내부로 필요하지 않은 워크로드를 작성하는 관련없는 통계를 수집하는 것을 방지할 수 있다. 다시 말해서 통계 어드바이저는 통계 품질 뿐만 아니라 통계 데이터 수집의 효율성도 개선하는 데 도움을 줄 수 있다.

쿼리 어드바이저를 실행하여 쿼리 설계 개선

액세스 경로 선택 이전에 DB2 최적화 프로그램은 조건부 푸시다운(pushdown) 또는 조건부 이행 종결을 사용하는 것과 같이 SQL 문을 시맨틱 상 동일한 양식으로 변환한다. 결과적으로 이는 가능한 액세스 경로를 개선할 수 있다. 반대로, Query Tuner는 쿼리를 추가로 제한하고 색인 활용을 높이며 데이터 읽기를 줄이는 추천사항을 작성하여 쿼리를 쓰거나 성능 조정하는 사람이 오류와 실수를 식별하는 데 도움을 준다. 쿼리 어드바이저는 다음을 수행하기 위해 쿼리에서 기회를 모색한다.

• 읽어야 하는 색인 페이지와 데이터 행의 수를 최소화한다. 예를 들어, 색인 자체로부터 필요한 행을 판별할 수 있는 쿼리에서 조건부를 보유하여 행 읽기를 최소화할 수 있다.
• 정렬 조작을 최소화한다. 예를 들어, ORDER BY 또는 GROUP BY 절이 쿼리에서 필요한지 여부 또는 색인 액세스로 해결될 수 있는 지이다.

구체적으로 말하면, 쿼리 어드바이저는 다음을 확인한다.

• 누락 조인 조건부. 하지만 외부 키가 정의되는 경우에만 해당된다.
• Stage 1 또는 색인 가능으로 다시 쓰여지는 경우 성능을 개선할 수 있는 Stage 2 조건부. Stage 1과 Stage 2 조건부에 대한 자세한 정보를 찾을 수 있는 DB2 for z/OS Information Center로의 링크는 참고자료를 참조하자.
• 색인 가능으로 다시 쓰여지는 경우 성능을 개선할 수 있는 Stage 1 조건부.
• 조건부 이행 종결을 제공할 수 있는 DB2로 자동으로 제공되지 않는 추가 로컬 조건부.
• 결과를 변경하지 않고 중첩 테이블 표현 또는 구체화된 뷰로 푸시다운되고 이미 자동으로 DB2로 수행되지 않은 조건부.
• OR, AND 및 () 소괄호가 들어있는 복잡한 WHERE 절로 추가되는 추가 조건부. 이는 결과를 변경하지 않고 성능을 개선할 수 있다.
• 특정 컬럼 목록으로 바꿀 수 있는 SELECT *의 사용.

그림 16에서 쿼리 어드바이저 권장사항의 세부사항과 함께 권장사항의 이유를 확인할 수 있다. UI는 자동으로 권장사항과 관련된 쿼리의 섹션을 강조표시한다. 이러한 방법으로 DBQ 또는 개발자는 진행하면서 쿼리 성능 조정에 대해 학습한다.

색인 어드바이저를 실행하여 쿼리 효율성 개선

Query Tuner는 색인 조언도 제공한다. 이는 쿼리를 분석하고 쿼리 액세스의 혜택을 누릴 추가 색인도 권장한다. 색인 어드바이저는 다음 이유로 색인을 권장할 수 있다.

• 정의된 색인이 없는 외부 키.
• SQL 문에 대한 색인 필터링 및/또는 스크리닝을 제공할 색인.
• SQL 문에 대한 색인 전용 액세스를 제공할 색인.
• 정렬을 방지하는 데 도움이 될 수 있는 색인.

그림 17은 이 기사에서 샘플 쿼리에 대한 색인 권장사항과 추정된 성능 개선 및 DASD 공간 요구사항을 함께 보여준다. 이는 색인을 작성하는 데 필요한 DDL도 작성하고 즉시 이를 실행하는 기능을 제공하여, 독자가 적절한 권한이 있거나 나중에 보기나 실행을 위해 저장한다고 가정한다.

또한 독자는 권장사항 및 DDL 작성을 사용자 정의할 수도 있다. 예를 들어, 색인 키의 일부가 될 수 있는 최대 컬럼 수를 지정하고 색인 DDL이 생성될 때에 사용되는 작성자 ID를 변경할 수 있다. 또한 새 색인에 대해 가정한 FREEPAGE, PCTFREE 및 CLUSTERRATIO 값과 색인 리프(leaf) 페이지 크기가 4 KB를 초과할 수 있는지 여부를 지정할 수도 있다.

권장된 색인을 배치 이전에 "가상(what-if) 분석"으로 제안하는 색인과 함께 테스트할 수도 있다. 이를 통해 독자는 색인 어드바이저에 다양한 제한조건을 적용할 수 있고, 이러한 제한조건이 권장사항을 어떻게 수정하는지 확인할 수 있다. "가상 분석"은 색인의 효율성을 평가하기 위해 몰래 가상 색인을 사용한다. 이렇게 하여 독자가 원하는 성능을 제공하는 데 도움을 줄 수 있는 색인을 작성하도록 선택할 수 있다.

결론

이 기사는 단일 쿼리를 성능 조정하기 위한 방법론을 소개했고, 방법론의 각 단계를 자세히 설명하기 위해 샘플 쿼리를 사용했다. 이는 쿼리 형식화, 어노테이션, 보고서 및 액세스 계획 그래프로 제공되는 시각적 지원을 사용하여 문제점 쿼리를 더 잘 이해하는 방법이 포함된다. 독자는 통계, 조건부 및 색인의 관점에서부터 문제점 쿼리를 분석하고 성능 조정하는 방법을 학습했다. 또한 Query Tuner 어드바이저를 사용하여 쿼리 성능 조정을 가속화하는 방법도 확인했다.

이 시리즈의 다음 기사에서는 쿼리 워크로드 범위로 논의를 확장하여, 워크로드 성능 조정을 자세히 소개하는 예제를 사용하여 워크로드 성능 조정이 워크로드 성능 조정을 위한 도전에 왜 필요한지 소개할 것이다.

위로

다운로드 하십시오

다운로드 방식에 대한 정보

참고자료

교육

• RSS 피드를 사용하여 곧 공개될 이 시리즈 기사에 대한 소식을 요청하자. (developerWorks 컨텐츠의 RSS 피드에 관해 자세히 확인해보자.)
  
  
• 액세스 경로를 이해하는 것에 대한 세부사항을 자세히 학습하는 데 유용한 "Optim Query Tuner를 사용하여 SQL 성능 조정하기, Part 1: 액세스 경로 이해하기"(developerWorks, 2010년 6월)기사를 참조하자.
  
  
• "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 제품 정보를 읽어보자.
  
  
• Information Center에서 OQT에서 다양한 기능을 실행하는 방법에 대한 자세한 소개글을 확인하자.
  
  
• 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 제품 정보를 읽어보자.
  
  
• DB2 for z/OS에서 쿼리의 성능을 조정할 수 있도록 Data Studio를 구성하는 데 도움을 주는 정보가 있는 "Best practices when using Data Studio and Optim Development Studio with DB2 for z/OS"(developerWorks, 2010년 6월) 기사를 읽어보자.
  
  
• developerWorks Optim 제품군 페이지를 참조하여 Optim 솔루션에 관해 자세히 알아보자. 기술 자료, 사용법 기사, 교육, 다운로드, 제품 정보 등을 찾아볼 수 있다.
  
  
• developerWorks 기술 행사 및 웹 캐스트를 통해 최신 정보를 얻을 수 있다. Optim 통합 데이터 관리 포트폴리오와 관련된 가상의 기술 브리핑에 대한 스케줄은 이 페이지를 참조한다.
  
  
• developerWorks Information Management 영역에서는 Information Management에 대한 정보를 제공한다. 기술 자료, 사용법 기사, 교육, 다운로드, 제품 정보 등을 찾아볼 수 있다.
  
  
• developerWorks 기술 행사 및 웹 캐스트를 통해 최신 정보를 얻을 수 있다.
  
  
• Twitter의 developerWorks 페이지를 살펴보자.
  
  

제품 및 기술 얻기

• Data Studio 무료 소프트웨어를 다운로드하자.
  
  
• developerWorks에서 직접 다운로드할 수 있는 IBM 시험판 소프트웨어를 사용하여 후속 개발 프로젝트를 구현해 보자.
  
  
• 자신에게 가장한 적합한 방법으로 IBM 제품을 평가해 보자. 시험판 제품을 다운로드하거나, 온라인으로 제품을 사용해 보거나, 클라우드 환경에서 제품을 사용하거나, SOA Sandbox에서 SOA(Service Oriented Architecture)를 효과적으로 구현하는 방법을 배울 수 있다.
  
  

토론

• 포럼에 참여하기.
  
  
• 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 has worked in Information Development, product management, and product marketing. Currently, she is the manager responsible for Information Development for Optim solutions for database development, administration, and design. Previously, she worked on the IBM Optim Solutions technical enablement team and was responsible for community development and communications.

Qiang Song is the technical lead of the IBM Optim Query Tuner and Optim Query Workload Tuner products, working in the IBM China development lab since 2005. He also leads the customer engagement and service work of the products. Qiang has rich experience in SQL performance tuning and Eclipse development.

아티클 순위

의견

위로

목차

• 소개
• Optim 쿼리 성능 조정 솔루션 정보
• 쿼리 최적화의 개요
• 쿼리 성능 조정 방법론
• Optim Query Tuner로 쿼리 성능 조정 가속화하기
• 결론
• 다운로드 하십시오
• 참고자료
• 필자소개
• 의견