문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣ7FS02PS
문서번호ㅣTADTI184
개요
본 장에서는 히스토그램 정밀 수집 방법에 대해 설명합니다.
방법
1. 히스토그램 정밀 수집
다음은 히스토그램을 정밀 수집하는 예입니다.
- 히스토그램 Bucket 개수를 10개 지정하여 수집합니다. (set_histogram.sql)
SQL> CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY', method_opt='FOR ALL COLUMNS SIZE 10', estimate_percent=100);
- 저장된 히스토그램을 확인합니다. (view_histogram.sql)
SQL> @view_histogram.sql
NAME BUCKET_CNT NULL_CNT DISTINCT_CNT LOW_VAL
HIGH_VAL DENSITY AVG_COL_SIZE
---- ---------- ---------- ------------ -------------------------------------------
------------------------------------------ --------- ------
ID 1 0 10000 1.0000000000000000000000000000000000000E+00
1.0000000000000000000000000000000000000E+04 .0001 2.9801
NAME 1 0 9996 AAAST ZZXVC
.00010004 5
JOB 1 0 5 ARCHER WIZARD
.2 7.67
SAL 1 0 99 1.0000000000000000000000000000000000000E+02
9.9000000000000000000000000000000000000E+03 .01010101 2
DEPT 1 0 5 DETAIL RETHINKING .2 7.25
NAME HIST_HEAD_ID BUCKET END_POINT END_POINT_ACTUAL
DISTINCT_CNT
---------- ------------ ---------- ---------- --------------------------------------------
------ ------------
ID 26 1 10000 1.0000000000000000000000000000000000000E+04
10000
NAME 27 1 2.5432E+16 ZZXVC
9996
JOB 28 1 2.4569E+16 WIZARD
5
SAL 29 1 9900 9.9000000000000000000000000000000000000E+03
99
DEPT 30 1 2.3157E+16 RETHINKING
5위 결과를 보면 GATHER_TABLE_STATS를 호출할 때 Bucket 개수를 지정하지 않았기 때문에 기본값 1이 적용되어 각 Column별 Bucket은 모두 1개입니다.
첫 번째 쿼리는 히스토그램의 헤더(_DD_HIST_HEAD) 결과로 Column내 전체의 최솟값, 최댓값, Distinct count 등의 정보를 나타냅니다.
두 번째 쿼리는 히스토그램 단위 Bucket(_DD_HISTOGRAM) 결과로 히스토그램 하나의 Bucket내의 최댓값, Distinct count 등의 정보를 가지고 있습니다. (최솟값은 헤더 및 앞 Bucket의 최댓값을 통해 알 수 있음)
결과를 분석해보면 ID Column값들은 고유하므로 Distinct count가 전체 Row 개수와 동일한 10,000로 나왔고, JOB이나 DEPT는 정해진 몇 개의 고유값이 있는 그대로 수치가 나온 것을 확인할 수 있습니다.
- 예측치와 실제 결과를 비교하기 위해 set autotrace traceonly exp plan stat을 사용합니다.
쿼리를 수행하여 Plan을 확인 합니다. (view_plan_detail_for_histogram.sql)
SQL> set lines 150
SQL> set autot traceonly exp plans
SQL> SELECT * FROM ARMY WHERE ID <= 100;
set autot off
SQL ID: 1434
Plan Hash Value: 2324995388
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Cost:7, %CPU:0, Rows:100)
2
INDEX (RANGE SCAN): ARMY_PK (Cost:2, %CPU:0, Rows:100)
Predicate Information
--------------------------------------------------------------------------------
2 - access: ("ARMY"."ID" <= 100) (0.010)
Execution Stat
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Time:.26 ms, Rows:100, CR:5, CU:0, Starts:1)
2 INDEX (RANGE SCAN): ARMY_PK (Time:.06 ms, Rows:100, CR:2, CU:0, Starts:1)Execution plan에서 보이는 Rows는 예측치이고, Execution Stat의 Rows는 실제 Plan 노드에서 결과로 올라가는 Row 개수입니다. ID 값은 균등하게 분포되어 있기 때문에 이와 같은 경우에서는 매우 정확하게 수치가 예측되었습니다.
- 통계 정보 오차와 실제 계획
다음은 DEPT Column에 대한 조건문이 있는 쿼리 결과입니다. (view_plan_detail_for_histogram.sql)
SQL> set lines 150
SQL> set autot traceonly exp plans
SQL> SELECT * FROM ARMY WHERE DEPT = 'PROMISE';
SQL ID: 1446
Plan Hash Value: 1631011846
Execution Plan
----------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): ARMY (Cost:211, %CPU:0, Rows:2000)
Predicate Information
----------------------------------------------------------------------------------
1 - filter: ("ARMY"."DEPT" = 'PROMISE') (0.200)
Execution Stat
-----------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): ARMY (Time:5.54 ms, Rows:2500, CR:481, CU:27, Starts:1)Optimizer에서 히스토그램이 없을때는 하나의 Bucket내에 존재하는 값들의 분포도는 균등하다고 가정하기 때문 에 위의 결과 PROMISE와 함께 RETHINKING 두 값 모두 동일한 Cardinality가 예측될 것입니다. 즉, 10,000개 중 고유 값이 5개이므로 10,000/5 = 2,000개로 예측한 것입니다.
하지만 실제로는 PROMISE는 2,500건, RETHINKING은 1,250건이 존재하므로 오차가 발생했다. 위의 예제에서는 통계 결과를 기반으로 한 Optimizer의 실행 계획에 큰 문제가 되지 않지만 다음의 예제에서는 성능 저하로 이어질 수 있습니다.
SQL> SELECT * FROM ARMY WHERE JOB = 'INFANTRY';
SQL ID: 1452
Plan Hash Value: 749881390
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Cost:197, %CPU:0, Rows:2000)
2
INDEX (RANGE SCAN): ARMY_IDX_JOB (Cost:6, %CPU:0, Rows:2000)
Predicate Information
--------------------------------------------------------------------------------
2 - access: ("ARMY"."JOB" = 'INFANTRY') (0.200)
SQL ID: 1452
Plan Hash Value: 749881390
Execution Stat
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Time:23.1 ms, Rows:8000, CR:472, CU:0, Starts:1)
2
INDEX (RANGE SCAN): ARMY_IDX_JOB (Time:.68 ms, Rows:8000, CR:26, CU:0, Starts:1)JOB Column의 히스토그램에 의하면 고유값은 5개이고 총 Row 개수는 10,000이므로 INFANTRY 조건을 만족하는 Row 개수를 2,000개로 예측한 것은 Optimizer가 맞게 예측했다는 것을 말합니다.
하지만 실제로는 8,000개가 존재했고 2,000개라고 예측하여 INDEX RANGE SCAN + ROWID SCAN을 태웠는데 실 제로는 8,000번의 ROWID SCAN이 발생하였습니다.
이는 잘못된 통계 정보 때문에 실행 계획까지 부정적 영향을 미친 예로 이 때문에 발생한 문제가 실제 더 대용량의 상황이었다면 치명적인 성능 저하로 이어질 수 있습니다.
2. 히스토그램 대상 선별 방법
다음은 히스토그램 대상 선별 방법에 대한 예이다. 이때 CLAN Table은 ARMY Table과 스키마와 Index를 동일하게 생성한 후 수행합니다.
SQL> SELECT ARMY.ID, ARMY.NAME, CLAN.SAL, CLAN.DEPT FROM ARMY, CLAN
WHERE ARMY.JOB=CLAN.JOB
AND ARMY.JOB='INFANTRY';
stage count cpu elapsed current query disk rows
------ ------ ------ -------- -------- -------- -------- ----------
parse 1 0.00 0.00 0 0 0 0
exec 1 0.00 0.00 0 0 0 0
fetch 25744 15.09 18.44 0 996 0 64000000
------ ------ ------ -------- -------- -------- -------- ----------
sum 25746 15.09 18.44 0 996 0 64000000
rows excution plan
-------- --------------------------------------------------
64000000 hash join (et=18103446, cr=0, cu=0, co=408, cpu=3, ro=4000000)
8000 table access (rowid) ARMY(1769) (et=9511, cr=472, cu=0, co=197, cpu=0,
ro=2000)
8000 index (range scan) ARMY_IDX_JOB(1772) (et=677, cr=26, cu=0, co=6, cpu=0, ro=2000)
8000 table access (rowid) CLAN(1775) (et=6843, cr=472, cu=0, co=195, cpu=0, ro=2000)
8000 index (range scan) CLAN_IDX_JOB(1778) (et=283, cr=26, cu=0, co=6, cpu=0, ro=2000)위 예의 sql trace에서 rows는 실제 출력 Row 갯수이며, execution plan의 ro= 항목은 예측된 Cardinality입니다.
ARMY와 CLAN 두 Table에 대하여 index range scan 예측은 각각 2,000개이며, join 결과는 4,000,000개로 예측되었으나 실제로는 그보다 훨씬 큰 64,000,000개입니다.
만약 이런 경우가 발생하면 tbsql에서 autotrace를 사용하여 확인합니다.
SQL> set autot traceonly exp
SQL> SELECT ARMY.ID, ARMY.NAME, CLAN.SAL, CLAN.DEPT FROM ARMY, ARMY CLAN
WHERE ARMY.JOB=CLAN.JOB
AND ARMY.JOB='INFANTRY';
SQL> set autot off
SQL ID: 117
Plan Hash Value: 1730287211
Execution Plan
--------------------------------------------------------------------
1 HASH JOIN (Cost:408, %CPU:3, Rows:4000000)
2 TABLE ACCESS (ROWID): ARMY (Cost:197, %CPU:0, Rows:2000)
3 INDEX (RANGE SCAN): ARMY_IDX_JOB (Cost:6, %CPU:0, Rows:2000)
4 TABLE ACCESS (ROWID): CLAN (Cost:195, %CPU:0, Rows:2000)
5 INDEX (RANGE SCAN): CLAN_IDX_JOB (Cost:6, %CPU:0, Rows:2000)
Predicate Information
------------------------------------------------------------------
1 - access: ("ARMY"."JOB" = "CLAN"."JOB") (1.000)
3 - access: ("ARMY"."JOB" = 'INFANTRY') (0.200)
5 - access: ("ARMY"."JOB" = 'INFANTRY') (0.200) 위와 같이 Predicate Information 항목을 통해 조건문별 Selectivity가 어떻게 결정되었는지 확인할 수 있습니다.
다음은 조건문을 만족하는 실제 Row가 몇 개인지 확인해보는 예이다. JOB Column의 통계 정보가 매우 부정확함을 알 수 있습니다.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='ARMY';
NUM_ROWS
----------
10000
1 row selected.
SQL> SELECT COUNT(*) FROM ARMY WHERE JOB='INFANTRY';
COUNT(*)
----------
8000
1 row selected.위 예의 결과는 히스토그램 Bucket 개수 부족으로 인한 결과일 가능성이 높으므로 히스토그램 Bucket 개수를 늘려서 다시 수집합니다.
Bucket 개수 1개와 2개 이상 사이에서는 통계 수집 성능이 차이가 크지만 2개 이상일 경우에는 어떠한 값이든 간에 큰 차이가 없습니다. 따라서 정확한 Bucket 개수를 정할 수 없을 경우에는 최댓값(256)을 넣도록 합니다.
FOR ALL COLUMNS SIZE 256 옵션을 주면 Table 모든 Column에 대해서 256개 Bucket으로 수집을 합니다.
SQL> CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',method_opt='FOR ALL COLUMNS SIZE 256');
만약 Table column 개수가 너무 많은 경우에는 FOR ALL INDEXED COLUMNS 옵션을 사용하도록 합니다. 이는 Index 걸린 Column들만 수집하는 것인데 대부분의 경우는 Index 걸린 Column들이 선택도를 좌우하므로 크게 문제가 되지 않습니다.