오라클 통계 산출

옵티마이징 단계에서 비용기준 옵티마이져 (CBO)는 데이터 딕셔너리에 저장된 테이블과 인덱스에 대한 통계정보를 광범위하게 사용합니다. 따라서 CBO가 작동하기 위해서는 통계정보가 반드시 필요하며, Analyze 명령 또는 DBMS_STATS 패키지의 다양한 프로시져 호출을 통해 수집됩니다. 또한 인덱스 컬럼값의 데이터 분포가 균일하지 않은 상황에서는 컬럼값에 대한 히스토그램 정보를 생성해 주는 것이 매우 유용한데, 만약 컬럼의 히스토그램 정보가 없으면 옵티마이져는 컬럼의 데이터 분포가 균일하다고 가정하기 때문입니다. 만일 컬럼 데이터의 분포가 균일하지 않은 상황에서 히스토그램 정보가 없다거나, 있는데도 옵티마이져가 엉뚱한 실행계획을 생성하는 경우에는 특별한 조치가 필요합니다. 특히 바인드 변수를 사용할 경우에는 앞으로 바인딩될 값을 옵티마이져가 미리 알 수 없기 때문에 균등분포를 가정한 실행계획을 수립하게 되어 아무리 히스토그램 정보를 생성해 두었어도 무용지물이 됩니다. 따라서 이런 경우에는 반드시 바인딩되는 값의 종류에 따라 실행계획이 분리될 수 있도록 SQL을 구사해 주어야 하며, 만약 이런 기법을 사용할 수 없는 상황이라면 Dynamic SQL을 사용하는 편이 오히려 나을 수도 있습니다. 예를 들어, 부동산 관련 사이트에서 아파트 매물 검색시 서울, 경기 지역과 기타 지역 간에는 데이터 분포가 상당히 많은 차이를 보이므로, 아래와 같이 어떤 지역에 대해 검색하느냐에 따라 실행계획을 분리시켜 줘야 합니다.

   

그런데 Analyze 명령어에 의해 수집되는 테이블 통계와 컬럼 히스토그램 정보만을 가지고 최적화를 수행하기에는 역부족이 아닐 수 없습니다. 컬럼의 히스토그램 정보를 얼마나 정확히 유지할 것인가? 조건식에 사용된 컬럼들간의 결합형태에 따른 정확한 분포도를 얻을 수 있는가? 서로 밀접한 상관관계를 갖는 컬럼들간의 결합분포도는 어떤 계산식을 사용해야 정확히 산정할 수 있을까? 그렇다고 컬럼들간의 가능한 결합형태에 따라 별도의 히스토그램 정보들을 모두 수집해서 관리할 수도 없는 노릇입니다. 뿐만 아니라 최적화 수행 당시의 시스템 환경, 예를 들면 CPU 상황, Memory 상황, Disk I/O 속도 등에 대한 고려가 중요한 변수인데, 사실 옵티마이져는 이런 실행환경에 대해서 많은 가정들을 세운 채 최적화를 수행하고 있습니다. 옵티마이져 자체가 특정 상황에 맞추어서 최적화되어 있다는 이야기인데, 각 시스템의 가변적인 환경요인에 의해 생기는 오차를 보정해 주기 위해 옵티마이져에게 도움을 줄 수 있는 몇몇 초기화 파라미터에 대해서는 이전 칼럼을 통해 소개한 바 있습니다. (※ 옵티마이져의 행동에 영향을 미칠 수 있는 파라미터) 이와 함께 Oracle9iR2부터는 시스템 통계(System Statistics)라는 기능을 제공하고 있는데, 이 기능에 대해 좀더 자세히 살펴 보겠습니다.   옵타미아져는 실행계획 각 단계의 액세스 비용을 평가하기 위해 주로 CPU Cost와 Disk I/O Cost를 고려합니다. 따라서 해당 시스템의 특징을 이해하고 I/O Cost와 CPU Cost 사이에 적절한 비중을 적용해서 실행계획을 평가하는 것이 가장 효율적인 실행계획을 선택하는 데 있어서 매우 중요한 요소입니다. 그런데 CBO에 영향을 미치는 각종 파라미터들의 기본 설정값들은 특정 상황에 맞추어져 있어 우리 시스템 환경에 맞지 않는 경우가 흔하고,  CPU와 디스크 성능에 대한 평가도 옵티마이져를 개발하기 위해 사용된 특정 머신에 맞추어져 있기 때문에 실제 오라클이 인스톨되는 플랫폼에는 상대적으로 맞지 않을 수 있습니다. 그리고 시스템의 I/O 특징이라는 것이 여러가지 다른 요인들에 의해 영향을 받기 때문에 항상 고정적이지도 않습니다. 그래서 최근에 오라클에서 제공하기 시작한 것이 시스템 통계(System Statistics)인데, 해당 시스템만의 독특한 성능특성을 실제적으로 측정함으로써 옵티마이져에게 더 정확한 정보를 제공하기 위해 사용합니다. 시스템 통계를 생성할 때는 지정한 기간 동안 해당 시스템에서 실제로 일어나고 있는 활동들을 관찰하고 분석합니다. CPU 성능, Single-Block I/O 성능, Multiblock I/O 성능이 주요관찰 대상인데, 추측이 아닌 실측치를 가지고 실행계획에 나타나는 각 오퍼레이션에 대한 CPU와 디스크 성능을 평가함으로써 더 정확한 실행계획을 수립할 수 있게 된 것입니다. 심지어 우리의 데이터베이스 시스템이 낮에는 OLTP성 트랜잭션을 처리하고 밤에는 주로 OLAP 레포트를 생성한다면 양쪽 모두에 대한 통계정보를 수집하고 나서 필요할 때마다 OLTP 또는 OLAP 통계정보로 활성화시킬 수가 있습니다. 이것은 옵티마이져가 현재 이용 가능한 시스템 리소스의 상황을 고려해서 비용을 산출하는 것과 비슷한 효과를 얻을 수 있게 합니다. 시스템 통계를 사용하는 실질적인 사례를 간단히 보여 드리겠습니다. 우선 시스템 통계를 수집해야 하는데, 시스템이 가장 일반적인 부하를 갖는 시간대를 선택해야 합니다. 아래 명령어를 통해 낮 시간에 720분 동안 통계를 수집하고 mystats 테이블에 저장하게 됩니다.   BEGIN DBMS_STATS.GATHER_SYSTEM_STATS(              gathering_mode => 'interval',              interval => 720,              stattab => 'mystats',              statid => 'OLTP'); END; /   그리고 아래 명령어를 통해 밤 시간에도 720분간 시스템 통계를 수집하고, mystats 테이블에 저장합니다.   BEGIN DBMS_STATS.GATHER_SYSTEM_STATS(              gathering_mode => 'interval',              interval => 720,              stattab => 'mystats',              statid => 'OLAP'); END; /   이제 남은 일은 수집된 통계정보를 가지고 데이터 딕셔너리를 갱신하는 것인데, 낮에는 낮 시간에 얻은 통계정보를, 밤에는 밤 시간에 수집한 통계정보를 사용하게 될 것입니다. 아래 명령어는 OLTP 통계정보를 Import합니다.   BEGIN   DBMS_STATS.IMPORT_SYSTEM_STATS('mystats', 'OLTP'); END; /   밤이 되면 다시 아래 명령어를 통해 OLAP 통계정보를 Import합니다.   BEGIN   DBMS_STATS.IMPORT_SYSTEM_STATS('mystats', 'OLAP'); END; /   이런 Import 작업들은 DBMS_JOB 패키지를 이용해서 JOB으로 등록하고 적당한 시간에 자동으로 수행되도록 하는 것이 낫겠지요. 이 때 주의할 것은, 테이블, 인덱스, 컬럼 통계정보와 달리 시스템 통계가 갱신되더라도 오라클은 새롭게 들어오는 SQL문만 새로운 통계정보를 이용해 파싱할 뿐 기존에 파싱되어 있던 SQL문들은 공유 풀(Shared Pool)에 남겨두고 그대로 사용한다는 점입니다. 따라서 시스템 통계를 새로이 임포트할 때마다 공유 풀을 비워야(flush) 하는 경우도 생길 수 있지만 대개의 경우 어차피 낮에 수행되는 SQL과 밤에 수행되는 SQL은 같지 않을 것이므로 그럴 필요가 없습니다. 출처 : 엔코아컨설팅 http://www.en-core.com/bin/main/module/solution/view.asp?solution_code=&searchString=&column=&article_id=17667&state=view&board_id=solution&page_num=1&group_id=19570&direction=n&step=0