ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

oracle数据库dbms_stats统计信息收集管理及测试过程(附件带脚本)

内容发布:风哥| 发布时间:2016-1-19 11:27:21
oracle数据库dbms_stats统计信息收集管理及测试过程(附件带脚本)

优化器统计范围:

表统计; --行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN;
列统计; --列中唯一值的数量(NDV),NULL值的数量,数据分布;
             --DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM;
索引统计;--叶块数量,等级,聚簇因子;
             --DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL;
系统统计;--I/O性能与使用率;
             --CPU性能与使用率;
             --存储在aux_stats$中,需要使用dbms_stats收集,I/O统计在X$KCFIO中;


在11g中,默认自动收集统计信息的时间为晚上10点(周一到周五,4个小时),早上6点(周六,周日,20个小时),如下所示:
select a.window_name, a.repeat_interval,a.duration
  from dba_scheduler_windows a, dba_scheduler_wingroup_members b
  where a.window_name = b.window_name
    and b.window_group_name = 'MAINTENANCE_WINDOW_GROUP';

执行如下语句开启:
BEGIN
  dbms_auto_task_admin.enable(
  client_name => 'auto optimizer stats collection',
  operation => NULL,
  window_name => NULL);
END;
/


确认已被开启:
SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection';


CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED


关闭这个job的语句:
BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection',
   operation => NULL,
   window_name => NULL);
END;  

/


[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
*************************************
1.Oracle统计信息管理:准备测试环境
*************************************
create table ITPUX01
as
select * from dba_objects;
create index ITPUX01_01 on ITPUX01(object_id);
analyze index ITPUX01_01 delete statistics; 
select count(*) from ITPUX01;

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

************************************************
2.Oracle统计信息管理:用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')),
   staITPUX01ype         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:即使表锁住了也收集统计信息.


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

-----Oracle统计信息管理:案例1:统计表、所有列、所有索引的统计信息


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

-----Oracle统计信息管理:案例2:只统计表的信息,采样比列为15%

begin
dbms_stats.gather_table_stats
(
ownname => 'ITPUX01',
tabname => 'ITPUX01' ,
estimate_percent =>15 ,
method_opt => 'for table' ,
cascade => false
);
end;
/
@show_stats_by_itpux.sql

***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
ITPUX01              75,300    1,094        0       0    0      97 YES    NO        11,295 12-25-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)                             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
----------
ITPUX01_IDX_01    NONUNIQUE                                          NO      NO



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




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

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


@show_stats_by_itpux.sql
***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
ITPUX01              74,908    1,094        0       0    0      97 YES    NO        74,908 12-25-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)                             NO     NO
OBJECT_NAME       VARCHAR2(128)          46,257       0       1      0 YES    N     74,908 12-25-2015
SUBOBJECT_NAME        VARCHAR2(30)                             NO     NO
OBJECT_ID         NUMBER(22)             74,908       0       1      0 YES    N     74,908 12-25-2015
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
----------
ITPUX01_IDX_01    NONUNIQUE                                          NO      NO



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




-----Oracle统计信息管理:案例4:只统计表的信息、表所有列以及表所有索引的统计信息

begin
dbms_stats.gather_table_stats
(
ownname => 'ITPUX01',
tabname => 'ITPUX01' ,
estimate_percent =>100 ,
cascade => true
);
end;
/

@show_stats_by_itpux.sql 
***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
ITPUX01              74,908    1,094        0       0    0      97 YES    NO        74,908 12-25-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)               23       0       1      0 YES    N     74,908 12-25-2015
OBJECT_NAME       VARCHAR2(128)          46,257       0       1      0 YES    N     74,908 12-25-2015
SUBOBJECT_NAME        VARCHAR2(30)              256       0       1     74,301 YES    N607 12-25-2015
OBJECT_ID         NUMBER(22)             74,908       0       1      0 YES    N     74,908 12-25-2015
DATA_OBJECT_ID        NUMBER(22)              9,385       0       1     65,483 YES    N      9,425 12-25-2015
OBJECT_TYPE       VARCHAR2(19)               44       0       1      0 YES    N     74,908 12-25-2015
CREATED           DATE                1,139       0       1      0 YES    N     74,908 12-25-2015
LAST_DDL_TIME         DATE                1,220       0       1      0 YES    N     74,908 12-25-2015
TIMESTAMP         VARCHAR2(19)            1,303       0       1      0 YES    N     74,908 12-25-2015
STATUS            VARCHAR2(7)                 1       1       1      0 YES    N     74,908 12-25-2015
TEMPORARY         VARCHAR2(1)                 2       1       1      0 YES    N     74,908 12-25-2015
GENERATED         VARCHAR2(1)                 2       1       1      0 YES    N     74,908 12-25-2015
SECONDARY         VARCHAR2(1)                 2       1       1      0 YES    N     74,908 12-25-2015
NAMESPACE         NUMBER(22)                 20       0       1      0 YES    N     74,908 12-25-2015
EDITION_NAME          VARCHAR2(30)                0       0       0     74,908 YES    N    12-25-2015

                  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
----------
ITPUX01_IDX_01    NONUNIQUE     1  166         74,908     74,908       1       1        1,177 YES    NO             74,908
12-25-2015


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

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


-----Oracle统计信息管理:案例5:删除表的统计信息



begin
dbms_stats.delete_table_stats
(
ownname => 'ITPUX01',
tabname => 'ITPUX01'
);
end;
/


@show_stats_by_itpux.sql 
***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
ITPUX01                                        NO     NO

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
----------
ITPUX01_IDX_01    NONUNIQUE                                          NO      NO



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



Select Table_Name,Partition_Name,High_Value,Partition_Position,Tablespace_Name,Num_Rows From dba_Tab_Partitions
where TABLE_NAME='ITPUX01';



这是对命令与工具包的一些总结
1、对于分区表,建议使用DBMS_STATS,而不是使用Analyze语句。
a) 可以并行进行,对多个用户,多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息

2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息,这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze,因为默认Cascade是False,需要手工指定为True
3、对于oracle 9里面的External Table,Analyze不能使用,只能使用DBMS_STATS来收集信息。




-----------2.gather_schema_stats


dbms_stats能良好地估量统计数据(尤其是针对较大的分区表),并能取得更好的统计后果,
最终制订出速度更快的SQL施行计划。
  exec dbms_stats.gather_schema_stats(
  ownname          => 'ITPUX01',
  options          => 'GATHER AUTO',
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt       => 'for all columns size repeat',
  degree           => 15
  )       
为了充沛认识dbms_stats的益处,需要仔细领会每一条次要的预编译指令(directive)。上面让咱们钻研每一条指令
,并领会如何用它为基于代价的SQL优化器搜罗最高品质的统计数据。
  options参数
  使用4个预设的法子之一,这个选项能把握Oracle统计的刷新方法:
  gather——重新剖析整个架构(Schema)。
  gather empty——只剖析目前还没有统计的表。
  gather stale——只重新剖析修改量超过10%的表(这些修改包含拔出、更新和删除)。
  gather auto——重新剖析以后没有统计的对象,以及统计数据过期(变脏)的对象。
注意,使用gather auto相似于组合使用gather stale和gather empty。
  注意,不论gather stale仍是gather auto,都请求进行监视。假如你施行一个alter table xxx monitoring命令,
Oracle会用dba_tab_modifications视图来跟踪发生发火变动的表。这样一来,你就确实地知道,自从上一次剖析统计数据以来,发生发火了多少次拔出、更新和删除操作。

  estimate_percent选项
  estimate_percent参数是一种比照新的设计,它答应Oracle的dbms_stats在搜罗统计数据时,自动估量要采样的
一个segment的最佳百分比:
  estimate_percent => dbms_stats.auto_sample_size
  要考证自动统计采样的准确性,你可检视dba_tables sample_size列。一个有趣的地方是,在使用自动采样时,
Oracle会为一个样本尺寸挑选5到20的百分比。记住,统计数据品质越好,CBO做出的抉择越好。

  method_opt选项
  method_opt:for table --只统计表
  for all indexed columns --只统计有索引的表列
  for all indexes --只剖析统计相干索引
  for all columns
  dbms_stats的method_opt参数尤其合适在表和索引数据发生发火变动时刷新统计数据。method_opt参数也
合适用于判断哪些列需要直方图(histograms)。
  某些情形下,索引内的各个值的散播会影响CBO是使用一个索引仍是施行一次全表扫描的决议计划。例如,
假如在where子句中指定的值的数量不合错误称,全表扫描就显得比索引走访更经济。
  假如你有一个高度歪斜的索引(某些值的行数不合错误称),就可创建Oracle直方图统计。但在现实世界中,
出现这种情形的机率相称小。使用 CBO时,最罕见的过失之一就是在CBO统计中不用要地引入直方图。根据经验,
只需在列值请求必需修改施行计划时,才应使用直方图。
  为了智能地生成直方图,Oracle为dbms_stats准备了method_opt参数。在method_opt子句中,还有一些首要的
新选项,包含skewonly,repeat和auto:
  method_opt=>'for all columns size skewonly'
  method_opt=>'for all columns size repeat'
  method_opt=>'for all columns size auto'


----Oracle统计信息管理:案例1

begin
  dbms_stats.gather_schema_stats(
  ownname          => 'ITPUX01',
  estimate_percent => 100,
  method_opt       => 'for all indexed columns'
  );
end; 

含义解释 ownname:填写需要分析的用户(该用户下所有表都将被分析) 
              estimate_percent:分析抽样的力度 
              cascade:是否对索引进行分析



---Oracle统计信息管理:案例2

Exec dbms_stats.gather_schema_stats
( 
ownname => 'ITPUX01', 
options => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all indexed columns '
) ;





------------3.GATHER_INDEX_STATS 

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

DBMS_STATS.GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (GET_PARAM('ESTIMATE_PERCENT')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type 
                                               (GET_PARAM('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);



*********/



begin
dbms_stats.gather_index_stats (
ownname          => 'ITPUX01',
indname      =>'ITPUX01_IDX_01',
estimate_percent => 100,
degree           => 2
);
end; 
/



@show_stats_by_itpux.sql

***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
ITPUX01                                        NO     NO

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
----------
ITPUX01_IDX_01    NONUNIQUE     1  166         74,908     74,908       1       1        1,177 YES    NO             74,908
12-25-2015


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


*************************************
Oracle统计信息管理:3.删除统计信息
*************************************

DELETE_COLUMN_STATS Procedure
DELETE_DATABASE_STATS Procedure
DELETE_DICTIONARY_STATS Procedure
DELETE_FIXED_OBJECTS_STATS Procedure
DELETE_INDEX_STATS Procedure
DELETE_SCHEMA_STATS Procedure
DELETE_SYSTEM_STATS Procedure
DELETE_TABLE_STATS Procedure


---3.1 DBMS_STATS.DELETE_TABLE_STATS

DBMS_STATS.DELETE_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   cascade_parts    BOOLEAN  DEFAULT TRUE, 
   cascade_columns  BOOLEAN  DEFAULT TRUE,
   cascade_indexes  BOOLEAN  DEFAULT TRUE,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);




--删除统计信息表中指定表的分析信息
BEGIN
DBMS_STATS.delete_table_stats(ownname => 'ITPUX01',tabname => 'ITPUX01');
END;
/


----3.2 DBMS_STATS.DELETE_SCHEMA_STATS 

DBMS_STATS.DELETE_SCHEMA_STATS (
   ownname          VARCHAR2, 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);



--删除指定schema的分析信息
BEGIN
DBMS_STATS.DELETE_SCHEMA_STATS(ownname => 'ITPUX01',tabname => 'ITPUX01');
END;
/


*************************************
Oracle统计信息管理:4.传输迁移统计信息
*************************************

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

DBMS_STATS.CREATE_STAT_TABLE (
   ownname  VARCHAR2, 
   stattab  VARCHAR2,
   tblspace VARCHAR2 DEFAULT NULL);



DBMS_STATS.EXPORT_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   cascade  BOOLEAN  DEFAULT TRUE,
   statown  VARCHAR2 DEFAULT NULL);


DBMS_STATS.EXPORT_SCHEMA_STATS (
   ownname VARCHAR2,
   stattab VARCHAR2, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);



DBMS_STATS.EXPORT_SYSTEM_STATS (
   stattab       VARCHAR2, 
   statid        VARCHAR2 DEFAULT NULL,
   statown       VARCHAR2 DEFAULT NULL);


DBMS_STATS.IMPORT_TABLE_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2,
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade       BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force         BOOLEAN DEFAULT FALSE);



DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname       VARCHAR2,
   stattab       VARCHAR2, 
   statid        VARCHAR2 DEFAULT NULL,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULITPUX01o_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force         BOOLEAN DEFAULT FALSE);



DBMS_STATS.IMPORT_SYSTEM_STATS (
   stattab       VARCHAR2, 
   statid        VARCHAR2 DEFAULT NULL,
   statown       VARCHAR2 DEFAULT NULL);


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

--步骤1:在使用DBMS_STATS分析表的时候,我们经常要保存之前的分析,以防分析后导致系统性能低下然后进行快速恢复。
首先创建一个分析表,该表是用来保存之前的分析值。

begin
dbms_stats.create_stat_table(ownname => 'ITPUX01',stattab => 'STAT_TABLE');
end;
/

分析表信息
BEGIN
--DBMS_STATS.delete_table_stats(ownname => 'ITPUX01',tabname => 'ITPUX01');
DBMS_STATS.gather_table_stats(ownname => 'ITPUX01',tabname => 'ITPUX01');
END;
/


--步骤2:导出表的分析信息到统计表stat_table中。
BEGIN
dbms_stats.export_table_stats(ownname => 'ITPUX01',tabname => 'ITPUX01',stattab => 'STAT_TABLE');
END;
/



--步骤3:将统计信息导入到你的目标库


--步骤4:从统计信息表中导入统计信息到当前模式
BEGIN
DBMS_STATS.import_table_stats(ownname => 'ITPUX01',tabname => 'ITPUX01',stattab => 'STAT_TABLE');
END;
/


exec dbms_stats.gather_table_stats('ITPUX01','ITPUX01',cascade => true);



execute dbms_stats.gather_table_stats(ownname => 'ITPUX01',tabname => 'ITPUX01' ,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'for all indexed columns' ,cascade => true,degree =>8 );




*************************************
Oracle统计信息管理:5.管理统计信息
*************************************

---5.1修改统计信息保留时间


DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (
   retention       IN     NUMBER);


  
---5.2还原以前版本的统计信息


恢复统计信息功能给了DBA亡羊补牢的机会.如果新版本的统计信息导致不可预知的问题

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

DBMS_STATS.RESTORE_SCHEMA_STATS( 
   ownname                VARCHAR2, 
   as_of_timestamp        TIMESTAMP WITH TIME ZONE, 
   force                  BOOLEAN DEFAULT FALSE,
   no_invalidate          BOOLEAN DEFAULT to_no_invalidate_type
                                                    (GET_PARAM('NO_INVALIDATE')));



DBMS_STATS.RESTORE_TABLE_STATS (
   ownname                   VARCHAR2, 
   tabname                   VARCHAR2, 
   as_of_timestamp           TIMESTAMP WITH TIME ZONE,
   restore_cluster_index     BOOLEAN DEFAULT FALSE,
   force                     BOOLEAN DEFAULT FALSE,
   no_invalidate             BOOLEAN DEFAULT to_no_invalidate_type
                                                    (GET_PARAM('NO_INVALIDATE')));



DBMS_STATS.RESTORE_SCHEMA_STATS( 
   as_of_timestamp        TIMESTAMP WITH TIME ZONE);


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



select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
28-1月 -15 02.10.00.252146000 下午 +08:00


exec DBMS_STATS.RESTORE_SCHEMA_STATS(
ownname=>'ITPUX01',
as_of_timestamp=>'28-1月 -15 02.10.00.252146000 下午 +08:00',
no_invalidate=>false
);



---5.3验证统计信息

PENDING功能就是防患于未然.dba收集了新的统计信息但是在确定
新的统计信息比现在使用的统计信息更好之前不想让数据库自动使用.
而由dba经过性能测试以后再发布到系统里让数据库使用新的统计信息.


/**********************************语法相关

--
DBMS_STATS.SET_TABLE_PREFS (
    ownname    IN  VARCHAR2,
    tabname    IN  VARCHAR2,
    pname      IN  VARCHAR2,
    pvalue     IN  VARCHAR2);

pname值:
CASCADE
DEGREE
ESTIMATE_PERCENT
GRANULARITY
INCREMENTAL
INCREMENTAL_LEVEL
INCREMENTAL_STALENESS
METHOD_OPT
NO_INVALIDATE
PUBLISH
STALE_PERCENT
TABLE_CACHED_BLOCKS
OPTIONS





--
DBMS_STATS.SET_SCHEMA_PREFS (
    ownname   IN   VARCHAR2,
    pname     IN   VARCHAR2,
    pvalue    IN   VARCHAR2);



pname值:
CASCADE
DEGREE
ESTIMATE_PERCENT
GLOBAL_TEMP_TABLE_STATS
GRANULARITY
INCREMENTAL
INCREMENTAL_LEVEL
INCREMENTAL_STALENESS
METHOD_OPT
NO_INVALIDATE
PUBLISH
STALE_PERCENT
TABLE_CACHED_BLOCKS
OPTIONS


BMS_STATS.SET_SCHEMA_PREFS('SH','CASCADE', 'DBMS_STATS.AUTO_CASCADE');
DBMS_STATS.SET_SCHEMA_PREFS('SH' 'ESTIMATE_PERCENT','9');
DBMS_STATS.SET_SCHEMA_PREFS('SH', 'DEGREE','99');


--
DBMS_STATS.PUBLISH_PENDING_STATS (
    ownname         IN  VARCHAR2 DEFAULT USER,
    tabname         IN  VARCHAR2,
    no_invalidate   BOOLEAN DEFAULT
       to_no_invalidate_type(get_param('NO_INVALIDATE')),
    force      IN  BOOLEAN DEFAULT FALSE);



--
DBMS_STATS.DELETE_PENDING_STATS (
    ownname    IN  VARCHAR2  DEFAULT USER,
    tabname    IN  VARCHAR2);





************************************/
----Oracle统计信息管理:6:使用演示:
*************************************


select count(*) from ITPUX02;

  COUNT(*)
----------
    990000



--收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('ITPUX01', 'ITPUX02');


--查看数据字典中相关的统计信息
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';


SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'ITPUX02';

TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------------
ITPUX02
    990000   9205 2015-02-28 17:16:51



这时默认的情况,如果修改全局设置,使得PENDING方式生效:

--设置阻止数据库自动发布统计信息
EXEC DBMS_STATS.SET_TABLE_PREFS('ITPUX01', 'ITPUX02', 'PUBLISH', 'FALSE');


delete from ITPUX02 where rownum<=500000;

commit;


select count(*) from ITPUX02;

  COUNT(*)
----------
    490000


--收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('ITPUX01', 'ITPUX02');


--
--查看数据字典中相关的统计信息
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'ITPUX02';


TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------------
ITPUX02
    990000   9205 2015-02-28 17:16:51


新收集的统计信息并没有覆盖数据字典中原始的统计信息,这是因为表T的全局属性被修改,
此时收集的统计信息并不会马上发布,而是至于PENDING状态.


--告诉优化器使用新收集的待定统计信息

alter session set optimizer_use_pending_statistics=true;



--对新的统计信息做一些需要的执行计划测试,看看是否满足需求




--发布新的统计信息
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('ITPUX01', 'ITPUX02');



--再次查看数据字典中相关的统计信息
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'ITPUX02';

TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------------
ITPUX02
    490000   9205 2015-02-28 17:20:14  --已经是新的统计信息



--删除新的统计信息
EXEC DBMS_STATS.delete_pending_stats('ITPUX01','DEPT');

show_stats_by_itpux.sql

7.89 KB, 下载次数: 8, 下载积分: IT币 -1

售价: 5 IT币  [记录]  [购买]

show_stats_by_itpux.sql



上一篇:Oracle用户对表空间配额(quota)配置过程与配额说明
下一篇:Oracle资源管理器(Oracle Database Resource Manager)资源限制与使用详解_附
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

内容发布:font2008| 发布时间:2016-8-11 08:41:54
很不错,疯哥厉害!!!
回复 支持 反对

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表