oracle統計資訊

標籤：oracle效能最佳化

1. create table t2 as select * from dba_objects;

  建索引：

create index idx_t2 on t2(object_id);

2.收集T2的表所有列和表上所有索引的統計資訊：

exec dbms_stats.gather_table_stats(ownname=>‘SYS‘,tabname=>‘T2‘,estimate_percent=>100,cascade=>true,degree=>4);

degree表示並行度；

3.查看統計資訊，指令碼(soxi.txt)

Please enter Name of Table Owner (Null = SYS): SYS

Please enter Table Name to show Statistics for: T2

***********

Table Level

***********

TableNumber       Empty AverageChain Average Global User    Sample Date

Name       of Rows Blocks       Blocks   SpaceCount Row Len Stats  Stats      Size MM-DD-YYYY

--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------

T287,361  1,246   0   0    0   98 YES    NO    87,361 07-07-2015

Column  Column       Distinct Number     Number Global User Sample Date

Name  Details Values Density Buckets      Nulls Stats  Stats   Size MM-DD-YYYY

------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------

OWNER  VARCHAR2(30)     31       0       1 0 YES  NO 87,361 07-07-2015

OBJECT_NAME  VARCHAR2(128) 52,669       0       1 0 YES  NO 87,361 07-07-2015

SUBOBJECT_NAME  VARCHAR2(30)    194       0       1     86,760 YES  NO    601 07-07-2015

OBJECT_ID  NUMBER(22) 87,361       0       1 0 YES  NO 87,361 07-07-2015

DATA_OBJECT_ID  NUMBER(22)  9,348       0       1     77,945 YES  NO  9,416 07-07-2015

OBJECT_TYPE  VARCHAR2(19)     45       0       1 0 YES  NO 87,361 07-07-2015

CREATED  DATE  1,244       0       1 0 YES  NO 87,361 07-07-2015

LAST_DDL_TIME  DATE  1,365       0       1 0 YES  NO 87,361 07-07-2015

TIMESTAMP  VARCHAR2(19)  1,440       0       1 0 YES  NO 87,361 07-07-2015

STATUS  VARCHAR2(7)      2       1       1 0 YES  NO 87,361 07-07-2015

TEMPORARY  VARCHAR2(1)      2       1       1 0 YES  NO 87,361 07-07-2015

GENERATED  VARCHAR2(1)      2       1       1 0 YES  NO 87,361 07-07-2015

SECONDARY  VARCHAR2(1)      2       1       1 0 YES  NO 87,361 07-07-2015

NAMESPACE  NUMBER(22)     21       0       1 0 YES  NO 87,361 07-07-2015

EDITION_NAME  VARCHAR2(30)      0       0       0     87,361 YES  NO07-07-2015

     B       Average   Average

Index   Tree Leaf   Distinct    Number Leaf Blocks Data BlocksCluster Global User      Sample Date

NameUnique  Level Blks       Keys   of Rows     Per Key   Per Key Factor Stats  StatsSize MM-DD-YYYY

--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------

IDX_T2NONUNIQUE     1  194     87,361    87,361     1 1  1,385 YES    NO      87,361 07-07-2015

IndexColumn   Col Column

NameName   Pos Details

--------------- ------------------------- ---- ------------------------

IDX_T2OBJECT_ID     1 NUMBER(22)

***************

Partition Level

***************

***************

SubPartition Level

***************

oracle統計資訊