태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

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

티스토리 툴바