oracle中using btree,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