----------------------------------------------------
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 Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
TT 74,908 1,094 0 0 0 97 YES NO 74,908 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) 46,257 0 1 0 YES N 74,908 12-25-2013
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) 74,908 0 1 0 YES N 74,908 12-25-2013
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)
-----案例4：只统计表的信息、表所有列和表所有索引的统计信息
begin
dbms_stats.gather_table_stats
(
ownname => "SCOTT",
tabname => "TT" ,
estimate_percent =>100 ,
cascade => true
);
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 74,908 1,094 0 0 0 97 YES NO 74,908 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) 23 0 1 0 YES N 74,908 12-25-2013
OBJECT_NAME VARCHAR2(128) 46,257 0 1 0 YES N 74,908 12-25-2013
SUBOBJECT_NAME VARCHAR2(30) 256 0 1 74,301 YES N607 12-25-2013
OBJECT_ID NUMBER(22) 74,908 0 1 0 YES N 74,908 12-25-2013
DATA_OBJECT_ID NUMBER(22) 9,385 0 1 65,483 YES N 9,425 12-25-2013
OBJECT_TYPE VARCHAR2(19) 44 0 1 0 YES N 74,908 12-25-2013
CREATED DATE 1,139 0 1 0 YES N 74,908 12-25-2013
LAST_DDL_TIME DATE 1,220 0 1 0 YES N 74,908 12-25-2013
TIMESTAMP VARCHAR2(19) 1,303 0 1 0 YES N 74,908 12-25-2013
STATUS VARCHAR2(7) 1 1 1 0 YES N 74,908 12-25-2013
TEMPORARY VARCHAR2(1) 2 1 1 0 YES N 74,908 12-25-2013
GENERATED VARCHAR2(1) 2 1 1 0 YES N 74,908 12-25-2013
SECONDARY VARCHAR2(1) 2 1 1 0 YES N 74,908 12-25-2013
NAMESPACE NUMBER(22) 20 0 1 0 YES N 74,908 12-25-2013
EDITION_NAME VARCHAR2(30) 0 0 0 74,908 YES N 12-25-2013
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 1 166 74,908 74,908 1 1 1,177 YES NO 74,908
12-25-2013
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
INDEX_01 OBJECT_ID 1 NUMBER(22)
***************
-----案例5：删除表的统计信息
begin
dbms_stats.delete_table_stats
(
ownname => "SCOTT",
tabname => "TT"
);
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 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
----------
INDEX_01 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
INDEX_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="USEROPERATELOG";
这是对命令与工具包的一些总结
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 => "SCOTT",
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"
----案例1
begin
dbms_stats.gather_schema_stats(
ownname => "JINRILOG",
estimate_percent => 100,
method_opt => "for all indexed columns"
);
end;
含义解释 ownname:填写须要剖析的用户（该用户下所有表都将被剖析）
estimate_percent:剖析抽样的力度
cascade:是不是对索引进行剖析
---案例2
Exec dbms_stats.gather_schema_stats
(
ownname => "JINRILOG",
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 => "SCOTT",
indname =>"INDEX_01",
estimate_percent => 100,
degree => 2
);
end;
/
@sosi.txt
***********
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 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
----------
INDEX_01 NONUNIQUE 1 166 74,908 74,908 1 1 1,177 YES NO 74,908
12-25-2013
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
INDEX_01 OBJECT_ID 1 NUMBER(22)
----------------------------------------------------
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 => "JINRILOG",tabname => "ORDERLOG");
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 => "JINRILOG",tabname => "ORDERLOG");
END;
/
----------------------------------------------------
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 DEFAULTto_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 => "JINRILOG",stattab => "STAT_TABLE");
end;
/
剖析表信息
BEGIN
--DBMS_STATS.delete_table_stats(ownname => "JINRILOG",tabname => "ORDERLOG");
DBMS_STATS.gather_table_stats(ownname => "JINRILOG",tabname => "ORDERLOG");
END;
/
--步骤2：导出表的剖析信息到统计表stat_table中。
BEGIN
dbms_stats.export_table_stats(ownname => "JINRILOG",tabname => "ORDERLOG",stattab => "STAT_TABLE");
END;
/
--步骤3：将统计信息导入到你的目的库
--步骤4：从统计信息表中导入统计信息到当前模式
BEGIN
DBMS_STATS.import_table_stats(ownname => "JINRILOG",tabname => "ORDERLOG",stattab => "STAT_TABLE");
END;
/
exec dbms_stats.gather_table_stats("JINRILOG","USEROPERATELOG",cascade => true);
execute 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 =>8 );
----------------------------------------------------
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=>"SCOTT",
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);
********************************/
----应用演示:
select count(*) from t1;
COUNT(*)
----------
990000
--搜集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS("SCOTT", "T1");
--查看数据字典中相关的统计信息
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 = "T1";
TABLE_NAME
--------------------------------------------------------------------------------
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------------
T1
990000 9205 2015-02-28 17:16:51
这时候默许的情形，如果修正全局设置，使得PENDING方法生效：
--设置阻挠数据库主动宣布统计信息
EXEC DBMS_STATS.SET_TABLE_PREFS("SCOTT", "T1", "PUBLISH", "FALSE");
delete from t1 where rownum<=500000;
commit;
select count(*) from t1;
COUNT(*)
----------
490000
--搜集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS("SCOTT", "T1");
--
--查看数据字典中相关的统计信息
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = "T1";
TABLE_NAME
--------------------------------------------------------------------------------
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------------
T1
990000 9205 2015-02-28 17:16:51
新搜集的统计信息并没有笼罩数据字典中原始的统计信息，这是由于表T的全局属性被修正，
此时搜集的统计信息其实不会马上宣布，而是至于PENDING状况.
--告知优化器应用新搜集的待定统计信息
alter session set optimizer_use_pending_statistics=true;
--对新的统计信息做一些须要的履行筹划测试，看看是不是满足需求
--宣布新的统计信息
EXEC DBMS_STATS.PUBLISH_PENDING_STATS("SCOTT", "T1");
--再次查看数据字典中相关的统计信息
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = "T1";
TABLE_NAME
--------------------------------------------------------------------------------
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------------
T1
490000 9205 2015-02-28 17:20:14 --已经是新的统计信息
--删除新的统计信息
EXEC DBMS_STATS.delete_pending_stats("SCOTT","DEPT");
--------------------------------------------------
阅读背景：
oracle dbms_stats统计信息管理
来源：互联网
分享到：
