태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Histogram'에 해당되는 글 3건

  1. 2008.12.17 불필요한 Histogram만 삭제하기 (1)
  2. 2008.01.22 Oracle 11g - Extended Statistics와 Histogram의 화려한 부활 (3)
  3. 2008.01.07 Bind Peeking의 딜레마와 Oracle 11g의 Adaptive Cursor Sharing (15)

불필요한 Histogram만 삭제하기

오라클 2008.12.17 11:40
간혹 원하지 않는 Histogram이 생성되어서 골치아픈 경우가 있다.

이런 경우 자연스럽게 다음과 같은 요구 사항이 생긴다.


다른 통계 정보는 그대로 두고 Histogram만 삭제할 수는 없을까?


Histogram을 없애기 위해 Table 통계 정보를 새로 수집하는 것은 대단히 위험한 선택이 될 수 있다.

DBMS_STATS Package를 잘 사용하면 위험을 최소화하면서 원하는 작업을 수행할 수 있다.

간단한 예제를 통해 방법을 알아 보자.

우선 Table을 만든다.

drop table t1 purge;

create table t1(c1 int, c2 int);

insert into t1
select level, level
from dual
connect by level <= 10000
;


Column c2에 불필요한 Histogram을 강제로 생성한다.

exec dbms_stats.gather_table_stats(user, 't1', -
        method_opt=>'for columns c1 size 1 c2 size 5');

      
통계 정보를 조회해 보면 Height-Balanced Histogram이 생성된 것을 알 수 있다.

@tab_stat t1

-----------------                                
TABLE_NAME                    : T1               
COLUMN_NAME                   : C2               
NUM_DISTINCT                  : 10000            
NUM_NULLS                     : 0                
DENSITY                       : .0001            
LOW_VALUE                     : C102             
HIGH_VALUE                    : C302             
HISTOGRAM                     : HEIGHT BALANCED  
-----------------                                

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE      
-------------------- -------------------- --------------- --------------------
T1                   C1                                 0 1()                 
T1                   C1                                 1 10000()             
T1                   C2                                 0 1()                 
T1                   C2                                 1 2000()              
T1                   C2                                 2 4000()              
T1                   C2                                 3 6000()              
T1                   C2                                 4 8000()              
T1                   C2                                 5 10000()             

Histogram만 없앨 수 없을까?

11g 이저 버전에서는 다음과 같이 DBMS_STATS.SET_COLUMN_STATS를 이용하면 된다.
우선 현재 Column 통계 정보를 저장한 후

col num_distinct new_value v_num_distinct
col density new_value v_density
col num_nulls new_value v_num_nulls
col avg_col_len new_value v_avg_col_len

select num_distinct, density, num_nulls, avg_col_len
from user_tab_col_statistics
where table_name = 'T1' and column_name = 'C2'
;
NUM_DISTINCT    DENSITY  NUM_NULLS AVG_COL_LEN  
------------ ---------- ---------- -----------  
       10000      .0001          0           4  


SET_COLUMN_STATS를 이용해 Histogram이 존재하지 않는 것처럼 저장하는 것이다.

exec dbms_stats.set_column_stats(user, 't1', 'c2', -
        distcnt => &v_num_distinct, -
        density => &v_density, -
        nullcnt => &v_num_nulls, -
        avgclen => &v_avg_col_len);

통계 정보를 조회해 보면 Histogram이 사라진 것을 알 수 있다.

@tab_stat t1       
 -----------------                     
TABLE_NAME                    : T1   
COLUMN_NAME                   : C2   
NUM_DISTINCT                  : 10000
NUM_NULLS                     : 0    
DENSITY                       : .0001
LOW_VALUE                     : C102 
HIGH_VALUE                    : C302 
HISTOGRAM                     : NONE 
-----------------                    

    
Oracle 11g부터는 DELETE_COLUMN_STATS 프로시저에 새롭게 추가된 col_stat_type 파라미터를 이용하면 매우 손쉽게 Histogram을 제거할 수 있다.

호출 방법은 다음과 같다.

exec dbms_stats.delete_column_stats(user, 't1', 'c2', col_stat_type=>'HISTOGRAM');

@tab_stat t1
-----------------                      
TABLE_NAME                    : T1     
COLUMN_NAME                   : C2     
NUM_DISTINCT                  : 10000  
NUM_NULLS                     : 0      
DENSITY                       : .0001  
LOW_VALUE                     : C102   
HIGH_VALUE                    : C302   
HISTOGRAM                     : NONE   
-----------------                      


역시 새로운 버전을 사용함으로써 누릴 수 있는 혜택 중 하나라고 하겠다.

PS)
Histogram이 불필요할 거 같은 Column에 Histogram이 생성되는 경우가 있다.

Oracle은 Density가 매우 낮은, 즉 거의 Unique한 Column이라도 Skewness가 존재한다고 판단되면 Histogram을 수집하기 때문이다.

METHOD_OPT 파라미터의 기본값이 'FOR ALL COLUMN SIZE AUTO'이기 때문에 이런 일이 발생한다.




신고
tags : Histogram
Trackbacks 3 : Comment 1
  1. bosoa 2012.05.22 17:32 Modify/Delete Reply

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

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


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

티스토리 툴바