IV. 도수분포 히스토그램(Frequency Histogram)
V. 높이균형 히스토그램(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. 샘플 데이터 생성
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. 통계정보 생성
-- 히스토그램 없이 통계정보 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'XSOFT_T',
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
-- 3. XSOFT_T 테이블 컬럼 통계정보 확인
-- 통계정보를 생성하지 않으면(FOR ALL COLUMNS SIZE 1) DENSITY 값은 (1 / NUM_DISTINCT) 값이 된다.
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('XSOFT_T')
;
TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE HISTOGRAM
---------- ---------- ------------ --------- ------- ---------- ---------- ----------
XSOFT_T FLAG 3 0 .333 C102 C104 NONE
;
-- 4. XSOFT_T 테이블 컬럼 히스토그램 확인
-- 통계정보를 생성하지 않은 상태에서 USER_TAB_HISTOGRAMS에 조회되는 값들은
-- 히스토그램 값이 아니라 최소값과 최대값에 대한 정보들 뿐이다.
-- 그러므로 ENDPOINT_NUMBER 값은 0과 1 밖에 없다.
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('XSOFT_T')
;
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ---------------------
XSOFT_T FLAG 0 1.000
XSOFT_T FLAG 1 3.000
;
-- 5. Cardinality 확인
-- 통계정보가 생성되어 있지 않으므로 XSOFT_T 테이블에서 1, 2, 3의 값이 SKEW 되어
-- 있음에도 불구하고 Cardinality(ROWS)는 999(Base Cardinality) / 3(Distincy) = 333 값이 된다.
EXPLAIN PLAN FOR
SELECT *
FROM XSOFT_T
WHERE FLAG = 1
UNION ALL
SELECT *
FROM XSOFT_T
WHERE FLAG = 2
UNION ALL
SELECT *
FROM XSOFT_T
WHERE FLAG = 3
;
@XPLAN
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 2997 | 9 (67)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL| XSOFT_T | 333 | 999 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| XSOFT_T | 333 | 999 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| XSOFT_T | 333 | 999 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=1)
3 - filter("FLAG"=2)
4 - filter("FLAG"=3)
;
-- 6. 히스토그램 포함 통계정보 재생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'XSOFT_T',
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY');
END;
/
-- 7. XSOFT 테이블 컬럼 통계정보 확인
-- 히스토그램을 생성하여(FOR ALL COLUMNS SIZE SKEWONLY) DENSITY 값은 (1 / NUM_DISTINCT) 값이 안된다.
-- 그리고 HISTOGRAM 컬럼에는 NULL이 아닌 'FREQUENCY' 히스토그램이 생성된다.
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('XSOFT_T')
;
TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE HISTOGRAM
---------- ---------- ------------ --------- ------- ---------- ---------- ----------
XSOFT_T FLAG 3 0 .001 C102 C104 FREQUENCY
-- 8. XSOFT_T 테이블 컬럼 히스토그램 확인
-- 히스토그램을 생성하였으므로 정확하게 분포도를 확인할 수 있다.
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('XSOFT_T')
;
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ---------------------
XSOFT_T FLAG 9 1.000
XSOFT_T FLAG 99 2.000
XSOFT_T FLAG 999 3.000
;
-- 9. Cardinality 재 확인
-- FLAG 컬럼에 히스토그램이 있기 때문에 Cardinality(ROWS) 값이 NDV 값이 아니라
-- 정확하게 예상 로우수를 인식하고 있다.
EXPLAIN PLAN FOR
SELECT *
FROM XSOFT_T
WHERE FLAG = 1
UNION ALL
SELECT *
FROM XSOFT_T
WHERE FLAG = 2
UNION ALL
SELECT *
FROM XSOFT_T
WHERE FLAG = 3
;
@XPLAN
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 2997 | 9 (67)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL| XSOFT_T | 9 | 27 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| XSOFT_T | 90 | 270 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| XSOFT_T | 900 | 2700 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=1)
3 - filter("FLAG"=2)
4 - filter("FLAG"=3)
;
-- 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)
;
-- 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)
-- 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. 통계정보 생성
-- 히스토그램 없이 통계정보 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'XSOFT_T',
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY');
END;
/
-- 3. bind peeking 활성화
-- 1) 바인드 변수 선언
VAR B1 NUMBER;
EXEC :B1 := 3;
-- 2) STATISTICS_LEVEL 활성화
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
-- 3) 분포도가 낮은 값 3으로 먼저 Peeking
SELECT /*+ OPT_PARAM('_OPTIM_PEEK_USER_BINDS' 'TRUE') */
*
FROM XSOFT_T
WHERE FLAG = :B1
;
-- 4) V$SQL에서 SQL_ID 확인
SELECT SUBSTR(SQL_TEXT, 1, 30),
SQL_ID,
CHILD_NUMBER
FROM V$SQL
WHERE UPPER(SQL_TEXT) LIKE '%XSOFT_T%'
ORDER BY FIRST_LOAD_TIME DESC
;
SUBSTR(SQL_TEXT,1,30) SQL_ID CHILD_NUMBER
------------------------------ ------------- ------------
SELECT SUBSTR(SQL_TEXT, 1, 30) 56bqkx2bgmvfc 0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a 0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a 1
SELECT SUBSTR(SQL_TEXT, 1, 20) b3qmfm55u7trs 0
SELECT SUBSTR(SQL_TEXT, 1, 20) atu0kk85um7v1 0
SELECT SQL_TEXT, SQL_FU 5hx6usvks1r75 0
SELECT SQL_TEXT, SQL_FU 5hx6usvks1r75 1
SELECT /*+ OPT_PARAM('_OPTIM_P 35x0sx3dxtt5r 0
EXPLAIN PLAN FOR SELECT /*+ OP bfa0u88cz3n78 0
select /*+ no_parallel(t) no_p 8zw3fzkk0gvwc 0
select substrb(dump(val,16,0,3 4agz0p1vt39fw 0
select /*+ no_parallel(t) no_ 2gckc3xgdabky 0
BEGIN DBMS_STATS.GATHER_TA 4qt4pb05pwh74 0
SELECT topology FROM SDO_T 997gattrj5nag 0
*/
-- 5) Runtime Plan 확인
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('35x0sx3dxtt5r',NULL,'ADVANCED ALLSTATS COST LAST'));
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| XSOFT_T | 1 | 333 | 999 | 3 (0)| 00:00:01 | 900 |00:00:00.01 | 64 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / XSOFT_T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "XSOFT_T"@"SEL$1")
END_OUTLINE_DATA
*/
;
-- 6) 분포도가 높은 값 1 사용
VAR B1 NUMBER;
EXEC :B1 := 1;
SELECT /*+ OPT_PARAM('_OPTIM_PEEK_USER_BINDS' 'TRUE') */
*
FROM XSOFT_T
WHERE FLAG = :B1
;
-- 7) V$SQL에서 SQL_ID 확인
SELECT SUBSTR(SQL_TEXT, 1, 30),
SQL_ID,
CHILD_NUMBER
FROM V$SQL
WHERE UPPER(SQL_TEXT) LIKE '%XSOFT_T%'
ORDER BY FIRST_LOAD_TIME DESC
;
SUBSTR(SQL_TEXT,1,30) SQL_ID CHILD_NUMBER
------------------------------ ------------- ------------
SELECT SUBSTR(SQL_TEXT, 1, 30) 56bqkx2bgmvfc 0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a 0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a 1
SELECT SUBSTR(SQL_TEXT, 1, 20) b3qmfm55u7trs 0
SELECT SUBSTR(SQL_TEXT, 1, 20) atu0kk85um7v1 0
SELECT SQL_TEXT, SQL_FU 5hx6usvks1r75 0
SELECT SQL_TEXT, SQL_FU 5hx6usvks1r75 1
SELECT /*+ OPT_PARAM('_OPTIM_P 35x0sx3dxtt5r 0
EXPLAIN PLAN FOR SELECT /*+ OP bfa0u88cz3n78 0
select /*+ no_parallel(t) no_p 8zw3fzkk0gvwc 0
select substrb(dump(val,16,0,3 4agz0p1vt39fw 0
select /*+ no_parallel(t) no_ 2gckc3xgdabky 0
BEGIN DBMS_STATS.GATHER_TA 4qt4pb05pwh74 0
SELECT topology FROM SDO_T 997gattrj5nag 0
*/
-- 8) Runtime Plan 확인
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('35x0sx3dxtt5r',NULL,'ADVANCED ALLSTATS COST LAST'));
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| XSOFT_T | 1 | 333 | 999 | 3 (0)| 00:00:01 | 9 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / XSOFT_T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "XSOFT_T"@"SEL$1")
END_OUTLINE_DATA
*/
-- 9) STATISTICS_LEVEL 비활성화
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL ;
| Property | Descriotion |
|---|---|
| Parameter type | String |
| Syntax | CURSOR_SHARING = ( SIMILAR , EXACT , FORCE ) |
| Default value | EXACT |
| Modifiable | ALTER SESSION, ALTER SYSTEM |
| Basic | No |
1) 히스토그램과 조인
SELECT T1.V1,
T2.V1
FROM T1,
T2
WHERE T1.N2 = 99
AND T1.N1 = T2.N1
;
이 SQL에서 T1.N1, T2.N1 컬럼에 히스토그램이 있더라도 실제로 값이 들어오는 곳은 히스토그램이 없는 T1.N2이기 때문에 N1 값은 바인드 변수로 인식되어 히스토그램을 사용하지 못한다.
2) 히스토그램과 분산 쿼리
-- 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. 테이블 T1 생성
DROP TABLE T1;
DROP TABLE GENERATOR;
CREATE TABLE GENERATOR AS
SELECT ROWNUM ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 2000
;
CREATE TABLE T1 (SKEW NOT NULL,
PADDING)
AS
SELECT /*+ ORDERED USE_NL(V2) */
V1.ID,
RPAD('X',400)
FROM GENERATOR V1,
GENERATOR V2
WHERE V1.ID <= 80
AND V2.ID <= 80
AND V2.ID <= V1.ID
ORDER BY V2.ID,V1.ID
;
CREATE INDEX T1_I1 ON T1(SKEW);
-- 2. 테이블 T1 데이터 조회
SELECT COUNT(*)
FROM T1
;
COUNT(*)
----------
3240
SELECT SKEW,
COUNT(*)
FROM T1
GROUP BY SKEW
ORDER BY SKEW
;
SKEW COUNT(*)
---------- ----------
1 1
2 2
.....................
79 79
80 80
-- 3. 테이블 T1 Height-Balance Histogram 포함된 통계정보 생성
-- Height-Balance Histogram 생성 시 Rows가 80개인 생태에서 Bucket을 120개 요청했지만 결과는 80개 로우만 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 120');
END;
/
SELECT ROWNUM,
ENDPOINT_NUMBER,
ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE COLUMN_NAME = 'SKEW'
AND TABLE_NAME = 'T1'
ORDER BY ENDPOINT_NUMBER
;
ROWNUM ENDPOINT_NUMBER ENDPOINT_VALUE
------ --------------- --------------
1 1 1
2 3 2
.....................................
79 3160 79
80 3240 80
;
-- 4. USER_TAB_HISTOGRAMS 조회하여 분포도 확인
SELECT ENDPOINT_VALUE ROW_VALUE,
CURR_NUM - NVL(PREV_NUM, 0) ROW_COUNT
FROM (SELECT ENDPOINT_VALUE,
ENDPOINT_NUMBER CURR_NUM,
LAG(ENDPOINT_NUMBER, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV_NUM
FROM USER_TAB_HISTOGRAMS
WHERE COLUMN_NAME = 'SKEW'
AND TABLE_NAME = 'T1')
ORDER BY ENDPOINT_VALUE
;
ROW_VALUE ROW_COUNT
---------- ----------
1 1
2 2
.....................
79 79
80 80
;
| 조건절 | 설명 | CBO | 사람 |
|---|---|---|---|
| SKEW = 40 | 상수 값 | 40 | 40 |
| SKEW = 40.5 | 존재하기는 않지만 범위에 포함 | 1 | 0 |
| SKEW BETWEEN 21 AND 24 | 범위 내에 포함되는 BETWEEN 조건 | 90 | 90 |
| SKEW BETWEEN 20.5 AND 24.5 | 범위 내에 포함되는 BETWEEN 조건 | 90 | 90 |
| SKEW BETWEEN 1 AND 2 | 시작점을 포함한 BETWEEN 조건 | 3 | 3 |
| SKEW BETWEEN 79 AND 80 | 종료점을 포함한 BETWEEN 조건 | 159 | 159 |
| SKEW > 4 AND SKEW < 8 | ">" AND "<" 범위 조건 | 18 | 18 |
| SKEW = -10 | 최소값 미만 | 1 | 0 |
| SKEW = 100 | 최대값 미만 | 1 | 0 |
| SKEW BETWEEN -5 AND -3 | 최소값 미만의 범위 조건 | 1 | 0 |
| SKEW BETWEEN 92 AND 94 | 최대값 미만의 범위 조건 | 1 | 0 |
| SKEW BETWEEN 79 AND 82 | 경계 값에 걸치는 범위 조건 | 159 | 159 |
| SKEW = :B1 | 바인드 변수 | 41 | ??? |
| SKEW BETWEEN :B1 AND :B2 | 바인드 변수의 BETWEEN 조건 | 8 | ??? |
-- 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. SKEW = 40
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW = 40
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 16160 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 40 | 16160 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=40)
;
-- 2. SKEW = 40.5
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW = 40.5
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 404 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 404 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=40.5)
;
-- 3. SKEW BETWEEN 21 AND 24
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN 21 AND 24
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90 | 36360 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 90 | 36360 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"<=24 AND "SKEW">=21)
;
-- 4. SKEW BETWEEN 20.5 AND 24.5
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN 20.5 AND 24.5
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90 | 36360 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 90 | 36360 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"<=24.5 AND "SKEW">=20.5)
;
-- 5. SKEW BETWEEN 1 AND 2
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN 1 AND 2
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90 | 36360 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 90 | 36360 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"<=24.5 AND "SKEW">=20.5)
;
-- 6. SKEW BETWEEN 79 AND 80
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN 79 AND 80
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 158 | 63832 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 158 | 63832 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW">=79 AND "SKEW"<=80)
;
-- 7. SKEW > 4 AND SKEW < 8
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW > 4 AND SKEW < 8
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 7272 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 18 | 7272 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"<8 AND "SKEW">4)
;
-- 8. SKEW = -10
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW = -10
;
@xplan
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 404 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 404 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW"=(-10))
;
-- 9. SKEW= 100
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW = 100
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 404 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 404 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=100)
;
-- 10. SKEW BETWEEN -5 AND -3
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN -5 AND -3
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 404 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 404 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"<=(-3) AND "SKEW">=(-5))
;
-- 11. SKEW BETWEEN 92 AND 94
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN 92 AND 94
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 404 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 404 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW">=92 AND "SKEW"<=94)
;
-- 12. SKEW BETWEEN 79 AND 82
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN 79 AND 82
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 158 | 63832 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 158 | 63832 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW">=79 AND "SKEW"<=82)
;
-- 13. SKEW = :B1
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW = :B1
;
@xplan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 16564 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 41 | 16564 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=TO_NUMBER(:B1))
;
-- 14. SKEW BETWEEN :B1 AND :B2
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE SKEW BETWEEN :B1 AND :B2
;
@xplan
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 3232 | 56 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 8 | 3232 | 56 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
2 - filter("SKEW">=TO_NUMBER(:B1) AND "SKEW"<=TO_NUMBER(:B2))
;
{INFO:TITLE="0의 Cardinality"}
일반적으로, 옵티마이저는 카디널리티 계산 과정에서 0이 전달되는 것을 허용하지 않는다.
계산된 카디널리티가 0이 될 때마다, 옵티마이저는 안전하게 1의 카디널리티를 사용한다.
하지만 조건절이 '1 = 0'처럼 논리적으로 모순된 경우에는 0 카디널리티가 허용된다.
{INFO}
1) 통계정보 수집 시 버킷 개수는 254 이하를 사용해야 값을 놓칠 위험히 적다.
2) 데이터의 중요한 값이 변경되면, 히스토그램을 재생성해 주어야 한다.
3) Frequency Histogram은 바인드 변수를 사용하게 되면 1/NDV 값을 사용한다.
4) 적절한 히스토그램이 존재할 경우, CBO는 범위조건이 LOW/HIGH 값을 벗어날 때 더 잘 처리한다.
책을 통해...
1) 만약 날짜타입의 컬럼을 이용하여 특정 기간의 데이터를 조회하였을 때 통계정보가 있을 경우
옵티마이저는 실 데이터와 근접한 예상 로우를 실행계획에서 보여준다. 하지만 이를 VARCAHR2
타입이나 NUMBER 타입으로 동일한 의미를 부여한 뒤 조회하였을 경우 옵티마이저는 정확한
계산을 할 수 없다.
2) 그 이유는 날짜타입일 경우 2009/12/31 ~ 2010/00/01 사이에 하루만 있다는 것을 알 수 있지만
'20091231 ~ 20100001'와 같은 VARCHAR2 타입일 경우 이 사이에 '20091232, 20091233, ...'
같은 값이 있을 것으로 예상하기 때문에 정확한 값을 추측할 수 없다.
3) 그러므로 가급적 사용 용도에 맞는 데이터타입을 설정하여 사용해야 하고 만약 그럴 수 없다면
히스토그램을 생성하여 '20091231 ~ 20100001' 사이에 데이터가 거의 없다는 정보를 옵티마이저에게
제공해야 한다.
4) 아래는 이에 대해 테스트 한 내용니다.
DROP TABLE T1;
CREATE TABLE T1 (D1 DATE,
N1 NUMBER(8),
V1 VARCHAR2(8))
;
INSERT INTO T1
SELECT D1,
TO_NUMBER(TO_CHAR(D1, 'YYYYMMDD')),
TO_CHAR(D1, 'YYYYMMDD')
FROM (SELECT TO_DATE('31-DEC-1999') + ROWNUM D1
FROM ALL_OBJECTS
WHERE ROWNUM <= 1827)
;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE D1 BETWEEN TO_DATE('30-DEC-2002', 'DD-MON-YYYY')
AND TO_DATE('05-JAN-2003', 'DD-MON-YYYY')
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 184 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 184 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D1">=TO_DATE('2002-12-30 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "D1"<=TO_DATE('2003-01-05 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
;
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE N1 BETWEEN 20021230
AND 20030105
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 9108 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 396 | 9108 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1">=20021230 AND "N1"<=20030105)
;
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE V1 BETWEEN '20021230'
AND '20030105'
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 9108 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 396 | 9108 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1">='20021230' AND "V1"<='20030105')
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 120');
END;
/
SELECT ROWNUM BUCKET,
PREV LOW_VAL,
CURR HIGH_VAL,
CURR - PREV WIDTH,
ROUND((1827 / 120) / (CURR - PREV), 4) 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 = 'N1')
WHERE PREV IS NOT NULL
ORDER BY CURR
;
BUCKET LOW_VAL HIGH_VAL WIDTH HEIGHT
------ ---------- ---------- ---------- --------------
1 20000101 20000116 15 1.015
2 20000116 20000201 85 .179
.......................................................
21 20001115 20001201 86 .177
22 20001201 20001217 16 .952
23 20001217 20010102 8885 .002
24 20010102 20010118 16 .952
25 20010118 20010203 85 .179
.......................................................
45 20011117 20011202 85 .179
46 20011202 20011217 15 1.015
47 20011217 20020101 8884 .002
48 20020101 20020116 15 1.015
49 20020116 20020131 15 1.015
.......................................................
70 20021127 20021212 85 .179
71 20021212 20021227 15 1.015
72 20021227 20030111 8884 .002
73 20030111 20030126 15 1.015
.......................................................
94 20031122 20031207 85 .179
95 20031207 20031222 15 1.015
96 20031222 20040106 8884 .002
97 20040106 20040121 15 1.015
98 20040121 20040205 84 .181
.......................................................
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE D1 BETWEEN TO_DATE('30-DEC-2002', 'DD-MON-YYYY')
AND TO_DATE('05-JAN-2003', 'DD-MON-YYYY')
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 138 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 6 | 138 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D1">=TO_DATE('2002-12-30 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "D1"<=TO_DATE('2003-01-05 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE N1 BETWEEN 20021230
AND 20030105
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 345 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 15 | 345 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1">=20021230 AND "N1"<=20030105)
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE V1 BETWEEN '20021230'
AND '20030105'
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 345 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 15 | 345 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1">='20021230' AND "V1"<='20030105')
-- 1. 테이블 생성
DROP TABLE GENERATOR;
CREATE TABLE GENERATOR AS
SELECT ROWNUM ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 2000
;
-- 1-1) 디폴트 값 있는 T1
CREATE TABLE T1
NOLOGGING
PCTFREE 0
AS
SELECT /*+ ORDERED USE_NL(V2) */
DECODE(MOD(ROWNUM - 1, 1000),
0,
TO_DATE('31-DEC-4000'),
TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100)) DATE_CLOSED
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 1827 * 100
;
-- 1-2) 디폴트 값 없는 T2
CREATE TABLE T2
NOLOGGING
PCTFREE 0
AS
SELECT /*+ ORDERED USE_NL(V2) */
TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100) DATE_CLOSED
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 1827 * 100
;
-- 2. 데이터 조회
-- 2-1) 디폴트 값 있는 T1
SELECT DATE_CLOSED, COUNT(*)
FROM (SELECT /*+ ORDERED USE_NL(V2) */
DECODE(MOD(ROWNUM - 1, 1000),
0,
TO_DATE('31-DEC-4000'),
TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100)) DATE_CLOSED
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 1827 * 100)
GROUP BY DATE_CLOSED
ORDER BY 1 DESC
;
DATE_CLOSED COUNT(*)
------------ ----------
4000-12-31 183
2004-12-31 100
2004-12-30 100
2004-12-29 100
.......................
2000-01-04 100
2000-01-03 100
2000-01-02 100
2000-01-01 99
;
-- 2-2) 디폴트 값 없는 T2
SELECT DATE_CLOSED, COUNT(*)
FROM (SELECT /*+ ORDERED USE_NL(V2) */
TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100) DATE_CLOSED
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 1827 * 100)
GROUP BY DATE_CLOSED
ORDER BY 1 DESC
;
DATE_CLOSED COUNT(*)
------------ ----------
2004-12-31 100
2004-12-30 100
2004-12-29 100
.......................
2000-01-03 100
2000-01-02 100
2000-01-01 100
;
-- 3. 통계정보 생성
-- 3-1) 디폴트 값 있는 T1
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
-- 3-2) 디폴트 값 없는 T2
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T2',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
-- 4. 통계정보 확인
-- 4-1) 디폴트 값 있는 T1
SELECT COLUMN_NAME,
NUM_DISTINCT,
DENSITY
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'T1'
;
COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------------------------------------ ------------ ----------
DATE_CLOSED 1828 .000547046
;
-- 4-2) 디폴트 값 없는 T2
SELECT COLUMN_NAME,
NUM_DISTINCT,
DENSITY
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'T2'
;
COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------------------------------------ ------------ ----------
DATE_CLOSED 1827 .000547345
-- 5. EXPLAIN PLAN
-- 5-1) 디폴트 값 있는 T1
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE DATE_CLOSED BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY')
AND TO_DATE('31-DEC-2003', 'DD-MON-YYYY')
;
@XPLAN;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 291 | 2328 | 89 (5)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 291 | 2328 | 89 (5)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_CLOSED"<=TO_DATE('2003-12-31 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "DATE_CLOSED">=TO_DATE('2003-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
;
-- 공식
Cardinality = base cardinality * ((required range) / (column high value - column low value) + N / num_distinct)
= 182700 * ((2003년 12월 31일 - 2003년 1월 1일) / (4000년 12월 31일 - 2000년 1월 1일) + 2 / 1828)
= 182700 * (364 / 730850 + 0.001094092)
= 182700 * 0.001592142
= 290
-- 5-2) 디폴트 값 없는 T2
EXPLAIN PLAN FOR
SELECT *
FROM T2
WHERE DATE_CLOSED BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY')
AND TO_DATE('31-DEC-2003', 'DD-MON-YYYY')
;
@XPLAN;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36620 | 286K| 89 (5)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 36620 | 286K| 89 (5)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_CLOSED">=TO_DATE('2003-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE('2003-12-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
;
-- 공식
Cardinality = base cardinality * ((required range) / (column high value - column low value) + N / num_distinct)
= 182700 * ((2003년 12월 31일 - 2003년 1월 1일) / (2004년 12월 31일 - 2000년 1월 1일) + 2 / 1828)
= 182700 * (364 / 1826 + 0.001094092)
= 182700 * 0.200436918
= 36619.82492
;
-- 6. T1 테이블 히스토그램 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 11');
END;
/
-- 7. 데이터 분포 확인
SELECT ROWNUM BUCKET,
TO_CHAR(TO_DATE(PREV, 'J'), 'DD-MON-YYYY') LOW_VAL,
TO_CHAR(TO_DATE(CURR, 'J'), 'DD-MON-YYYY') HIGH_VAL,
CURR - PREV WIDTH,
ROUND((182700 / 11) / (CURR - PREV), 4) 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 = 'DATE_CLOSED')
WHERE PREV IS NOT NULL
ORDER BY CURR
;
BUCKET LOW_VAL HIGH_VAL WIDTH HEIGHT
---------- ---------------------------------- ---------------------------------- ---------- ----------
1 01-JAN-2000 15-JUN-2000 166 100.0548
2 15-JUN-2000 28-NOV-2000 166 100.0548
3 28-NOV-2000 13-MAY-2001 166 100.0548
4 13-MAY-2001 27-OCT-2001 167 99.4556
5 27-OCT-2001 11-APR-2002 166 100.0548
6 11-APR-2002 24-SEP-2002 166 100.0548
7 24-SEP-2002 09-MAR-2003 166 100.0548
8 09-MAR-2003 23-AUG-2003 167 99.4556
9 23-AUG-2003 05-FEB-2004 166 100.0548
10 05-FEB-2004 20-JUL-2004 166 100.0548
11 20-JUL-2004 31-DEC-4000 729188 .0228
-- 8. T1 테이블 다시 실행계획 검토
EXPLAIN PLAN FOR
SELECT *
FROM T1
WHERE DATE_CLOSED BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY')
AND TO_DATE('31-DEC-2003', 'DD-MON-YYYY')
;
@XPLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36320 | 283K| 89 (5)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 36320 | 283K| 89 (5)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_CLOSED">=TO_DATE('2003-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE('2003-12-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))