개요

방법

1. 통계의 이해

1.1 효율적이 통계 수집 정책

실행 계획의 최적화를 위해 주로 사용하는 방법이 통계 정보 수집이다. 하지만 가장 중요한 것은 바로 수집된 통계 정보의 정확성입니다.

최신 통계 정보라고 해도 통계 정보 자체가 부정확하다면 이로 인해 잘못된 실행 계획이 수립되어 Database의 성능에 부정적 영향을 줄 수 있기 때문입니다. 그러므로 Database 관리자(DBA)는 효율적인 통계 정보 수집 정책을 세우고 관리하여 통계 정보를 정확하고 안정적으로 관리해야 합니다.

다음은 통계 정보의 정확성과 안정성을 유지하기 위해 DBA가 관리해야 하는 항목입니다.

• 오래된 통계 정보 수집 일자 존재 여부 확인

통계 정보 수집 일자가 오래된 경우 성능 문제가 발생할 수 있다. 특히 Partition일 경우 추가된 Partition에 값은 입력되지만 통계 정보가 변경 되지 않을 때 문제가 주로 발생합니다.

• 수집된 통계 정보의 정확성

최신 통계 정보라고 해도 부정확한 통계 정보는 Database의 성능에 부정적 영향을 줄 수 있습니다.

• 통계 정보 수집 정책 관리

효율적인 통계 정보 수집 정책을 바탕으로 통계 정보를 정확하고 안정적으로 관리해야 합니다.

1.2 통계 수집 고려 사항

통계 정보는 주기적으로 수집하면서 동시에 정확성과 안정성을 높여야 한다. 효율적인 통계 수집 정책을 위해 고려해야 하는 사항은 다음과 같습니다.

• 빠른 수집을 위해 부하 없는 시간대를 선택합니다.
• 수집되는 샘플 크기를 최소화하여 영향도를 줄인다. 단, 정확성과 안정성을 확보할 수 없다면 전수 검사를 권장합니다.
• 전수 검사할 때 통계 수치에 근접하도록 정확성이 높아야 합니다.
• 데이터 변화가 크지 않을 경우 잦은 통계 수집으로 인해 통계 수치가 변경되지 않도록 안정성을 고려합니다.

1.3 성능 저하 원인

효율적인 통계 수집 정책을 위해 DBA는 통계 수집을 위한 고려 사항들을 숙지해야 정확하고 안정적인 통계 수집을 할 수 있습니다. 하지만 통계 수집이 성능 저하의 원인 중 하나가 될 수 있다는 것을 인지하고 있어야 합니다.

대부분의 운영 시스템에서 통계 정보 수집 후 갑자기 Hard parse가 몰리면서 Library cache pin 경합으로 인해 시스템 성능 저하 현상이 발생하는 경우가 많습니다.

시스템 성능 저하 현상은 다음과 같은 상황에서 자주 발생합니다.

• 통계 정보 수집으로 통계 정보가 변경됩니다.
• 통계 정보가 변경되면 관련된 SQL cursor들이 Invalidate 됩니다.
• SQL cursor가 Invaldate되면 다음에 해당 SQL에 Access 때 Hard parse가 발생합니다.
• Hard parse때 PP cache object에 대해 PP cache pin을 Exclusive모드로 획득합니다.
• Hard parse가 진행 중인 PP cache object에 접근하려는 다른 Session들은 PP cache pin 이벤트를 대기합니다.

위와 같은 상황은 통계 정보의 갱신으로 인한 Cursor Invalidation으로 인해 성능 문제가 발생할 수 있기 때문에 no_invalidate 옵션으로 제어합니다. 이처럼 성능 저하의 원인이 통계 수집에 있을 수 있으며, 이외에도 잘못된 통계 수집 옵션 등 다양한 이유가 있을 수 있습니다.

1.4 통계 수집 전략

통계 수집이 성능을 높이는 수단이지만 통계 수집으로 인해 성능 저하가 발생할 수 있습니다. 따라서, 통계 수집을 할 때 고려해야 하는 다양한 사항들을 바탕으로 업무 별로 운영하는 시스템에 맞게 정책을 가지고 있어야 한합니다.

즉, DBA는 통계 수집 전략을 세워야 하며 Tibero에서는 다음과 같은 통계 수집 전략을 제시합니다.

1) 통계 수집 정책 수립은 필수

• 통계를 수집할 필요가 없는 오브젝트에 대해서는 lock 옵션으로 통계 정보를 고정해 변경을 막습니다.
• 통계 정보에 영향을 받아선 안되는 중요한 일부 핵심 프로그램에 대해선 힌트를 적용해 실행 계획을 고정합니다.
• 통계 정보 변화 때문에 애플리케이션 성능에 심각한 문제가 발생했을 때를 대비해 가장 안정적이었던 최근 통계 정보를 항상 백업합니다.

2) 컬럼 히스토그램 수집

• 히스토그램은 이를 수집하고 관리하는 비용이 만만치 않습니ㅣ다. 따라서 필요한 컬럼에만 히스토그램을 수집해야 하며, 조건절에 자주 사용되면서 편중된 데이터 분포를 갖는 컬럼이 주 대상입니다. 
• 테이블을 엑세스하고 나서의 최종 선택도를 계산할 때는 인덱스가 없는 조건절 컬럼의 선택도도 인자로 사용되고, 그렇게 구해진 선택도에 따라 다른 집합과의 조인 순서 및 조인 방식이 결정되어집니다.

3) 통계의 정확성과 성능 사이의 균형

정확한 통계 수집을 위해서 많은 데이터를 수집하고 분석해야 하지만 수집할 때 Database 성능에 부정적 영향을 미칠 수 있습니다. 반대로 성능을 고려하여 적은 데이터를 수집하면 통계가 부정확할 수 있습니다.

4) 통계 수집 주기

 통계 수집 주기는 데이터의 속성에 따라 선택해야 합니다. 자주 갱신되는 데이터를 가진 Table이나 짧은 주기로 파티션이 추가되는 경우가 빈번하다면 통계 수집 주기를 짧게 설정하거나 적절한 통계 수집 옵션을 선택해야 합니다. 반면 에 데이터의 갱신이 자주 일어나지 않는다면 통계 수집 주기를 길게 설정합니다.

5) 통계 수집 레벨

통계 수집 레벨은 모든 Object와 Partition-level로 나눈다. 모든 Object에 대한 통계인 Global 통계는 전체 Table 대상이기 때문에 대용량 Database에서 Global 통계를 수집할 때 많은 시간과 부하가 생깁니다. 이를 단축 가능한 방법으로 Partition-level 통계 수집('PARTITION' granularity)을 고려해 볼 수 있습니다. 하지만 Partition-level 통계 수집은 통계 수집 시간은 단축할 수 있으나 부정확한 Number of Distinct Value(NDV)값으로 실행 계획을 만들어 성능에 문제가 될 수 있기 때문에 통계 수집 레벨은 신중히 결정해야 합니다. 

Partition-level로 수집된 통계 수치를 이용하여 Global 통계를 집계하는 경우('APPROX_GLOBAL AND PARTITION' granularity ) Partition-level 정보를 이용하여 Global-level 통계 정보의 근사치를 산출함으로써 수집 성능과 통계 정보의 정확성을 확보할 수 있습니다. 하지만 Global-level로 수집된 통계 정보에 비하면 여전히 부정확할 수 있음에 유의합니다.

6) 샘플링 비율

샘플링 비율을 높일수록 통계정보의 정확도는 높아지지만 통계정보를 수집하는데 더 많은 시간이 소요됩니다. 반대로 샘플링 비율을 낮추면 정확도는 다소 떨어지지만 더 빠르게 통계를 수집할 수 있습니다.

7) Column 데이터 분포도

Index column값의 데이터 분포가 균일하지 않을 경우 Column에 대한 히스토그램 정보를 생성하는 것이 중요합니다. 만약 Column의 히스토그램 정보가 없다면 히스토그램 생성 여부를 Optimizer가 판단하여 모든 Column에 대 해 통계 정보를 수집합니다. 이때 대부분 Optimizer가 적절한 히스토그램 Bucket을 할당하지만 불필요한 히스토그램을 생성하는 경우가 있을 수 있습니다.

8) System 통계

주기적인 통계 수집과 Column 히스토그램만으로는 최적화하기에 부족하므로 System 통계 기능을 사용합니다.

9) 자동 수집 통계

일정 주기의 갱신이 일어나는 대부분의 DB Object에는 자동 통계 수집이 효율적일 수 있다. 하지만 업무 중에 Table이 Delete 또는 Truncate되고, 재생성되는 Table이나 Object의 전체 크기 중 10% 이상이 대량으로 Load되는 경우에는 자동 통계 수집이 비효율적입니다.

• 통계 수집 대안 전략

- 통계 Settting

- 동적 샘플링

- Incremental Global 통계

1.5 Cost Based Optimizer (CBO)

Tibero는 Plan을 실행했을 때 예상되는 시간을 정량화하여 Cost가 낮은 Plan을 기반으로 최적화를 수행하는 비용 기반 Optimizer를 채택하고 있습니다. 

SQL문이 Tibero에서 실행될 때 쿼리 Optimizer는 다양한 요소를 고려하여 가장 효율적인 실행 계획을 선택합니다. 실행 계획은 SQL문의 수행 시간에 큰 영향을 주기 때문에 매우 중요한 요소입니다. 

CBO에서는 동일한 쿼리라도 데이터의 특성에 따라 실행 계획이 달라지고 복잡한 Cost 산정 원리를 포함하고 있으므로 이에 대한 정확한 지식이 필요합니다. 

비용 측정은 I/O요청 횟수를 평가하는 I/O비용과 CPU연산을 감안하여 수행량을 시간으로 환산해 평가하는 CPU비용이 포함됩니다. 이러한 평가를 위하여 미리 수행한 Table과 Index등에 대한 다양한 통계 정보를 기반으로 수행 단계별 예상 비용을 산정하고 총 비용이 가장 낮은 실행 계획을 선택합니다. 그러므로 데이터 상태를 잘 반영한 통계 정보를 유지해야 최적의 실행 계획이 나올 수 있습니다.

• CBO 수행 단계
• Optimizer는 가능한 접근 경로와 Hint를 기반으로 SQL문에 대한 Plan을 만듭니다. 
• Optimizer는 SQL문에 의해 접근하는 Table, Index 및 파티션의 Storage 특성과 데이터 분포에 대해서 Data dictionary통계를 기준으로 Plan의 비용을 추정합니다. 비용은 SQL을 실행할 때 필요한 자원의 비율을 추정한 값이며 Optimizer는 예상되는 I/O, CPU 및 Memory를 포함한 시스템의 자원을 기반으로 접근 경로와 Join 순서의 비용을 계산합니다. 
• Optimizer는 Plan의 비용을 비교하여 가장 낮은 비용의 Plan을 선택합니다.

• 비용 측정의 3가지 기능

  Plan의 비용을 측정할 때 다음과 같은 방법을 이용하여 최소의 비용을 갖는 SQL문장을 Plan generator에게 넘겨 실행 계획을 생성합니다.

최적화 작업 단위로 디스크 I/O, CPU 사용량, 메모리 사용량 등을 사용한다. Selectivity 및 Cardinality를 기반으로 계산합니다.

참고

비용 측정의 기능에 대한 자세한 내용은 “Selectivity와 Cardinality” 참고합니다.