OPTIMIZER_INDEX_CACHING과 OPTIMIZER_INDEX_COST_ADJ 매개변수 조정하기

OPTIMIZER_INDEX_CACHING설정하기 (0-100 default:0)

• 이 파라메터는 Nested Loops 조인이나 IN-List 탐침으로 수행되어 인덱스가 반복해서 랜덤 액세스를 할 때 인덱스 블록들이 버퍼에 캐쉬되어 있을 확률을 나타낸다.
• 이것은 곧 랜덤 액세스에 대한 부하가 감소되는 것을 의미하므로 옵티마이져가 Nested Loops 조인이나 IN-List 탐침으로 실행계획을 수립하는 경향이 증가한다.

기본값은 0으로 지정되어 있다.

• 예를 들어 100으로 설정할 경우 SQL 조건에 따라 읽어들여야 하는 인덱스 블록이 100% DB Buffer Cache에서 찾아질 것을 가정하는 것이고 0으로 설정할 경우 Buffer Cache에서 찾아질 가능성이 0이라고 가정하는 것입니다.
• 만약 랜덤 액세스가 많이 발생하는 환경에서 반복 액세스가 일어나는 인덱스가 적은 블록을 가지고 있거나 특정한 범위를 주로 액세스하여 재사용률이 매우 높다면 이 값을 증가시켜 주는 것이 좋다.
• 그러나 이것은 비용의 조정을 의미하기 때문에 옵티마이저에게 커다란 영향을 미칠 수 있으므로 주의하여야 한다.

책 429~431 방법대로 테스트를 해 보았으나 동일한 쿼리 계획을 얻음

OPTIMIZER_INDEX_COST_ADJ설정하기 (1-1000 default:100)

• OPTIMIZER_INDEX_COST_ADJ 파라미터는 디폴트 100으로 설정되어 있으며 가능한 값의 범위는 1부터 10000 까지입니다.
• 이 파라미터는 인덱스 탐색비용에 대한 평가를 조정하기 위해 사용하는 것으로서, 디폴트 설정값 100은 인덱스에 대한 액세스 비용이 보통의 비용모델에 근거해서 평가될 것이고, 10으로 설정한다면 보통의 인덱스 액세스 비용의 1/10로 평가될 것임을 의미합니다.
• 따라서 이 값이 작을수록 인덱스를 경유한 테이블 액세스(a Single-Block I/O) 비용이 더 적게 평가될 것이고, 클수록 인덱스를 경유하지 않는 Full Tabe Scan(a Multiblock I/O)의 액세스 비용이 더 적게 평가될 것입니다.
• 바꾸어 말하면, 이 파라미터는 Single-Block I/O와 Multiblock I/O에 대한 상대적인 평가로 해석할 수 있고, 따라서 테이블 데이터 블록이 평균적으로 얼마만큼 캐싱되어 있는지에 대한 의미로 해석할 수도 있습니다.
• 앞에서 설명한 OPTIMIZER_INDEX_CACHING이 인덱스가 캐싱되어 있을 확률을 가리키는 것처럼 말입니다.

OPTIMIZER_INDEX_CACHING과 OPTIMIZER_INDEX_COST_ADJ 요약

• 기본값인 OPTIMIZER_INDEX_CACHING = 0과 OPTIMIZER_INDEX_COST_ADJ = 100 : 이들은 일반적으로 데이터 웨어하우스/보고용 시스템에 적합.
• OPTIMIZER_INDEX_CACHING = 90과 OPTIMIZER_INDEX_COST_ADJ = 25 : 이들은 일반적으로 트랜잭션/OLTP 시스템에 적합.

시스템 통계의 사용 (p433)

• 오라클9i부터 OPTIMIZER_INDEX_CACHING과 OPTIMIZER_INDEX_COST_ADJ를 대체할 수 있는 DBMS_STATS패키지를 제공함으로써 통계를 수집할 수 있도록 강화됨.

작업 부하 시뮬레이션 설치

• 목적 - 오라클이 어떤 작업에 어떤 종류의 비용을 할당 하는지 보기 위함.

• 1) 순수한 OLTP 작업 부하
• 2) 순수한 보고용 또는 의사결정 시스템(DSS) 작업 부하
• 3) OLTP와 보고용 활동이 혼합된 작업 부하

1) OLTP 작업 부하 대상

create or replace procedure oltp_style
as
    l_rec big_table%rowtype;
    l_n   number;
begin
    for i in 1 .. 10000
    loop
        l_n := trunc( dbms_random.value( 2, 1000000 ) );
        select * into l_rec from big_table where id = l_n;
    end loop;
end;
/

2) 데이터웨어하우스 혹은 보고 형태의 쿼리

create or replace procedure dw_style
as
    l_n number;
begin
    select count(*) into l_n
      from (
    select /*+ USE_HASH(t1,t2)
               FULL(t1) FULL(t2)
               NOPARALLEL(t1) NOPARALLEL(t2) */
           t1.data_object_id, t2.data_object_id
      from big_table t1, big_Table t2
     where t1.id = t2.id
           );
end;
/

작업부하 시뮬레이션의 실행

exec dbms_stats.drop_stat_table( user, 'SYSTEM_STATS' );
exec dbms_stats.create_stat_table( user, 'SYSTEM_STATS' );
exec dbms_stats.delete_system_stats;

시뮬레이션 실행 절차

1. 시스템통계를 수집하지 않은 채로 저장 프로시져를 실행한다.
2. 프로시져를 N번에 걸쳐 작업 큐에 제출함으로써 백그라운드에서 실행되도록 한다.
3. DBMS_JOB 호출의 커밋을 완료함으로써 작업큐가 큐에 있는 작업들을 볼 수 있도록 한다.
4. 시스템 통계를 수집한다. OLTP/DW/MIXED의 식별태그 사용
5. 작업이 종료될 때까지 5초마다 주기적으로 작업큐를 조사한다.
6. 시스템 통계의 수집을 종료한다.

OLTP작업부하 실행

declare
    n number;
begin
    oltp_style;
    dbms_job.submit( n, 'oltp_style;' );
    dbms_job.submit( n, 'oltp_style;' );
    dbms_job.submit( n, 'oltp_style;' );
    commit;

    dbms_stats.gather_system_stats( gathering_mode => 'START',
                                    stattab => 'SYSTEM_STATS',
                                    statid => 'OLTP' );

    select count(*) into n from user_jobs where what = 'oltp_style;';
    while ( n > 0 )
    loop
        dbms_lock.sleep(5);
        select count(*) into n from user_jobs where what = 'oltp_style;';
    end loop;

    dbms_stats.gather_system_stats( gathering_mode => 'STOP',
                                    stattab => 'SYSTEM_STATS',
                                    statid => 'OLTP' );
end;
/

데이터웨어하우스 시뮬레이션 실행

declare
    n number;
begin
    dw_style;
    dbms_job.submit( n, 'dw_style;' );
    dbms_job.submit( n, 'dw_style;' );
    dbms_job.submit( n, 'dw_style;' );
    dbms_job.submit( n, 'dw_style;' );
    commit;
end;
/

혼합 작업 부하 시뮬레이션 실행

declare
    n number;
begin

    dbms_job.submit( n, 'dw_style;' );
    dbms_job.submit( n, 'dw_style;' );
    dbms_job.submit( n, 'dw_style;' );
    dbms_job.submit( n, 'oltp_style;' );
    dbms_job.submit( n, 'oltp_style;' );
    dbms_job.submit( n, 'oltp_style;' );
    commit;
end;
/

최적화기의 쿼리 계획에 대한 재고

alter system flush shared_pool;

alter session set optimizer_index_cost_adj=100;

alter session set optimizer_index_caching=0;

begin
   dbms_stats.import_system_stats
   ( stattab => 'SYSTEM_STATS', statid => 'OLTP', statown => user );
end;
/

set autotrace traceonly explain
select /* TAGGED OLTP */ *
  from big_table t1, big_table t2
 where t1.id = t2.id
   and t2.id between 50 and 55;

SELECT * FROM SYS.AUX_STATS$

시스템 통계 요약

• DBMS_STATS GATHER_SYSTEM_STATS을 사용하면 OPTIMIZER_INDEX_CACHING과 OPTIMIZER_INDEX_COST_ADJ 매개변수의 상세한 튜닝을 하지 않아도 된다
• 시스템에는 각기 다른 부하 프로파일을 나타내는 여러벌의 시스템 통계가 있어야 한다.(OLTP, 보고/DSS, 배치)
• => DSS (decision support system) ; 의사결정지원시스템
• 시스템 통계의 수집은 일과성으로 끝날 작업이 아니라 끊임없이 계속되어야 하는 이벤트이다