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 <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');
