태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'Oracle 11g'에 해당되는 글 7건

  1. 2009.03.25 오라클 11g의 automatic memory management(AMM)에 대한 아름다운 Test Case (7)
  2. 2008.02.09 Oracle 11g Interval Partition의 버그 (3)
  3. 2008.01.30 _session_wait_history 파라미터의 변화
  4. 2008.01.22 Oracle 11g - Extended Statistics와 Histogram의 화려한 부활 (3)
  5. 2008.01.13 Oracle 8i Nosegment Index vs. Oracle 11g Invisible Index
  6. 2008.01.07 Bind Peeking의 딜레마와 Oracle 11g의 Adaptive Cursor Sharing (15)
  7. 2008.01.02 (Oracle 10g) dbms_stats.gather_schema_stats 수행에 시간이 너무 많이 걸려요... (1)

오라클 11g의 automatic memory management(AMM)에 대한 아름다운 Test Case

오라클 2009.03.25 19:19
오라클 11g는 SGA뿐 아니라 PGA까지 포함하는 강력한 자동 메모리 관리(Automatic Memory Management:AMM) 기능을 제공한다.

앞으로는 오라클에 얼마만큼의 메모리를 써라~고 지정만 해주면 된다는 것을 의미한다. 멋진 일이긴 하지만, 실제 적용할 때는 염두에 두어야 할 몇가지 함정들이 있을 것이다.

간단해 보이지만, AMM의 속성을 잘 알 수 있는 Test Case를 만들어 보겠다.

  • Memory Target, Memory Max Target 값을 200M로 설정한다.
  • DB Cache와 Shared Pool 간의 경합을 시뮬레이션하기 위해, logical reads와 hard parse를 혼합해서 과도하게 발생시킨다.
  • 이 상황에서, PGA aggregate target 의 변화가 발생하는지 체크한다.


*.memory_target=200m
*.memory_max_target=200m

declare
  pat1     varchar2(1000);
  pat2     varchar2(1000);
  va       number;
begin
  select ksppstvl into pat1
    from sys.xm$ksppi i, sys.xm$ksppcv v   -- synonym of x$ table
    where i.indx = v.indx
    and i.ksppinm = '__pga_aggregate_target';

  for idx in 1 .. 1000000 loop

    -- Mixed workloads of heavy logical reads and hard parse
    execute immediate
       'select count(*) from t3 where 10 = mod('||idx||',10)+1' into va;

    if mod(idx, 100) = 0 then
      sys.dbms_system.ksdwrt(2, idx || 'th execution');

      for p in (select ksppinm, ksppstvl
          from sys.xm$ksppi i, sys.xm$ksppcv v
          where i.indx = v.indx
          and i.ksppinm in
           ('__shared_pool_size', '__db_cache_size',
              '__pga_aggregate_target'))  
             loop

          sys.dbms_system.ksdwrt(2, p.ksppinm || ' = ' || p.ksppstvl);
      end loop;

      select ksppstvl into pat2
      from sys.xm$ksppi i, sys.xm$ksppcv v
      where i.indx = v.indx
      and i.ksppinm = '__pga_aggregate_target';

      -- Do I have PGA aggregate target change?
      if pat1 <>  pat2 then
        sys.dbms_system.ksdwrt(2, 'yep, I got it! pat1=' || pat1 ||', pat2='||pat2);
        exit;
      end if;
    end if;
  end loop;
end;
/


결과는 아래와 같다. (alert log 파일)

100th execution
__shared_pool_size = 92274688
__db_cache_size = 16777216
__pga_aggregate_target = 83886080
200th execution
__shared_pool_size = 92274688
__db_cache_size = 16777216
__pga_aggregate_target = 83886080
300th execution
__shared_pool_size = 88080384
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
400th execution
__shared_pool_size = 92274688
__db_cache_size = 16777216
__pga_aggregate_target = 83886080
500th execution
__shared_pool_size = 88080384
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
600th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
700th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
800th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
900th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
1000th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
1100th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080

1200th execution
__shared_pool_size = 92274688
__db_cache_size = 37748736
__pga_aggregate_target = 58720256
yep, I got it! pat1=83886080, pat2=58720256


기대했던대로, Oracle은 부하에 따라 Buffer Cache의 크기와 Shared Pool의 크기를 주거니 받거니 하면서 변경하는 것을 볼 수 있다.

그런데, 1200번째 실행에서는 갑자기 PGA aggregate target 영역에서 메모리를 훔쳐서 Buffer Cache로 가져다쓴다. 이 역시 AMM(Automatic Memory Management)에서는 충분히 발생가능한 일이다.

문제는 어떤 특정 상황에서는 위험이 발생할 수 있다는 것이다. PGA aggregate target가 바뀐다는 것은 Hash/Sort관련 Operation의 Cost가 변경된다는 것을 의미한다. 따라서 갑자기 실행 계획이 바뀌는 것과 같은 위험한 상황이 발생 할 수도 있다. PGA Aggregate Target이 변하면 Hash/Sort의 성능 자체에도 영향을 주게 된다.

조금 극단적인 Test Case라고 할 수 있지만, 이런 현상이 논리적인 빈틈이나 버그로 인해 끔찍한 결과를 초래할 수 있다는 것은 경험으로 잘 알 고 있을 것이다.

오라클 11g가 좀 더 대중화되면, 이런 유형의 문제에 대한 실사례를 접할 수 있을 것이다.


신고
Trackbacks 5 : Comments 7
  1. oracler 2009.03.31 22:54 신고 Modify/Delete Reply

    끄응...이 글과 상관없는 걸 질문해도 될련지요. 염치불구하고.. 쩝

    1. clob, blob 처럼 lob 타입이 컬럼으로 있는 테이블에 대량의 데이타(1000만건 이상)
    을 insert 할때는 현저하게 느려지는데 성능 개선 방법이 있는지요.
    lob 타입컬럼을 빼고 insert 하는 경우와 lob 타입 컬럼을 포함해서
    insert 하는것이 성능 차이가 아주 많이 납니다. 10배 100 이상 극단적으로..

    2. lob 타입 컬럼에 대해서는 테이블생성시 자동으로 인덱스가 만들어지는데 (SYS_XX)
    이 lob type 인덱스를 수작업으로 제거하거나 재생성할수 있는지요.
    위 1번에서 대량 데이타 insert 시 미리 index 제거하고 하는데
    이 lob type 컬럼 인덱스는 처리를 못했습니다.
    이 인덱스때문에 insert 가 느린건지는 알수없네요.

    3. lob type 의 인덱스는 사이즈가 다른 btree 인덱스에 비해 아주 작더군요.
    이게 무슨 메커니즘으로 작동되는지 궁금합니다.
    꼭 있어야 하는건지. (하긴 꼭 있어야 하니깐 자동으로 만들어지겠지만)

    미리 감사드립니다.

  2. 욱짜 2009.04.01 08:16 신고 Modify/Delete Reply

    번거로우시겠지만, 이 질문을 다른 분들도 공유할 수 있도록 http://ask.ex-em.com 에 올려주시겠습니까?

  3. oracler 2009.04.01 23:52 신고 Modify/Delete Reply

    인덱스 문제가 아니었군요 cache 는 메모리 소진문제로 사용못할거 같네요. nocache 로 놓고 logging nologging 을 확인해봐야 할듯합니다.. 감사합니다.

  4. 욱짜 2009.04.02 08:34 신고 Modify/Delete Reply

    넵. 다양한 옵션을 테스트하셔서 최적의 설정을 찾으시는 것이 가장 좋은 방법일 듯 합니다.

    한가지 아이디어는 Insert시에는 cache 속성을 켰다가 Insert가 끝나면 cache 속성을 끄는 것도 생각해 볼 수 있습니다.

  5. oracler 2009.04.07 00:19 신고 Modify/Delete Reply

    대용량 DML 튜닝은 쿼리와는 다를거 같은데요.

    cache / logging 옵션 alter 도 있긴한데..
    이처럼 대용량 insert 시에 더 무서운것이 아카이브의 변수가 있습니다.

    blob 컬럼 1000만건 insert 처럼 대용량 insert 의 경우엔
    데이타 볼륨 자체가 매우커서 DB 아카이브 상태가 다른 일반적인 insert 보다 훨씬 더 큽니다.

    exem 답변은 cache 가 답인듯으로 받아들여질수 있는데
    insert 그것만 따지면 빠르겠지만 다른 쿼리들이나 시스템 영향 주는것 생각하면
    blob 컬럼에 대한 cache 옵션은 대다수의 경우엔
    일반적인 답으로서는 고려하면 안될듯한 옵션이고
    그런 DB 전체적 관점에서 부작용까지 같이 언급을 해주시면 좋겠습니다.

    성능 튜닝이 insert 등 DML 에 촛점을 맞춘 경우는 드물었던것 같습니다.
    DML 로 접어들면 select 때와는 다르게 고려할 변수가 상당히 많죠.
    사실 대부분이 "성능"하면 Query (SELECT) 를 먼저 생각하는데, 고객은 그런것 모르고 오로지 응답만을 봅니다.

    운영DB 는 아카이브겠지만 구축환경은 대부분 노아카이브입니다.
    DML 의 성능관점에서 보면 SQL 트레이스 뜨는것보다
    이런 환경적인 튜닝요소가 더 중요할수 있다는겁니다.

  6. 욱짜 2009.04.07 08:25 신고 Modify/Delete Reply

    이 의견도 Ask Exem에 남겨주시면 더 좋을텐데요. ^^

    DML 튜닝은 Query 튜닝과는 또 다른 요소들이 많죠. 특히 주의할 것이 말씀하신 것처럼 Redo 양을 튜닝하는 것입니다. Redo를 최적화하기 위해 다양한 기법을 사용할 수 있습니다.

    시스템마다 CPU 성능, Memory 크기, Storage 성능, 환경설정이 다르고 DML Pattern마다 Logical Reads와 Redo가 생성되는 패턴이 다르기 때문에 "DML 튜닝은 이렇게 하라"는 법칙을 만들기는 거의 불가능합니다. 몇 가지 중요한 법칙은 기억하되 개별 DML은 모두 다른 문제로 인식하고 튜닝을 하는 것이 정답일 것 같습니다.

    DML 튜닝에 있어서도 SQL*Trace는 중요한 역할을 합니다. 만일 Redo 양에 문제가 있다면 log file parallel write나 log file switch 관련 대기 현상이 기록될 겁니다. 지나친 Data File I/O가 문제라면 free buffer space나 db file parallel write같은 대기 현상이 나타날 것이구요. 단, Redo의 양이나 기타 중요한 정보들은 SQL*Trace에는 나타나지 않기 때문에 저 같은 경우는 SQL*Trace와 함께 제가 구현한 일종의 Session Monitoring Script를 사용합니다. Session Stats, Time Model, Wait Event, Latch Activity 등의 정보를 종합적으로 봐서 튜닝 요소를 찾는 방법을 선호합니다. 이런 다양한 정보들을 수집해야만 좀 더 과학적인 튜닝이 가능합니다.

  7. 이재현 2011.06.02 17:46 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


Oracle 11g Interval Partition의 버그

오라클 2008.02.09 13:19
Oracle 11g에서 추가된 Interval Partition과 관련된 버그가 Oracle Forum을 통해 발견되었다. 상당히 재미있는 버그이고, 실제로 Interval Partition을 사용하는데 있어 문제를 일으킬 만한 소지가 있다. 그래서 블로그를 통해 정보를 공유한다. 아래 테스트 결과를 보자.

SQL> drop table t_int_part purge;
테이블이 삭제되었습니다.

SQL>
SQL> CREATE TABLE t_int_part ( part_key number )
  2  PARTITION BY RANGE (part_key) INTERVAL(10)
  3  ( PARTITION p0 VALUES LESS THAN (10) )
  4  ;
테이블이 생성되었습니다.

SQL> -- 인덱스 생성
SQL> ALTER TABLE t_int_part ADD CONSTRAINT t_int_part_pk PRIMARY KEY (part_key) USING INDEX LOCAL;
테이블이 변경되었습니다.

-- 12건의 데이터 생성. 이 과정에서 세 개의 Partition이 동적으로 생성된다.
SQL> insert into t_int_part values(2);
SQL> insert into t_int_part values(4);
SQL> insert into t_int_part values(6);

SQL>
SQL> insert into t_int_part values(12);
SQL> insert into t_int_part values(14);
SQL> insert into t_int_part values(16);

SQL>
SQL> insert into t_int_part values(22);
SQL> insert into t_int_part values(24);
SQL> insert into t_int_part values(26);

SQL> -- 주의!!! 아래 과정에서 비연속적인 Internval에 의한 파티션이 생긴다.(10,20,40)
SQL> insert into t_int_part values(42);
SQL> insert into t_int_part values(44);
SQL> insert into t_int_part values(46);

SQL> commit;
커밋이 완료되었습니다.

-- 파티션 확인
SQL> select partition_name from dba_tab_partitions where table_name = 'T_INT_PART';
PARTITION_NAME
------------------------------
SYS_P84
SYS_P85
P0
SYS_P83

-- Full Table Scan. 정상적으로 12건이 나온다.
SQL> select /*+ full(t_int_part) */ * from t_int_part;
  PART_KEY                                                                     
----------                                                                     
         2                                                                     
         4                                                                     
         6                                                                     
        12                                                                     
        14                                                                     
        16                                                                     
        22                                                                     
        24                                                                     
        26                                                                     
        42                                                                     
        44                                                                     
        46                                                                     

12 개의 행이 선택되었습니다.

-- Index Full Scan. 정상적으로 12건이 나온다.
SQL> select /*+ index(t_int_part) */ * from t_int_part;
  PART_KEY                                                                     
----------                                                                     
         2                                                                     
         4                                                                     
         6                                                                     
        12                                                                     
        14                                                                     
        16                                                                     
        22                                                                     
        24                                                                     
        26                                                                     
        42                                                                     
        44                                                                     
        46                                                                     

12 개의 행이 선택되었습니다.

-- Index Range Scan. 정상적으로 12건이 나온다.
SQL> select /*+ index(t_int_part) */ * from t_int_part where part_key > 0;

  PART_KEY                                                                     
----------                                                                     
         2                                                                     
         4                                                                     
         6                                                                     
        12                                                                     
        14                                                                     
        16                                                                     
        22                                                                     
        24                                                                     
        26                                                                     
        42                                                                     
        44                                                                     
        46                                                                     

12 개의 행이 선택되었습니다.

-- Index Fast Full Scan. 12건이 아니라 9건이 나온다.
SQL> select /*+ index_ffs(t_int_part) */ * from t_int_part;

  PART_KEY                                                                     
----------                                                                     
         2                                                                     
         4                                                                     
         6                                                                     
        12                                                                     
        14                                                                     
        16                                                                     
        22                                                                     
        24                                                                     
        26                                                                     

9 개의 행이 선택되었습니다.

위의 테스트 결과를 보면 Index Fast Full Scan의 경우 비연속적인 마지막 파티션(42,44,46의 값)을 인식하지 못하는 것을 확인할 수 있다. 반면 Table Full Scan, Index Range Scan, Index Full Scan에서는 이런 오류가 없다.

위의 내용은 Oracle Forum에서 한 사용자의 질문에서 시작되어 결국 오라클의 버그로 판명났다. 아마 다음 패치때 수정된 버전이 제공될 것이다. 그 전까지는 Oracle 11g에서 Interval Partition을 사용하는 경우에는 Index Fast Full Scan이 비정상적으로 작동한다는 사실을 잘 기억해야 할 것이다.

Oracle Forum의 원문은 여기를 참조한다.


신고
Trackback 0 : Comments 3
  1. steve.kim 2008.02.11 20:22 신고 Modify/Delete Reply

    오 신기한데요. 혹시나 해서 _fast_full_scan_enabled 파라미터를 세션레벨에서 FALSE로 바꾸어도 여전히 INDEX FAST FULL SCAN으로 풀리면서 결과가 비정상으로 나온데요. 11g에서는 해당 파라미터가 안먹는군요.

  2. 욱짜 2008.02.11 20:58 신고 Modify/Delete Reply

    Oracle 11g의 버그 Parade는 이제 시작인거 같습니다. 앞으로 기대가 됩니다. ^^;

  3. 서상서 2011.01.07 16:42 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


_session_wait_history 파라미터의 변화

오라클 2008.01.30 17:48
Oracle 10g에서 Active Session History와 함께 Wait History의 개념이 소개되었다.
v$session_wait_history 뷰를 통해서 Wait Event의 이력을 조회하는 기능이다.

Session이 대기하는 Wait Event를 즉석에서 간편하게 조회할 수 있다는 점에 대단히 유용한 뷰라고 할 수 있다. 단, 치명적인 단점이 하나 있는데 최대 "10개"까지의 이력 정보만 보관하고 있다는 점이다. 이 때문인지 이 뷰를 이용하는 경우는 잘 없는 거 같다.

Oracle 11g가 출시되었을 때 한 가지 기대한 것이 이 v$session_wait_history 뷰가 보관하는 이력이 더 많아지지 않았을까 하는 것이었다. 아래가 그 확인 결과이다.

v$session_wait_history 뷰가 저장하는 이력의 길이는 _session_wait_history라는 히든 파라미터에 의해 결정된다. 이것은 10g와 11g에 공통된 사항이다. 단, 10g에서는 이 파라미터 값을 10보다 크게 주면 다음과 같은 에러가 발생한다.

SQL10g> alter system set "_session_wait_history"=50 scope=spfile;
alter system set "_session_wait_history"=50 scope=spfile
*
ERROR at line 1:
ORA-00068: invalid value 50 for parameter _session_wait_history, must be between 0 and 10

즉, 10이 최대값인 것이다. 하지만 11g에서는 이런 제약이 없다.

SQL11g> alter system set "_session_wait_history"=50 scope=spfile;

시스템이 변경되었습니다

그럼 11g에서의 최대값은?

SQL> alter system set "_session_wait_history"=10000 scope=spfile;
alter system set "_session_wait_history"=10000 scope=spfile
*
1행에 오류:
ORA-00068: 10000 값은 _session_wait_history 매개변수에 부적당하며, 0와 100 사이의 값이어야 합니다.

즉, 11g에서의 최대값은 100으로 제한된다. 이 값을 50으로 설정하면 다음과 같이 50개의 Wait History가 조회 가능하다.

select event, p1, p2, p3, wait_time
from v$session_wait_history
where sid = 132;

EVENT    P1    P2    P3    WAIT_TIME
----------------------------------------------------
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
log file sync    2339    0    0    0
SQL*Net message from client    1413697536    1    0    4288
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    172
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
log file sync    2316    0    0    0
SQL*Net message from client    1413697536    1    0    34660
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
db file sequential read    1    74705    1    0
db file sequential read    1    74705    1    1
SQL*Net message from client    1413697536    1    0    904
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
db file sequential read    1    11370    1    2
db file sequential read    1    8518    1    0
db file sequential read    1    56227    1    0
db file sequential read    1    8520    1    0
db file sequential read    1    8519    1    1
db file sequential read    1    17221    1    0
db file sequential read    1    17224    1    2
db file sequential read    1    56225    1    0
db file sequential read    1    56216    1    1
db file sequential read    1    13730    1    1
db file sequential read    1    1430    1    0
db file sequential read    1    13641    1    0
db file sequential read    1    13634    1    1
db file sequential read    1    1413    1    0
db file sequential read    1    10896    1    1
db file sequential read    1    1437    1    3
log file sync    2149    0    0    0
SQL*Net message from client    1413697536    1    0    17365
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0
SQL*Net message from client    1413697536    1    0    0
SQL*Net message to client    1413697536    1    0    0

Wait Event를 이용해 시스템 문제를 심도깊게 분석하고자 하는 경우에는 11g의 확장된 Wait History를 이용해도 좋을 거 같다.

-- (첨부)---------------------------------------------------------------------
애석하게도 _session_wait_history 파라미터 값은 System 전체 레벨로만 적용 가능하면 변경된 값을 적용하려면 Instance를 Rebound(재시작) 시켜야 한다. 내가 기대한 것은 ...

alter session set "_session_wait_history" = 100;

과 같이 세션 레벨에서 동적으로 변경하는 것이었으나 오라클이 거기까지 고려하지는 못한 거 같다.
신고
Trackback 0 : Comment 0

Write a comment


Oracle 11g - Extended Statistics와 Histogram의 화려한 부활

오라클 2008.01.22 17:49
Chris Antognini라는 이름이 꽤 알려진 Oracle Consultant가 있는데, 어느 뉴스그룹에서 이런 말을 남긴 것을 본 적이 있다.

"Extended Statistics는 Oracle 11g의 핵심적인 기능 중 하나이다"

Oracle 11g에 Extended Statistics 기능이 추가된 것은 익히 알고 있었지만, 이것이 Oracle 11g의 핵심적인 기능이라니?

지나친 과장이 아닌가... 하는 생각도 들지만 Oracle의 핵심 중의 핵심이 Optimizer이고, Optimizer가 정상적으로 작동하기 위해서는 통계 정보가 필요하며, Extended Statistics는 Oracle의 고질적인 문제인 멀티 컬럼에서의 부정확한 통계 문제의 해결책이라는 것을 생각하면 결코 과장만은 아닌 셈이다.

그래서 간단한 테스트를 통해 왜 Extended Statistics가 왜 이렇게 극진한 대접을 받는지 알아보고자 한다.

1. 정확한 Cardinality 계산
우선 다음과 같은 간단한 테이블을 생성한다.

create table t_ext_stat(country varchar2(20), city varchar2(10), code char(5));

insert into t_ext_stat
select 'Korea', decode(mod(rownum,2),0, 'Seoul', 'Busan'), rpad(rownum,5,' ')
from all_objects where rownum <= 10000;

insert into t_ext_stat
select 'Korea', 'Jeju', '11111' from dual;

insert into t_ext_stat
select 'Japan', decode(mod(rownum,3),0,'Tokyo', 1, 'Osaka', 'Kyoto'), rpad(rownum,5,' ')
from all_objects where rownum <= 10000;

insert into t_ext_stat
select 'Japan', 'Okinawa', '11111' from dual;

commit;

country + city 의 정확한 분포는 다음과 같다.
_ Korea + Seoul = 5000건 = 25%
- Korea + Busan = 5000건 = 25%
- Korea + Jeju = 1건 = 0%
- Japan + Tokyo = 3333건 = 16.7%
- Japan + Osaka = 3334건 = 16.7%
- Japan + Kyoto = 3333건 = 16.7%
- Japan + Okinawa = 1건 = 0%
총 20,002 건
country+city의 density = 1/7 = 0.142857143 가 된다.

country의 분포는 다음과 같다.
- Korea = 10001건 (50%)
- Japan = 10001건 (50%)
country의 desnity = 1/2 = 0.5 가 된다.

city의 분포는 다음과 같다.
_ Seoul = 5000건 = 25%
- Busan = 5000건 = 25%
- Jeju = 1건 = 0%
- Tokyo = 3333건 = 16.7%
- Osaka = 3334건 = 16.7%
- Kyoto = 3333건 = 16.7%
- Okinawa = 1건 = 0%
city의 density는 1/7 = 0.142857143 가 된다.

1.1 Extended Statistics가 없는 경우

exec dbms_stats.gather_table_stats(user,'T_EXT_STAT', estimate_percent=>100,      method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>true);

통계 정보를 확인해 보면 위에서 계산한 결과와 동일한 것을 확인할 수 있다.

select column_name, num_distinct, density, histogram
from dba_tab_col_statistics
where table_name = 'T_EXT_STAT';

COLUMN_NAME    NUM_DISTINCT    DENSITY                 HISTOGRAM
CODE             10001            9.99900009999E-5      NONE
CITY             7               0.142857142857143      NONE
COUNTRY          2               0.5                    NONE

(country, city)에 대한 Extended Statistics가 없는 경우에는 Bind Variable에 대한 Cardinality는 어떻게 계산될까? 아래 테스트 결과를 보면...

explain plan for
select * from t_ext_stat
where country = :c1 and city = :c2;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1429 | 25722 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EXT_STAT |  1429 | 25722 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------

1429라는 Cardinality는 어떻게 계산되는가를 추론해보면...

select 20002 * 0.5 * 0.142857 from dual = 1428.7

즉 country 컬럼의 density와 city 컬럼의 density를 곱한 결과라는 것을 확인할 수 있다. 일반적으로 다음과 같은 공식이 사용된다.
density (c1 and c2) = density(c1) * density(c2)
density (c1 or c2) = density(c1) + density(c2) - density(c1) * density(c2)

하지만 우리의 위의 결과가 잘못된 것이라는 걸 알고 있다.
country and city 컬럼의 density는 0.14*0.5 가 아니라, 0.14이기 때문이다. 따라서 정확한 통계 정보를 가지고 있다면 위의 실행 계획에서 Rows(Cardinality)는 1429가 아니라 2857(20002 *  0.14)가 되어야 한다.

1.2 country + city에 대해 Extended Statistics가 있는 경우
아래와 같이 Extended Statistics를 수집한 후 동일한 쿼리에 대한 Cardinality의 차이를 보자.

exec dbms_stats.gather_table_stats(user,'T_EXT_STAT', estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE SKEWONLY (COUNTRY, CITY)', cascade=>true);

select column_name, endpoint_number, endpoint_value
from dba_tab_histograms
where table_name = 'T_EXT_STAT';

COLUMN_NAME                                    ENDPOINT_NUMBER    ENDPOINT_VALUE
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    5000                     1352131667
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    5001                     1438906098
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    5002                     1767302319
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    8335                     2811161969
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    11669                   5980721804
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    15002                   7501819795
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    20002                   7717880445
...

explain plan for
select * from t_ext_stat
where country = :c1 and city = :c2;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  2857 | 54283 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EXT_STAT |  2857 | 54283 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Extended Statistics를 생성한 후에는 데이터를 정확하게 반영한 값인 2857의 Cardinality가 계산된 것을 알 수 있다.

위의 예에서는 큰 의미가 없어보이지만, 이 정도의 정확한 Cardinality를 계산해내는 것만으로도 대단한 개선이 아닐 수 없다.

2. 정밀한 Histogram
Extended Statistics는 Histogram의 사용에서 그 진가가 드러난다. 일단 Histogram에 따른 실행 계획의 변동을 테스트하기 위해 다음과 같이 country + city 컬럼에 대해 인덱스를 생성한다.

create index t_ext_stat_idx on t_ext_stat(country, city);

2.1 Extended Statistics가 없는 경우
country = 'Korea' 조건과 city = 'Jeju' 조건을 동시에 만족하는 데이터가 1건에 불과하다는 것을 알고 있다. 하지만 불행하게도 Oracle은 이 사실을 전혀 알 수 없다. 두 컬럼을 합친 통계 정보가 기본적으로 존재하지 않기 때문이다.

explain plan for
select * from t_ext_stat
where country = 'Korea' and city = 'Jeju';

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  2857 | 51426 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EXT_STAT |  2857 | 51426 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------

위의 테스트 결과를 보면 country와 city 컬럼에 대해 1/7의 density를 적용한 Cardinality를 사용한 것을 알 수 있다. (country + city) 인덱스를 이용해 Distinct 값 수가 7개라는 것을 알기 때문이다.

덕분에 Index Range Scan이 아닌 Full Table Scan을 사용하게 된다. 비록 결과는 1건에 불과하지만 Oracle은 그것을 알 길이 없기 때문이다

2.2 country + city에 대해 Extended Statistics가 있는 경우
결과는 매우 극적이다. 아래 테스트 결과를 보자.

explain plan for
select * from t_ext_stat
where country = 'Korea' and city = 'Jeju';

select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    19 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_EXT_STAT     |     1 |    19 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_EXT_STAT_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Oracle은 country = 'Korea' and city = 'Jeju' 조건에 해당하는 Row수가 "1"건이라고 가정했으며, 덕분에 Index Range Scan을 수행한다. 실제 건수는 1건이므로 매우 정확한 예측이라고 할 수 있다. 위의 1건은 어디서 나온 결과인가? Histogram 정보를 보면 답이 나온다.

select column_name, endpoint_number, endpoint_value
from dba_tab_histograms
where table_name = 'T_EXT_STAT';

COLUMN_NAME                                    ENDPOINT_NUMBER    ENDPOINT_VALUE
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    5000                     1352131667
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    5001                     1438906098
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    5002                     1767302319
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    8335                     2811161969
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    11669                   5980721804
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    15002                   7501819795
SYS_STUIV5GX7MPO71YP6CTLXMRZ21    20002                   7717880445
...

즉, Oracle은 country + city의 조합 컬럼에 대한 통계 정보(Histogram)을 이용해 정확한 로우수를 예측할 수 있었고, 덕분에 Full Table Scan이 아닌 효율적인 Index Range Scan을 사용할 수 있는 것이다.

위의 테스트 결과는 비록 매우 단순한 내용을 담고 있지만 Extended Statistics가 내포하고 있는 심오한 목적을 이해하기에 충분할 것이다. 그 목적이란 한 마디로 "통계 정보를 정말 쓸만하게 만들어보자"는 것이다. Histogram은 매우 중요한 정보임에도 불구하고 Oracle의 Histogram 구현 한계로 인해 사용이 자제되어 왔다. 이제 Extended Statistics의 소개로 Histogram은 다시 한번 생명의 불씨를 지피는 셈이다. Oracle 11g의 또 다른 중요한 기능 중 하나인 Adaptive Cursor Sharing 과 함께 이제야말로 제대로 된 Histogram 사용이 가능해진 셈이다.

PS) Histogram의 수집은 통계 정보 수집 시간을 지연시키는 중요한 이유 중의 하나이다.  Extended Statistics의 부하는 특히 심하다고 할 수 있다. 꼭 필요한 컬럼에 대해서만 Histogram을 수집하는 센스가 필요하다.

신고
Trackbacks 10 : Comments 3
  1. 2010.07.12 09:12 Modify/Delete Reply

    비밀댓글입니다

    • 욱짜 2010.07.09 16:25 신고 Modify/Delete

      CITY 컬럼에 Histogram이 생긴 결과로 city = 'Jeju'에 해당하는 데이터 건수가 1건에 불과하다는 것을 알기 때문입니다.

      포스트의 테스트 케이스가 조금 문제가 있네요! Extended Statistics의 장점을 더 잘 나타내는 테스트 케이스였어야 하는데요. ^^;

    • 방문객 2010.07.12 09:13 신고 Modify/Delete

      답변 감사드립니다.^^

Write a comment


Oracle 8i Nosegment Index vs. Oracle 11g Invisible Index

오라클 2008.01.13 01:43
Oracle 11g New Feature에 새로운 이야기들을 많이 들었을 것이다.
이중 유난히 나의 관심을 끄는 한 가지가 Invisiblie Index이다. Invisilble Index란 말 그대로 "보이지" 않는 인덱스를 의미한다. 더 정확하게 말하면 인덱스 세그먼트로서 물리적으로 존재는 하지만 Optimizer에게는 보이지 않는 인덱스를 의미한다.

테이블에 새로운 인덱스를 추가하면 Optimizer의 비용(Cost) 계산에 영향을 주어 기존 쿼리의 실행 계획에 영향을 주게 된다. 물론 이것은 바람직한 것이고, 인덱스의 존재 이유이기도 하다.

하지만, 간혹 인덱스를 추가했을 때 이 인덱스로 인해 성능 개선이 이루어질 것인지만을 테스트해고 싶은 경우가 있다. 이런 경우에는 어떻게 해야할까?

잘 알려지지 않은 사실이지만, Oracle 8i에서 Virtual Index 기능이 추가되었다. Virtual Index란 말 그대로 가상의 인덱스로 실제로는 인덱스가 존재하지 않지만, 인덱스가 존재하는 것처럼 데이터를 조작하는 것을 의미한다. 이런 의미에서 흔히 Fake Index(가짜 인덱스)라고 부르기도 한다. 아래 테스트 스크립트를 보자.

select * from v$version;
--------------------------------------------------------
BANNER
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0    Production
TNS for HPUX: Version 8.1.7.0.0 - Development
NLSRTL Version 3.4.1.0.0 - Production

-- Table 생성
create table t_noseg(id int, id2 int);

-- Clustering Factor를 달리 하기 위해 id, id2의 순서를 다르게 삽입한다.
insert into t_noseg
select rownum, decode(mod(rownum,2),1,50000-rownum+1,rownum) from all_objects t1, all_objects t2
where rownum <= 50000

commit;

-- Virtual Index를 생성한다. (NOSEGMENT 속성 부여)
-- id 컬럼에 대한 인덱스는 Clustering Factor가 매우 우수하다.
-- 하지만 id2 컬럼에 대한 인덱스는 Clustering Factor가 매우 불량하다.
create index t_noseg_idx on t_noseg(id) nosegment;
create index t_noseg_idx2 on t_noseg(id2) nosegment;

-- 통계 정보를 생성한다.
exec dbms_stats.gather_table_stats(user,'T_NOSEG', cascade=>true);

-- Virtual Index는 실제로는 사용되지 않으며 실행 계획을 테스트하는 목적으로만 사용된다.
-- _use_nosegment_indexes 값을 true로 변경하면 실행 계획에 반영할 수 있다.
alter session set "_use_nosegment_indexes" = true;

-- 다음과 같이 id 컬럼에 대해 가상의 인덱스를 선택하는 실행 계획이 수립됨을 확인할 수 있다.
-- Cost가 4이고, Index는 Analyze가 되지 않았다는 사실에 주목하자.
select * from t_noseg
where id between 1 and 100;

 SELECT STATEMENT CHOOSE-Cost : 4
  TABLE ACCESS BY INDEX ROWID MAXGAUGE.T_NOSEG(1) Analyzed : 20080113
   INDEX RANGE SCAN MAXGAUGE.T_NOSEG_IDX(NU) (ID) NOT ANALYZED

-- id2 컬럼에 대해서도 역시 인덱스를 선택하는 실행 계획이 수립된다.
-- 하지만, Clustering Factor에 의한 비용 증가는 계산되지 않는다.
select * from t_noseg
where id2 between 1 and 100;

 SELECT STATEMENT CHOOSE-Cost : 4
  TABLE ACCESS BY INDEX ROWID MAXGAUGE.T_NOSEG(1) Analyzed : 20080113
   INDEX RANGE SCAN MAXGAUGE.T_NOSEG_IDX2(NU) (ID2) NOT ANALYZED
  
-- 세그먼트가 존재하는가? 
select count(*) from dba_segments where segment_name = 'T_NOSEG_IDX';
----------------------------------------------------------------
0

select count(*) from dba_objects where object_name = 'T_NOSEG_IDX';
----------------------------------------------------------------
1

위의 테스트 결과를 보면 다음과 같은 사실을 추론할 수 있다.
- Virtual Index는 실제로는 세그먼트가 생성되지 않으며, 딕셔너리에만 존재하는 인덱스이다.
- 따라서 실행 계획을 추측해보는데는 도움이 되지만, 실제로 실행에 반영할 수는 없다.
- 실제 세그먼트가 존재하지 않기 때문에 통계 정보를 수집할 수 없다. 따라서 Clustering Factor와 같은 중요한 요소는 계산되지 않는다. 이는 비용 계산에 오류를 초래할 수 있다.

Virtual Index는 오라클 내부적으로만 사용될 목적으로 만들어졌으며 대중적으로 공개가 되어 있지 않다.

Oracle 11g는 Virtual Index를 계승 발전시켜서 Invisible Index라는 전혀 새로운 개념을 소개하고 있다. Invisible Index는 Virtual Index와는 달리 실제로 세그먼트 형태로 존재하는 인덱스이다. 다만 Optimizer에게 보이지 않을 뿐이다. 아래 테스트 스크립트를 보자.

-- Table 생성
create table t_invisible(id int, id2 int);

-- 역시 Clustering Factor를 달리 하기 위해 id, id2의 순서를 다르게 삽입한다.
insert into t_invisible
select rownum, decode(mod(rownum,2),1,50000-rownum+1,rownum) from all_objects t1, all_objects t2
where rownum <= 50000

commit;

-- Invisible Index를 생성한다.
create index t_invisible_idx on t_invisible(id) invisible;
create index t_invisible_idx2 on t_invisible(id2) invisible;

-- Invisible Index를 사용하게끔 변경한다. 이렇게 해야만 통계 정보가 수집된다.
alter session set optimizer_use_invisible_indexes=true;

-- 통계 정보 수집
exec dbms_stats.gather_table_stats(user,'T_INVISIBLE', cascade=>true);

-- 다음과 같이 id 컬럼에 대해 가상의 인덱스를 선택하는 실행 계획이 수립됨을 확인할 수 있다.
-- Cost가 3이고, Index가 Analyze되었음에 유의하자.
select * from t_invisible
where id between 1 and 100;

 SELECT STATEMENT ALL_ROWS-Cost : 3
  TABLE ACCESS BY INDEX ROWID UKJA.T_INVISIBLE(1)
   INDEX RANGE SCAN UKJA.T_INVISIBLE_IDX (ID)

-- id2 컬럼에 대해서는 Full Table Scan을 사용하다.
-- id2 컬럼은 Clustering Factor가 매우 불량하기 때문에 이것이 비용에 반영된 결과이다.
select * from t_invisible
where id2 between 1 and 100;

 SELECT STATEMENT ALL_ROWS-Cost : 21
  TABLE ACCESS FULL UKJA.T_INVISIBLE(1)

-- 실제 id2 컬럼에 대해 인덱스를 사용하면 비용이 어떻게 계산되지는 확인해보면 아래와 같다.
-- 비용이 102로 Full Table Scan보다 오히려 비싸게 계산되는 것을 확인할 수 있다.
select /*+ index(t_invisible t_invisible_idx2) */* from t_invisible
where id2 between 1 and 100;

 SELECT STATEMENT ALL_ROWS-Cost : 102
  TABLE ACCESS BY INDEX ROWID UKJA.T_INVISIBLE(1)
   INDEX RANGE SCAN UKJA.T_INVISIBLE_IDX2 (ID2)
 
-- 세그먼트가 존재하는가?
select count(*) from dba_segments where segment_name = 'T_INVISIBLE_IDX';
-----------------------------------------------------------------
1

Invisible Index는 Virtual Index와는 달리 실제로 존재하는 인덱스이고 따라서 통계 정보가 수집된다. 따라서 보다 정확한 테스트를 수행할  수 있다. 테스트후 반영여부가 결정되면 다음과 같이 손쉽게 Visible Index로 전환할 수 있다.

alter index t_invisible_idx visible;

위의 간단한 테스트 결과를 보면 Invisible Index가 기존의 Virtual Index에 비해서 훨씬 유용한 기능을 제공하는지 잘 알 수 있다. 아무쪼록 많이 활용되었으면 한다.

(참고) Invisible Index가 존재하는 테이블/인덱스에 대해 통계 정보를 수집하려고 하면 ORA-904: Invalid Column 에러가 발생한다. 이것은 11g R1의 버그이다. 패치가 나오기 전까지는 optimizer_use_invisible_indexes 값을 true로 변경한 후 통계 정보를 수집해야 한다.
신고
Trackback 0 : Comment 0

Write a comment


Bind Peeking의 딜레마와 Oracle 11g의 Adaptive Cursor Sharing

오라클 2008.01.07 11:11
Oracle 9i에서 Bind Peeking 기능이 소개된 것은 익히 알려진 사실이다.
Bind Peeking이란 Bind 변수를 사용하는 경우, Query가 최초로 실행되는 시점의 실제 Bind 값을 이용해서(Peeking해서) 실행 계획을 세우는 것을 의미한다.

Bind 변수를 사용하는 경우 실제로 실행되는 시점에 어떤 값이 들어오는지 알 수 없기 때문에 컬럼의 평균적인 분포만을 가지고 비용을 계산할 수 밖에 없다. 대부분의 경우에는 큰 문제가 안되지만, 다음과 같은 경우에는 치명적인 단점을 가지고 있다.

가령 status 컬럼의 분포가 다음과 같다고 하면...
 - status = 1  : 99%
 - status = 99 : 1%

이 경우
- Where status = '1' 이면 인덱스를 경유하지 않고 Full Table Scan을 하는 것이 유리하다
- Where status = '99' 이면 인덱스를 경유하는 것이 유리하다.

하지만,
- Where status = :b1 과 같이 Bind 변수를 사용하면 어떤 값이 올지 모르기 때문에 평균적인 분포를 따르게 된다. 따라서 이 경우 반드시 Full Table Scan을 선택한다.

Bind Peeking은 이러한 단점을 피하기 위해 쿼리가 실행되는 최초 시점에 Bind 변수에 들어온 값을 이용하게 된다. 즉, 쿼리가 최초로 실행되는 시점에

exec :b1 := '1';
... Where status = :b1

과 같이 실행되면 Full Table Scan을,

exec :b2 := '99';
... Where status = :b1

과 같이 실행되면 Index Range scan을 선택하게 된다.
단, 위와 같이 작동하려면 컬럼 값이 "1" 인 경우와 "99"인 경우의 분포도의 차이를 Oracle이 명확하게 이해하고 있어야 되므로 Histogram이 반드시 존재해야 한다. 가령

exec dbms_stats.gather_table_stats(user, 'TABLE_NAME', method_opt=>'for columns size 2 status');

와 같이 컬럼에 대해 적당한 크기의 Histogram이 생성되어 있어야 된다.

위의 설명을 이해했다면 Bind Peeking에는 기본적인 딜레마가 있음을 알 수 있다. 위의 예를 보면 쿼리가 최초로 실행되는 시점에 "1"의 값이 오느냐, "99"의 값이 오느냐에 따라 실행 계획이 결정된다. 어떤 쪽이든 한 쪽은 손해를 볼 수 밖에 없는 결정적인 구조적 한계를 가지고 있다.

이런 구조적 한계를 가진 기능을 제공한다는 것은 기본적인 설계의 결함이라고 할 수 있다. 덕분에 많은 시스템에서 Bind Peeking 기능을 비활성화시켜 버리고 있다. _optim_peek_user_binds 파라미터 값을 false로 지정해 버리는 것이다.

이런 경향은 10g에서 특히 심한데, 그 이유는 10g에서 dbms_stats의 method_opt 파라미터의 기본값이 for all columns size auto로 바뀌는 바람에 Histogram의 생성 여부를 Oracle이 자동으로 판단해버리는데 있다. 이전 버전에서는 for all columns size 1 이 기본값으로 Histogram이 생성되지 않았다.
Histogram이 없으면 Bind Peeking에 의한 부작용은 거의 없다. 10g에서 Histogram의 생성 여부를 Oracle이 판단함으로써 이전 버전에서는 존재하지 않던 Histogram이 갑자기 생성되고 이로 인해 Bind Peeking의 결함이 더욱 두드러지게 나타나게 되어 버린 것이다.
이 때문에 Oracle 10g에서는 _optim_peek_user_binds 파라미터를 False로 바꾸는 것이 기본 권고안처럼 되어 버릴 정도가 되었다.

하지만, Oracle 11g에서 아주 큰 희소식이 전달되었다. 이른바 Adaptive Cursor Sharing이라는 기능이 소개된 것인데, 이 기능으로 인해 Bind Peeking과 Histogram의 조합에 의한 부작용이 말끔히 해소될 수 있다.

Adaptive Cursor Sharing이란 말 그대로 상황에 맞게 유연하게 Cursor를 Share하겠다는 의미이다. 이 개념을 구현하기 위해 Oracle은 Bind Sensitive Cursor, Bind Aware Cursor라는 새로운 개념을 도입했다.
Bind Sensitive Cursor란, 말 그대로 Bind 값에 민감한 Cursor라는 의미이다. 즉, Bind 값이 바뀌면 그것을 민감하게 처리하겠다는 의미이다. 1) Equal 조건에서는 조건절에 쓰인 컬럼에 Histogram이 있고 2) Range 조건인 경우 Oracle은 이것을 Bind Senstive Cursor라고 부른다.
Bind Aware Cursor란, Bind Sensitive Cursor에 입력된 Bind 값에 따라 실행 계획이 분화된 Cursor를 의미한다. 즉, Bind Aware Cursor가 생성되었다는 것은 Bind 변수의 값에 따라 Oracle이 적절한 Child Cursor를 생성했다는 것을 의미한다.

Adaptive Cursor Sharing 기법을 사용하면 Bind Peeking에 의해 최초 실행 시점에 실행 계획이 결정되더라도 이후 새로운 Bind 변수 값이 사용되면 이에 맞게 실행 계획을 분화(새로운 Child Cursor 생성)시킨다. 따라서 Bind Peeking에 의한 부작용이 사실상 없어지게 된다.
단, 조건절에 쓰인 컬럼에 Histogram이 있고, Histogram의 분포도에 따라 실행 계획에 큰 차이가 있을 수 있다고 판단된다는 조건이 중요하다. 즉, 적절한 Histogram 없이는 의미가 없다는 것이다. Histogram은 이제 우리가 반드시 이해하고 활용해야 하는 존재가 되었다.
(dbms_stats의 method_opt 파라미터의 기본값이 for all columns size auto로 바뀌는 순간 이미 Histogram은 피할 수 없는 존재가 되어버렸다는 사실을 기억할 필요가 있겠다)

아래 샘플 코드를 이용하면 Oracle 11g의 Adaptive Cursor Sharing 기법을 이해할 수 있다.

-------------------------------------
-- Oracle 11g Bind Aware Cursor
-- Author: 조동욱
--------------------------------------

-- create objects
drop table acs_table;

create table acs_table(id int, name char(10));

create index acs_table_idx on acs_table(id);

insert into acs_table select 1, 'name' from all_objects where rownum <= 100000

insert into acs_table values(99, 'name');

commit;

-- gather statistics with histogram
exec dbms_stats.gather_table_stats(user, 'ACS_TABLE', estimate_percent=>100, method_opt=>'FOR COLUMNS SIZE 2 ID', cascade=>true);

-- check histogram
select * from dba_tab_histograms where table_name = 'ACS_TABLE';

-- Bind query
alter system flush shared_pool

var id number;

-- id == 1
-- 각 단계마다 아래 쿼리 결과 확인
select sql_id, sql_text,is_bind_sensitive,is_bind_aware
from v$sql where sql_text like 'select count(name) from acs_table%';

exec :id := 1;

select count(name) from acs_table where id = :id;

-- id == 99
exec :id := 99;

select count(name) from acs_table where id = :id;

select count(name) from acs_table where id = :id;

-- id == 1 again
exec :id := 1;

select count(name) from acs_table where id = :id;

-- check mismatch
select * from v$sql_shared_cursor where sql_id = '<sql_id>';

Oracle 11g의 Adaptive Cursor Sharing은 Oracle이 Bind 변수와 Histogram의 기능 개선에 얼마나 노력을 기울이고 있는지를 잘 보여주는 단적인 예이다. 아마 기대컨데, 더 이상 Bind Peeking의 부작용에 대해 고민하지 않아도 되기를 기대해본다.



신고
Trackback 1 : Comments 15
  1. 쌀맛나는세상 2008.01.10 14:25 신고 Modify/Delete Reply

    엑셈의 조동욱씨 블로그인가 보군요. 책은 잘보고 있습니다. 어쩌다 알게된 OWI 때문에 갈등이 많은 DB 엔지니어에요.( 사실 DB, 개발자 겸직 하고 있습니다. ㅜ.ㅜ)
    건강하시고 좋은 자료 공유 부탁 드립니다.

  2. 욱짜 2008.01.10 21:06 신고 Modify/Delete Reply

    고맙습니다. 좋은 정보를 공유할 수 있도록 항상 노력하겠습니다.

  3. 이명진 2008.08.25 09:22 신고 Modify/Delete Reply

    항상 네이버로 오라클의 모르는 부분을 검색하면 조차장님 블로그로 연결이 되네요^^ 좋은 정보 감사합니다^^

  4. 욱짜 2008.08.25 10:36 신고 Modify/Delete Reply

    실력좋은 엔지니어들이 블로그같은 온라인 미디어를 통해 더욱 많은 컨텐츠들을 공유할 수 있기를 기대합니다. 이명진씨도 조만간 블로그를... ^^

  5. KT 2008.11.28 16:14 신고 Modify/Delete Reply

    제가 알고 있는 bind peeking의 문제점은... 파티션 테이블에서 나타난다. 한달에 한번 유지보수를 위해서 시스템을 내리고, 새로운 파티션을 생성하고... 다시 restart...
    이때 제일먼저 들어오는 Query가 데이터가 없는 새로운 파티션 또는 maxval 파티션을 읽는다. 이제 Plan은 파티션을 full scan하도록 수립된다. 그리고는 그다음부터 다른 파티션(수GB이상)에 대한 조회도 full scan으로 발생한다. 요게, 10g에서 버그로 인해... 바인드 피킹이 완전히 막히지 않으면서 바인드 피킹을 죽여놔도... 동작된다는데 문제가 있다.
    이현상에 대해서 검증해 보지는 않았다. 그저 나타난 현상에 대해서 추정을 그렇게 해본 것으로, 실제 아닐 수도 있다는 ^^;

  6. 욱짜 2008.11.28 17:16 신고 Modify/Delete Reply

    위의 이유와 다른 이유들 때문에 Partition Key에 대한 Predicate는 Bind 변수를 사용하지 않는 것이 나은 경우가 많습니다.

    이래 저래 Bind Peeking은 문제가 많네요.

  7. Ejql 2009.03.09 14:50 신고 Modify/Delete Reply

    또.. OOO을 읽던 중.. 처음보는 bind peeking에서해서 찾아봤더니. 조동욱님사이트에 또 오게 되었습니다.
    정말 공부를 해도해도 끝도 없고, 잊어버리기만 하고, 정말 DB 어렵네요. ㅎㅎ
    그래도 쌓이는것이 있다고 믿기에, 계속 읽고, 또 공부하고 그럽니다. 언젠가는 그냥 머리속에서 나올날일 기다리면서 ㅎㅎ
    이제, RAC 책만 사면 엑셈에서 나온 책은 다산듯하네요. 또 다시 한번씩 보려면 시간이 꽤 걸리겠네요.
    그동안 다른 책나오지 않길 빕니다. ㅎㅎ

    많은 공부되고 있습니다. 감사합니다.

  8. Ejql 2009.03.09 14:52 신고 Modify/Delete Reply

    bind peeking가 일어날 경우. 한 가지 의문이 생겼습니다.
    bind peeking시에 2개로 test를 한다면 하드파싱이 2번로 나오는것인가요?

  9. 욱짜 2009.03.09 16:34 신고 Modify/Delete Reply

    2개로 테스트한다는 것의 정확한 의미가 무엇인가요?

    가령 Bind 변수가 2개, 혹은 두번 실행?

  10. hank 2010.10.13 13:53 신고 Modify/Delete Reply

    다른 bind 값으로 같은 쿼리를 수행했을 때 바로 다른 실행계획이 세워지지 않고
    원래 있던 실행계획을 공유한 다음 다시 같은 쿼리를 수행했을 때 다른 실행계획이 세워지는 이유가 뭘까요?
    위에 예제에서도 두번째 쿼리는 두번 수해 하셨는데요.
    아래 시나리오 입니다.



    CREATE TABLE EXACT4 AS SELECT LEVEL AS ID, 'A' AS NAME FROM DUAL CONNECT BY LEVEL <= 1000;

    UPDATE EXACT4 SET NAME ='B' WHERE ID <=10;

    CREATE UNIQUE INDEX IDX_EXACT4_ID ON EXACT4 (ID);

    CREATE INDEX IDX_EXACT4_NAME ON EXACT4 (NAME);

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYS', TABNAME =>'EXACT4', METHOD_OPT=>'FOR ALL COLUMNS SIZE 2', CASCADE=>TRUE);

    VARIABLE X VARCHAR2(1);

    EXEC :X := 'A';

    SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM EXACT4 WHERE NAME = :X;

    EXEC :X := 'B';

    SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM EXACT4 WHERE NAME = :X;

    SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM EXACT4 WHERE NAME = :X;


    결과

    --'A'
    SQL_ID 8gugyuqs2tpfx, child number 0 Plan hash value: 2764798145

    TABLE ACCESS FULL SCAN

    SQL_ID VERSION_COUNT LOADS PARSE_CALLS I I
    ------------- ------------- ---------- ----------- - -
    8gugyuqs2tpfx 1 1 1 Y N

    --'B' 첫번째
    SQL_ID 8gugyuqs2tpfx, child number 0 Plan hash value: 2764798145

    TABLE ACCESS FULL SCAN

    SQL_ID VERSION_COUNT LOADS PARSE_CALLS I I
    ------------- ------------- ---------- ----------- - -
    8gugyuqs2tpfx 1 1 2 Y N

    --'B' 두번째
    SQL_ID 8gugyuqs2tpfx, child number 1 Plan hash value: 1130814501

    SQL_ID VERSION_COUNT LOADS PARSE_CALLS I I
    ------------- ------------- ---------- ----------- - -
    8gugyuqs2tpfx 2 2 3 Y Y

    • 욱짜 2010.10.14 13:42 신고 Modify/Delete

      "A"로 첫번째 수행시: 히스토그램 때문에 해당 Cursor가 Bind Sensitive하다는 것을 발견합니다.

      "B"로 두번째 수행시, 일단은 기존의 실행 계획을 따릅니다. 하지만 *실행하고보니* Bind Sensitive한 Cursor라는 사실에 기반해서 "B" 값을 처리하는데는 기존 실행 계획으로는 부적합하다는 판단을 내립니다.

      "B"로 세번째 실행시, 이전 단계에서 기존의 실행 계획이 부적합하다는 판단을 했기 때문에 새로운 Child Cursor를 만들고 실행 계획을 다시 만들어봅니다.

      이런 이유때문에 2번째 실행해야 비로소 새로운 Child Cursor가 생기는 것으로 이해하시면 되겠습니다.

  11. hank 2010.10.14 16:38 신고 Modify/Delete Reply

    답변 감사합니다. 오라클 문서에 보니 "After a few executions" 라는 구문이 눈에 띄네요. 어쨋든 몇 번의 FULL SCAN은 감수해야 한다는 것 같습니다.

  12. jhKim 2011.04.05 10:48 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  13. jhKim 2011.04.06 15:01 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  14. barato timberland 2012.12.25 14:37 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


(Oracle 10g) dbms_stats.gather_schema_stats 수행에 시간이 너무 많이 걸려요...

오라클 2008.01.02 22:41
dbms_stats.gather_schema_stats 프로시저를 이용해서 통계 정보를 수집하는 경우 수행 시간이 너무 많이 걸린다는 불만이 종종 있다. 이런 현상은 대부분 다음과 같은 경우에 발생한다.

- 특정 테이블들의 크기가 매우 크다.
- 일부 큰 테이블들은 Partitioning이 되어 있다.

즉 큰 테이블들이 있고 더구나 Partitioning까지 되어 있으니(Global/Partiton Statistics모두 수집해야 하므로) 그만큼 통계 정보 수집에 시간이 걸리기 마련이다. 하지만 Oracle 10g에서는 estimate_percent 파라미터와 method_opt 파라미터의 값이 Oracle에 의해 적절하게 판단되어 통계 정보 생성 시간을 최적화한다. 그렇다면 왜 필요 이상 많은 시간이 걸리는 경우가 발생하는가?

이것은 이 두 파라미터에 다음과 같은 결함이 있기 때문이다.

- estimate_percent 파라미터의 값이 AUTO이다. 즉, Oracle이 테이블의 크기에 따라 적절한 샘플 크기를 선택한다는 의미이다. 대부분의 경우 Oracle은 테이블의 크기에 따라 적절한 값을 찾지만, 간혹 아주 큰 테이블에 대해 100%의 값을 사용하는 경우가 있다.
- method_opt 파라미터의 기본값이 FOR ALL COLUMNS SIZE AUTO이다. 즉 모든 컬럼에 대해 통계정보를 수집하면 히스토그램의 생성 여부는 Oracle이 판단한다. 대부분의 경우 Oracle은 적절한 히스토그램 버킷 크기를 할당하지만, 간혹 불필요하게(가령 Unique Key에 대해) 히스토그램을 생성하는 경우가 있다.

위와 같은 상황이 발생하면 예상보다 지나치게 많은 시간이 걸리게 되는 셈이다.

이런 상황을 해소할 수 있는 방법이 있을까? 가령 다음과 같이 하면...?

dbms_stats.gather_schema_stats(estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

물론 위의 방법으로 수행 시간 문제는 해결하겠지만, 좋은 방법은 될 수 없다. 만일 크기가 작은 테이블들이 있다면 샘플 크기가 작으면 왜곡된 통계 정보가 수집될 수 있다. 만일 특정 테이블에 대해서는 모든 컬럼(ALL COLUMNS)에 대해 통계 정보를 수집해야 한다면? 또는 특정 테이블의 특정 컬럼에 대해서는 히스토그램 크기를 주고 싶다면?

그렇다고 테이블 레벨로 일일이 dbms_stats.gather_table_stats를 호출하는 것은 너무나 지겨운 일이다.

그래서 보통 다음과 같은 트릭을 종종 사용한다.(편의상 정확한 문법은 생략)

-- 특정 테이블 통계 정보 변경을 막음
dbms_stats.lock_table_stats(user, 'TABLE1');

-- 스키마 레벨로 통계 정보 수집
dbms_stats.gather_schema_stats(user);

-- 통계 정보 활성화 후 이 테이블만 다시 통계 정보 수집
dbms_stats.unlock_table_stats(user, 'TABLE1');
dbms_stats.gather_table_stats(user,'TABLE', estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

위와 같이 하면 Schema 레벨로 대부분의 테이블에 대해 통계 정보를 수집한 후 특정 문제가 되는 테이블에 대해서만 별도로 통계 정보를 수집할 수 있다.

지저분한 트릭같지만 매우 유용한 방법이다.

Oracle 11g에서는 "Preference"라는 새로운 개념을 통해 위와 같은 불편을 일시에 해소하고 있다. Oracle 11g에서라면 다음과 같이 훨씬 직관적이고 깔끔한 방법을 쓸 수 있다.

-- 아래와 같이 한번만 설정
dbms_stats.set_table_prefs(user,'TABLE1', 'ESTIMATE_PERCENT', '10');
dbms_stats.set_table_prefs(user,'TABLE1','METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- Schema 레벨에서 통계 정보 수집
dbms_stats.gather_schema_stats(user);

즉, 특정 테이블별로 Preference(선호도)를 저장할 수 있어서 10g에서와 같은 부자연스러운 트릭은 불필요한 것이다. 11g에서 가장 마음에 드는 New Feature중 하나이다.
신고
Trackback 0 : Comment 1
  1. 김민기 2011.10.22 17:50 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바