Oracle 表空间索引存储与碎片检查

合集下载

Oracle 数据库中的碎片管理和表收缩

Oracle 数据库中的碎片管理和表收缩【内容】简介碎片是我们要尽量避免的常见问题。

其表现形式多种多样、遍及多种组件，可能导致各种问题。

在本文中，我将讨论导致空间浪费的表空间碎片。

导致表空间碎片的原因很多，但我从未想到经常执行“shrink table”命令会这么快地导致这种碎片。

表收缩命令在Oracle 10gR1 之前，表的高水位线(HWM) 向前移动（由于表中插入新行）之后就无法后退来减小表大小和回收空间，将空间退回给表空间的可用空间。

我们从表中删除多行时，HWM 和表大小保持不变，减小表大小的唯一方法是截断表。

Oracle 在10gR1 中引入了一项激动人心的特性“shrink table”。

它是如何工作的？执行shrink 命令时，Oracle 使用行移动（必须在表上启用）将行从表的最后数块移至表开头处。

移动行之后，会发生表锁定，此时Oracle 将HWM 向后移动。

然后，可以释放HWM 之后的块，减小表大小。

从下面可以看出，在正常操作中，表中包括“已用块”（蓝色块）和“空块”（橙色块）。

将行插入表中，而“已用块”中没有空间时，Oracle 会将HWM（黑线）移向表末尾，将这些“空块”标记为“已用”。

一旦没了可用块，就会再分配一个区。

现在让我们看看shrink table 是如何工作的。

在下图中，我们看到HWM 位于表的末尾。

假设我们删除了许多行，现在表块中有许多地方可容纳新行。

shrink table 命令将这些行从表末尾移到靠近表开头部分的空闲位置（第一幅图）。

然后，Oracle 可以将HWM 移到表的最后一行，该行现在不位于表的末尾（第二幅图）。

移动了HWM 之后，HWM 以外的块被视为可用块（第三幅图），然后可将这些块从表中释放，退回给表空间（最后一幅图）。

本地管理的表空间shrink table 命令只适用于本地管理的、支持自动段空间管理的表空间中的段。

使用本地管理的表空间时，可以通过两种方式配置区分配：∙系统分配— Oracle 决定区大小，无论是何种用户配置。

oracle表碎片详解

oracle表碎片详解Oracle表碎片是指表中的数据在物理存储上出现不连续的情况，这可能会影响数据库性能和管理。

表碎片化可能发生在多种情况下，比如频繁的数据插入、更新和删除操作，或者由于表的存储空间不足而发生的数据移动等。

下面我将从多个角度对Oracle表碎片进行详细解释。

1. 形成原因，表碎片化可能由于多种原因导致。

比如，当表中的数据频繁插入、更新和删除时，会导致数据在磁盘上不连续存储，从而产生碎片。

此外，如果表的存储空间不足，Oracle数据库可能会将表的数据分散存储在不同的区域，也会导致表碎片化。

2. 影响，表碎片化可能会对数据库性能和管理造成影响。

首先，碎片化的表可能导致查询性能下降，因为数据库需要在不连续的存储空间中查找数据。

其次，碎片化还可能增加数据库的存储空间占用，因为碎片化的表需要更多的存储空间来存储数据。

3. 解决方法，针对Oracle表碎片化问题，可以采取一些解决方法来优化表的存储结构。

比如，可以使用Oracle提供的表重组（Table Reorganization）功能来重新组织表的存储结构，从而减少碎片化。

此外，还可以定期进行表空间的整理和重建，以优化表的存储结构，减少碎片化的发生。

4. 管理工具，Oracle数据库提供了一些管理工具来帮助识别和解决表碎片化问题。

比如，可以使用Oracle Enterprise Manager来监控表的碎片化情况，并采取相应的措施来优化表的存储结构。

此外，还可以使用Oracle提供的存储管理工具来管理表空间，以减少表碎片化的发生。

总的来说，Oracle表碎片化是指表中的数据在物理存储上出现不连续的情况，可能由于多种原因导致，并可能对数据库性能和管理造成影响。

针对表碎片化问题，可以采取一些解决方法来优化表的存储结构，并可以使用Oracle提供的管理工具来帮助识别和解决表碎片化问题。

希望以上回答能够全面解释Oracle表碎片化问题。

oracle索引，分析索引，索引碎片整理

oracle索引，分析索引，索引碎⽚整理概述索引分为B树索引和位图索引。

我们主要研究B树索引，B树索引如下图（图⽚源⾃⽹络）：索引是与表相关的⼀个可选结构，在逻辑上和物理上都独⽴于表数据，索引能优化查询，不能优化DML，oracle⾃动维护索引，频繁的DML操作反⽽会引起⼤量的索引维护。

如果sql语句仅仅访问被索引的列，那么数据库只需从索引中读取数据，⽽不会读取表；如果该语句还要访问未被索引的列，那么数据库会使⽤rowid来查找表中的⾏，通常，为检索表数据，数据库以交换⽅式先读取索引块，然后读取对应的表。

索引的⽬的是减少IO，1. ⼤表，返回的⾏数<5%2. 经常使⽤where⼦句查询的列3. 离散度⾼的列4. 更新键值代价低5. 逻辑AND、OR效率⾼6. 查看索引建在哪表哪列select * from user_indexes;select * from user_ind_columns;索引的使⽤1. 唯⼀索引create unique index empno_idx on emp(empno);2. ⼀般索引create index empno_idx on emp(empno);3. 组合索引create index job_deptno_idx on emp(job,deptno);4. 函数索引：查询时必须⽤到这个函数才会使⽤到。

create index fun_idx on emp(lower(ename));5. 。

查看执⾏计划：set autotrace traceonly explain；索引碎⽚问题：由于基表做DML操作，导致索引表块的⾃动更改操作，尤其是基表的delete操作会引起index表的index_entries的逻辑删除，注意只有当⼀个索引块中的全部index_entry都被删除了，才会把这个索引块删除，索引对基表的delete、insert操作都会产⽣索引碎⽚问题。

ORACLE表空间碎片整理

ORACLE表空间的碎片整理---- 1、碎片是如何产生的---- 当生成一个数据库时，它会分成称为表空间（tablespace）的多个逻辑段（segment），如系统（system）表空间,临时（temporary）表空间等。

一个表空间可以包含多个数据范围（extent）和一个或多个自由范围块，即自由空间（free space）。

---- 表空间、段、范围、自由空间的逻辑关系如下：---- 当表空间中生成一个段时，将从表空间有效自由空间中为这个段的初始范围分配空间。

在这些初始范围充满数据时，段会请求增加另一个范围。

这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。

最理想的状态就是一个段的数据可被存在单一的一个范围中。

这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。

但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的，如图〈1〉。

当要满足一个空间要求时，数据库不再合并相邻的自由范围（除非别无选择），而是寻找表空间中最大的自由范围来使用。

这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。

---- 2、碎片对系统的影响---- 随着时间推移，基于数据库的应用系统的广泛使用，产生的碎片会越来越多，将对数据库有以下两点主要影响：---- （1）导致系统性能减弱---- 如上所述，当要满足一个空间要求时，数据库将首先查找当前最大的自由范围，而"最大"自由范围逐渐变小，要找到一个足够大的自由范围已变得越来越困难，从而导致表空间中的速度障碍，使数据库的空间分配愈发远离理想状态；---- （2）浪费大量的表空间---- 尽管有一部分自由范围（如表空间的pctincrease为非0）将会被smon（系统监控）后台进程周期性地合并，但始终有一部分自由范围无法得以自动合并，浪费了大量的表空间。

oracle需要碎片整理的表

一、背景介绍Oracle数据库作为业界著名的关系型数据库管理系统，在处理大型数据和复杂查询时表现出色。

然而，随着数据库使用时间的增长，数据库内产生的碎片也越来越多，这些碎片将严重影响数据库的性能和查询速度。

对于存在碎片的数据库表，需要进行碎片整理以提高数据库的性能和效率。

二、碎片整理的定义数据库中的碎片是指由于删除、更新等操作而导致数据在磁盘上不连续存储的现象。

碎片整理即是对数据库中的碎片进行清理和重组，使数据在磁盘上存储更加连续，以提高数据库的性能和效率。

三、为何需要碎片整理1. 提高查询速度：碎片整理可以减少数据在磁盘上的碎片化，使得数据库的数据存储更加紧凑，从而提高查询的速度。

2. 节省存储空间：碎片整理可以释放由于碎片化造成的存储空间，减少对磁盘空间的占用。

3. 降低数据库的维护成本：经常进行碎片整理可以减少数据库的维护成本，提高数据库的稳定性和可靠性。

四、如何进行碎片整理1. 使用Oracle自带工具：Oracle提供了一些自带的工具来进行碎片整理，例如DBMS_REDEFINITION包、GATHER_DICTIONARY_STATS过程等。

2. 使用第三方工具：市面上也有一些第三方的数据库碎片整理工具，可以根据具体需求选择适合的工具进行碎片整理操作。

3. 手动整理：在一些特殊情况下，可以选择手动进行碎片整理。

需要建立临时表将数据重新整理后再将数据导入新表来进行碎片整理。

五、碎片整理的注意事项1. 在进行碎片整理前，建议先备份数据库，以防操作出现问题造成数据丢失。

2. 碎片整理操作可能会对数据库的性能产生影响，建议在业务低峰期进行操作，以减少对业务的影响。

3. 在进行碎片整理之前，需要进行全面的评估和规划，确保整理操作的顺利进行。

六、碎片整理的效果评估1. 碎片整理后，需要对数据库进行性能的评估，包括查询速度、存储空间占用等方面进行评估。

2. 如果整理效果不理想，需要及时调整整理策略，并重新进行评估，直到达到预期的整理效果。

数据库索引碎片整理

数据库索引碎片整理数据库索引是提高查询效率的重要组成部分，而索引碎片则会影响数据库性能和查询速度。

本文将以简洁、流畅的语句，论述数据库索引碎片整理的方法和重要性，以帮助数据库管理员优化数据库性能。

一、了解索引碎片在介绍索引碎片整理之前，我们需要先了解索引碎片的概念。

当数据库进行插入、更新或删除操作时，索引数据可能会变得不连续，导致数据存储在磁盘上的非相邻块中。

这就是索引碎片的存在。

索引碎片会降低数据库性能，增加查询时间，并占用不必要的存储空间。

二、确定索引碎片为了确定数据库中存在的索引碎片，管理员可以使用数据库管理工具提供的碎片分析功能。

通过该功能，可以检查索引碎片程度，并确定需要进行整理的索引。

三、索引碎片整理的方法1. 重建索引重建索引是最常用的索引碎片整理方法之一。

管理员可以使用数据库管理工具的重建索引功能，对于存在碎片的索引进行重建操作。

重建索引会删除所有索引，然后重新构建一个连续有序的索引，以提高查询性能。

2. 压缩索引压缩索引也是有效的索引碎片整理方法之一。

此方法会重新组织索引数据，以减少索引的物理存储空间。

通过压缩索引，可以将索引数据中的碎片迁移到一起，提高查询效率。

3. 清空表并重新插入数据在某些情况下，重建索引和压缩索引可能无法完全解决索引碎片问题。

此时，清空表并重新插入数据是一个可行的选择。

管理员可以备份数据后，清空表并重新插入数据，以去除所有的索引碎片。

四、索引碎片整理的重要性1. 提高查询性能索引碎片会导致查询性能下降，因为数据库需要在非连续的磁盘块中查找索引数据。

通过整理索引碎片，可以重新组织索引，使得查询操作更加高效，提高数据库的响应速度。

2. 减少存储空间占用索引碎片会占用不必要的存储空间。

通过整理索引碎片，可以将碎片合并，减少索引的物理存储空间占用，节省数据库存储资源。

3. 优化数据库性能数据库索引是提高数据库性能的关键因素之一。

通过定期整理索引碎片，可以保持索引的连续性，提高查询效率，优化数据库性能。

Oracle索引详解

2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression 时即“基于函数的索引”3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)4）STORAGE：可进一步设置表空间的存储参数5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)6）COMPUTE STATISTICS：创建新索引时收集统计信息7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区1.2 索引特点：第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

oracle表空间碎片计算方法

oracle表空间碎片计算方法Oracle表空间碎片是指表空间中存在的未分配和未使用的空间，它可能会导致数据库的性能下降和空间浪费。

计算Oracle表空间碎片的方法如下：1. 查询表空间的碎片率：使用以下SQL语句查询表空间的碎片率：SELECT tablespace_name, (1 - (sum(bytes) / sum(maxbytes))) * 100 AS fragmentation_rateFROM dba_data_filesGROUP BY tablespace_name;这将返回表空间的碎片率，以百分比表示。

2. 查询碎片的数据文件和段：使用以下SQL语句查询碎片的数据文件和段：SELECT tablespace_name, segment_name, file_id, block_id, blocksFROM dba_extentsWHERE tablespace_name = 'your_tablespace_name'ORDER BY tablespace_name, segment_name;这将返回在指定表空间中存在碎片的数据文件和段的信息。

3. 查询碎片的表和索引：使用以下SQL语句查询碎片的表和索引：SELECT owner, segment_name, segment_typeFROM dba_segmentsWHERE tablespace_name = 'your_tablespace_name'ORDER BY owner, segment_name;这将返回在指定表空间中存在碎片的表和索引的信息。

通过以上方法，可以计算Oracle表空间的碎片率，并查询碎片的数据文件、段、表和索引的详细信息，以帮助优化表空间的使用和提高数据库性能。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle 表空间索引存储与碎片检查Oracle 表空间索引存储检查，Oracle 表空间索引碎片查看，包括查看系统表中的用户索引、索引的存储情况检查、索引的选择性、确定索引的实际碎片。

1、查看系统表中的用户索引在 Oracle 中，SYSTEM 表是安装数据库时自动建立的，它包含数据库的全部数据字典Oracle 表空间索引存储检查，Oracle 表空间索引碎片查看，包括查看系统表中的用户索引、索引的存储情况检查、索引的选择性、确定索引的实际碎片。

1、查看系统表中的用户索引在 Oracle 中，SYSTEM 表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。

一般来说，应该尽量避免在 SYSTEM 表中存储非 SYSTEM 用户的对象。

因为这样会带来数据库维护和管理的很多问题。

一旦 SYSTEM 表损坏了，只能重新生成数据库。

我们可以用下面的语句来检查在 SYSTEM 表内有没有其他用户的索引存在。

以下为引用内容：SELECT *FROM dba_indexesWHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS', 'SYSTEM')2、索引的存储情况检查Oracle 为数据库中的所有数据分配逻辑结构空间。

数据库空间的单位是block 、extent 和 segment 。

Block ：Oracle 使用和分配的最小存储单位。

由数据库建立时设置的DB_BLOCK_SIZE 决定的。

一旦数据库生成了，数据块的大小不能改变。

要想改变只能重新建立数据库。

Extent ：由一组连续的 block 组成的。

一个或多个 extent 组成一个segment 。

当一个 segment 中的所有空间被用完时，Oracle 为它分配一个新的extent 。

Segment ：是由一个或多个 extent 组成的。

它包含某表空间中特定逻辑存储结构的所有数据。

一个段中的 extent 可以是不连续的，甚至可以在不同的数据文件中。

表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 存储层次关系一个 object 只能对应于一个逻辑存储的 segment ，我们通过查看该 segment 中的 extent ，可以看出相应 object 的存储情况。

1. 查看索引段中 extent 的数量以下为引用内容：SELECT segment_name, COUNT ( * )FROM dba_extentsWHERE segment_type = 'INDEX' AND owner = UPPER ('NEWCCS')GROUP BY segment_name2. 查看表空间内的索引的扩展情况以下为引用内容：SELECT SUBSTR (segment_name, 1, 20) "SEGMENT NAME", bytes, COUNT (bytes) FROM dba_extentsWHERE segment_name IN (SELECT index_nameFROM dba_indexesWHERE tablespace_name = UPPER ('NEWCCS'))GROUP BY segment_name, bytesORDER BY segment_name3、索引的选择性索引的选择性是指索引列中不同值的数目与表中记录数的比。

如果一个表中有2000 条记录，表索引列有 1980 个不同的值，那么这个索引的选择性就是1980/2000=0.99 。

一个索引的选择性越接近于1，这个索引的效率就越高。

如果是使用基于 cost 的最优化，优化器不应该使用选择性不好的索引。

如果是使用基于 rule 的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。

确定索引的选择性，可以有两种方法：手工测量和自动测量。

1. 手工测量索引的选择性如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：列的选择性=不同值的数目/行的总数 /* 越接近1越好 */以下为引用内容：select count(distinct 第一列||"%"||第二列)/count(*) from 表名如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。

手工方法的优点是在创建索引前就能评估索引的选择性。

2. 自动测量索引的选择性如果分析一个表，也会自动分析所有表的索引。

• 为了确定一个表的确定性，就要分析表。

以下为引用内容：analyze table 表名 compute statistics• 确定索引里不同关键字的数目以下为引用内容：select distinct_keys from user_indexes where 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="索引名" andi.table_name=t.table_name• 可以查询 USER_TAB_COLUMNS 以了解每个列的选择性。

表中所有行在该列的不同值的数目以下为引用内容：select column_name, num_distinct from user_tab_columns wheretable_name="表名"列的选择性 =NUM_DISTINCT/ 表中所有行的总数，查询 USER_TAB_COLUMNS 有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。

要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。

4、确定索引的实际碎片随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。

插入删除越频繁的表，索引碎片的程度也越高。

碎片的产生使访问和使用该索引的 I/O 成本增加。

碎片较高的索引必须重建以保持最佳性能。

1. 利用验证索引命令对索引进行验证。

这将有价值的索引信息填入 index_stats 表。

以下为引用内容：validate index 用户名.索引名或者：以下为引用内容：analyze index index_name validate structure;index_stats 只保存最近一次分析的结果2. 查询 index_stats 表以确定索引中删除的、未填满的叶子（Leaf）行的百分比和 height 字段。

以下为引用内容：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 索引的时间可以一次生成扩展大于10次的索引的脚本。

以下为引用内容：SELECT 'alter index ' || owner || '.' || segment_name || ' rebuild;' FROM ( SELECT COUNT ( * ),owner,segment_name,t.tablespace_nameFROM dba_extents tWHERE t.segment_type = 'INDEX'AND t.owner NOT IN ('SYS', 'SYSTEM')GROUP BY owner, segment_name, t.tablespace_nameHAVING COUNT ( * ) > 10ORDER BY COUNT ( * ) DESC);4. 如果出于空间或其他考虑，不能重建索引，可以整理索引。

以下为引用内容：alter index用户名.索引名 coalesce5. 清除分析信息以下为引用内容：analyze index 用户名.索引名 delete statistics。