오라클 성능 고도화 원리와 해법 II (2012년)
히스토그램 0 0 55,776

by 구루비스터디 히스토그램 HISTOGRAMS [2018.04.01]


  1. 히스토그램 유형( dba_histograms, dba_tab_histograms )
  2. dba_tab_columns ( 10g 컬럼 추가 : histogram )
  3. 도수분포 히스토그램 ( value-based 히스토그램 ) page. 418
  4. user_histograms
  5. 높이균형 히스토그램 ( equi-depth 히스토그램 ) page 421
  6. popular value에 대한 선택도/카디널리티 계산
  7. non-popular value에 대한 선택도/카디널리티 계산
  8. density
  9. 바인드 변수 사용 시 카디널리티 계산
  10. '=' 조건일 때
  11. 범위검색 조건일 때 ( 1~4 = 5%, 5~8 = 0.25% )
  12. 결합 선택도
  13. 동적 샘플링
  14. 다중 컬럼 통계
  15. 비용
  16. _OPTIMIZER_COST_MODEL 파라미터
  17. I/O 비용 모델
  18. 인덱스를 경유한 테이블 액세스 비용
  19. 비용 계산


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


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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입