태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

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

티스토리 툴바