(아래 출처의 글을 번역하고 테스트하여 정리한 것입니다.)
히스토그램
히스토그램은 테이블 열의 데이터 분포에 대한 더 자세한 정보를 제공하는 특수한 유형의 컬럼 통계입니다.
"버킷"이라고 부르는 공간에 정렬된 값들을 넣습니다.
NDV(Number of distinct values)와 데이터의 분포에 따라 데이터베이스는 히스토그램의 유형을 선택하게 됩니다.
(경우에 따라 히스토그램을 생성할때 내부적으로 미리 지정된 수의 행을 샘플링하기도 합니다.)
유형은 아래와 같습니다.
Frequency histograms and top frequency histograms [도수분포]
Height-Balanced histograms (legacy) [높이균형분포]
Hybrid histograms [도수분포 + 높이균형분포]
목적
기본적으로 옵티마이저는 열의 distinct value에 걸친 행이 균일하게 분포되어 있다고 가정합니다.
데이터 skew(열 내부의 데이터가 불균일하게 분포된 상태)가 포함된 열의 경우 히스토그램을 사용하면 옵티마이저에서 skew 열에 대해서 정확한 filter와 join predicates를 생성할 수 있습니다.
예를 들어,
캘리포니아 95%,오리건 4%,네바다 1%로 배송하는 서점이 있습니다.
책 주문 테이블에는 300,000건의 행이 있습니다.
테이블 컬럼중 한개는 주문이 배송되는 장소(캘리포니아,오리건,네바다)가 저장됩니다.
사용자는 오리건으로 배송되는 책의 수를 조회합니다.
이때 히스토그램이 없는 옵티마이저는 300,000 / 3(NDV는 3)의 균등 분포(even distribution)을 가정하고 100,000행에서 카디널리티로 추정합니다.
※카티널리티 - 실행계획에서 작업에 의해 반환될 것으로 예상되거나 반환되는 행의 수
이 추정치를 사용하여 옵티마이저는 FULL TABLE SCAN을 선택합니다. 히스토그램이 있는 옵티마이저는 책의 4%가 오리건으로 배송되는 것으로 계산하고 INDEX SCAN을 선택합니다.
언제 히스토그램을 만들까?
DBMS_STATS 패키지로 테이블에 대한 통계정보를 수집할 때, DBMS_STATS 패키지가 이 테이블의 열을 참조한 경우, 데이터베이스는 이전 쿼리 작업량에 따라 자동적으로 히스토그램을 자동으로 만듭니다.
기본적인 프로세스는 아래와 같습니다.
1) DBMS_STATS로 테이블 통계정보를 수집시 METHOD_OPT에 default로 SIZE AUTO로 수행됩니다.
2) 유저가 DBMS_STATS를 수행합니다.
3) 데이터베이스는 통계정보 수집중의 predicates를 기록하고 SYS.COL_USAGE$테이블에 정보를 업데이트합니다.
※predicates - 반환할 행의 하위집합을 지정하는데 사용하는 구문. SQL문의 WHERE 절에 지정됨.
4) DBMS_STATS를 다시 수행하면, DBMS_STATS가 SYS.COL_USAGE$를 조회하여 이전 DBMS_STAS의 작업량을 기준으로 히스토그램이 필요한 열을 결정할 수 있습니다.
AUTO 기능의 결과는 다음과 같습니다.
시간이 지남에 따라 DBMS_STATS는 수집되는 통계를 변경할 수 있습니다.
예를들어, 테이블의 데이터가 변경되지 않았더라도 조회 쿼리 및 DBMS_STATS 작업을 통해 이 테이블을 참조하는 쿼리에 대한 실행계획이 변경될 수 있습니다.
테이블에 대한 통계를 수집하고 테이블을 조회하지 않으면 데이터베이스가 이 테이블의 열에 대한 히스토그램을 만들지 않습니다.
히스토그램을 자동으로 생성하려면 반드시 하나 또는 그이상의 쿼리를 실행하여 열 사용정보를 SYS.COL_USAGE$에 남겨야 합니다.
자동 통계정보 생성 시나리오
sh.sales와 동일한 행을 포함하는 테이블로 sales2를 만들고 CTAS로 데이터 insert까지 수행합니다.
이 때 sales2에 대한 통계가 자동으로 생성됩니다. 인덱스도 다음과 같이 생성합니다.
CREATE TABLE sales2 AS SELECT * FROM sh.sales;
CREATE INDEX sh_12c_idx1 ON sales2(prod_id);
CREATE INDEX sh_12c_idx2 ON sales2(cust_id,time_id);
컬럼의 통계정보를 조회해보면, 아직, 히스토그램이 없습니다. sales2가 조회되지 않았기 때문입니다.
SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'SALES2';
COLUMN_NAME NOTES HISTOGRAM
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------
PROD_ID STATS_ON_LOAD NONE
CUST_ID STATS_ON_LOAD NONE
TIME_ID STATS_ON_LOAD NONE
CHANNEL_ID STATS_ON_LOAD NONE
PROMO_ID STATS_ON_LOAD NONE
QUANTITY_SOLD STATS_ON_LOAD NONE
AMOUNT_SOLD STATS_ON_LOAD NONE
prod_id = 42의 쿼리를 조회하고 나서 GATHER AUTO로 통계정보를 수집합니다.
SQL> SELECT COUNT(*) FROM sales2 WHERE prod_id = 42;
COUNT(*)
----------
12116
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'SALES2',OPTIONS=>'GATHER AUTO');
딕셔너티 뷰에서는 직전 쿼리중에 수집된 정보를 바탕으로 prod_id 열에 히스토그램을 생성하였습니다.
SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'SALES2';
COLUMN_NAME NOTES HISTOGRAM
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------
PROD_ID HISTOGRAM_ONLY FREQUENCY
CUST_ID STATS_ON_LOAD NONE
TIME_ID STATS_ON_LOAD NONE
CHANNEL_ID STATS_ON_LOAD NONE
PROMO_ID STATS_ON_LOAD NONE
QUANTITY_SOLD STATS_ON_LOAD NONE
AMOUNT_SOLD STATS_ON_LOAD NONE
7 rows selected.
오라클이 히스토그램 유형을 선택하는 방법.
Oracle Database는 빈도분석, Top N 빈도분석, 높이 균형 또는 하이브리드 등 여러 기준을 사용하여 히스토그램을 생성할 수 있습니다.
히스토그램 공식은 다음 변수를 사용합니다:
NDV(Number of Distinct values) - 이것은 한 열의 서로 다른 값의 수를 나타냅니다. 예를 들어, 한 열에 100, 200 및 300 값만 포함되어 있으면 이 열의 NDV는 3입니다.
n - 이 변수는 히스토그램 버킷의 수를 나타냅니다. 기본값은 254입니다.
p - 이 변수는 (1–(1/n)) * 100과 같은 내부 백분율 임계값을 나타냅니다. 예를 들어, n = 254이면 p는 99.6입니다.
DBMS_STAT 통계 수집 절차의 estimate_percent 파라미터가 AUTO_SAMPLE_SIZE(기본값)로 설정되어 있는지 여부도 추가 기준입니다.
다음 다이어그램은 히스토그램 생성을 위한 의사결정 트리를 보여줍니다.
히스토그램 사용 시 카티널리티 알고리즘
히스토그램의 경우 카디널리티에 대한 알고리즘은 Endpoint number 및 value, popular / nonpopular value 여부와 같은 요인에 따라 달라집니다.
Endpoint Numbers and Values
엔드포인트 번호는 버킷을 고유하게 식별하는 번호입니다.
빈도분석 및 하이브리드 히스토그램에서 엔드포인트 번호는 현재 및 이전 버킷에 포함된 모든 값의 누적 빈도입니다.
예를 들어, 끝점 번호가 100인 버킷은 현재 및 이전의 모든 버킷에 있는 값의 총 빈도수가 100임을 의미합니다.
높이 균형 히스토그램에서, 옵티마이저는 0 또는 1부터 순차적으로 버킷 번호를 매깁니다. 모든 경우에 엔드포인트 번호는 버킷 번호입니다.
엔드포인트 값은 버킷의 값 범위에서 가장 높은 값입니다. 예를 들어, 버킷에 값 52794와 52795만 포함되어 있으면 엔드포인트 값은 52795입니다.
Popular and Nonpopular Values
히스토그램에서 값의 인기도는 카디널리티 추정 알고리즘에 영향을 미칩니다.
Popular values
popular 값은 여러 버킷의 엔드포인트 값으로 발생합니다. 두개 이상버킷에서 엔드포인트에서 값이 동일한 값이 있다면 그것은 popular value입니다.
옵티마이저는 값이 버킷에 대한 엔드포인트 값인지 먼저 확인하여 popular 값인지 여부를 결정합니다.
그다음, 히스토그램에 따라 나뉩니다.
빈도분석 히스토그램에 대해 옵티마이저는 현재 버킷의 엔드포인트 번호에서 이전 버킷의 엔드포인트 번호를 뺍니다.
하이브리드 히스토그램은 이미 각 끝점에 대해 이 정보를 개별적으로 저장합니다. 이 값이 1보다 크면 값이 인기가 있습니다.
옵티마이저는 다음 공식을 사용하여 일반적인 값에 대한 카디널리티 추정치를 계산합니다
Popular value 의 추정치 = (테이블의 행의 수) * (이 값이 걸쳐져있는 endpoint value의 수 / endpoint의 총 수)
Nonpopular values
Non Popular value 의 추정치 = (테이블의 행의 수) * density
옵티마이저는 버킷 수 및 NDV와 같은 인자를 기반으로 내부 알고리즘을 사용하여 밀도(density)를 계산합니다.
밀도는 0과 1 사이의 십진수로 표시됩니다.
1에 가까운 값은 옵티마이저가 이 열을 참조하는 쿼리에 의해 많은 행이 반환될 것으로 예상함을 나타냅니다.
0에 가까운 값은 최적화기가 소수의 행이 반환될 것으로 예상함을 나타냅니다.
※ user_tab_col_statistics 의 density 컬럼을 확인합니다.
Bucket Compression
경우에 따라 전체 버킷수를 줄이기 위해 옵티마이저는 여러 버킷을 하나의 버킷으로 압축합니다.
예를들어 다름 빈도분석 히스토그램은 첫번째 버킷번호가 1이고 마지막 버킷번호가 23입니다.
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52795
10 52796
12 52797
14 52798
23 52799
위의 결과를 보면 여러개의 버킷이 누락되었습니다. 원래 버킷2부터 6까지는 각각 값 52793을 포함해서 있엇습니다.
옵티마이저가 이 모든 버킷을 가장 높은 엔드포인트 번호를 가진 버킷으로 압축했고, 현재 값 52793의 버킷 5개를 포함합니다.
이 값(52763)은 현재 버킷(6)과 직전 버킷(1)의 엔드포인트 번호의 차이가 5이기 때문에 popular value입니다.
따라서, 압축하기 전에 값 52793은 5개의 버킷에 대한 엔드포인트였습니다.
다음 표에는 압축된 버킷과 popular value가 나와 있습니다.
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792 -> nonpopular
6 52793 -> buckets 2-6 compressed into 6; popular
8 52794 -> buckets 7-8 compressed into 8; popular
9 52795 -> nonpopular
10 52796 -> nonpopular
12 52797 -> buckets 11-12 compressed into 12; popular
14 52798 -> buckets 13-14 compressed into 14; popular
23 52799 -> buckets 15-23 compressed into 23; popular
Frequency Histograms (빈도 분석)
빈도 히스토그램에서 각각 다른 열 값은 히스토그램의 단일 버킷에 대응합니다. (NDV <= n)
각각의 값에는 고유한 전용 버킷이 있기 때문에 버킷마다 값의 양이 일정하지 않은 특징이 있습니다.
(특정값은 많고, 특정값은 적기 때문)
빈도 히스토그램에 비유할 수 있는 것은 동전을 분류하여 각 동전이 처음에 자신의 버킷를 얻는 것입니다.
예를 들어, 첫 번째 페니는 버킷 1에, 두 번째 페니는 버킷 2에, 첫 번째 니켈은 버킷 3에 있습니다.
그런 다음 모든 페니를 하나의 페니 버킷으로, 모든 니켈을 하나의 니켈 버킷으로 통합합니다.
빈도분석 히스토그램의 기준
빈도 히스토그램은 요청된 히스토그램의 버킷 수에따라 달라집니다.
위의 다이어그램에서 볼 수 있듯 다음 기준을 충족할 때 빈도 히스토그램을 만듭니다.
NDV는 n보다 작거나 같으며, 여기서 n은 히스토그램 버킷의 수(기본값 254)입니다.
예를 들어 sh.countries.country_subregion_id 열은 순서대로 52792에서 52799까지 8개의 서로 다른 값을 갖습니다.(NDV = 8) n이 기본값 254이면 8 <= 254이므로 옵티마이저에서 빈도 히스토그램을 만듭니다.
DBMS_STAT 통계 수집 절차의 estimate_percent 파라미터는 사용자가 지정한 값 또는 AUTO_SAMPLE_SIZE로 설정됩니다.
12c부터 샘플링 크기가 AUTO_SAMPLE_SIZE의 default인 경우 데이터베이스는 전체 테이블 스캔(full table scan)에서 빈도 히스토그램을 만듭니다.
다른 모든 샘플링 백분율 사양(ex 5 10 ... )의 경우 데이터베이스는 샘플에서 빈도 히스토그램을 가져옵니다.
Oracle Database 12c 이전 릴리스에서는 데이터베이스가 빈도가 낮은 값이 샘플에 나타나지 않는 경우가 많았습니다.
이러한 경우에 밀도(density)를 사용하면 옵티마이저가 선택성을 과대평가하게 되는 경우가 있었습니다.
빈도 히스토그램 모으기
sh의 countries 테이블을 사용합니다. country_subregion_id 열에 빈도 히스토그램을 생성하려고 한다고 가정합니다.
이 테이블에는 23개의 행이 있습니다.
다음 쿼리는 country_subregion_id 열에 불균일하게 분포된 8개의 고유한 값(샘플 출력 포함)이 포함되어 있음을 보여줍니다.
SQL> select count(*) from SH.COUNTRIES;
COUNT(*)
---------
23
1 rows selected.
SQL> SELECT country_subregion_id, count(*)
FROM sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
COUNTRY_SUBREGION_ID COUNT(*)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
8 rows selected.
버킷의 수는 default(254)로 하여 특정 컬럼의 통계정보를 수집합니다.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'COUNTRIES'
, method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID'
);
END;
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
전
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------- ----------------------- ------------ ---------------
COUNTRIES COUNTRY_SUBREGION_ID 8 NONE
후
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------- ----------------------- ------------ ---------------
COUNTRIES COUNTRY_SUBREGION_ID 8 FREQUENCY
엔드포인트 번호와 값을 확인할 수 있습니다.
SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52795
10 52796
12 52797
14 52798
23 52799
8 rows selected.
그림에 표시된 s는 각각의 고유 값이 고유한 버킷을 갖습니다. 빈도 히스토그램이므로 엔드포인트 번호는 엔드포인트의 누적 빈도입니다. 52793의 경우 엔드포인트 번호 6은 값이 5번(6 - 1) 나타나는 것을 나타냅니다.
52794의 경우 엔드포인트 번호 8은 값이 2번(8 - 6) 나타나는 것을 나타냅니다.
엔드포인트가 이전 엔드포인트보다 2이상 큰 모든 버킷은 Popular 값을 걸쳤다고 표현할 수 있습니다. 따라서, 버킷 6, 8, 12, 14 및 23은 Popular 값을 포함합니다.
옵티마이저는 엔드포인트 번호에 기초하여 그들의 카디널리티를 계산합니다.
예를 들어, 옵티마이저는 다음 공식을 사용하여 값 52799의 카디널리티(C)를 계산하고, 여기서 테이블의 행의 수는 23입니다.
C = 테이블 행의 총 수 * (해당 값의 버킷 수 / 총 버킷 수)
C = 23 * ( 9 / 23 )
버킷 1, 9 및 10에는 Nonpopular 값이 포함되어 있습니다. 옵티마이저는 밀도를 기반으로 그들의 카디널리티를 추정합니다.
Top Frequency Histograms (Top N 빈도분석)
Top N 빈도분석 히스토그램은 통계적으로 유의하지 않은 Nonpopular 값을 무시하는 빈도분석 히스토그램의 변형입니다.
예를들어,1000개의 동전 더미에 1페니 1개만 들어있다면, 동전을 버킷으로 분류할 때 1페니를 무시할 수 있습니다.
Top N 빈도의 히스토그램은 매우 인기 있는 값에 대해 더 나은 히스토그램을 생성할 수 있습니다.
Top N 빈도 히스토그램의 기준
적은 수의 값이 행의 대부분을 차지하는 경우, NDV가 요청된 히스토그램 버킷의 수보다 많을 때도 이 작은 값 집합에 빈도 히스토그램을 작성하는 것이 유용합니다.
popular 값에 대해 더 나은 품질의 히스토그램을 작성하기 위해, 옵티마이저는 Nonpopular 값은 무시하고 상위 빈도 히스토그램을 작성합니다.
Top N 빈도 히스토그램 모으기
sh의 countries 테이블을 사용합니다. country_subregion_id 열에 빈도 히스토그램을 생성하려고 한다고 가정합니다.
이 테이블에는 23개의 행이 있습니다.
다음 쿼리는 country_subregion_id 열에 불균일하게 분포된 8개의 고유한 값(샘플 출력 포함)이 포함되어 있음을 보여줍니다.
SELECT country_subregion_id, count(*)
FROM sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
COUNTRY_SUBREGION_ID COUNT(*)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
NDV 보다 작은 버킷(7)로 설정 후 통계정보를 수집합니다.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'COUNTRIES'
, method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
);
END;
확인합니다.
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
---------- -------------------- ------------ ---------------
COUNTRIES COUNTRY_SUBREGION_ID 7 TOP-FREQUENCY
sh. countries.country_subregion_id 열에는 8개의 서로 다른 값이 포함되어 있지만 히스토그램에는 7개의 버킷만 있으므로 n=7이 됩니다.
이 경우 데이터베이스는 Top N 빈도또는 하이브리드 히스토그램만 만들 수 있습니다.
country_subregion_id 열에서 가장 빈도가 높은 상위 7개의 값이 행의 95.6% (1–(1/23(행의 수))) * 100 를 차지하여 임계값인 85.7% (1–(1/7(버킷의 수))) * 100 를 초과하여 Top N 빈도 히스토그램이 생성됩니다.
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52796
11 52797
13 52798
22 52799
그림에서 보는 바와 같이 52795를 제외한 각 구별값들은 고유의 버킷을 가지고 있는데, 이 버킷은 비인기적이고 통계적으로 유의하지 않기 때문에 히스토그램에서 제외됩니다. (위의 빈도 분석의 버킷그림과 비교해보면 됩니다)
표준 빈도 히스토그램에서와 같이 엔드포인트 번호는 값들의 누적 빈도를 나타냅니다.
Height-Balanced Histograms (Legacy) (높이 균형 분석)
각 버킷에 거의 동일한 수의 행이 포함되도록 열 값이 버킷으로 나눠집니다.
예를들어, 4개의 양동이에 분배할 99개의 동전이 있다면, 각 양동이는 약 25개의 동전을 포함합니다. 높이균형 히스토그램은 엔드포인트가 어디에 해당하는지를 보여줍니다.
높이 균형 히스토그램의 기준
12c 이전에는 NDV가 n보다 클 때 (NDV > n )데이터베이스에서 높이 균형 히스토그램을 생성했습니다.
이러한 유형의 히스토그램은 범위 조건 predicates에 유용했고, equal 조건 predicates은 적어도 두개의 버킷의 엔드포인트로 나타나는 값에 대해 생성했습니다.
NDV는 n보다 크며 여기서 n은 히스토그램의 버킷수 (default 254)입니다.
DBMS_STATS의 estimate_percent는 AUTO_SAMPLE_SIZE로 설정되지 않습니다.
12c부터는 새 히스토그램을 만들때 estimate_percent가 AUTO_SAMPLE_SIZE이면 Top N 빈도 또는 하이브리드 히스토그램을 생성하고, 높이균형 히스토그램은 생성하지 않습니다.
11g에서 12c로 업그레이드하는 경우,업그레이드 전에 생성된 모든 높이균형 히스토그램은 계속 사용됩니다.
그러나 히스토그램이 생성된 테이블의 통계를 새로수집하면 히스토그램을 바꿉니다.
바꿀때 NDV와 다음 기준에 따라 달라집니다.
샘플링 백분율이 AUTO_SAMPLE_SIZE인 경우 하이브리드 히스토그램 또는 빈도분석 히스토그램을 만듭니다.
샘플링 백분율이 AUTO_SAMPLE_SIZE가 아닌 경우 데이터베이스는 높이 균형 히스토그램 또는 빈도분석 히스토그램을 만듭니다.
높이균형 히스토그램 모으기
sh의 countries 테이블을 사용합니다. country_subregion_id 열에 빈도 히스토그램을 생성하려고 한다고 가정합니다.
이 테이블에는 23개의 행이 있습니다.
다음 쿼리는 country_subregion_id 열에 불균일하게 분포된 8개의 고유한 값(샘플 출력 포함)이 포함되어 있음을 보여줍니다.
SELECT country_subregion_id, count(*)
FROM sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
COUNTRY_SUBREGION_ID COUNT(*)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
※ 11g 처럼 동작하게 하려면 estimate_percent를 기본값이 아닌 값으로 설정합니다. 기본값이 아닌 백분율을 지정하면 빈도 또는 높이 균형 히스토그램이 생성됩니다.
BEGIN DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'COUNTRIES'
, method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
, estimate_percent => 100
);
END;
확인합니다.
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
---------- -------------------- ------------ ---------------
COUNTRIES COUNTRY_SUBREGION_ID 8 HEIGHT BALANCED
옵티마이저는 NDV가 8이므로 버킷수(7)보다 크고 샘플링 백분율이 default가 아니므로 높이균형 히스토그램을 선택합니다.
SELECT COUNT(country_subregion_id) AS NUM_OF_ROWS, country_subregion_id
FROM countries
GROUP BY country_subregion_id
ORDER BY 2;
NUM_OF_ROWS COUNTRY_SUBREGION_ID
----------- --------------------
1 52792
5 52793
2 52794
1 52795
1 52796
2 52797
2 52798
9 52799
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 52792
2 52793
3 52795
4 52798
7 52799
버킷 번호는 엔드포인트 번호와 동일합니다. 옵티마이저는 각 버킷의 마지막 행의 값을 엔드포인트 값으로 기록한 다음 최소값이 첫 번째 버킷의 엔드포인트 값이고 최대값이 마지막 버킷의 엔드포인트 값인지 확인하도록 검사합니다.
이 예에서, 옵티마이저는 최소값(52792)이 버킷의 엔드포인트가 되도록 버킷 0을 추가합니다.
옵티마이저는 지정된 7개의 히스토그램 버킷에 23개의 행을 균등하게 분배해야 하므로, 각 버킷은 대략 3개의 행을 포함합니다. 그러나, 옵티마이저는 동일한 엔드포인트을 가진 버킷을 압축합니다.
따라서, 값 52793의 2개의 값을 포함하는 버킷 1과 값 52793의 3개의 값을 포함하는 버킷 2 대신, 옵티마이저는 값 52793의 값 5개를 모두 버킷 2에 넣습니다.
마찬가지로, 각 버킷의 엔드포인트가 52799인 버킷 5, 6 및 7이 각각 3개의 값을 포함하는 대신, 옵티마이저는 값 52799의 값 9개를 모두 버킷 7에 넣습니다.
이 예에서, 버킷 3 및 4는 현재 엔드포인트 번호와 이전 엔드포인트 번호 사이의 차이가 1이기 때문에 Nonpopular 값을 포함합니다.
옵티마이저는 밀도에 기초하여 이들 값에 대한 카디널리티를 계산합니다. 나머지 버킷은 popular 값을 포함합니다.
옵티마이저는 엔드포인트 번호에 기초하여 이들 값에 대한 카디널리티를 계산합니다.
Hybrid Histograms (하이브리드 히스토그램)
하이브리드 히스토그램은 높이균형 히스토그램과 빈도 히스토그램의 특성을 결합합니다.
이 접근법은 옵티마이저가 일부 상황에서 더 나은 선택성 추정치를 얻을 수 있게 해줍니다.
높이균형 히스토그램은 때때로 popular 값에 대한 부정확한 추정치를 생성합니다.
예를 들어, 버킷 하나의 엔드포인트 값으로 발생하지만 거의 두 개의 버킷을 차지하는 값은 인기가 없는 것으로 간주됩니다.
이 문제를 해결하기 위해 하이브리드 히스토그램은 어떤 값도 하나 이상의 버킷을 차지하지 않도록 값을 분배한 다음 각 엔드포인트(버킷)에 대해 엔드포인트 값을 반복한 횟수인 endpoint repeat count 값을 히스토그램에 저장합니다. 반복 횟수를 사용하여 옵티마이저는 popular 값에 대한 정확한 추정치를 얻을 수 있습니다.
Endpoint Repeat Counts는 어떻게 동작하는가
아래의 데이터를 가진 테이블이 있습니다.
이 테이블에 대한 통계를 수집하여 DBMS_STATS.GATER_TABLE_STATS의 method_opt 인수를 FOR ALL Columns SIZE 3으로 설정합니다. 이 경우 옵티마이저는 다음 그림과 같이 초기에 코인 열의 값을 세 개의 버킷으로 그룹화합니다.
값의 일부가 두개의 버킷에 존재해 값을 분할하는 경우, 옵티마이저는 값의 모든 발생을 포함하도록 버킷 경계(및 다른 모든 후속 버킷 경계)를 앞으로 시프트합니다. 예를 들어, 옵티마이저는 값 5를 시프트하여 값 5가 이제 완전히 첫 번째 버킷에 있고, 값 25가 이제 두 번째 버킷에 완전히 있도록 합니다.
endpoint repeat count는 오른쪽 버킷 경계의 값인 해당 버킷 엔드포인트가 반복되는 횟수를 측정합니다.
예를들어 첫번째 버킷에서는 값5가 3번 반복되므로 endpoint repeat count는 3입니다.
높이 균형 히스토그램은 하이브리드 히스토그램만큼 많은 정보를 저장하지 않습니다. endpoint repeat count 를 확인하여 옵티마이저는 엔드포인트 값이 정확히 몇번 있는지 확인할 수 있습니다. 예를들어 옵티마이저는 값 5가 3번 값 25가 4번 값 100이 2번 나타나는 것을 알고 있습니다. 이 정보는 옵티마이저가 더 나은 카티널리티 추정치를 생성하는 데 도움이 됩니다.
하이브리드 히스토그램의 기준
Top N 빈도 히스토그램과 비교하여 하이브리드 히스토그램을 구별하는 유일한 기준은 상위 n개의 빈도 값이 내부 임계값 p보다 작다는 것입니다.
NDV는 n보가 크며 n은 히스토그램의 버킷수(default 254)입니다.
Top N 빈도 히스토그램에 대한 기준은 적용되지 않습니다.
이것은 상위 n개 빈도수 값이 차지하는 행의 비율이 임계값 p보다 작다는 것을 나타내는 다른 방법입니다.
p는 (1-(1/n)*100)입니다.
DBMS_STAT 통계 수집 절차의 estimate_percent 파라미터는 AUTO_SAMPLE_SIZE로 설정됩니다.
사용자가 자신의 백분율을 지정하면 데이터베이스가 빈도 또는 높이 균형 히스토그램을 만듭니다.
하이브리드 히스토그램 모으기
sh.products 테이블을 사용합니다. prod_subcategory_id 컬럼을 사용할 것이며, 이테이블에는 72개의 행이 있습니다. 해당 컬럼에는 22개의 NDV가 있습니다.
10개의 버킷으로 통계정보를 생성합니다. (샘플링 백분율은 default로 합니다. 값을 지정시에는 빈도 or 높이균형으로 수집됩니다)
BEGIN DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'PRODUCTS'
, method_opt => 'FOR COLUMNS PROD_SUBCATEGORY_ID SIZE 10'
);
END;
SELECT COUNT(prod_subcategory_id) AS NUM_OF_ROWS, prod_subcategory_id
FROM products
GROUP BY prod_subcategory_id
ORDER BY 1 DESC;
NUM_OF_ROWS PROD_SUBCATEGORY_ID
----------- -------------------
8 2014
7 2055
6 2032
6 2054
5 2056
5 2031
5 2042
5 2051
4 2036
3 2043
2 2033
2 2034
2 2013
2 2012
2 2053
2 2035
1 2022
1 2041
1 2044
1 2011
1 2021
1 2052
22 rows selected.
버킷의 수(10)가 NDV 22개보다 작기 때문에 옵티마이저는 빈도 히스토그램을 만들 수 없습니다. 옵티마이저는 하이브리드 or Top N 빈도 히스토그램을 모두 고려합니다. Top N 빈도 히스토그램에 적합하기 위해서는 상위 10개의 빈도수 값이 차지하는 행의 백분율이 임계값 p와 같거나 커야 하며, 여기서 p는 (1-(1/10)*100) 90%입니다. 이경우 상위 10개의 빈도수 값은 72개 중 54개의 행을 차지하며 이는 전체의 75%(54/72*100)에 불과합니다. 따라서 옵티마이저는 Top N 빈도 히스토그램에 대한 기준이 적용되지 않기 때문에 하이브리드 히스토그램을 선택합니다.
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME='PRODUCTS'
AND COLUMN_NAME='PROD_SUBCATEGORY_ID';
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
---------- ------------------- ------------ ---------
PRODUCTS PROD_SUBCATEGORY_ID 22 HYBRID
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='PRODUCTS'
AND COLUMN_NAME='PROD_SUBCATEGORY_ID'
ORDER BY 1;
ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT
--------------- -------------- ---------------------
1 2011 1
13 2014 8
26 2032 6
36 2036 4
45 2043 3
51 2051 5
52 2052 1
54 2053 2
60 2054 6
72 2056 5
10 rows selected.
높이 균형 히스토그램에 옵티마이저는 지정된 10개의 버킷에 72개의 행을 균등하게 분배하여 한 버킷당 약 7개의 행을 포함하게 됩니다. 여기서 옵티마이저는 하이브리드 히스토그램이므로 어떤 값도 하나 이상의 버킷을 차지하지 않도록 값들을 분배합니다. 예를들어 값 2036의 값을 하나의 버킷에 넣고 다른 버킷에 넣지 않습니다. 모든 2036값은 버킷 36에 있습니다.
endpoint repeat count는 버킷에서 가장 높은 값이 반복된 횟수를 보여줍니다. 이들 값에 대한 엔드포인드 번호와 endpoint repeat count를 사용하여 카디널리티를 추정할 수 있습니다. 예를들어 버킷36의 값들은 (2033,2034,2035,2036)입니다.
엔드포인트 2035은 4개의 endpoint repeat count를 가지므로 옵티마이저는 이값의 4개가 존재함을 알고있습니다. 엔드포인트가 아닌 2033과 같은 값들에 대해서는 밀도를 사용하여 카디널리티를 추정합니다.
여기까지가 히스토그램에 대한 정리인데 생각보다 복잡한 원리로 운영되네요.
기존에 정리글은 11g기준인지 버킷 최대값이 254인데 12c는 2048개의 버킷까지 생성할 수 있으며, 하이브리드라는 좀더 고급 히스토그램을 사용할 수 있게 되었습니다.
아래 히스토그램을 통해 컬럼이 where절에서 사용되어 predicates를 추정하는 카디널리티를 추정할때 반환되는 행의 수를 보여주게 되고 이를 통해 index를 탈것인지, table full scan을 할것인지 등을 옵티마이저가 결정하게 되네요.
'Oracle > 튜닝' 카테고리의 다른 글
| 같은 SQLID 다른 PLAN (Histogram 차이) (0) | 2023.12.22 |
|---|---|
| 플랜 고정해주는 sql profile disable/drop (1) | 2023.12.18 |
| 공간관리와 Direct Path Load (0) | 2023.05.10 |
| 자동으로 튜닝을 권고해주는 SQL_TUNE ADVISOR (0) | 2023.03.10 |
| [DBMS_SQLTUNE] Profile로 SQL변경없이 실행계획 변경해보기 (0) | 2023.01.20 |
댓글