Oracle Histogram内部实现学习
| 版本 | 修订人 | 修订日期 | 备注 |
|---|---|---|---|
| v0.1 | yangze.yz | 2016-10-29 | Oracle Histogram实现研究 |
我们可以通过Trace日志来查看Oracle收集统计信息的具体过程。
**注：
在Oracle 12c开始，在默认AUTO_SAMPLE_SIZE情况下，NDV等分析是通过FULL TABLE SCAN。
在12c之前，AUTO_SAMPLE_SIZE不会直接做full table scan分析NDV，而是使用采样方式。
设置采样比方式的内部实现，和12c之前版本的统计方式是相近的。**
Trace设置
Trace的设置是使用bit flag，各个bit对应的意义如下。
1 (DSC_DBMS_OUTPUT_TRC); // output the trace log, not into trace file
2 (DSC_SESSION_TRC); // trace in session only, will not modify the value of TRACE parameter
4 (DSC_TAB_TRC); // trace log when gathering table stats data
8 (DSC_IND_TRC); // trace log when gathering index stats data
16 (DSC_COL_TRC); // trace log when gathering column stats data
32 (DSC_AUTOST_TRC); // save session state log into sys.stats_target$_log
64 (DSC_SCALING_TRC); // trace the scaling log
128 (DSC_ERROR_TRC); // trace the errors/exceptions
256 (DSC_DUBIOUS_TRC); // trace the dubious stats data
512 (DSC_AUTOJOB_TRC); // trace the auto gathering job's event & error
1024 (DSC_PX_TRC); // trace the parallel log
2048 (DSC_Q_TRC); // dump the queries during gathering stats data
4096 (DSC_CCT_TRC); // trace the internal process of gathering MV stats data
8192 (DSC_DIFFST_TRC); // trace the differences of stats data before/after gathering通过下面语句设置trace level。
exec dbms_stats.set_param('TRACE', 2048+4+64+16+8+4096);设置trace 日志文件名。默认trace文件名为 instancename_ora_ spid.trc
alter session set tracefile_identifier=stats_trace; 在trace日志目录下查看trace日志
app/oracle/diag/rdbms/$instance/$instance/trace/AUTO_SAMPLE_SIZE统计信息收集实例
frequency histogram与舍弃histogram
Param信息
DBMS_STATS: Start gather table stats -- tabname: T
DBMS_STATS: job name:
DBMS_STATS: |--> Operation id: 501
DBMS_STATS: gather_table_stats:
<params><param name="block_sample" val="FALSE"/>
<param name="cascade" val="NULL"/>
<param name="concurrent" val="FALSE"/><param name="degree" val="NULL"/>
<param name="estimate_percent" val="DBMS_STATS.AUTO_SAMPLE_SIZE"/>
<param name="force" val="FALSE"/><param name="granularity" val="AUTO"/>
<param name="method_opt" val="FOR ALL COLUMNS SIZE AUTO"/>
<param name="no_invalidate" val="NULL"/>
<param name="ownname" val="SYS"/>
<param name="partname" val=""/>
<param name="reporting_mode" val="FALSE"/>
<param name="statid" val=""/>
<param name="statown" val=""/>
<param name="stattab" val=""/>
<param name="stattype" val="DATA"/>
<param name="tabname" val="T"/></params>初步统计信息收集，表的ROW COUNT，COLUMNS的MIN、MAX、NOT NULL COUNT
SELECT
/*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
TO_CHAR (COUNT("C1")),
SUBSTRB (
DUMP (MIN("C1"), 16, 0, 64),
1,
240
),
SUBSTRB (
DUMP (MAX("C1"), 16, 0, 64),
1,
240
),
TO_CHAR (COUNT("C2")),
SUBSTRB (
DUMP (MIN("C2"), 16, 0, 64),
1,
240
),
SUBSTRB (
DUMP (MAX("C2"), 16, 0, 64),
1,
240
),
COUNT (ROWIDTOCHAR(ROWID))
FROM
"SYS"."T" /* TOPN,NIL,NIL,TOPN,NIL,NIL,RWID,U254,U254U*/分析TOP N VALUES for COLUMNS
DBMS_STATS: start processing top n values for column "C1"
DBMS_STATS: >> frequency histograms is not feasible
(topn_values is null), skip!
/*C1 通过top n values分析，frequency Histogram不适合*/
DBMS_STATS: start processing top n values for column "C2"
DBMS_STATS: Parsing topn values..
DBMS_STATS: Extracted 10 rowid-freq pairs.
/*C2有10个top n values*/获取frequency histogram的top n values
SELECT
/*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
SUBSTRB (DUMP("C2", 16, 0, 64), 1, 240) val,
ROWIDTOCHAR (ROWID) rwid
FROM
"SYS"."T" T
WHERE
ROWID IN (
CHARTOROWID ('AAAWduAABAAAYD5AAA'),
CHARTOROWID ('AAAWduAABAAAYD5AAB'),
CHARTOROWID ('AAAWduAABAAAYD5AAC'),
CHARTOROWID ('AAAWduAABAAAYD5AAD'),
CHARTOROWID ('AAAWduAABAAAYD5AAE'),
CHARTOROWID ('AAAWduAABAAAYD5AAF'),
CHARTOROWID ('AAAWduAABAAAYD5AAG'),
CHARTOROWID ('AAAWduAABAAAYD5AAH'),
CHARTOROWID ('AAAWduAABAAAYD5AAI'),
CHARTOROWID ('AAAWduAABAAAYD5AAJ')
)
ORDER BY
"C2"frequency histogram的列分析
DBMS_STATS: removal_count: -244 total_nonnull_rows: 9999 mnb: 254
DBMS_STATS: hist_type in exec_get_topn: 1024 ndv:10 mnb:254
DBMS_STATS: Evaluating frequency histogram for col: "C2"
DBMS_STATS: number of values = 10, max # of buckects = 254, pct = 100, ssize = 9999
DBMS_STATS: csr.hreq: 0 Histogram gathering flags: 1035
DBMS_STATS: done_hist in process_topn: TRUE csr.ccnt: 1
DBMS_STATS: Mark column "C2" as top N computed
DBMS_STATS: no histogram: setting density to 1/ndv (.0001000100010001000100010001000100010001)
DBMS_STATS: Skip topn computed column "C2" numhist: 1
DBMS_STATS: Iteration: 1 numhist: 1
DBMS_STATS: Iteration 1, percentage 100 nblks: 20不适合frequency histogram的列分析
首先尝试hybrid histogram。但是由于c1列NDV 9999，表记录数9999，因此放弃的直方图。
DBMS_STATS: Building Histogram for C1
DBMS_STATS: bktnum=-1, nnv=9999, snnv=9999, sndv=9999, est_ndv=9999, mnb=254
DBMS_STATS: Trying hybrid histogram
DBMS_STATS: Starting query at 23-AUG-16 08.43.47.125081000 PM +08:00
SELECT
SUBSTRB (DUMP(val, 16, 0, 64), 1, 240) ep,
freq,
cdn,
ndv,
(SUM(pop) OVER()) popcnt,
(SUM(pop * freq) OVER()) popfreq,
SUBSTRB (
DUMP (MAX(val) OVER(), 16, 0, 64),
1,
240
) maxval,
SUBSTRB (
DUMP (MIN(val) OVER(), 16, 0, 64),
1,
240
) minval
FROM
(
SELECT
val,
freq,
(SUM(freq) OVER()) cdn,
(COUNT(*) OVER()) ndv,
(
CASE
WHEN freq > ((SUM(freq) OVER()) / 254) THEN
1
ELSE
0
END
) pop
FROM
(
SELECT
/*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
"C1" val,
COUNT ("C1") freq
FROM
"SYS"."T" T
WHERE
"C1" IS NOT NULL
GROUP BY
"C1"
)
)
ORDER BY
val
DBMS_STATS: > cdn 9999, popFreq 0, popCnt 0,
bktSize 39.51778656126482213438735177865612648221, bktSzFrc .51778656126482213438735177865612648221
DBMS_STATS: Evaluating hybrid histogram: cht.count 254, mnb 254, ssize 9999, min_ssize 2500,
appr_ndv TRUE, ndv 9999, selNdv 0, selFreq 0, pct 100, avg_bktsize 39, csr.hreq FALSE, normalize TRUE
DBMS_STATS: Histogram gathering flags: 11
DBMS_STATS: Discarding histogram最终的表统计信息
最终我们得到COLUMNS的统计信息如下：
====================================================================================================
DBMS_STATS: Statistics from clist:
DBMS_STATS: ====================================================================================================
DBMS_STATS: Number of rows in the table = 9999
DBMS_STATS: COL_NAME ACL NV SNNVDV NDV SNDV DENS CCNT
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: C1 3.98E+00 0 9999 9999 9999 NULL 0
DBMS_STATS: min: Typ=2 Len=2: c1,2
DBMS_STATS: max: Typ=2 Len=3: c2,64,64
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: COL_NAME ACL NV SNNVDV NDV SNDV DENS CCNT
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: C2 2.9E+00 0 9999 10 10 .00005000 1
DBMS_STATS: min: Typ=2 Len=1: 80
DBMS_STATS: max: Typ=2 Len=2: c1,a
DBMS_STATS: Histograms:
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: BVAL RPCNT EAVAL ENVAL EDVAL
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: 999 80 0 Typ=2 Len=1: 80
DBMS_STATS: 1999 C102 1 Typ=2 Len=2: c1,2
DBMS_STATS: 2999 C103 2 Typ=2 Len=2: c1,3
DBMS_STATS: 3999 C104 3 Typ=2 Len=2: c1,4
DBMS_STATS: 4999 C105 4 Typ=2 Len=2: c1,5
DBMS_STATS: 5999 C106 5 Typ=2 Len=2: c1,6
DBMS_STATS: 6999 C107 6 Typ=2 Len=2: c1,7
DBMS_STATS: 7999 C108 7 Typ=2 Len=2: c1,8
DBMS_STATS: 8999 C109 8 Typ=2 Len=2: c1,9
DBMS_STATS: 9999 C10A 9 Typ=2 Len=2: c1,a
DBMS_STATS: Need Actual Values (DSC_EAVS)
DBMS_STATS: Histogram Type: FREQUENCY Data Type: 2
DBMS_STATS: Histogram Flags: 4100 Histogram Gathering Flags: 1290
DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: arlen = 7 ssize = 9999
DBMS_STATS: prepare reporting structures...
DBMS_STATS: End of construct analyze using sql.TOP-FREQUENCY Histogram
分析top n values
DBMS_STATS: start processing top n values for column "C1"
DBMS_STATS: Parsing topn values..
DBMS_STATS: Extracted 200 rowid-freq pairs.
DBMS_STATS: topn sql (len: 7106):
/*200个top n values*/
SELECT
/*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
SUBSTRB (DUMP("C1", 16, 0, 64), 1, 240) val,
ROWIDTOCHAR (ROWID) rwid
FROM
"SYS"."T" T
WHERE
ROWID IN (
CHARTOROWID ('AAAWduAABAAAYD5AAA'),
CHARTOROWID ('AAAWduAABAAAYD6AF9'),
CHARTOROWID ('AAAWduAABAAAYD8ACh'),
CHARTOROWID ('AAAWduAABAAAYaYAJh'),
.....
CHARTOROWID ('AAAWduAABAAAYaZADg'),
CHARTOROWID ('AAAWduAABAAAYaZADh'),
CHARTOROWID ('AAAWduAABAAAYbVAKI')
)
ORDER BY
"C1"结果分析
DBMS_STATS: remove last bucket: Typ=2 Len=3: c2,64,5f add: Typ=2 Len=3: c2,64,64
DBMS_STATS: removal_count: 1 total_nonnull_rows: 119995 mnb: 200
DBMS_STATS: adjusted coverage: .997
DBMS_STATS: hist_type in exec_get_topn: 2048 ndv:506 mnb:200
DBMS_STATS: Evaluating frequency histogram for col: "C1"
DBMS_STATS: number of values = 200, max # of buckects = 200, pct = 100, ssize = 119995
DBMS_STATS: csr.hreq: 1 Histogram gathering flags: 2063
DBMS_STATS: done_hist in process_topn: TRUE csr.ccnt: 1
DBMS_STATS: Mark column "C1" as top N computed
DBMS_STATS: no histogram: setting density to 1/ndv (0)
DBMS_STATS: Skip topn computed column "C1" numhist: 0
=============================================================================
DBMS_STATS: Statistics from clist:
DBMS_STATS: =================================================================
DBMS_STATS: Number of rows in the table = 119995
DBMS_STATS: COL_NAME ACL NV SNNVDV NDV SNDV DENS CCNT
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: C1 3.004E+00 0 119995 506 506 .00000416 1
DBMS_STATS: min: Typ=2 Len=2: c1,2
DBMS_STATS: max: Typ=2 Len=3: c2,64,64
DBMS_STATS: Histograms:
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: BVAL RPCNT EAVAL ENVAL EDVAL
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: 20998 C102 1 Typ=2 Len=2: c1,2
DBMS_STATS: 41997 C103 2 Typ=2 Len=2: c1,3
DBMS_STATS: 111995 C104 3 Typ=2 Len=2: c1,4
DBMS_STATS: 115995 C105 4 Typ=2 Len=2: c1,5
DBMS_STATS: 118995 C106 5 Typ=2 Len=2: c1,6
DBMS_STATS: 119495 C107 6 Typ=2 Len=2: c1,7
DBMS_STATS: 119496 C26002 9501 Typ=2 Len=3: c2,60,2
DBMS_STATS: 119497 C26007 9506 Typ=2 Len=3: c2,60,7
DBMS_STATS: 119498 C26008 9507 Typ=2 Len=3: c2,60,8
DBMS_STATS: 119499 C2600B 9510 Typ=2 Len=3: c2,60,b
........
DBMS_STATS: 119687 C2645E 9993 Typ=2 Len=3: c2,64,5e
DBMS_STATS: 119688 C2645F 9994 Typ=2 Len=3: c2,64,5f
DBMS_STATS: 119689 C26464 9999 Typ=2 Len=3: c2,64,64
DBMS_STATS: Need Actual Values (DSC_EAVS)
DBMS_STATS: Histogram Type: TOP-FREQUENCY Data Type: 2
DBMS_STATS: Histogram Flags: 8196 Histogram Gathering Flags: 2318
DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1hybrid histogram
Frequency Histogram不合适
DBMS_STATS: start processing top n values for column "C1"
DBMS_STATS: >> frequency histograms is not feasible
(topn_values is null), skip!尝试hybrid histogram
DBMS_STATS: bktnum=-1, nnv=9999, snnv=9999, sndv=8002, est_ndv=8002, mnb=254
DBMS_STATS: Trying hybrid histogram
如前面所述sql
DBMS_STATS: > cdn 9999, popFreq 1999, popCnt 2, bktSize 27.89243027888446215139442231075697211155, bktSzFrc .89243027888446215139442231075697211155
DBMS_STATS: Evaluating hybrid histogram: cht.count 254, mnb 254, ssize 9999, min_ssize 2500, appr_ndv TRUE, ndv 8002, selNdv 2, selFreq 1999, pct 100, avg_bktsize 39, csr.hreq FALSE, normalize TRUE
DBMS_STATS: scaled density=.000111098766803688479057882457504721697589,invden=9001.000000000000000000000000000000000013
DBMS_STATS: Histogram gathering flags: 15
DBMS_STATS: Accepting histogram
存在两个popular数据，因此采用hybrid histogram。从下面可以看到1和2两个值是popular value，frequency分别是999和1000。
====================================================================================================
DBMS_STATS: Statistics from clist:
DBMS_STATS: ====================================================================================================
DBMS_STATS: Number of rows in the table = 9999
DBMS_STATS: COL_NAME ACL NV SNNVDV NDV SNDV DENS CCNT
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: C1 3.792E+00 0 9999 8002 8002 .000111 1
DBMS_STATS: min: Typ=2 Len=2: c1,2
DBMS_STATS: max: Typ=2 Len=3: c2,64,64
DBMS_STATS: Histograms:
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: BVAL RPCNT EAVAL ENVAL EDVAL
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: 999 999 C102 1 Typ=2 Len=2: c1,2
DBMS_STATS: 1999 1000 C103 2 Typ=2 Len=2: c1,3
DBMS_STATS: 2027 1 C2151C 2027 Typ=2 Len=3: c2,15,1c
DBMS_STATS: 2055 1 C21538 2055 Typ=2 Len=3: c2,15,38
DBMS_STATS: 2083 1 C21554 2083 Typ=2 Len=3: c2,15,54
......
DBMS_STATS: 9000 1 C25B 9000 Typ=2 Len=2: c2,5b
DBMS_STATS: 9999 1 C26464 9999 Typ=2 Len=3: c2,64,64
DBMS_STATS: Need Actual Values (DSC_EAVS)
DBMS_STATS: Histogram Type: HYBRID Data Type: 2
DBMS_STATS: Histogram Flags: 4 Histogram Gathering Flags: 14
DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1
DBMS_STATS: ----------------------------------------------------------------------------------------------------指定采样率，height-balanced histogram
params
指定estimate_percent 10%
DBMS_STATS: gather_table_stats:
<params><param name="block_sample" val="FALSE"/>
<param name="cascade" val="NULL"/>
<param name="concurrent" val="FALSE"/>
<param name="degree" val="NULL"/>
<param name="estimate_percent" val="10"/> //指定estimate_percent 10%
<param name="force" val="FALSE"/>
<param name="granularity" val="AUTO"/>
<param name="method_opt" val="FOR ALL COLUMNS SIZE AUTO"/>
<param name="no_invalidate" val="NULL"/>
<param name="ownname" val="SYS"/>
<param name="partname" val=""/>
<param name="reporting_mode" val="FALSE"/>
<param name="statid" val=""/>
<param name="statown" val=""/>
<param name="stattab" val=""/>
<param name="stattype" val="DATA"/>
<param name="tabname" val="T"/>
</params>建立临时表
CREATE GLOBAL TEMPORARY TABLE sys.ora_temp_1_ds_20012 sharing = NONE ON COMMIT PRESERVE ROWS CACHE NOPARALLEL
AS SELECT
/*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
"C1",
"C2"
FROM
"SYS"."T" SAMPLE (10.0000000000) T
WHERE
1 = 2
授权
grant select,insert on sys.ora_temp_1_ds_20012 to "SYS"插入采样数据
INSERT /*+ append */
INTO sys.ora_temp_1_ds_20012 SELECT
/*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
"C1",
"C2"
FROM
"SYS"."T" SAMPLE (10.0000000000) T
UNION ALL
SELECT
"C1",
"C2"
FROM
sys.ora_temp_1_ds_20012
WHERE
1 = 0初步分析
SELECT
/*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
COUNT (*),
COUNT ("C1"),
SUM (SYS_OP_OPNSIZE("C1")),
COUNT ("C2"),
SUM (SYS_OP_OPNSIZE("C2"))
FROM
sys.ora_temp_1_ds_20012 T列分析
NTILE函数：窗口函数，根据给出的分组数和数据记录，计算出每个分组的大小，
然后依次设置每条数据记录的分组编号；当前分组如果有空间就放入当前分组，否则放入下一个分组。
由于是采样分析，如果满足kkesdv放缩条件，则进行kkesdv放缩。
如果满足线性放缩条件则进行线性放缩。
**注：
在12c之前，AUTO_SAMPLE_SIZE不会直接做full table scan分析NDV，而是使用采样方式。
因此12c之前的AUTO_SAMPLE_SIZE方式，当小的采样比上述两种放缩都不满足的时候，则调高采样比。**
DBMS_STATS: Building Histogram for C1
DBMS_STATS: bktnum=-1, nnv=1226880, snnv=122688, sndv=, est_ndv=636345, mnb=254
DBMS_STATS: Trying height balanced histogram
SELECT
MIN (minbkt),
maxbkt,
SUBSTRB (
DUMP (MIN(val), 16, 0, 64),
1,
240
) minval,
SUBSTRB (
DUMP (MAX(val), 16, 0, 64),
1,
240
) 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_expand_table(t) index_rs(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
"C1" val,
NTILE (254) OVER (ORDER BY "C1") bkt
FROM
sys.ora_temp_1_ds_20012 T
WHERE
"C1" IS NOT NULL
)
GROUP BY
val
)
GROUP BY
maxbkt
ORDER BY
maxbkt
DBMS_STATS: Evaluating height-balanced histogram: cht.count 254, ssize 122688, min_ssize 2500, appr_ndv FALSE, popcnt 1026, popcntsq 1052676, pct 10, csr.hreq FALSE
DBMS_STATS: scaling density: pct=10,ssize=122688,popcnt=1026,ssizesq=1777800,popcntsq=1052676,slsv=0,invden=20584.7102785178810796498254091713968921
DBMS_STATS: kkesdv_p: sndv=20584.7102785178810796498254091713968921,snnv=122688,nnv=1226880,hind=0,slsv=0
DBMS_STATS: totcnt=1,popndv=0,lsv=0
DBMS_STATS: npndv=20624
DBMS_STATS: scaled density=.00004848719937936384794414274631497284716835,invden=20624
DBMS_STATS: Histogram gathering flags: 15采样下的频率直方图
DBMS_STATS: Building Histogram for C2
DBMS_STATS: bktnum=-1, nnv=1226880, snnv=122688, sndv=, est_ndv=10, mnb=254
DBMS_STATS: Trying frequency histogram
SELECT
SUBSTRB (DUMP(val, 16, 0, 64), 1, 240) ep,
cnt
FROM
(
SELECT
/*+ no_expand_table(t) index_rs(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */
"C2" val,
COUNT (*) cnt
FROM
sys.ora_temp_1_ds_20012 T
WHERE
"C2" IS NOT NULL
GROUP BY
"C2"
)
ORDER BY
val
DBMS_STATS: Evaluating frequency histogram for col: "C2"
DBMS_STATS: number of values = 10, max # of buckects = 254, pct = 10, ssize = 122688
DBMS_STATS: csr.hreq: 0 Histogram gathering flags: 1035
DBMS_STATS: Scaling NDV of C1
DBMS_STATS: Need larger sample: sndv = 116857, snnvdv = 122688, nnv = 1226880, ndv = 629530
DBMS_STATS: Scaling NDV of C2
DBMS_STATS: kkesdv_p: sndv=10,snnv=122688,nnv=1226880,hind=255,slsv=0
DBMS_STATS: cht.count=264
DBMS_STATS: Frequency Histogram
DBMS_STATS: kkesdv scaling or variant, ndv=10直方图结果
由于使用采样所以hybrid_enabled为FALSE
C1使用了height-balanced histogram，C2使用了frequency histogram。
DBMS_STATS: Start fill_cstats - hybrid_enabled: FALSE
DBMS_STATS: ====================================================================================================
DBMS_STATS: Statistics from clist:
DBMS_STATS: ====================================================================================================
DBMS_STATS: Number of rows in the table = 1226880
DBMS_STATS: COL_NAME ACL NV SNNVDV NDV SNDV DENS CCNT
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: C1 4.918E+00 0 122688 629530 116857 .00004848 1
DBMS_STATS: min: Typ=2 Len=2: c1,2
DBMS_STATS: max: Typ=2 Len=4: c3,64,64,64
DBMS_STATS: Histograms:
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: BVAL RPCNT EAVAL ENVAL EDVAL
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: 0 C102 1 Typ=2 Len=2: c1,2
DBMS_STATS: 1 C105 4 Typ=2 Len=2: c1,5
DBMS_STATS: 2 C107 6 Typ=2 Len=2: c1,7
DBMS_STATS: 4 C109 8 Typ=2 Len=2: c1,9
DBMS_STATS: 5 C10A 9 Typ=2 Len=2: c1,a
DBMS_STATS: 6 C10C 11 Typ=2 Len=2: c1,c
DBMS_STATS: 7 C20F05 1404 Typ=2 Len=3: c2,f,5
......
DBMS_STATS: 252 C3640404 990303 Typ=2 Len=4: c3,64,4,4
DBMS_STATS: 253 C364364A 995373 Typ=2 Len=4: c3,64,36,4a
DBMS_STATS: 254 C3646464 999999 Typ=2 Len=4: c3,64,64,64
DBMS_STATS: Need Actual Values (DSC_EAVS)
DBMS_STATS: Histogram Type: HEIGHT-BALANCED Data Type: 2
DBMS_STATS: Histogram Flags: 4 Histogram Gathering Flags: 14
DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: COL_NAME ACL NV SNNVDV NDV SNDV DENS CCNT
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: C2 2.902E+00 0 122688 10 10 .00000040 1
DBMS_STATS: min: Typ=2 Len=1: 80
DBMS_STATS: max: Typ=2 Len=2: c1,a
DBMS_STATS: Histograms:
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: BVAL RPCNT EAVAL ENVAL EDVAL
DBMS_STATS: ---------------------------------------------------------------------------------------------------
DBMS_STATS: 12001 80 0 Typ=2 Len=1: 80
DBMS_STATS: 24318 C102 1 Typ=2 Len=2: c1,2
DBMS_STATS: 36598 C103 2 Typ=2 Len=2: c1,3
DBMS_STATS: 49002 C104 3 Typ=2 Len=2: c1,4
DBMS_STATS: 61313 C105 4 Typ=2 Len=2: c1,5
DBMS_STATS: 73615 C106 5 Typ=2 Len=2: c1,6
DBMS_STATS: 86001 C107 6 Typ=2 Len=2: c1,7
DBMS_STATS: 98187 C108 7 Typ=2 Len=2: c1,8
DBMS_STATS: 110417 C109 8 Typ=2 Len=2: c1,9
DBMS_STATS: 122688 C10A 9 Typ=2 Len=2: c1,a
DBMS_STATS: Need Actual Values (DSC_EAVS)
DBMS_STATS: Histogram Type: FREQUENCY Data Type: 2
DBMS_STATS: Histogram Flags: 4100 Histogram Gathering Flags: 1034
DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: arlen = 8 ssize = 122688
DBMS_STATS: prepare reporting structures... 删除临时表
DBMS_STATS: truncate table sys.ora_temp_1_ds_20012
DBMS_STATS: drop table sys.ora_temp_1_ds_20012
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
