by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

6. 히스토그램




06 히스토그램

  • 분포도가 균일하지 않는 컬럼으로 조회할 때 효과를 발휘

(1) 히스토그램 유형( 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


(2) 도수분포 히스토그램 ( 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 : 버킷이 담당하는 가장 큰 값
  • 버킷 < 컬럼
  • 하나의 버킷이 여러개의 값을 관리함
  • 각 버킷의 높이가 같다.
  • 각 버킷은
    Unknown macro: { 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행
    
    

07 비용

  • 오라클이 사용하는 비용 모델 : I/O 비용 모델, CPU 비용 모델

_OPTIMIZER_COST_MODEL 파라미터

  • IO : I/O 비용 모델 ( 예상되는 I/O 요청 횟수만을 쿼리 수행 비용 )
  • CPU : CPU 비용 모델( IO + 시간 개념 )
  • CHOOSE : 시스템 통계가 있으면 CPU , 없으면 I/O ( 기본값 )
  • CPU_COSTING : 힌트 쿼리 레벨 비용모델 가능
  • NO_CPU_COSTING : 힌트 쿼리 레벨 비용모델 가능

(1) 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은 브랜치 레벨을 의미하며 리프 블록에 도달하기 전에 릭게 될 브랜치 블록 개수임
  • 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절 만족하는 레코드를 찾기 위해 스캔할 것으로
    예상되는 비율, 리프 블록에는 인덱스 레코드가 정렬된 상태로 저장되므로이 비율이 곧 방문할 리프블록 비율
  • 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이브을 방문할 것으로 예상되는 비율
    클러스터링 팩터는 인덱스를 경유해 전체 로우를 액세스할때 읽힐 것으로 예상되는 테이블 블록 개수이므로
    여기는 선택도를 곱함으로� 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수를 구할 수 있음.
    
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.