oracle dbms_stats统计信息管理

最新推荐文章于 2024-07-02 07:30:00 发布

Angus_yang

最新推荐文章于 2024-07-02 07:30:00 发布

阅读量5.7k

点赞数 1

分类专栏： oracle 快乐DBA 文章标签： oracle统计信息 dbms_stats

本文链接：https://blog.csdn.net/yangzhawen/article/details/44017249

版权

本文详细介绍了Oracle数据库中DBMS_STATS包的使用，包括收集表、索引的统计信息，以及如何管理统计信息。通过案例展示了GATHER_TABLE_STATS、GATHER_INDEX_STATS等不同选项的用法，并探讨了如何删除、传输和管理统计信息，以优化数据库性能。

摘要由CSDN通过智能技术生成

----------------------------------------------------
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 <list> SIZE <N> | 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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
TT			    75,300    1,094	       0       0	0      97 YES	 NO	       11,295 12-25-2013

Column			  Column		       Distinct 	 Number     Number Global User		     Sample Date
Name			  Details			 Values Density Buckets      Nulls Stats  Stats 	       Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER			  VARCHAR2(30)							   NO	  NO
OBJECT_NAME		  VARCHAR2(128) 						   NO	  NO
SUBOBJECT_NAME		  VARCHAR2(30)							   NO	  NO
OBJECT_ID		  NUMBER(22)							   NO	  NO
DATA_OBJECT_ID		  NUMBER(22)							   NO	  NO
OBJECT_TYPE		  VARCHAR2(19)							   NO	  NO
CREATED 		  DATE								   NO	  NO
LAST_DDL_TIME		  DATE								   NO	  NO
TIMESTAMP		  VARCHAR2(19)							   NO	  NO
STATUS			  VARCHAR2(7)							   NO	  NO
TEMPORARY		  VARCHAR2(1)							   NO	  NO
GENERATED		  VARCHAR2(1)							   NO	  NO
SECONDARY		  VARCHAR2(1)							   NO	  NO
NAMESPACE		  NUMBER(22)							   NO	  NO
EDITION_NAME		  VARCHAR2(30)							   NO	  NO

			      B 					   Average     Average
Index			   Tree Leaf	   Distinct		Number Leaf Blocks Data Blocks	    Cluster Global User 	      Sample
Name		Unique	  Level Blks	       Keys	       of Rows	   Per Key     Per Key	     Factor Stats  Stats		Size
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------
Date
MM-DD-YYYY
----------
INDEX_01	NONUNIQUE									       NO	   NO



Index		Column			   Col Column
Name		Name			   Pos Details
--------------- ------------------------- ---- ------------------------
INDEX_01	OBJECT_ID		     1 NUMBER(22)




-----案例3：只统计表的信息、其中两列的信息不收集直方图

begin
dbms_stats.gather_table_stats
(
ownname => 'SCOTT',
tabname => 'TT' ,
estimate_percent =>100 ,
method_opt => 'for columns size 1 object_name object_id' ,
cascade => false
);
end;
/


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


Table			    Number		   Empty Average    Chain Average Global User	       Sample