琢磨不透的cbo

第1章  表信息

-- Create table

create table WFWIPARTICIPANT

(

  WIPARTICID      NUMBER not null,

  WORKITEMID      NUMBER,

  PARTICIPANTTYPE VARCHAR2(20),

  PARTICIPANT     VARCHAR2(256),

  PARTICIPANT2    VARCHAR2(64),

  WORKITEMSTATE   NUMBER(2),

  PARTIINTYPE     VARCHAR2(20),

  EXTEND1         VARCHAR2(64)

)

tablespace TSSAPUB

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 384K

    minextents 1

    maxextents unlimited

  );

-- Create/Recreate primary, unique and foreign key constraints

alter table WFWIPARTICIPANT

  add constraint PK_WFWIPARTICIPANT primary key (WIPARTICID)

  using index

  tablespace TSSAPUB

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 192K

    minextents 1

    maxextents unlimited

  );

alter table WFWIPARTICIPANT

  add constraint FK_WFWIPART_REFERENCE_WFWORKIT foreign key (WORKITEMID)

  references WFWORKITEM (WORKITEMID);

-- Create/Recreate indexes

create index IDX_WFWIPART_WIID on WFWIPARTICIPANT (WORKITEMID)

  tablespace TSSAPUB

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 192K

    minextents 1

    maxextents unlimited

  );

create index WF_IDX_PARTICIPANT on WFWIPARTICIPANT (PARTICIPANT)

  tablespace TSSAINDEX

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 3M

    minextents 1

    maxextents unlimited

  );

create index WF_IDX_PART_EXTEND1 on WFWIPARTICIPANT (EXTEND1)

  tablespace TSSAPUB

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 256K

    minextents 1

    maxextents unlimited

  );

create index WF_IDX_PART_TYPE on WFWIPARTICIPANT (PARTICIPANTTYPE, PARTICIPANT)

  tablespace TSSAPUB

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 256K

    minextents 1

    maxextents unlimited

  );

 

 

 

SELECT WORKITEMID FROM WFWIPARTICIPANT WHERE PARTICIPANT IN ('771', '99999', '41', '146', '123')

常用：

select t.num_rows, i.num_rows, c.cnt

  from (select num_rows

          from user_tables

         where table_name = 'WFWIPARTICIPANT') t,

       (select num_rows

          from user_indexes

         where table_name = 'WFWIPARTICIPANT') i,

       (select count(distinct column_name) cnt

          from user_tab_histograms

         where table_name = 'WFWIPARTICIPANT') c;

        

select a.owner,

       a.index_name,

       a.index_type,

       partitioned,

       b.num_rows,

       b.distinct_keys,

       b.num_rows / b.distinct_keys avg_row_per_key,

       b.distinct_keys / b.num_rows SELECTIVITY,

       b.last_analyzed,

       b.stale_stats

  from dba_indexes a, dba_ind_statistics b

 where a.owner = b.owner

   and a.index_name = b.index_name

   and a.index_name = 'WF_IDX_PARTICIPANT';

  

select owner, table_name, column_name, num_distinct, histogram, num_buckets

  from dba_tab_col_statistics

 where table_name = 'WFWIPARTICIPANT'

   and column_name = 'PARTICIPANT';

第2章  各种条件

 

2.1    analyze table WFWIPARTICIPANT  delete statistics;

删除统计信息Oracle采用动态采样

2.2    dmbs分析1

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TSSA',tabname =>'WFWIPARTICIPANT',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all columns size repeat',degree => DBMS_STATS.AUTO_DEGREE);

统计信息8列都做了记录

表一共有94729列，但是索引的列上面只有4592个不同的值，也就是说索引选择性为4%,很显然，这列数据分布不均衡。现在查看列上面有没有直方图：

没有直方图

全表扫描 不是期望的

 

 

 

 

 

 

 

2.3    analyze分析1

删除统计信息：

analyze table WFWIPARTICIPANT  delete statistics;

执行：

analyze table WFWIPARTICIPANT  compute statistics  for all indexes for all columns for table

 

 

状态依旧

有直方图了 看看计划

问题依旧

 

 

 

 

 

2.4    analyze分析2

删除统计信息执行

analyze table WFWIPARTICIPANT  compute statistics

没变

选择率没变看看直方图

没了 看看执行计划

走索引了

2.5    dbms分析2

analyze table WFWIPARTICIPANT  delete statistics;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TSSA',TABNAME=>'WFWIPARTICIPANT',ESTIMATE_PERCENT=>100,DEGREE=>16,method_opt=>'for columns size 1 PARTICIPANT',CASCADE=>TRUE);

 

 

 

 

最后感谢落落无私帮助