《Oracle 表空间索引存储与碎片检查》由会员分享，可在线阅读，更多相关《Oracle 表空间索引存储与碎片检查（5页珍藏版）》请在金锄头文库上搜索。
1、OracleOracle 表空间索引存储与碎片检查表空间索引存储与碎片检查Oracle 表空间索引存储检查，Oracle 表空间索引碎片查看，包括查看系 统表中的用户索引、索引的存储情况检查、索引的选择性、确定索引的实际 碎片。 1、查看系统表中的用户索引 在 Oracle 中，SYSTEM 表是安装数据 库时自动建立的，它包含数据库的全部数据字典 Oracle 表空间索引存储检查，Oracle 表空间索引碎片查看，包括查看系统表 中的用户索引、索引的存储情况检查、索引的选择性、确定索引的实际碎片。1、查看系统表中的用户索引在 Oracle 中，SYSTEM 表是安装数据库时自动建立的，它包含
2、数据库的全部数 据字典，存储过程、包、函数和触发器的定义以及系统回滚段。一般来说，应 该尽量避免在 SYSTEM 表中存储非 SYSTEM 用户的对象。因为这样会带来数据 库维护和管理的很多问题。一旦 SYSTEM 表损坏了，只能重新生成数据库。我 们可以用下面的语句来检查在 SYSTEM 表内有没有其他用户的索引存在。以下为引用内容： SELECT * FROM dba_indexes WHERE tablespace_name = SYSTEM AND owner NOT IN (SYS, SYSTEM)2、索引的存储情况检查Oracle 为数据库中的所有数据分配逻辑结构空间。数据库空间的
3、单位是 block 、extent 和 segment 。Block ：Oracle 使用和分配的最小存储单位。由数据库建立时设置的 DB_BLOCK_SIZE 决定的。一旦数据库生成了，数据块的大小不能改变。要想改 变只能重新建立数据库。Extent ：由一组连续的 block 组成的。一个或多个 extent 组成一个 segment 。当一个 segment 中的所有空间被用完时，Oracle 为它分配一个新 的 extent 。Segment ：是由一个或多个 extent 组成的。它包含某表空间中特定逻辑存储 结构的所有数据。一个段中的 extent 可以是不连续的，甚至可以在不同的
4、数 据文件中。表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 存储层次关系一个 object 只能对应于一个逻辑存储的 segment ，我们通过查看该 segment 中的 extent ，可以看出相应 object 的存储情况。1. 查看索引段中 extent 的数量以下为引用内容： SELECT segment_name, COUNT ( * ) FROM dba_extents WHERE segment_type = INDEX AND owner = UPPER (NEWCCS) GROUP BY segment_name2. 查看表空
5、间内的索引的扩展情况以下为引用内容： SELECT SUBSTR (segment_name, 1, 20) “SEGMENT NAME“, bytes, COUNT (bytes) FROM dba_extents WHERE segment_name IN (SELECT index_name FROM dba_indexes WHERE tablespace_name = UPPER (NEWCCS) GROUP BY segment_name, bytes ORDER BY segment_name3、索引的选择性 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有
6、2000 条记录，表索引列有 1980 个不同的值，那么这个索引的选择性就是 1980/2000=0.99 。一个索引的选择性越接近于 1，这个索引的效率就越高。如果是使用基于 cost 的最优化，优化器不应该使用选择性不好的索引。如果 是使用基于 rule 的最优化，优化器在确定执行路径时不会考虑索引的选择性 （除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。确定索引的选择性，可以有两种方法：手工测量和自动测量。1. 手工测量索引的选择性如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择 性：列的选择性=不同值的数目/行的总数 /* 越接近 1 越好 *
7、/以下为引用内容： select count(distinct 第一列|“%“|第二列)/count(*) from 表名如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可 以知道另一列索引的选择性。手工方法的优点是在创建索引前就能评估索引的 选择性。2. 自动测量索引的选择性如果分析一个表，也会自动分析所有表的索引。 为了确定一个表的确定性，就要分析表。以下为引用内容： analyze table 表名 compute statistics 确定索引里不同关键字的数目以下为引用内容： select distinct_keys from user_indexes where
8、table_name=“表名“ and index_name=“索引名“ 确定表中行的总数以下为引用内容： select num_rows from user_tables where table_name=“表名“ 索引的选择性=索引里不同关键字的数目/表中行的总数以下为引用内容： select i.distinct_keys/t.num_rows from user_indexes i, user_tables t where i.table_name=“表名“ and i.index_name=“索引名“ and i.table_name=t.table_name 可以查询 USER_T
9、AB_COLUMNS 以了解每个列的选择性。表中所有行在该列的不同值的数目以下为引用内容： select column_name, num_distinct from user_tab_columns where table_name=“表名“列的选择性 =NUM_DISTINCT/ 表中所有行的总数，查询 USER_TAB_COLUMNS 有 助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。要想 测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新 分析表。4、确定索引的实际碎片随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶 子行在索引中
10、被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的 程度也越高。碎片的产生使访问和使用该索引的 I/O 成本增加。碎片较高的索 引必须重建以保持最佳性能。1. 利用验证索引命令对索引进行验证。这将有价值的索引信息填入 index_stats 表。以下为引用内容： validate index 用户名.索引名或者：以下为引用内容： analyze index index_name validate structure; index_stats 只保存最近一次分析的结果2. 查询 index_stats 表以确定索引中删除的、未填满的叶子（Leaf）行的百 分比 和 height 字段。以下
11、为引用内容： select name,height, del_lf_rows, lf_rows, round(del_lf_rows/(lf_rows+0.0000000001)*100) “Frag Percent“ from index_stats3. 如果索引的叶子行的碎片超过 10%，或者 index_stats 中 height =4, 可 以考虑对索引进行重建。以下为引用内容： alter index 用户名.索引名 rebuild tablespace 表空间名 storage ( initial 初始值 next 扩展值) nologging如何加快建 index 索引 的时间
12、可以一次生成扩展大于 10 次的索引的脚本。以下为引用内容： SELECT alter index | owner | . | segment_name | rebuild; FROM ( SELECT COUNT ( * ), owner, segment_name, t.tablespace_name FROM dba_extents t WHERE t.segment_type = INDEX AND t.owner NOT IN (SYS, SYSTEM) GROUP BY owner, segment_name, t.tablespace_name HAVING COUNT ( * ) 10 ORDER BY COUNT ( * ) DESC);4. 如果出于空间或其他考虑，不能重建索引，可以整理索引。以下为引用内容： alter index 用户名.索引名 coalesce5. 清除分析信息以下为引用内容： analyze index 用户名.索引名 delete statistics
