CBO, 테이블 통계, 인덱스 통계

CBO (Cost Based Optimizer) 비용 산정 을 위해 테이블 통계, 인덱스 통계, 히스토그램을 참조한다. 

 

 

 

 TUNING.ORDERS

ORDERNO	ORDERDATE	TOTAL	PAYTYPE	STATUS	CUSTNO	CODE
200004	2017-12-13 0:00	272900	신용카드	배송중	6091	29
200005	2017-12-15 0:00	61500	체크카드	배송중	5590	75
200006	2018-06-14 0:00	9700	체크카드	배송중	7693	7
200010	2019-11-24 0:00	593250	신용카드	결제완료	6162	62
200011	2019-06-30 0:00	575550	계좌이체	배송준비중	4498	98
200012	2018-02-22 0:00	9150	계좌이체	구매확정	3381	1
200013	2019-11-20 0:00	50700	신용카드	배송중	3822	25
200014	2017-11-30 0:00	62200	계좌이체	구매확정	1844	33
200015	2018-04-25 0:00	268400	신용카드	결제완료	1232	7
200007	2019-05-08 0:00	577350	체크카드	배송완료	4048	35
200008	2020-07-31 0:00	36200	체크카드	배송완료	6928	24
200009	2019-12-28 0:00	136250	계좌이체	결제완료	2152	96
200001	2019-03-17 0:00	92400	신용카드	배송완료	7568	93
200002	2020-03-22 0:00	329750	신용카드	배송완료	1684	84
200003	2018-02-07 0:00	693000	계좌이체	배송완료	9902	14
200034	2018-08-22 0:00	186300	체크카드	결제완료	7538	80
200035	2018-12-27 0:00	88900	계좌이체	구매확정	132	34
200036	2020-02-22 0:00	509000	체크카드	결제완료	2710	2
200037	2019-07-21 0:00	229000	신용카드	배송중	4656	12
200038	2020-02-06 0:00	164400	신용카드	배송준비중	7997	70

 

- TUNING.ORDERS 통계정보 수집

EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNING', 'ORDERS');

 

SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='ORDERS';

행의 갯수, 블럭 수, 통계 수집 날짜 등...

NUM_ROWS	BLOCKS	LAST_ANALYZED
899999	302559	2022-01-27 14:22

 

 

SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, LAST_ANALYZED, HISTOGRAM
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME='ORDERS';

 

*

NUM_DISTINCT : 고유한 값의 갯수

DENSITY : 1/NUM_DISTINCT

HISTOGRAM : NONE < 수집 안됨

COLUMN_NAME	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	LAST_ANALYZED	HISTOGRAM
ORDERNO	899999	C102	C40215	0.0000011111	2022-01-27 14:27	NONE
ORDERDATE	1000	78750B0B010101	78780806010101	0.001	2022-01-27 14:27	NONE
TOTAL	15835	C133	C4021D06	0.0000631512	2022-01-27 14:27	NONE
PAYTYPE	3	EAB384ECA28CEC9DB4ECB2B4	ECB2B4ED81ACECB9B4EB939C	0.3333333333	2022-01-27 14:27	NONE
STATUS	5	EAB2B0ECA09CEC9984EBA38C	EBB0B0EC86A1ECA491	0.2	2022-01-27 14:27	NONE
CUSTNO	10000	C102	C302	0.0001	2022-01-27 14:27	NONE
CODE	100	C102	C202	0.01	2022-01-27 14:27	NONE

옵티마이저는 문장을 실행하지 않고, 전체 행의 33.3%가 리턴되는 것으로 예측한다. 

SELECT * FROM TUNING.ORDERS WHERE PAYTYPE = '체크카드';

 

 

히스토그램의 개념

- 컬럼 값의 분포를 나타냄 

- 히스토그램 정보가 있으면, 옵티마이저는 쿼리를 실행하지 않아도, 몇 건의 결과가 리턴 될 것인지를 정확히 예측 할 수 있다. 

- 히스토그램 정보가 없으면, 옵티마이저는 해당 값이 최소값과 최대값 내에서 균등하게 분포되어 있다고 가정한다.

 => 즉, 특정 컬럼의 값 분포가 어느 한쪽으로 치우침 없이 균등하다면 히스토그램을 생성 할 필요는 없다.

 

 

히스토그램 수집 

EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNING', 'ORDERS', method_opt=>'for all columns');

                                                                       -- 모든 컬럼의 히스토그램을 수집하겠다. 

 

SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, LAST_ANALYZED, HISTOGRAM
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME='ORDERS';

 

* 히스토그램 수집 확인

COLUMN_NAME	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	LAST_ANALYZED	HISTOGRAM
ORDERNO	899999	C102	C40215	0.000001	2022/01/28 9:21:38	HYBRID
ORDERDATE	1000	78750B0B010101	78780806010101	0.000996	2022/01/28 9:21:38	HYBRID
TOTAL	15835	C133	C4021D06	0.000063	2022/01/28 9:21:38	HYBRID
PAYTYPE	3	EAB384ECA28CEC9DB4ECB2B4	ECB2B4ED81ACECB9B4EB939C	0.0000005555561728	2022/01/28 9:21:38	FREQUENCY
STATUS	5	EAB2B0ECA09CEC9984EBA38C	EBB0B0EC86A1ECA491	0.0000005555561728	2022/01/28 9:21:38	FREQUENCY
CUSTNO	10000	C102	C302	0.0001	2022/01/28 9:21:38	HYBRID
CODE	100	C102	C202	0.009901	2022/01/28 9:21:38	HYBRID

* 히스토그램 생성 시(비용 계산 시) 

  히스토그램이 있으면 히스토그램 정보를 이용해서 조건에 의한 결과 값을 리턴

  히스토그램이 없다면 Density 로 컬럼에 의한 비율로만 계산함

SELECT endpoint_number, endpoint_value
FROM dba_histograms
WHERE table_name='ORDERS' AND column_name='PAYTYPE';

 

 

* 테이블 통계 수집 시, 많이 사용하는 옵션

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('TUNING', 'ORDERS', method_opt=>'for all columns size skewonly', cascade=>true, no_invalidate=>false);

 

[method_opt=>'for all columns size skewonly'] : 컬럼 중에 분포가 불균등한 컬럼에 대하여 히스토그램 작성

[cascade=>true] : ORDERS 테이블과 연관된 객체에 대해서도 통계 수집

[no_invalidate=>false] : 캐시된 실행 계획 모두 무효화

 

 

 

 

인덱스 통계

: 인덱스는 생성 즉시 통계가 수집 된다.

CREATE INDEX TUNING.ORDERS_CODE_IDX ON TUNING.ORDERS (CODE) ;

 

 

인덱스 통계 직접 수집

: 인덱스가 참조하는 테이블의 컬럼들에 DML이 많이 수행된 경우, 인덱스의 통계를 갱신 할 필요가 있다.

EXEC DBMS_STATS.GATHER_INDEX_STATS('TUNING', 'ORDERS_CODE_IDX');

 

보통의 경우 테이블 통계를 수집하면서 테이블과 관련된 모든 객체에 대해 통계를 수집하는게 일반적이다. 

 

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('TUNING', 'ORDERS', method_opt=>'for all columns size skewonly', cascade=>true, no_invalidate=>false);

 

 

SELECT blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, last_analyzed
FROM dba_indexes
WHERE index_name = 'ORDERS_CODE_IDX';

 

BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS	AVG_LEAF_BLOCKS_PER_KEY	AVG_DATA_BLOCKS_PER_KEY	CLUSTERING_FACTOR	NUM_ROWS	LAST_ANALYZED
2	2880	100	28	8826	882657	891134	2022/01/28 9:41:53

*

BLEVER : 브랜치 레이어의 수

LEAF_BLOCKS : 리프 블록 수

CLUSTERING_FACTOR : 클러스터링 팩터

 

* 테이블 내에 데이터가 아무리 많아도 브랜치 레벨의 수는 보통 3 이하이다.

  그러므로 4이상이 되면 통계를 다시 수집할 필요성이 있다.

클러스터링 팩터(Clustering Factor)?
: 리프 블록 내의 인덱스 항목들을 순차적으로 읽고, 테이블에 접근 할 때, 테이블 블록에 방문한 횟수
: 인덱스에 저장된 인덱스 항목의 정렬 순서와 테이블에 저장된 행의 정렬 순서가 얼마나 일치하는지를 나타내는 값

* 옵티마이저는 사용 가능한 인덱스가 여러 개 존재하면 브랜치 레벨과 클러스터링 팩터가 낮으며, 
  통계 자료가 최근에 업데이트 된 인덱스를 선호한다.