비용기반의 오라클 원리 (2009년)
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.