Oracle Histogram内部实现学习
来源：互联网 发布：数据库宝典 编辑：程序博客网 时间：2024/05/20 10:20
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文件名为
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: TDBMS_STATS: job name:DBMS_STATS: |--> Operation id: 501DBMS_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) rwidFROM "SYS"."T" TWHERE 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: 254DBMS_STATS: hist_type in exec_get_topn: 1024 ndv:10 mnb:254DBMS_STATS: Evaluating frequency histogram for col: "C2"DBMS_STATS: number of values = 10, max # of buckects = 254, pct = 100, ssize = 9999DBMS_STATS: csr.hreq: 0 Histogram gathering flags: 1035DBMS_STATS: done_hist in process_topn: TRUE csr.ccnt: 1DBMS_STATS: Mark column "C2" as top N computedDBMS_STATS: no histogram: setting density to 1/ndv (.0001000100010001000100010001000100010001)DBMS_STATS: Skip topn computed column "C2" numhist: 1DBMS_STATS: Iteration: 1 numhist: 1DBMS_STATS: Iteration 1, percentage 100 nblks: 20不适合frequency histogram的列分析
首先尝试hybrid histogram。但是由于c1列NDV 9999，表记录数9999，因此放弃的直方图。
DBMS_STATS: Building Histogram for C1DBMS_STATS: bktnum=-1, nnv=9999, snnv=9999, sndv=9999, est_ndv=9999, mnb=254DBMS_STATS: Trying hybrid histogramDBMS_STATS: Starting query at 23-AUG-16 08.43.47.125081000 PM +08:00SELECT 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 ) minvalFROM ( 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 valDBMS_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 = 9999DBMS_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: 2DBMS_STATS: Histogram Flags: 4100 Histogram Gathering Flags: 1290DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1DBMS_STATS: ----------------------------------------------------------------------------------------------------DBMS_STATS: arlen = 7 ssize = 9999DBMS_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) rwidFROM "SYS"."T" TWHERE 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,64DBMS_STATS: removal_count: 1 total_nonnull_rows: 119995 mnb: 200DBMS_STATS: adjusted coverage: .997DBMS_STATS: hist_type in exec_get_topn: 2048 ndv:506 mnb:200DBMS_STATS: Evaluating frequency histogram for col: "C1"DBMS_STATS: number of values = 200, max # of buckects = 200, pct = 100, ssize = 119995DBMS_STATS: csr.hreq: 1 Histogram gathering flags: 2063DBMS_STATS: done_hist in process_topn: TRUE csr.ccnt: 1DBMS_STATS: Mark column "C1" as top N computedDBMS_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 = 119995DBMS_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: 2DBMS_STATS: Histogram Flags: 8196 Histogram Gathering Flags: 2318DBMS_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=254DBMS_STATS: Trying hybrid histogram如前面所述sqlDBMS_STATS: > cdn 9999, popFreq 1999, popCnt 2, bktSize 27.89243027888446215139442231075697211155, bktSzFrc .89243027888446215139442231075697211155DBMS_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 TRUEDBMS_STATS: scaled density=.000111098766803688479057882457504721697589,invden=9001.000000000000000000000000000000000013DBMS_STATS: Histogram gathering flags: 15DBMS_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 = 9999DBMS_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: 2DBMS_STATS: Histogram Flags: 4 Histogram Gathering Flags: 14DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1DBMS_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 NOPARALLELAS 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) TWHERE 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) TUNION 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 C1DBMS_STATS: bktnum=-1, nnv=1226880, snnv=122688, sndv=, est_ndv=636345, mnb=254DBMS_STATS: Trying height balanced histogramSELECT 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) unqrepFROM ( 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 maxbktORDER 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 C2DBMS_STATS: bktnum=-1, nnv=1226880, snnv=122688, sndv=, est_ndv=10, mnb=254DBMS_STATS: Trying frequency histogramSELECT SUBSTRB (DUMP(val, 16, 0, 64), 1, 240) ep, cntFROM ( 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为FALSEC1使用了height-balanced histogram，C2使用了frequency histogram。DBMS_STATS: Start fill_cstats - hybrid_enabled: FALSEDBMS_STATS: ====================================================================================================DBMS_STATS: Statistics from clist:DBMS_STATS: ====================================================================================================DBMS_STATS: Number of rows in the table = 1226880DBMS_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: 2DBMS_STATS: Histogram Flags: 4 Histogram Gathering Flags: 14DBMS_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: 2DBMS_STATS: Histogram Flags: 4100 Histogram Gathering Flags: 1034DBMS_STATS: Incremental: FALSE Fix Control 13583722: 1DBMS_STATS: ----------------------------------------------------------------------------------------------------DBMS_STATS: arlen = 8 ssize = 122688DBMS_STATS: prepare reporting structures... 删除临时表
DBMS_STATS: truncate table sys.ora_temp_1_ds_20012DBMS_STATS: drop table sys.ora_temp_1_ds_20012阅读全文
0 0
- Oracle Histogram内部实现学习
- oracle histogram
- Oracle Join 内部实现区别
- Oracle Performance tuning - Histogram
- ORACLE柱状图(histogram)
- Oracle Histogram直方图解析
- Oracle Histogram -- 潜在危险
- Oracle Histogram 基础介绍
- OpenCV学习Histogram&equalizeHist&Lomography effect&Cartoonize effect实现
- 学习Oracle Library cache内部机制
- Oracle histograms -- interpreting frequency histogram
- NIO学习系列：缓冲区内部实现机制
- NIO学习系列：缓冲区内部实现机制
- COM学习笔记（二）CoCreateInstance具体内部实现
- Java泛型学习-理解泛型的内部实现
- Python学习之列表的内部实现详解
- POJ2136-Vertical Histogram（C语言实现）
- 基础学习笔记——Histogram Equalization
- 一些CSS小结
- Dubbo之——分布式服务子系统的划分
- 循环小程序——百分比和旋转小横线
- go 迭代string数组 go for string[]
- 快速排序算法
- Oracle Histogram内部实现学习
- 堆排序算法分析 和代码实现
- SQL解析利器General SQL Parser
- JQ实现手风琴菜单
- 线性表的链式存储
- 历史上mysql最经典的34个SQL语句（百看不厌)
- 汇编2——完整的例子集合
- JavaWeb开发知识总结(内省,MVC,事务)
- 【工具】C盘垃圾自动清理软件
