oracledbms_stats统计信息管理 - 数据库编程

----------------------------------------------------
1.测试环境
----------------------------------------------------

create table tt
as
select * from dba_objects;


create index index_01 on tt(object_id);


analyze index index_01 delete statistics;



select count(*) from tt;

  COUNT(*)
----------
     74908




----------------------------------------------------
2.用DBMS_STATS包收集统计信息
----------------------------------------------------




GATHER_INDEX_STATS        Index statistics
 
GATHER_TABLE_STATS         Table, column, and index statistics
 
GATHER_SCHEMA_STATS       Statistics for all objects in a schema


GATHER_DATABASE_STATS     Statistics for all objects in a database



-----------1.dbms_table_stats


/****************

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'DATA',
   force            BOOLEAN  DEFAULT FALSE);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样.
常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle绝定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns 




 SIZE  | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.
N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;
SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:决定并行度.默认值为null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascace:是收集索引的信息.默认为falase.

stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.
statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE.
The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.


 **************/




-----案例1：统计表、所有列、所有索引的统计信息


begin
dbms_stats.gather_table_stats
(
ownname => 'JINRILOG',
tabname => 'ORDERLOG' ,
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE ,
method_opt => 'for all indexed columns' ,
cascade => true,
degree =>10
);
end;
/





-----案例2：只统计表的信息，采样比列为15%

begin
dbms_stats.gather_table_stats
(
ownname => 'SCOTT',
tabname => 'TT' ,
estimate_percent =>15 ,
method_opt => 'for table' ,
cascade => false
);
end;
/


***********
Table Level
***********


Table			    Number		   Empty Average    Chain Average Global User	       Sample Date
Name			   of Rows   Blocks	  Blocks   Space    Count Row Len Stats  Stats	         Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ------
oracledbms_stats统计信息管理(一)
