비용기반의 오라클 원리 (2009년)
히스토그램 내부 생성 로직 0 0 32,067

by 구루비스터디 히스토그램 Histogram [2023.09.23]


II. 히스토그램 내부 생성 로직

  • Oracle이 Frequency, Height-Balance 히스토그램을 생성할 때 어떤 SQL을 수행하여 통계정보에 반영하는지 10046 Trace를 통혜 살펴본다.


1. Frequency Histogram



-- 0. Oracle Version
SELECT *
FROM   V$VERSION
;

BANNER
------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
NLSRTL Version 10.2.0.3.0 - Production
;

-- 1. 샘플 데이터 생성
DROP TABLE XSOFT_T;

CREATE TABLE XSOFT_T AS
SELECT DECODE(LENGTH(LEVEL),  1, 1,
                              2, 2,
                              3, 3) FLAG
FROM   DUAL
CONNECT BY LEVEL <= 999
;



-- 2. 통계정보 생성(Frequency Histogram 생성)
-- 10046 Trace Enable
SELECT S.SID,
       S.SERIAL#,
       S.AUDSID,
       S.PROCESS CLIENT,
       P.SPID SERVER
FROM   V$PROCESS P,
       V$SESSION S
WHERE  P.ADDR = S.PADDR
AND    S.AUDSID = USERENV('SESSIONID');

ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

ALTER SESSION SET SQL_TRACE=TRUE;

SET TERMOUT OFF;
/

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

-- 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'XSOFT_T',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY');
END;
/

-- 트레이스 비활성화
ALTER SESSION SET SQL_TRACE=FALSE;

EXIT;



-- 3. 10046 Trace 내용
SELECT SUBSTRB(DUMP(VAL, 16, 0, 32), 1, 120) EP,
       CNT
FROM   (SELECT /*+ NO_PARALLEL(T)
                   NO_PARALLEL_INDEX(T)
                   DBMS_STATS
                   CURSOR_SHARING_EXACT USE_WEAK_NAME_RESL
                   DYNAMIC_SAMPLING(0)
                   NO_MONITORING  */
               FLAG VAL,
               COUNT(*) CNT
        FROM   XSOFT_T T
        WHERE  FLAG IS NOT NULL
        GROUP  BY FLAG)
ORDER  BY VAL
;

-- Output Data
EP                    CNT
-------------------- ----
Typ=2 Len=2: c1,2       9
Typ=2 Len=2: c1,3      90
Typ=2 Len=2: c1,4     900
;

-- 10046 Trace Data
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          4          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.00          0          4          0           3

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  SORT GROUP BY (cr=4 pr=0 pw=0 time=1095 us)
    999   TABLE ACCESS FULL XSOFT_T (cr=4 pr=0 pw=0 time=83 us)
;


2. Height-Balance Histogram



-- 0. Oracle Version
SELECT *
FROM   V$VERSION
;

BANNER
------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
NLSRTL Version 10.2.0.3.0 - Production
;

-- 1. 랜덤 함수 수행
EXECUTE DBMS_RANDOM.SEED(0)

-- 2. 테이블 생성
DROP TABLE T1;

DROP TABLE KILO_ROW;

CREATE TABLE KILO_ROW AS
SELECT ROWNUM ID
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 1000
;

CREATE TABLE T1
AS
WITH KILO_ROW AS (SELECT /*+ MATERIALIZE */
                         ROWNUM
                  FROM   ALL_OBJECTS
                  WHERE  ROWNUM <= 1000)
SELECT  TRUNC(7000 * DBMS_RANDOM.NORMAL)	NORMAL
FROM    KILO_ROW	K1,
	    KILO_ROW	K2
WHERE   ROWNUM <= 1000000
;

-- 3. T1 조회
SELECT MIN(NORMAL), MAX(NORMAL)
FROM   T1
;

MIN(NORMAL) MAX(NORMAL)
----------- -----------
     -32003       34660
;

SELECT COUNT(*)
FROM   (SELECT DISTINCT NORMAL FROM T1)
;

  COUNT(*)
----------
     42117
;


-- 4. 통계정보 생성(Height-Balance Histogram 생성)
-- 10046 Trace Enable
SELECT S.SID,
       S.SERIAL#,
       S.AUDSID,
       S.PROCESS CLIENT,
       P.SPID SERVER
FROM   V$PROCESS P,
       V$SESSION S
WHERE  P.ADDR = S.PADDR
AND    S.AUDSID = USERENV('SESSIONID');

ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

ALTER SESSION SET SQL_TRACE=TRUE;

SET TERMOUT OFF;
/

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

-- 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'T1',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE 10');
END;
/

-- 트레이스 비활성화
ALTER SESSION SET SQL_TRACE=FALSE;

EXIT;

-- 5. 통계정보 확인
SELECT S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_NULLS,
       S.DENSITY,
       S.LOW_VALUE,
       S.HIGH_VALUE,
       S.HISTOGRAM
FROM   USER_TAB_COLS S
WHERE  S.TABLE_NAME = UPPER('T1')
;

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS    DENSITY LOW_VALUE  HIGH_VALUE HISTOGRAM
---------- ---------- ------------ --------- ---------- ---------- ---------- ---------------
T1         NORMAL            27797         0 .000054843 3C63194B66 C3035562   HEIGHT BALANCED
;

SELECT TABLE_NAME                      AS TABLE_NAME,
       COLUMN_NAME                     AS COLUMN_NAME,
       ENDPOINT_NUMBER                 AS ENDPOINT_NUMBER,
       ROUND(ENDPOINT_VALUE, 5)        AS ENDPOINT_VALUE,
       ROUND(ENDPOINT_ACTUAL_VALUE, 5) AS ENDPOINT_ACTUAL_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = UPPER('T1')
;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ---------------------
T1         NORMAL                   0         -27626
T1         NORMAL                   1          -8930
T1         NORMAL                   2          -5901
T1         NORMAL                   3          -3708
T1         NORMAL                   4          -1775
T1         NORMAL                   5             17
T1         NORMAL                   6           1809
T1         NORMAL                   7           3674
T1         NORMAL                   8           5902
T1         NORMAL                   9           9012
T1         NORMAL                  10          28497
;


-- 6. LAG() 함수를 이용하여 Height-Balance Histogram 분포도 확인
SELECT ROWNUM TENTH,
       PREV LOW_VAL,
       CURR HIGH_VAL,
       CURR - PREV WIDTH,
       ROUND(100000 / (CURR - PREV), 2) HEIGHT
FROM   (SELECT ENDPOINT_VALUE CURR,
               LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
        FROM   USER_TAB_HISTOGRAMS
        WHERE  TABLE_NAME  = 'T1'
        AND    COLUMN_NAME = 'NORMAL')
WHERE  PREV IS NOT NULL
ORDER  BY CURR
;

     TENTH    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
         1     -27626      -8930      18696       5.35
         2      -8930      -5901       3029      33.01
         3      -5901      -3708       2193       45.6
         4      -3708      -1775       1933      51.73
         5      -1775         17       1792       55.8
         6         17       1809       1792       55.8
         7       1809       3674       1865      53.62
         8       3674       5902       2228      44.88
         9       5902       9012       3110      32.15
        10       9012      28497      19485       5.13


-- 7_1. NTILE() 함수를 이용하여 Height-Balance Histogram 분포도 확인
-- NTILE() 함수를 이용하여 10개의 그룹으로 나누고 이들을 100,000으로 나누어 높이를 일정하게 유지함
SELECT TENTH TENTH,
       MIN(NORMAL) LOW_VAL,
       MAX(NORMAL) HIGH_VAL,
       MAX(NORMAL) - MIN(NORMAL) WIDTH,
       ROUND(100000 / (MAX(NORMAL) - MIN(NORMAL)), 2) HEIGHT
FROM   (SELECT NORMAL,
               NTILE(10) OVER(ORDER BY NORMAL) TENTH
        FROM   T1)
GROUP  BY TENTH
ORDER  BY TENTH
;

     TENTH    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
         1     -32003      -8966      23037       4.34
         2      -8966      -5883       3083      32.44
         3      -5883      -3659       2224      44.96
         4      -3659      -1761       1898      52.69
         5      -1761         17       1778      56.24
         6         17       1792       1775      56.34
         7       1792       3678       1886      53.02
         8       3678       5897       2219      45.07
         9       5897       8974       3077       32.5
        10       8974      34660      25686       3.89
;

-- 7_2. NTILE() 함수를 이용하여 Height-Balance Histogram 분포도 그래프로 확인
SELECT TENTH,
       LPAD('*', ROUND(100000 / (MAX(NORMAL) - MIN(NORMAL)), 0), '*') HEIGHT
FROM   (SELECT NORMAL,
               NTILE(10) OVER(ORDER BY NORMAL) TENTH
        FROM   T1)
GROUP  BY TENTH
ORDER  BY TENTH
;

TENTH HEIGHT
----- ------------------------------------------------------------
    1 ****
    2 ********************************
    3 *********************************************
    4 *****************************************************
    5 ********************************************************
    6 ********************************************************
    7 *****************************************************
    8 *********************************************
    9 ********************************
   10 ****

-- 8. 10046 Trace 내용(10.2.0.3 기준)
-- 10046 Trace에서 Height-Balance Histogram 값을 구할때 NTILE() 함수를 이용
SELECT MIN(MINBKT),
       MAXBKT,
       SUBSTRB(DUMP(MIN(VAL), 16, 0, 32), 1, 120) MINVAL,
       SUBSTRB(DUMP(MAX(VAL), 16, 0, 32), 1, 120) MAXVAL,
       SUM(REP) SUMREP,
       SUM(REPSQ) SUMREPSQ,
       MAX(REP) MAXREP,
       COUNT(*) BKTNDV,
       SUM(CASE WHEN REP = 1 THEN 1
                ELSE 0
                END) UNQREP
FROM   (SELECT VAL,
               MIN(BKT) MINBKT,
               MAX(BKT) MAXBKT,
               COUNT(VAL) REP,
               COUNT(VAL) * COUNT(VAL) REPSQ
        FROM   (SELECT /*+ NO_PARALLEL(T)
                           NO_PARALLEL_INDEX(T)
                           DBMS_STATS CURSOR_SHARING_EXACT
                           USE_WEAK_NAME_RESL
                           DYNAMIC_SAMPLING(0)
                           NO_MONITORING */
                       NORMAL VAL,
                       NTILE(10) OVER(ORDER BY NORMAL) BKT
                FROM   T1 T
                WHERE  NORMAL IS NOT NULL)
        GROUP  BY VAL)
GROUP  BY MAXBKT
ORDER  BY MAXBKT
;

-- Output Data
MINBKT MAXBKT MINVAL                      MAXVAL                   SUMREP   SUMREPSQ MAXREP BKTNDV UNQREP
------ ------ --------------------------- ------------------------ ------ ---------- ------ ------ ------
     1      1 Typ=2 Len=5: 3c,62,51,62,66 Typ=2 Len=4: 3d,c,22,66   99987    1508195     42  12104   2334
     1      2 Typ=2 Len=4: 3d,c,23,66     Typ=2 Len=4: 3d,2b,11,66  99981    3405721     65   3083      0
     2      3 Typ=2 Len=4: 3d,2b,12,66    Typ=2 Len=4: 3d,41,29,66 100029    4618195     77   2224      0
     3      4 Typ=2 Len=4: 3d,41,2a,66    Typ=2 Len=4: 3d,54,27,66  99968    5366210     79   1898      0
     4      5 Typ=2 Len=4: 3d,54,28,66    Typ=2 Len=2: c1,11       100017    5726127    109   1778      0
     5      6 Typ=2 Len=2: c1,12          Typ=2 Len=3: c2,12,5c    100010    5733940     82   1775      0
     6      7 Typ=2 Len=3: c2,12,5d       Typ=2 Len=3: c2,25,4e     99978    5405886     78   1886      0
     7      8 Typ=2 Len=3: c2,25,4f       Typ=2 Len=3: c2,3b,61    100021    4629505     71   2219      0
     8      9 Typ=2 Len=3: c2,3b,62       Typ=2 Len=3: c2,5a,4a     99999    3406427     64   3077      0
     9     10 Typ=2 Len=3: c2,5a,4b       Typ=2 Len=4: c3,4,2f,3d  100010    1508894     40  12073   2311

-- 10046 Trace Data
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      5.74       5.60          0       1535          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.74       5.61          0       1535          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT GROUP BY (cr=1535 pr=0 pw=0 time=5605496 us)
  42117   VIEW  (cr=1535 pr=0 pw=0 time=5475345 us)
  42117    SORT GROUP BY NOSORT (cr=1535 pr=0 pw=0 time=5391092 us)
1000000     VIEW  (cr=1535 pr=0 pw=0 time=4516804 us)
1000000      WINDOW SORT (cr=1535 pr=0 pw=0 time=3516523 us)
1000000       TABLE ACCESS FULL T1 (cr=1535 pr=0 pw=0 time=90 us)

"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4413

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

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

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