oracle dbms_stat與analyze 獲取有效的統計資訊(6)

analyze 使用
主要用於收集表INDEX 統計資訊(分割槽表的收集不了)
驗證表INDEX,結構
檢查row chains,row migrate
在基於成本的優化器中應該使用dbms_stats收集,要收集empty_blocks,row_chains,index_stat才用analyze

#完全收集統計資訊
analyze table compute statistics(不指定引數的話=for table+for all columns+for all indexes)

#取樣收集統計資訊
analyze table estimate statistics sample 20 percent;(一般建議為20)

#刪除統計資訊
analyze   table  delete   statistics;

#驗證結構
analyze  index validate structure
用來判斷是否需要rebulid,及其index compress(OPT_CMPR_COUNT OPT_CMPR_PCTSAVE 兩個欄位判斷)
(預設offline這將對錶產生TM 4級LOCK，建議online選項 不會lock表),結果存在index_stats中)

#檢視是否需要重建
select name, del_lf_rows, lf_rows,       round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent"from index_stats;
1.索引高度大於等於4(high)
2.索引中被刪除的資料超過索引資料的20%。(del_lf_rows/lf_rows)
3.索引的狀態為VALID

 

#
 ANALYZE TABLE VALIDATE STRUCTURE CASCADE;(CASCADE連index一起檢查了)
#checks objects for logical corruption ,可以用來檢視邏輯壞塊

$ORACLE_HOME/rdbms/admin/utlvalid.sql  (建立invalid_rows)

 

SQL> desc invalid_rows;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 HEAD_ROWID                                         ROWID
 ANALYZE_TIMESTAMP                                  DATE

 

For a table, Oracle Database verifies the integrity of each of the data blocks and rows. For an index-organized table, the database also generates compression statistics (optimal prefix compression count) for the primary key index on the table.

For a cluster, Oracle Database automatically validates the structure of the cluster tables.

For a partitioned table, Oracle Database also verifies that each row belongs to the correct partition. If a row does not collate correctly, then its rowid is inserted into the INVALID_ROWS table.

For a temporary table, Oracle Database validates the structure of the table and its indexes during the current session.

For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform. these operations by validating the structure of the table with the CASCADE clause

 

VALIDATE STRUCTURE
Specify VALIDATE STRUCTURE to validate the structure of the analyzed object. The statistics collected by this clause are not used by the Oracle Database optimizer.
For a table, Oracle Database verifies the integrity of each of the data blocks and rows. For an index-organized table, the database also generates compression statistics (optimal prefix compression count) for the primary key index on the table.
For a partitioned table, Oracle Database also verifies that each row belongs to the correct partition. If a row does not collate correctly, then its rowid is inserted into the INVALID_ROWS table.

INTO
The INTO clause of VALIDATE STRUCTURE is valid only for partitioned tables. Specify a table into which Oracle Database lists the rowids of the partitions whose rows do not collate correctly. If you omit schema, then the database assumes the list is in your own schema. If you omit this clause altogether, then the database assumes that the table is named INVALID_ROWS. The SQL script. used to create this table is UTLVALID.SQL.

CASCADE
Specify CASCADE if you want Oracle Database to validate the structure of the indexes associated with the table or cluster. If you use this clause when validating a table, then the database also validates the indexes defined on the table. If you use this clause when validating a cluster, then the database also validates all the cluster tables indexes, including the cluster index.
If you use this clause to validate an enabled (but previously disabled) function-based index, then validation errors may result. In this case, you must rebuild the index.

 

# online與offline的區別
ONLINE | OFFLINE Specify ONLINE to enable Oracle Database to run the validation while DML operations are ongoing within the object. The database reduces the amount of validation performed to allow for concurrency.

Note:

When you validate the structure of an object ONLINE, Oracle Database does not collect any statistics, as it does when you validate the structure of the object OFFLINE.
Specify OFFLINE, to maximize the amount of validation performed. This setting prevents INSERT, UPDATE, and DELETE statements from concurrently accessing the object during validation but allows queries. This is the default.

Restriction on ONLINE You cannot specify ONLINE when analyzing a cluster.

 

#INDEX_STATS view

INDEX_STATSINDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

Note:

The ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE statement must be used in order to collect statistics
Column Datatype NULL Description
HEIGHT NUMBER   Height of the B-Tree
BLOCKS NUMBER NOT NULL Blocks allocated to the segment
NAME VARCHAR2(30) NOT NULL Name of the index
PARTITION_NAME VARCHAR2(30)   Name of the partition of the index which was analyzed. If the index is not partitioned, null is returned.
LF_ROWS NUMBER   Number of leaf rows (values in the index)
LF_BLKS NUMBER   Number of leaf blocks in the B-Tree
LF_ROWS_LEN NUMBER   Sum of the lengths of all the leaf rows
LF_BLK_LEN NUMBER   Usable space in a leaf block
BR_ROWS NUMBER   Number of branch rows in the B-Tree
BR_BLKS NUMBER   Number of branch blocks in the B-Tree
BR_ROWS_LEN NUMBER   Sum of the lengths of all the branch blocks in the B-Tree
BR_BLK_LEN NUMBER   Usable space in a branch block
DEL_LF_ROWS NUMBER   Number of deleted leaf rows in the index
DEL_LF_ROWS_LEN NUMBER   Total length of all deleted rows in the index
DISTINCT_KEYS NUMBER   Number of distinct keys in the index (may include rows that have been deleted)
MOST_REPEATED_KEY NUMBER   How many times the most repeated key is repeated (may include rows that have been deleted)
BTREE_SPACE NUMBER   Total space currently allocated in the B-Tree
USED_SPACE NUMBER   Total space that is currently being used in the B-Tree
PCT_USED NUMBER   Percent of space allocated in the B-Tree that is being used
ROWS_PER_KEY NUMBER   Average number of rows per distinct key (this figure is calculated without consideration of deleted rows)
BLKS_GETS_PER_ACCESS NUMBER   Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.
PRE_ROWS NUMBER   Number of prefix rows (values in the index)
PRE_ROWS_LEN NUMBER   Sum of lengths of all prefix rows
OPT_CMPR_COUNT NUMBER   Optimal key compression length
OPT_CMPR_PCTSAVE NUMBER   Corresponding space savings after an ANALYZE

 

#row_chain,row migrate
$ORACLE_HOME\RDBMS\ADMIN\utlchain.sql;
ANALYZE TABLE LIST CHAINED ROWS;
#analyze table   COMPUTE STATISTICS;查表中的dba_tables的 chain_cnt

 

 

# 這個viwe可以查詢 表空間 是否有碎片(不相鄰的區未合併,從percent_blocks_coalesced !=0),LMT中 基本不會有碎片 ,有的話alter tablespace xx coalesce(只合並相鄰的) or exp/imp
DBA_FREE_SPACE_COALESCED
DBA_FREE_SPACE_COALESCED describes statistics on coalesced space in all tablespaces in the database.

Column Datatype NULL Description
TABLESPACE_NAME VARCHAR2(30)   Name of the tablespace
TOTAL_EXTENTS NUMBER
 Total number of free extents in the tablespace
EXTENTS_COALESCED NUMBER
 Total number of coalesced free extents in the tablespace
PERCENT_EXTENTS _COALESCED NUMBER  
 Percentage of coalesced free extents in the tablespace
TOTAL_BYTES NUMBER  
 Total number of free bytes in the tablespace
BYTES_COALESCED NUMBER  
 Total number of coalesced free bytes in the tablespace
TOTAL_BLOCKS NUMBER  
 Total number of free Oracle blocks in the tablespace
BLOCKS_COALESCED NUMBER  
 Total number of coalesced free Oracle blocks in the tablespace
PERCENT_BLOCKS _COALESCED NUMBER  
 Percentage of coalesced free Oracle blocks in the tablespace

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-664243/，如需轉載，請註明出處，否則將追究法律責任。