오라클 성능 고도화 원리와 해법 II (2012년)
- 히스토그램 유형( dba_histograms, dba_tab_histograms )
- dba_tab_columns ( 10g 컬럼 추가 : histogram )
- 도수분포 히스토그램 ( value-based 히스토그램 ) page. 418
- user_histograms
- 높이균형 히스토그램 ( equi-depth 히스토그램 ) page 421
- popular value에 대한 선택도/카디널리티 계산
- non-popular value에 대한 선택도/카디널리티 계산
- density
- 바인드 변수 사용 시 카디널리티 계산
- '=' 조건일 때
- 범위검색 조건일 때 ( 1~4 = 5%, 5~8 = 0.25% )
- 결합 선택도
- 동적 샘플링
- 다중 컬럼 통계
- 비용
- _OPTIMIZER_COST_MODEL 파라미터
- I/O 비용 모델
- 인덱스를 경유한 테이블 액세스 비용
- 비용 계산
- 분포도가 균일하지 않는 컬럼으로 조회할 때 효과를 발휘
히스토그램 유형( dba_histograms, dba_tab_histograms )
- 높이균형 히스토그램
- 도수분표 히스토그램
dba_tab_columns ( 10g 컬럼 추가 : histogram )
- FREQUENCY : 값별로 빈도수를 저장하는 도수분포 히스토그램( 값의 수 = 버킷 개수 )
- BEIGHT-BALANCED : 각 버킷의 높이가 동일한 높이균형 히스토그램( 값의 수 = 버킷 개수 )
- NONE : 히스토그램 없음
CREATE SEQUENCE SEQ;
CREATE TABLE MEMBER( MEM_ID NUMBER, AGE NUMBER( 2 ) );
EXEC DBMS_RANDOM.SEED(0);
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 1,19 ) FROM DUAL CONNECT BY LEVEL <= 50;
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 20,29 ) FROM DUAL CONNECT BY LEVEL <= 270;
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 30,39 ) FROM DUAL CONNECT BY LEVEL <= 330;
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 30,39 ) FROM DUAL CONNECT BY LEVEL <= 330;
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, 40 FROM DUAL CONNECT BY LEVEL <= 1000; --> POPULAR VALUE
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 41,49 ) FROM DUAL CONNECT BY LEVEL <= 200;
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 50,59 ) FROM DUAL CONNECT BY LEVEL <= 100;
INSERT INTO MEMBER
SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 60,99 ) FROM DUAL CONNECT BY LEVEL <= 50;
SQL> SELECT CASE WHEN AGE <= 19 THEN '10'
2 WHEN AGE >=20 AND AGE < 30 THEN '20'
3 WHEN AGE >=30 AND AGE < 40 THEN '30'
4 WHEN AGE >=40 AND AGE < 50 THEN '40'
5 WHEN AGE >=50 AND AGE < 60 THEN '50'
6 WHEN AGE >=60 THEN '60'
7 END AGE_GRP, COUNT(*)
8 FROM MEMBER
9 GROUP BY CASE WHEN AGE <= 19 THEN '10'
10 WHEN AGE >=20 AND AGE < 30 THEN '20'
11 WHEN AGE >=30 AND AGE < 40 THEN '30'
12 WHEN AGE >=40 AND AGE < 50 THEN '40'
13 WHEN AGE >=50 AND AGE < 60 THEN '50'
14 WHEN AGE >=60 THEN '60'
15 END
16 ORDER BY AGE_GRP;
AG COUNT(*)
-- ----------
10 50
20 270
30 660
40 1000
50 100
60 50
도수분포 히스토그램 ( value-based 히스토그램 ) page. 418
- 값별로 빈도수를 저장하는 히스토그램
- 값마다 하나의 버킷을 할당
- 버킷 개수 >= 컬럼수
SQL> SELECT COUNT(*), COUNT( DISTINCT AGE ) FROM MEMBER;
COUNT(*) COUNT(DISTINCTAGE)
---------- ------------------
2130 79
SQL> begin
2 dbms_stats.gather_table_stats( user, 'MEMBER', method_opt=> 'for all columns size 100' ); --히스토그램 생성
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
2 FROM USER_TAB_COL_STATISTICS
3 WHERE TABLE_NAME = 'MEMBER'
4 AND COLUMN_NAME = 'AGE'
5 ;
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
79 79 FREQUENCY
user_histograms
- endpoint_value : 버킷에 할당된 컬럼 값
- endpoint_number : endpoint_value로 정렬했을 때, 최소값부터 현재 값까지의 누적수량
SQL> SELECT T.AGE "연령", T.CNT "인원수(명)"
2 , nvl2( h.prev, h.RUNNING_TOTAL - h.prev, h.running_total ) frequency
3 , h.running_total
4 FROM (SELECT AGE, COUNT(*) CNT FROM MEMBER GROUP BY AGE ) T
5 , (SELECT ENDPOINT_VALUE AGE, ENDPOINT_NUMBER RUNNING_TOTAL
6 , LAG(ENDPOINT_NUMBER) OVER( ORDER BY ENDPOINT_VALUE) PREV
7 FROM USER_HISTOGRAMS
8 WHERE TABLE_NAME = 'MEMBER'
9 AND COLUMN_NAME = 'AGE' ) H
10 WHERE H.AGE = T.AGE
11 ORDER BY "연령";
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
1 2 2 2
2 3 3 5
3 8 8 13
4 2 2 15
5 3 3 18
6 2 2 20
7 3 3 23
8 5 5 28
9 3 3 31
10 1 1 32
11 1 1 33
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
12 1 1 34
13 1 1 35
14 2 2 37
15 3 3 40
16 4 4 44
17 1 1 45
18 4 4 49
19 1 1 50
20 17 17 67
21 39 39 106
22 32 32 138
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
23 26 26 164
24 23 23 187
25 14 14 201
26 24 24 225
27 30 30 255
28 46 46 301
29 19 19 320
30 52 52 372
31 87 87 459
32 61 61 520
33 74 74 594
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
34 61 61 655
35 87 87 742
36 60 60 802
37 81 81 883
38 72 72 955
39 25 25 980
40 1000 1000 1980
50 5 5 1985
51 9 9 1994
52 14 14 2008
53 10 10 2018
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
54 13 13 2031
55 12 12 2043
56 15 15 2058
57 9 9 2067
58 4 4 2071
59 9 9 2080
60 1 1 2081
65 3 3 2084
67 2 2 2086
68 1 1 2087
69 1 1 2088
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
70 1 1 2089
71 1 1 2090
72 2 2 2092
75 6 6 2098
77 1 1 2099
78 1 1 2100
79 3 3 2103
80 1 1 2104
82 2 2 2106
85 2 2 2108
86 2 2 2110
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
87 1 1 2111
88 2 2 2113
89 3 3 2116
90 1 1 2117
91 1 1 2118
92 1 1 2119
93 1 1 2120
94 3 3 2123
95 1 1 2124
96 1 1 2125
97 1 1 2126
연령 인원수(명) FREQUENCY RUNNING_TOTAL
---------- ---------- ---------- -------------
98 3 3 2129
99 1 1 2130
79 개의 행이 선택되었습니다.
높이균형 히스토그램 ( equi-depth 히스토그램 ) page 421
- endpoint_number : 버킷번호
- endpoint_value : 버킷이 담당하는 가장 큰 값
- 버킷 < 컬럼
- 하나의 버킷이 여러개의 값을 관리함
- 각 버킷의 높이가 같다.
- 각 버킷은 { 1 / ( 버킷 개수 ) * 100 } %의 데이터 분포를 갖는다. ( ex : 2000( 컬럼 가진 값수 ) / 20( 버킷수 ) = 100에 해당하는 노피( 분포 5% ) )
begin
dbms_stats.gather_table_stats( user, 'MEMBER', method_opt=> 'for all columns size 20' ); --히스토그램 생성
end;
/
SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
2 FROM USER_TAB_COL_STATISTICS
3 WHERE TABLE_NAME = 'MEMBER'
4 AND COLUMN_NAME = 'AGE';
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
79 20 HEIGHT BALANCED
-- ENDPOINT_NUMBER = 1 버킷은 1~22 연령대 구간을, 20은 55~99연령대 구간을 대표한다.
SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
2 FROM USER_HISTOGRAMS
3 WHERE TABLE_NAME= 'MEMBER'
4 AND COLUMN_NAME = 'AGE';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1 <== 첫 번째 레코드는 버킷이 아니라 '최소값' 을 표현하는 용도
1 22
2 26
3 30
4 31
5 33
6 34
7 36
8 37
9 39
18 40 <== popular value ( 압축 )
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
19 54
20 99
13 개의 행이 선택되었습니다.
SQL> SELECT '~' || AGE "연령대", ENDPOINT_NUMBER, DIFF
2 , ROUND( 100 * diff / sum( diff) over() ) "RATION(%)"
3 , ROUND( T.NUM_ROWS * DIFF / SUM( DIFF ) OVER()) "인원수(명)"
4 FROM (SELECT TABLE_NAME
5 , ENDPOINT_VALUE AGE, ENDPOINT_NUMBER
6 , ENDPOINT_NUMBER - LAG( ENDPOINT_NUMBER ) OVER (ORDER BY ENDPOINT_VALUE) DIFF
7 , LAG(ENDPOINT_NUMBER) OVER( ORDER BY ENDPOINT_VALUE) PREV
8 FROM USER_HISTOGRAMS
9 WHERE TABLE_NAME = 'MEMBER'
10 AND COLUMN_NAME = 'AGE' ) H, USER_TABLES T
11 WHERE H.ENDPOINT_NUMBER > 0
12 AND T.TABLE_NAME = H.TABLE_NAME
13 ORDER BY 1;
연령대 ENDPOINT_NUMBER DIFF RATION(%) 인원수(명)
----------------------------------------- --------------- ---------- ---------- ----------
~22 1 1 5 107
~26 2 1 5 107
~30 3 1 5 107
~31 4 1 5 107
~33 5 1 5 107
~34 6 1 5 107
~36 7 1 5 107
~37 8 1 5 107
~39 9 1 5 107
~40 18 9 45 959
~54 19 1 5 107
~99 20 1 5 107
-- 오라클은 popular value( 40 )에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고
나머지는 미리 구해놓은 density 값을 이용한다.
12 개의 행이 선택되었습니다.
popular value에 대한 선택도/카디널리티 계산
--조건절 값이 두 개 이상 버킷을 가진 popular value이면 아래 공식을 따른다.
선택도 = ( 조건절 값의 버킷 개수 ) / ( 총 버킷 개수 )
카디널리티 = 총 로우 수 * 선택도
= (총 로우 수 ) * ( 조건절 값의 버킷 개수 ) / ( 총 버킷 개수 )
= 2130 * 9 / 20 = 958.5
SQL> SELECT COUNT(*) FROM MEMBER ;
COUNT(*)
----------
2130
SQL> SELECT COUNT(*) FROM MEMBER WHERE AGE = 40;
COUNT(*)
----------
1000
SQL> @XPLAN
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 5 |
|* 2 | TABLE ACCESS FULL| MEMBER | 1 | 959 | 1000 |00:00:00.01 | 5 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("AGE"=40)
non-popular value에 대한 선택도/카디널리티 계산
카디널리티 = 총 로우 수 * 선택도 = 총 로우수 * density
SQL> SELECT 1/NUM_DISTINCT, TO_CHAR( density, 'FM999.999999999999999999999' ) AS density
2 , round( TO_CHAR( density, 'FM999.999999999999999999999' ) * 2130 ) AS CD
3 FROM USER_TAB_COL_STATISTICS
4 WHERE TABLE_NAME = 'MEMBER'
5 AND COLUMN_NAME = 'AGE'
6 ;
1/NUM_DISTINCT DENSITY CD
-------------- -------------------------- ----------
.012658228 .0234101956873987 50
SQL> SELECT COUNT(*) FROM MEMBER WHERE AGE = 39;
COUNT(*)
----------
25
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 5 |
|* 2 | TABLE ACCESS FULL| MEMBER | 1 | 15 | 25 |00:00:00.01 | 5 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("AGE"=39)
density
- = 조건으로 검색할 때의 선택도를 미리 구해 놓은 값으로 이해하면된다.
- 히스토그램이 없을때 = 1/num_distinct
- 높이 군형 히스토그램일 때 = 시그마( 모든 non-popular value 빈도스 ) 제곱 / null을 제외한 총 로우 수 * 시그마( 든 non-popular value 빈도스 )
- 도수 분포 히스토그램일 때 = 1 / ( 2 * null을 제외한 총 로우 수 )
- 카디널리티 = 총 로우수 * 선택도 = NUM_ROWS * density
바인드 변수 사용 시 카디널리티 계산
- SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포를 사용하지 못하는 문제점을 갖는다.
- 평균 분포를 가정한 실행계획을 생성한다.
'=' 조건일 때
- 히스토그램이 없을 때 : 1/num_distinct 사용
- 도수분포 히스토그램일 때 : 1/num_distinct 사용
- 높이균형 히스토그램일 때 : density 사용
범위검색 조건일 때 ( 1~4 = 5%, 5~8 = 0.25% )
- 1 : 번호 > :NO
- 2 : 번호 < :NO
- 3 : 번호 >= :NO
- 4 : 번호 <= :NO
- 5 : 번호 BETWEEN :NO1 AND :NO2
- 6 : 번호 >= :NO1 AND 번호 < :NO2
- 7 : 번호 >= :NO1 AND 번호 < :NO2
- 8 : 번호 > :NO1 AND 번호 < :NO2
DROP TABLE T PURGE;
CREATE TABLE T AS
SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 1000;
begin
dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size 254' );
end;
/
SQL> SELECT COUNT(*) FROM T WHERE NO <= :NO;
COUNT(*)
----------
0
SQL> @XPLAN
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| T | 1 | 50 | 0 |00:00:00.01 |
---------------------------------------------------------------------------
SQL> VAR NO1 NUMBER;
SQL> VAR NO2 NUMBER;
SQL> SELECT COUNT(*) FROM T WHERE NO BETWEEN :NO1 AND :NO2;
COUNT(*)
----------
0
SQL> @XPLAN
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
|* 2 | FILTER | | 1 | | 0 |00:00:00.01 |
|* 3 | TABLE ACCESS FULL| T | 0 | 3 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
-- 상수일경우
SQL> SELECT COUNT(*) FROM T WHERE NO <= 100;
COUNT(*)
----------
100
SQL> @XPLAN
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | TABLE ACCESS FULL| T | 1 | 98 | 100 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO"<=100)
SQL> SELECT COUNT(*) FROM T WHERE NO BETWEEN 500 AND 600;
COUNT(*)
----------
101
SQL> @XPLAN
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | TABLE ACCESS FULL| T | 1 | 98 | 101 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("NO">=500 AND "NO"<=600))
결합 선택도
- 아무리 히스토그램을 많이 만들어 두어도 두개 이상 컬럼에 대한 결합 선택도를 구할 대는 정확성이 떨어진다.
- 9i부터 이 문제를 해결하기 위해 동적 샘플링을 시도함
동적 샘플링
DROP TABLE T PURGE
CREATE TABLE T AS
SELECT EMPNO , ENAME , SAL , SAL * 0.1 SAL_BO FROM SCOTT.EMP;
begin
dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for columns SAL size 254 SAL_BO SIZE 254' );
end;
/
SQL> SELECT * FROM T
2 WHERE SAL >= 2000
3 AND SAL_BO >= 200;
EMPNO ENAME SAL SAL_BO
---------- ---------- ---------- ----------
7566 JONES 2975 297.5
7698 BLAKE 2850 285
7782 CLARK 2450 245
7788 SCOTT 3000 300
7839 KING 5000 500
7902 FORD 3000 300
6 개의 행이 선택되었습니다.
SQL> @XPLAN
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 3 |
|* 1 | TABLE ACCESS FULL| T | 1 | 2 | 6 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SAL">=2000 AND "SAL_BO">=200))
SQL> SELECT /*+ DYNAMIC_SAMPLING( 4 ) */ * FROM T
2 WHERE SAL >= 2000
3 AND SAL_BO >= 200;
EMPNO ENAME SAL SAL_BO
---------- ---------- ---------- ----------
7566 JONES 2975 297.5
7698 BLAKE 2850 285
7782 CLARK 2450 245
7788 SCOTT 3000 300
7839 KING 5000 500
7902 FORD 3000 300
6 개의 행이 선택되었습니다.
SQL> @XPLAN
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 3 |
|* 1 | TABLE ACCESS FULL| T | 1 | 6 | 6 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SAL">=2000 AND "SAL_BO">=200))
다중 컬럼 통계
- 11g
- 확장형 통계 : 컬럼 그룹 통계생성
-- 패키지 설치 해야할거같은데. 시간이 없어요 ;;
SQL> var ret varchar2(30);
SQL> exec :ret := dbms_stats.create_extended_stats( user, 't', '(SAL, SAL_BO )' );
BEGIN :ret := dbms_stats.create_extended_stats( user, 't', '(SAL, SAL_BO )' ); END;
*
1행에 오류:
ORA-20000: Unable to create extension: not supported for SYS owned table
ORA-06512: "SYS.DBMS_STATS", 8433행
ORA-06512: "SYS.DBMS_STATS", 32587행
ORA-06512: 1행
SQL> SHOW USER
USER은 "SYS"입니다
SQL> begin
2 dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size skewonly for columns ( sal,sal_bo ) size 254' );
3 end;
4 /
begin
*
1행에 오류:
ORA-20000: Unable to create extension: not supported for SYS owned table
ORA-06512: "SYS.DBMS_STATS", 23829행
ORA-06512: "SYS.DBMS_STATS", 23880행
ORA-06512: 2행
비용
- 오라클이 사용하는 비용 모델 : I/O 비용 모델, CPU 비용 모델
_OPTIMIZER_COST_MODEL 파라미터
- IO : I/O 비용 모델 ( 예상되는 I/O 요청 횟수만을 쿼리 수행 비용 )
- CPU : CPU 비용 모델( IO + 시간 개념 )
- CHOOSE : 시스템 통계가 있으면 CPU , 없으면 I/O ( 기본값 )
- CPU_COSTING : 힌트 쿼리 레벨 비용모델 가능
- NO_CPU_COSTING : 힌트 쿼리 레벨 비용모델 가능
I/O 비용 모델
- 디스크 I/O CALL 횟수 (논리적 / 물리적으로 읽은 블록 개수가 아닌 I/O CALL 횟수 )
인덱스를 경유한 테이블 액세스 비용
- Single Block I/O 방식
- 블록 개수 = I/O CALL 횟수
DROP TABLE T PURGE;
CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;
CREATE INDEX T_OWNER_IDX ON T( OWNER ) ;
begin
dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size 1' );
end;
/
ALTER SESSION SET "_OPTIMIZER_COST_MODEL" = io;
SET AUTOTRACE TRACEONLY EXP;
SQL> SELECT /*+ INDEX( T ) */ * FROM T WHERE OWNER = 'SYS';
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3139 | 297K| 91 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3139 | 297K| 91 | -- 91 - 8 예상함 ( 클러스터링 팩터가 비용 계산식에 고려 됨 )
|* 2 | INDEX RANGE SCAN | T_OWNER_IDX | 3139 | | 8 | -- 8 예상함
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Note
-----
- cpu costing is off (consider enabling it)
;
비용 계산
--유효 인덱스 선택도 : 인덱스 Access Predicate
--유효 테이블 선택도 : 인덱스 Access Predicate와 Filter Predicate에 의해 결정된다.
--(최종) 테이블 선택도 : 테이블 Filter Predicate 까지 포함한 모든 조건절에 의해 결정
비용 = blevel + -- 인ㄷ게스 수직적 탐색 비용
(리프 블록 수 * 유효 인덱스 선택도 ) + -- 인덱스 수평적 탐색 비용
(클러스터링 팩터 * 유효 테이블 선택도 ) -- 테이블 Random 엑세스 비용
- blevel은 브랜치 레벨을 의미하며 리프 블록에 도달하기 전에 릭게 될 브랜치 블록 개수임
- 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율, 리프 블록에는 인덱스 레코드가 정렬된 상태로 저장되므로이 비율이 곧 방문할 리프블록 비율
- 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이브을 방문할 것으로 예상되는 비율 클러스터링 팩터는 인덱스를 경유해 전체 로우를 액세스할때 읽힐 것으로 예상되는 테이블 블록 개수이므로 여기는 선택도를 곱함으로쎠 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수를 구할 수 있음.
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3280
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.