통계 정보를 수집하지 않으면 실행 계획이 절대 변하지 않을까요

개요

저희 회사의 컨설턴트가 다음과 같은 사례를 공유하였습니다. 바로 한 사이트에서 갑자기 실행 계획이 변경되어 낭패를 본 경우였습니다. 기존에는
인덱스를 타던 실행 계획이 어느 날 갑자기 Full scan을 하는 실행 계획으로 변경된 것입니다. 물론 어떠한 옵티마이저 관련 파라미터의 변경이나 오브젝트 변경은 없었습니다.
오브젝트 통계 정보는 갑자기 실행 계획이 변경되는 상황을 우려하여 수집하지 않은 상태였습니다(물론
시스템 통계도 수집하지 않음).

실제로 DBA들과
대화를 나눠보면 대부분 실행 계획이 갑자기 변경되는 바람에 낭패를 본 경험을 한 두 번 가지고 있더군요. 또한
경험이 많은 튜너들조차 통계 정보를 수집하지 않으면 실행 계획이 고정된다고 알고 있는 경우가 많았습니다. 정말로
그럴까요

조선시대에 한반도의 인구는 최대 7백만명 정도였다고 합니다. 당시에도 세금이나 병역 등의 이유로 인구
통계를 수집했을 겁니다. 그런데 어느 날 갑자기 전쟁이 일어나서 수 많은 사람이 죽거나 다쳤다고 가정해
보겠습니다. 이 경우 전쟁이 발발하기 전의 인구 통계를 수정하지 않고 그대로 사용하면 상당한 혼란이
있을 겁니다. 우리가 사용하는 DBMS도 마찬가지 입니다. 데이터에 뭔가 커다란 변경(대량 갱신, 삭제, 추가 등)이 일어나거나
인덱스가 추가 되는 등 큰 변화가 일어날 때마다 DBA는 잊지 말고 통계 정보를 재 수집 해줘야 합니다.

또 다른 예를 들어보겠습니다. 위와 같이 인구 통계는 전쟁 같은 역사적 사건에 따라서도 변하지만, 시간에
따라서도 변화하는 속성을 갖고 있습니다. 조선 시대에 살던 사람이 현대로 시간 이동을 했다고 가정해
보겠습니다. 누군가 이 사람에게 ‘한반도의 인구가 몇 명인가’ 라고 물어본다면, 7백만명이라고 답변할 것입니다. 만약 현대의 정치인 중 누군가 이 사람의 답변대로 7백만명이라는
통계에 근거해서 정책을 수립한다면 커다란 혼란이 벌어질 겁니다. 결론적으로 통계는 시간에 따라 계속
변화하는 속성을 가지고 있습니다. 우리가 사용하는 DBMS도
이와 마찬가지로 항상 데이터가 갱신되고 삭제되고 추가 됩니다. 따라서 통계 정보를 수집하지 않고 사용할
경우 실제 데이터와 기존에 수집된 통계 정보의 불일치가 심각해지는 상황이 반드시 옵니다.

이 문제를 다른 컨설턴트들과도 이야기를 나눈 적이 있는데, 어차피 초기 서비스 운영을 거치고 안정화 단계에 접어들면 데이터의 분포도나 양이 갑자기 변하는 경우는 없으니
통계 수집을 중단하는 것이 실행 계획 안정성에 좋지 않겠는가라는 의견이 많았습니다.

그러나 다음 사례에서 살펴보겠지만 데이터가 계속 추가, 변경, 삭제되는 한 통계도 지속적으로 수집해주는 것이 좋습니다.

테스트

그러면 통계 정보에 어떠한 변경이 없음에도 불구하고 실행
계획이 갑자기 변경된 사례를 살펴보겠습니다.

먼저 다음과 같은 테스트용 테이블을 생성하겠습니다.

create table mytest
(id number, my_date date, my_name varchar2(30));

위의 테스트 테이블에 다음과 같이 지난 3년치의 데이터를 넣겠습니다. 데이터는 하루에 1000건씩 쌓인다고 가정하였으므로 3년 x 1000건 = 1,095,000건입니다. 이는 약 1백만건에 해당합니다.

INSERT INTO mytest

SELECT ROWNUM no, my_date, my_name

FROM (
SELECT LEVEL,

SYSDATE - (365 * 3) +
LEVEL AS my_date,

'JHKIM' AS my_name

FROM DUAL

CONNECT BY LEVEL <= (365 * 3))
cal,

(
SELECT LEVEL no

FROM DUAL

CONNECT BY LEVEL <= 1000)

다음과 같이 WHERE 조건에서
사용할 칼럼에 대해 인덱스를 생성해줍니다.

create index
mytest_id on mytest(my_date)

마지막으로 테스트 테이블에 대해 통계 정보를 수집합니다. 논의를 단순화하기 위해 ‘size 1’을 사용하여 히스토그램은 수집하지
않습니다.

BEGIN

DBMS_STATS.gather_table_stats (USER,
'MYTEST', method_opt => 'for all
columns size 1’);

END;

이제 수집된 통계 정보를 살펴보겠습니다.

SELECT
t.TABLE_NAME,

t.LAST_ANALYZED,

COLUMN_NAME,

NUM_DISTINCT,

LOW_VALUE,

HIGH_VALUE,

NUM_BUCKETS,

HISTOGRAM

FROM user_tables t, user_tab_col_statistics c

WHERE t.table_name = c.table_name AND
t.table_name = 'MYTEST'

TABLE_NAME	NUM_ROWS	LAST_ANALYZED	COLUMN_NAME	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	HISTOGRAM
MYTEST	1095000	06-2월-2015 13:16:38	ID	1091456	C102	C4020A33	NONE
MYTEST	1095000	06-2월-2015 13:16:38	MY_DATE	1095	787002080E0E0D	787302060E0E0D	NONE
MYTEST	1095000	06-2월-2015 13:16:38	MY_NAME	1	4A484B494D	4A484B494D	NONE

위에서 수집된 통계 정보를 살펴보면 HISTOGRAM=NONE으로 수집되지 않았음을 알 수 있습니다.

총 레코드는 NUM_ROWS에서
알 수 있으며 1095000건으로 실제 데이터의 총 건수와 일치합니다.

NDV(Number of
Distinct Value)는 NUM_DISTINCT
칼럼을 보면 되며 MY_DATE 칼럼의 경우 1095(365x3)입니다. 이는 하루에 1000건씩이므로 실제 데이터와 일치함을 알 수 있습니다.

LOW_VALUE와 HIGH_VALUE는 히스토그램이 없을 경우 범위 조건의
선택도를 계산하는데 사용됩니다. 이 값은 오라클 엔진이 내부적으로 사용하는 이진형식이므로 별도로 다음과
같은 과정을 거쳐야 해독할 수 있습니다.

이를 해독하기 위해서는 utl_raw 패키지에서 제공하는 cast_to_number, cat_to_varchar2 등의
함수를 사용해도 되고, dbms_stats 패키지에서 제공하는
convert_raw_value 프로시저를 사용해도 됩니다.

여기에서는 dbms_stats
패키지에서 제공하는 프로시저를 사용하기 위해 다음과 같은 함수를 생성합니다.

create or replace
function display_raw (rawval raw, type varchar2)

return varchar2

is

cn
number;

cv
varchar2(32);

cd
date;

cnv
nvarchar2(32);

cr
rowid;

cc
char(32);

cbf
binary_float;

cbd binary_double;

begin

if (type = 'VARCHAR2') then

dbms_stats.convert_raw_value(rawval,
cv);

return to_char(cv);

elsif (type = 'DATE') then

dbms_stats.convert_raw_value(rawval,
cd);

return to_char(cd,'YYYY/MM/DD');

elsif (type = 'NUMBER') then

dbms_stats.convert_raw_value(rawval,
cn);

return to_char(cn);

elsif (type = 'BINARY_FLOAT') then

dbms_stats.convert_raw_value(rawval,
cbf);

return to_char(cbf);

elsif (type = 'BINARY_DOUBLE') then

dbms_stats.convert_raw_value(rawval,
cbd);

return to_char(cbd);

elsif (type = 'NVARCHAR2') then

dbms_stats.convert_raw_value(rawval,
cnv);

return to_char(cnv);

elsif (type = 'ROWID') then

dbms_stats.convert_raw_value(rawval,
cr);

return to_char(cr);

elsif (type = 'CHAR') then

dbms_stats.convert_raw_value(rawval,
cc);

return to_char(cc);

else

return 'UNKNOWN DATATYPE';

end if;

end;

/

다음과 같이 위의 함수를 사용하여 LOW_VALUE, HIGH_VALUE를 해독하여 조회해보겠습니다.

SELECT t.TABLE_NAME,

NUM_ROWS,

t.LAST_ANALYZED,

c.COLUMN_NAME,

c.NUM_DISTINCT,

display_raw(c.LOW_VALUE,
data_type) as low_value,

display_raw(c.HIGH_VALUE, data_type) as high_value,

c.NUM_BUCKETS,

c.HISTOGRAM

FROM user_tables t, user_tab_col_statistics
c, user_tab_cols d

WHERE t.table_name = c.table_name AND
c.table_name = d.table_name and c.column_name = d.column_name and t.table_name = 'MYTEST'

COLUMN_NAME

NUM_DISTINCT