[20170612]FO途胜 ALL COLUMNS SIZE repeat(11g).txt

[20170612]FOR ALL COLUMNS SIZE repeat(11g).txt

–//后日看了https://jonathanlewis.wordpress.com/2017/06/01/histogram-upgrade-2/,提到了直方图的问题,
–//尤其是FO奥迪Q5 ALL COLUMNS SIZE repeat引起的标题,在有个别独特境况要注意.

1.环境:
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER



x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 – 64bit Production

create table t (id number,pad varchar2(20),flag varchar2(1));
insert into t select rownum,lpad(‘a’,20,’a’) ,’a’ from dual connect by
level<=2e4;
insert into t select rownum+2e4,lpad(‘d’,20,’d’) ,’d’ from dual connect
by level<=2;
commit ;

SCOTT@book> SELECT flag, COUNT(*) FROM t GROUP BY flag;
F   COUNT(*)


d          2
a      20000

SCOTT@book> exec dbms_stats.Gather_table_stats(ownname => user,
tabname => ‘T’,method_opt => ‘FOR ALL COLUMNS ‘);
PL/SQL procedure successfully completed.

select * from DBA_TAB_COL_STATISTICS where owner=user and
table_name=’T’ and column_name=’FLAG’;

Record View
As of: 2017/6/12 9:35:03

OWNER:          SCOTT
TABLE_NAME:     T
COLUMN_NAME:    FLAG
NUM_DISTINCT:   2
LOW_VALUE:      61
HIGH_VALUE:     64
DENSITY:        0.000024997500249975
NUM_NULLS:      0
NUM_BUCKETS:    2
LAST_ANALYZED:  2017/6/12 9:33:35
SAMPLE_SIZE:    20002
GLOBAL_STATS:   YES
USER_STATS:     NO
AVG_COL_LEN:    2
HISTOGRAM:      FREQUENCY

–//建立了直方图.

SCOTT@book> set numw 36
SCOTT@book> select * from USER_TAB_HISTOGRAMS where
column_name=’FLAG’;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER                      
ENDPOINT_VALUE ENDPOINT_A



T          FLAG                  20000
503652795277878000000000000000000000
T          FLAG                  20002
519229685853483000000000000000000000

–//假诺应用形式发生变化,参加了2种值.

insert into t select rownum+2e4+2,lpad(‘b’,20,’b’) ,’b’ from dual
connect by level<=2e4;
insert into t select rownum+4e4+2,lpad(‘c’,20,’c’) ,’c’ from dual
connect by level<=2;
commit ;

SCOTT@book> exec dbms_stats.Gather_table_stats(ownname => user,
tabname => ‘T’,method_opt => ‘FOR ALL COLUMNS size
repeat’,no_invalidate=> false);
PL/SQL procedure successfully completed.

select * from DBA_TAB_COL_STATISTICS where owner=user and
table_name=’T’ and column_name=’FLAG’;

Record View
As of: 2017/6/12 9:38:59

OWNER:          SCOTT
TABLE_NAME:     T
COLUMN_NAME:    FLAG
NUM_DISTINCT:   4
LOW_VALUE:      61
HIGH_VALUE:     64
DENSITY:        0.0000126552145963607
NUM_NULLS:      0
NUM_BUCKETS:    4
LAST_ANALYZED:  2017/6/12 9:38:10
SAMPLE_SIZE:    5432
GLOBAL_STATS:   YES
USER_STATS:     NO
AVG_COL_LEN:    2
HISTOGRAM:      FREQUENCY

–//因为bucket桶变成了五个,直方图依然是FREQUENCY.

SCOTT@book> select * from USER_TAB_HISTOGRAMS where
table_name=’T’ and column_name=’FLAG’;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER                      
ENDPOINT_VALUE ENDPOINT_A



T          FLAG                   2775
503652795277878000000000000000000000
T          FLAG                   5430
508845092136413000000000000000000000
T          FLAG                   5431
514037388994948000000000000000000000
T          FLAG                   5432
519229685853483000000000000000000000

SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@test01p> select * from t where flag=’c’;
   ID PAD                  F


40003 cccccccccccccccccccc c
40004 cccccccccccccccccccc c

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  g3pmd0h5vnw5r, child number 0

select * from t where flag=’c’

Plan hash value: 1601196873


| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost

(%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

|   0 | SELECT STATEMENT  |      |      1 |        |       |    68
(100)|          |      2 |00:00:00.01 |     204 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      7 |   196 |    68  

(0)| 00:00:01 |      2 |00:00:00.01 |     204 |

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$1 / T@SEL$1

Predicate Information (identified by operation id):

   1 – filter(“FLAG”=’c’)
23 rows selected.

–//能够窥见E-rows 与 A-rows
差别不大,而12c的情景就不相同了.12c假使采纳method_opt => ‘FOR ALL
COLUMNS size repeat’后,
–//bucket=2,直方图变成了混合型直方图.约等于一旦升级12c,要小心分析方法method_opt
=> ‘FOEvoque ALL COLUMNS size repeat’带来的难点.
–//再写有点长,其它写看12c的测试.

–//当然借使数据模型没有爆发变化,难题能够不考虑.

相关文章