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

oracle数据库dbms_stats统计信息收集管理及测试过程(附件带脚本)[p=19, null, left][color=rgb(75, 75, 75)][size=13px]优化器统计范围：[p=19, null, left][size=13px]表统计； –行数，块数，行平均长度；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中；[p=19, null, left][size=13px]
在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;

/
[p=19, null, left][size=13px]
[p=19, null, left][size=13px][mw_shl_code=sql,false]*************************************
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 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:即使表锁住了也收集统计信息.

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

—–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
***********

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

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

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

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

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

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

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');[/mw_shl_code]

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

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

相关推荐

联系我们