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

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의 부작용에 대해 고민하지 않아도 되기를 기대해본다.

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