db_file_multiblock_read_count 파라미터의 몰락...

Oracle 10g R2에서 사람들이 전혀 눈치채지 못한 큰 변화가 있었다.
흔히 MBRC라고 줄여서 부르는 db_file_multiblock_read_count 파리미터가 공식적으로 몰락한 것이다.
물론 여전히 사용 가능하지만, 그 내부 작동 방식으로 보아서 이 파라미터는 사실상 가치가 없어진 셈이다.

사실 이러한 조짐은 이전부터 있었다.
바로 System Statistics가 9i에서 소개된 것이다. 9i에서 System Statistics를 수집하면 다음과 같은 데이터가 sys.aux_stats$ 테이블에 저장된다.

SQL> SELECT * FROM sys.aux_stats$;
SNAME    PNAME    PVAL1    PVAL2
SYSSTATS_INFO    STATUS        COMPLETED
SYSSTATS_INFO    DSTART        12-30-2007 15:21
SYSSTATS_INFO    DSTOP        12-30-2007 15:21
SYSSTATS_INFO    FLAGS    1  
SYSSTATS_MAIN    SREADTIM    1         <-- Single Block Read Time
SYSSTATS_MAIN    MREADTIM    2        <-- Mulit Block Read Time
SYSSTATS_MAIN    CPUSPEED    500     <-- CPU Speed
SYSSTATS_MAIN    MBRC    8               <-- Multi Block Read Count
SYSSTATS_MAIN    MAXTHR    -1  
SYSSTATS_MAIN    SLAVETHR    -1

System Statistics가 없는 상황에서는 db_file_multiblock_read_count, optimizer_index_cost_adj와 같은 파라미터들이 Index Scan과 Table Scan중 어느 것을 선택할지를 결정하는데 큰 역할을 한다.

하지만, System Statistics가 수집된 경우에는?
위의 값들을 보면 알겠지만, System Statistics는 Index Scan과 Table Scan을 결정하는데 필요한 모든 중요한 정보들을 다 가지고 있다. 따라서 Oracle은 SREADTIME, MBREADTIM, CPUSPEED, MBRC 등의 정보를 이용해 비용을 계산한다.

그러면, System Statistics가 활성화되어 있으면 db_file_multiblock_read_count 파라미터는 무시되는 것인가? Optimizer에 의해 실행 계획이 생성될 때는 무시되지만 실제 Fetch과정에는 db_file_multiblock_read_count 크기만큼 Multi Block I/O를 한다.

즉, Optimizer에 의해서는 사용되지 않고 실제 쿼리를 실행하고 Fetch하는 단계에서만 사용된다.

이러한 원리는 Oracle 10g R1에서도 거의 비슷하다. Oracle 9i와 10g의 차이점은 System Statistics가 없을 때의 동작 방식이다. Oracle 10g에서는 System Statistics에 다음과 같은 항목이 추가되었다.

SNAME    PNAME    PVAL1    PVAL2
SYSSTATS_MAIN    CPUSPEEDNW    1845.90945194599  
SYSSTATS_MAIN    IOSEEKTIM    10  
SYSSTATS_MAIN    IOTFRSPEED    4096  

즉, 기본적인 CPU Speed와 IO Seek Time, IO Transfer Speed 값이 추가되었다. 이것을 흔히 "Noworkload" System Statistics라고 부른다. 말 그대로 Oracle이 하는 일과 무관하며 순수하게 Hardware로부터 취득한 정보라는 의미이다. Oracle은 Noworkload에 대해서는 적절한 기본값을 가지고 있으며, 필요하다면 이 값을 이용한다.

Oracle 10g R1은 System Statistics가 없으면 CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 값과 db_file_multiblock_read_count 값을 함께 이용해서 비용을 계산한다. 즉, db_file_multiblock_read_count 파라미터가 사용되기는 하되 9i와 같이 직접 사용되는 것이 아니라 Noworkload 통계 정보와 함께 조합되어서 사용된다.

하지만, Oracle 10g R2에서 또 한번의 변화가 생겼다. Oracle 10g R2에는 Multi Block I/O와 관련된 파라미터가 다음과 같이 세개로 늘어났다.

    * db_file_multiblock_read_count
    * _db_file_optimizer_read_count
    * _db_file_exec_read_count

즉, db_file_multiblock_read_count라는 파라미터가 Optimizer가 사용할 값(_db_file_optimizer_read_count)과 실행시에 사용할 값(_db_file_exec_read_count)으로 세분화된 것이다.
이 파라미터들의 사용방식은 다음과 같다.

1. _db_file_optimizer_read_count 파라미터는 System Statistics가 없는 경우에 Optimizer가 비용을 계산한기 위해 사용한다(앞서 설명한 바와 같이 Noworkload 통계값과 같이 사용). System Statistics가 수집된 경우에는 이 값은 무시된다.

2. _db_file_exec_read_count 파라미터는 쿼리를 실행하는 과정에서 Multi Block I/O를 수행할 때 한번에 읽을 블록수를 결정한다. 이 값은 System Statistics의 수집 여부와 무관하게 사용된다.

3. db_file_optimizer_read_count 파라미터값을 명시적으로 변경하면 _db_file_optimizer_read_count 값과 _db_file_exec_read_count 값이 모두 같이 변경된다.

전체적인 사용방식은 이전 버전과 동일하지만, db_file_multiblock_read_count 파라미터가 사실상 없어진 것과 마찬가지이며, 목적에 따라 명확하게 구분된 두 개의 히든 파라미터로 나뉘어졌다. 우리가 설사 db_file_multiblock_read_count 값을 지정하더라도 Oracle은 내부적으로 서로 다른 두 개의 히든 파라미터를 사용하는 셈이다.

언뜻 복잡해 보이는 위의 논의들은 사실은 다음과 같은 결론을 위한 것이다.

"System Statistics 정보를 잘 수집하자. 너무나 좋은 기능이다."

System Statistics 정보를 이용하면 기존에 Optimizer의 부족한 판단을 보완해주기 위해 db_file_multiblock_read_count, optimizer_index_cost_adj 같은 파라미터의 값을 변경해줄 수고가 줄어들며, 훨씬 정확한 정보에 기반한 비용 계산이 가능해진다. 더불어 db_file_multiblock_read_count 파라미터는 더 이상 Optimizer에게 영향을 주지 못하고 말 그대로 Multi Block I/O를 실제로 수행할 경우에만 사용되므로 훨씬 직관적이고 오해의 소지가 없는 셈이다.

많은 시스템들이 이 정보를 잘 사용하고 있지만, 특정 시스템은 아직 잘 모르거나 아니면 새로운 기능에 대한 두려움 때문에 사용하지 못하고 있을 것이다. 만일 사용하고 있지 않다면 반드시 테스트해보기 바란다.

Oracle 11g에서는 또 한번의 변화가 있는 것 같다. _db_file_optimizer_read_count 파라미터의 값이 10g R2에서는 8이다. Table Scan을 지나치게 선호하지 않도록 비교적 낮은 값을 지정한 것으로 보인다. 하지만 11g에서는 놀랍게도 기본값이 128로 지정되어 있다. 사용 방식이 바뀐 것인지...? 이 부분은 추후에 테스트가 필요할 것이다.

출처 : http://ukja.tistory.com/85