【转】为何在查询中索引未被使用 (Doc ID 1549181.1)

适用于:

Oracle Database – Personal Edition – 版本 8.1.7.4 和更高版本
Oracle Database – Enterprise Edition – 版本 8.1.7.4 和更高版本
Oracle Database – Standard Edition – 版本 8.1.7.4 和更高版本
本文档所含信息适用于所有平台

用途

这篇文章用来解答下面的问题:为什么我的索引没有被使用?

排错步骤

为什么索引没有被使用”是一个涉及面较广的问题。有很多种原因会导致索引没有被使用。下面是一些非常有用的检查列表。请点击下面链接来查看文章的具体内容:

 

快速检查

  • 表上是否存在索引?

    检查您认为应该通过索引访问的表上是否真的有定义索引。那些索引可能已经被删掉或者在创建的时候就失败了 – 比如一种可能的场景是,在对表做导入或 load 操作后,由于软件或人为错误造成索引没有被创建。下面的语句可以用来检查索引是否存在。

     SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;
    
  • 索引是否应该被使用?

    Oracle 不会仅仅因为有索引存在就一定要使用索引。如果一个查询需要检索出这个表里所有的记录(比如说表之间做连接操作),那为什么还要既访问索引的所有数据又访问表的所有数据呢?在这种情况下只访问表的数据会更快。对所有的查询 Oracle Optimizer 会基于统计信息来计算各种访问路径,包括索引,从而选出最优的一个。

 

索引本身的问题

  • 索引列或者索引的前置列是否在单表(non-join)查询的 Where 条件中(predicate list)?

    如果不是,至少需要索引前置列在查询谓词列表中,查询才能使用索引。(例外:请见下面的 Skip Scan)。

    示例:
    在列 EMP.EMPNO 上定义了单列索引 EMPNO_I1,同时在列 EMP.EMPNO 和 EMP.DEPT 上定义了联合索引 EMPNO_DEPT_I2(EMP.EMPNO为索引前置列)。那么必须在查询谓词列表中(where从句)使用列 EMP.EMPNO,优化器才能使用这两个索引中的某一个。

     SELECT ename, sal, deptno FROM emp WHERE empno<100;

    例外:

    • 只要索引中包含查询所需的所有列, 而且至少有一个索引列中含有非空约束,CBO 就能够使用索引快速全扫描(INDEX_FFS)。执行 INDEX_FFS 不需要索引前置列。需要注意的是 INDEX_FFS 不能保证返回的行是排序的。结果的顺序是与读取索引块的顺序一致的,只有当使用了 ‘order by’ 子句时才能保证结果是排序的。请参照:
      Document 344135.1 Ordering of Result Data
      Document 70135.1 Index Fast Full Scan Usage To Avoid Full Table Scans
    • CBO 能使用 Index Skip Scan (INDEX_SS). 执行 INDEX_SS 不需要索引前置列。请参照:
      Document 212391.1 Index Skip Scan Feature
    • CBO 能够选用一个索引来避免排序,但是索引列必须存于在 order by 子句中才可以。
      请参照

      Document 67409.1 When will an ORDER BY use an Index to Avoid Sorting?
      Document 10577.1 Driving ORDER BY using an Index
  • 索引列是否用在连接谓词中(join predicates)?

    例如,下面这个连接谓词定义了如何在表 emp 和 dept 的 deptno 列上做连接:

     emp.deptno = dept.deptno

    如果索引列是连接谓词的一部分,那么查询在执行时使用了哪种类型的连接?

    • 哈希/排序合并连接(Hash / Sort Merge Join): 对于哈希连接和排序合并,在连接执行的时候,外部表的信息还没有获得,因此无法进行对内部表的行检索。它的处理方式是将外部表和内部表分别查询后将结果合并。哈希连接和排序合并的内部表不能通过连接的索引列单独被访问。这是连接类型的执行机制的限制。嵌套循环连接有所不同,它们允许通过索引查询内部表的连接列。
    • 嵌套循环连接(Nested Loops Join):嵌套循环连接读取外部表,然后利用所收集的信息访问内部表。该算法允许对内部表基于索引进行查询。

    只有嵌套循环连接(Nested loops join)允许索引在内部表中仅基于连接列进行查找。

    另外,连接的顺序(join order)是否允许使用索引?
    一个嵌套循环连接的外部表必须已经访问过,才可以在内部表中使用索引。查看 explain plan,以确定哪些访问路径已经使用。由于这个限制,表的连接顺序是很重要的。

    例如:如果我们通过”emp.deptno = dept.deptno”来对 EMP 和 DEPT 做连接,并且在 EMP.DEPTNO 有一个索引,并假设查询中没有与 EMP.DEPTNO 相关的其他谓词,EMP 是在 DEPT 前被访问,然后没有值可用于在 EMP.DEPTNO 索引中查询。在这种连接顺序下,要想使用这个索引我们只能使用全索引扫描或索引快速全扫描。在这种情况下,全表扫描(FTS)的成本可能更小。

  • 索引列在 IN 或者多个 OR 语句中?

    比如:

    emp.deptno IN (10,23,34,....)

       emp.deptno = 10
    OR emp.deptno = 23
    OR emp.deptno = 34
    ....

    这种情况下查询可能已经被转化为不能使用索引的语句。请参照:

    Document 62153.1 Optimization of large inlists/multiple OR`s
  • 索引列是否被函数修改?

    索引不能用于被函数修改的列。函数索引(function based  indexes)可以用来解决这个问题。

    Oracle® Database Concepts
    11g Release 2 (11.2)
    Part Number E25789-01
    Chapter 3 Indexes and Index-Organized Tables
    Section: Function-Based Indexes
    http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT1161

    Oracle® Database Performance Tuning Guide
    11g Release 2 (11.2)
    Part Number E16638-07
    Chapter 14 Using Indexes and Clusters
    Section 14.2 Using Function-based Indexes for Performance
    http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#PFGRF94785

  • 隐式类型转换(implicit type conversion)是什么?

    如果进行比较的两个值的数据类型不同,则 Oracle 必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换。通常当开发人员将数字存储在字符列时会导致这种问题的产生。Oracle 在运行时会强制转化其中一个值,(由于固定的规则)在索引字符列使用 to_number。由于添加函数到索引列所以导致索引不被使用。实际上,Oracle 也只能这么做,类型转换是一个应用程序设计因素。由于转换是在每行都进行的,这会导致性能问题。详见:

    Document 232243.1 ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates
  • 是否在语义(semantically)上无法使用索引?

    出于对查询整体成本的考虑,一个成本较低的执行计划中可能是无法使用索引的。某索引可能已经被考虑在某种连接排序及方法中,但是成本最低的那个执行计划中却无法从“语义”角度使用该索引。

  • 错误类型的索引扫描?

    例如:快速全索引扫描而不是索引范围扫描
    这可能是优化器选择了所需的索引,但却使用了客户不希望的扫描方法。在这种情况下,利用 INDEX_FFS,INDEX_ASC 和 INDEX_DESC 提示来强制使用需要的扫描类型。  请参照:

    Document 62339.1 Init.ora Parameter “FAST_FULL_SCAN_ENABLED” Reference Note

    我们还可以定义索引的排序顺序为递增或递减。Oracle 对待降序索引就好像它是基于函数的索引,因此与缺省使用的升序的执行计划不同。通过查看执行计划,您看不到使用升序或降序,需要额外检查视图 DBA_IND_COLUMNS 的’DESCEND’列。

  • 是否索引列为可空?

    索引不存储 NULL 值,除非该索引为联合索引(即多列索引),或者它是一个位图索引。
    只有至少有一个索引列有值,联合索引才存储空值。联合索引中尾部的空值也会被存放在索引中。如果所有列的值都为空,这行将不会存储在索引中。由于索引中缺乏 NULL 值,那么一些结果中可能会返回 NULL 值(如count)的操作可能会被禁用索引。这是因为优化器不能保证在单独使用索引时可以获得准确的信息。关于使用 NOT IN 和 NULL 的其他一些考虑,请参考

    Document 28934.1 Use of indexes with NOT IN subquery

    位图索引允许存储空值。因此优化器会使用这些索引,无论它们的结果可信与否。索引上的空值有时很有用,特别对于某些类型的 SQL 语句,如与聚合函数 COUNT 查询。示例:

    SELECT count(*) FROM emp;

    位图索引的更多信息请参考

    Document 70067.1 All about Bitmap Indexes
  • NLS_SORT是否设置为二进制(BINARY)?

    如果 NLS_SORT 未设置为二进制,索引将不会被使用。这是因为索引是基于 Key 值的二进制顺序来建立的(pre-sorted使用二进制值)。无论优化器设置为何种方法,NLS_SORT 不是二进制时,将使用全表扫描,。更多关于NLS_SORT和索引的使用,请参考:

    Document 30779.1 Init.ora Parameter “NLS_SORT” Reference
    Document 227335.1 Linguistic Sorting – Frequently Asked Questions (section 4.)
  • 是否使用的是不可见索引(invisible indexes)?

    从 Oracle Database 11g Release 1开始,您可以创建不可见索引或将一个已经存在的索引标记为不可见。Optimizer 不会考虑不可见索引,除非在 session 或 system 级将参数 OPTIMIZER_USE_INVISIBLE_INDEXES 设置为 TRUE。DML 操作还是会维护这些不可见索引的。详见:

    Oracle® Database Administrator’s Guide
    11g Release 2 (11.2)
    Part Number E25494-02
    Guidelines for Managing Indexes
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes002.htm#ADMIN13279

 

优化器和成本计算相关问题

  • 是否存在准确且合适的统计信息(Statistics)?

    CBO 依赖于准确的、最新的和完整的统计信息来确定一个特定查询的最佳执行计划。如果使用 CBO,请确保统计信息已经收集。如果没有统计信息, CBO 将使用预定义的统计信息,这样是很可能不会产生良好的计划或让应用程序使用索引。请参照:

    Document 754931.1 Cost Based Optimizer – Common Misconceptions and Issues – 10g and Above
  • 请注意,CBO 会根据开销(COST)来决定使用不同的索引。除了基本的表和索引的信息之外,如果说在某些列上数据分布是不均匀的,那么还需要收集这些列的数据的分布。关于收集统计信息的建议,请参见以下文档:
    Document 1369591.1 Optimizer Statistics – Central Point
    Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines

    在一般情况下,对象的数据或结构的改变会使以前的统计信息不准确,因此应该重新收集新的统计信息。例如,对表装载了大量的数据后,需要收集新的统计信息。安装新补丁集(Patchset)后,也建议重新收集统计信息。表访问最佳效果是统计信息是在相同版本的数据库中生成的。 下边的文档讨论了为什么数据库升级后查询的性能会下降:

    Document 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance
  • 一个索引是否与其它的索引有相同的等级或者成本(cost)?

    对于相同开销(COST)的索引,CBO 会使用多种办法将不同的索引区分开,如将索引名称按字母顺序排序,完全匹配的索引扫描会选择更大的NDK(不同键值的个数)的索引(不适用于快速全扫描)或选择叶块数量较少的索引。请注意一般很少发生这种情况。 请查看

    Document 73167.1 Handling of equally ranked (RBO) or costed (CBO) indexes
  • 索引的选择度不高?

    • 索引的选择度不高
      使用它可能不是一个好的选择…
      列数据不是平均分布的。
    • CBO 假定列数据不会倾斜,并均匀分布。如果不是这样,那么统计信息可能没有反映真实情况,那么即使某些值的选择度高,索引也会因为整个列的选择度不高而不适用索引。 如果是这种情况,那么应考虑采用直方图记录更准确的列的数据分布或者采用提示(hint)。
    • 统计信息不准确导致索引看起来选择性不高而不被选择。可能的规避方法:
    • 收集更精确的统计值。 请查看
      Document 1369591.1 Optimizer Statistics – Central Point
      Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
    • 对于数据分布不均匀的列考虑收集列的统计信息
    • 使用 hint 或 outline。请参考
      Document 29236.1 QREF: SQL Statement HINTS
      Document 50607.1 How to specify an INDEX Hint
  • 在总体成本中,表扫描的成本占大部分

    通常来说,当使用索引的时候,我们需要再次检索表本身来找到索引中不存在的字段的值,这个操作比检索索引本身的开销要大很多。由于 optimizer 是基于总体的成本来计算执行计划,如果通过索引检索表的成本很大,并且超过了某个阀值,optimizer 就会考虑其他的访问路径。

    比如

    SELECT empno FROM emp WHERE empno=5

    这条语句可能会使用基于列 empno的索引,因为所有需要的数据都存放在索引中所以不需要再对表做而外的访问。反之:

     SELECT ename FROM emp WHERE empno=5

    这条语句会需要对表做而外的访问,因为 ename 字段没有存放在索引中。检索 ename 的开销会随着查询返回记录条数的增加而变得昂贵。

    Optimizer 使用”Clustering Factor”来判断如果使用 index 的话需要而外对表做多少次访问,详见:

    Document 39836.1 Clustering Factor
  • 访问空索引并不意味着比访问有值的索引高效。

    Reorganization, Truncation 或删除操作不一定会影响 SQL 语句执行的成本。需要注意的是删除操作并不会从对象中真正释放空间。也就是说,删除操作不会重置对象的高水位线。Truncate 操作会重置高水位线。空块的存在会使索引/表扫描的成本比实际应该的成本高。删掉并重建会重组对象的结构从而有可能会有帮助(也有可能变坏)。这类问题通常在比较两个有相同数据的不同系统查询性能时更容易看到。

  • 参数设置

    某些参数的设置可能会影响索引的使用。比如在大多数情况下都建议使用 DB_FILE_MULTIBLOCK_READ_COUNT 和 OPTIMIZER_INDEX_COST_ADJ 的默认值。除非某些特定的操作有特定的建议,使用其它值会使索引的成本不现实的减少或变大从而极大的降低查询的性能。

 

其它问题

  • 是否使用了视图/子查询?

    查询涉及到视图或者子查询时可能会被改写,导致不使用索引(尽管该改写的目标之一是扩展更多的访问路径)。这些改写(rewrite)一般来说都是合并(merging)操作。请查看

    Document 199070.1 Optimizing statements that contain views or subqueries
  • 是否存在远程表(remote table)?

    通常远程表不会使用索引。索引在分布式查询中的使用依赖于被发送到远程的查询。CBO 将评估远程访问的成本,并评估比较发送或者不发送索引的谓词到远程站点的成本。因此,CBO 可以做出有关远程表上使用索引的更加明智的决定。一个非常有效的方法就是,在远程建立包含相关谓词的视图并强制使用索引,之后在本地查询中使用这个视图。 请参考

    Document 68809.1 Distributed Queries
  • 是否使用并行执行(PX)?

    在并行执行时索引的采用比在串行执行((serial execution))时更加严格。一个快速检测的方法就是禁用并行,然后查看该索引是否被使用。

  • 是否是包含了子查询的Update语句?

    在一些情况下,基于成本的考虑,索引没有被选使用是因为它依赖于一个子查询返回的值。这种情况下,可以使用提示(hint)来强制使用索引。请参考

    Document 68084.1 Using hints to optimize an Update with a subquery that is not using an index on the updated table.
  • 查询是否使用了绑定变量?

    CBO 对 like 或范围谓词的绑定变量不能产生准确的成本(cost)。这可能会导致索引不被选择。 请参考

    Document 68992.1 Predicate Selectivity
  • 查询是否引用了带有延迟约束的列?

    如果一个表中的某一列上含有延迟约束(比如 NOT NULL)并且这一列上有索引,那么不管这个约束当前是延迟状态或是被显式地设置为立即使用,我们都不会考虑使用这一列上的索引。例如:

    CREATE TABLE tdc
    ( x INT CONSTRAINT x_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED RELY,
    y INT CONSTRAINT y_not_null NOT NULL,
    z VARCHAR2(30)
    );
    CREATE INDEX t_idx ON tdc(x);

    SET CONSTRAINTS ALL IMMEDIATE; <– 将所有延迟约束置为立即使用

    SET AUTOTRACE TRACEONLY EXPLAIN

    SELECT COUNT(1) FROM tdc;        <– 索引不会被使用

    Execution Plan
    ———————————————————-
    Plan hash value: 2532426293

    ——————————————————————-
    | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
    ——————————————————————-
    |   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| TDC  |     1 |     2   (0)| 00:00:01 |
    ——————————————————————-

    这个现象在以下 bug 中记录,关闭为”not a bug”:

    Bug 17895663  optimizer ignoring deferrable constraints even when not deffered and not in tx
  • 索引提示(hint)不工作

    请使用表的别名。 请参考

    Document 69992.1 Why is my hint ignored?
    Document 50607.1 How to specify an INDEX Hint
    有用的 hints:
    FIRST_ROWS 相当于提示使用索引
    ORDERED 强制查询的关联顺序(join order of a query)。Oracle 推荐使用 LEADING hint 因为它更好用。
    LEADING 这个 hint 告诉 optimizer 先使用指定的表做连接。它比 ORDERED 更好用。
    INDEX 强制使用索引扫描, 并禁用快速模式(INDEX_FFS)
    INDEX_FFS 强制使用快速索引扫描INDEX_FFS
    INDEX_ASC 强制使用升序的索引范围扫描(Ascending Index Range Scan)
    INDEX_DESC 强制使用降序的索引范围扫描(Descending Index Range Scan)

    参见:

    Document 29236.1 QREF: SQL Statement HINTS

Oracle索引块分裂split信息汇总

 

索引块分裂概念介绍

 

 

 

索引中的数据块 按照其作用分为:root block 根块、branch block  枝块、leaf block 叶块。

 

root block根块:

 

  • 根块是索引的入口
  • 对于一个索引而言根块的DBA data block address是固定的

 

 

 

Branch Block:

 

  • 中间的媒介块, root指向branch ,branch指向 leaf
  • 数据格式 <Child DBA> <Sseparator>

 

Leaf  Block:

 

  • 最低级别的索引块
  • 实际数据存放在leaf blocks中
  • 数据格式<Flag> <Lock> <Keydata> <Key> <NonKey>

 

 

index btree

 

 

oracle中的索引块分裂主要分成 以下几种:

  • leaf node 90-10 splits
  • leaf node 50-50 splits
  • branch node splits
  • root node splits

 

 

 

 

索引块分裂index block split 发生在 当某一个 索引块(root branch leaf) 的空闲空间不足以容纳新加入的记录时, 一般来说INSERT是引起索引块分裂的主要操作。

 

 

按照 不同的 索引块分类, 其分裂行为可以分为:

  • Leaf Block Split
  • Branch Block Split
  • Root Block Split

 

按照 leaf Block Split 分裂时的行为 又可以分为:

leaf node 90-10 splits    插入到索引leaf block叶子块中的索引键是该块中最大的键值(包括块中已删除的索引键值)。 在此种情况下实施 90-10 split( 实际是 99-1 ),原叶子块仍保持99%的full, 而到另一个空的叶子块中插入该条新的最大键值记录。如图:

 

90-10 split

 

leaf node 90-10 splits  行为的次数可通过v$sysstat视图 中leaf node 90-10 splits获得,AWR中也有对应记录:

 

leaf node split

 

 

 

leaf node 50-50 splits   当插入到索引叶子块中的索引键值不是该块中的最大值时(包括块中已删除的索引键值), 将发生 50/50 split分裂, 这意味着有一半索引记录仍存在当前块,而另一半数据移动到新的叶子块中。

 

leaf node 50-50 splits  行为的次数可通过 v$sysstat视图 中的 (leaf node splits)-(leaf node 90-10 splits)获得,AWR中也是类似:

 

leaf node split

 

如上图中, 则在该AWR性能报告覆盖的时间中leaf node 50-50 split 发生了(1320-267)=1053 次

 

 

50-50 block split

 

 

Branch Block  50-50 Split  由于不断的索引叶块分裂需要将新的leaf block的信息加入到branch block中,当branch block没有足够空间容纳新的记录时,又会引发branch block的 Split 。  branch block的split 是50-50的,即将一半记录移动到新的branch block中。

 

Branch Block  50-50 Split   行为的次数可通过 v$sysstat视图 中的branch node splits获得,AWR中也是类似:

 

 

branch node splits

 

 

 

 

Root Block 50-50 Split    root block 根块实际是一种特殊的branch block, 当root block 50-50 split分裂发生时将分配2个新的数据块, 分裂将一半的数据移动到一个新块中,另一半数据移动到另一个新块中。  并更新原来的root block,使之指向那2个新的数据块,实际上是2个branch block了。

 

但是root block的数据块位置本身没有变,仍旧是原来的那个数据块。 当root block split发生时 会导致索引的高度上升。

 

Root Block 50-50 Split   行为的次数可通过 v$sysstat视图 中的root node splits获得,AWR中也是类似:

 

 

root node splits

 

 

 

索引的高度 height 视乎 索引中记录的多少 、Leaf Block的数量而定,一般Height 为3~4; 举例来说当一个索引的高度为4,但是其中包含大量删除的记录,那么通过索引rebuild 往往可以降低其高度,例如从4降低到3。 假设该索引再次插入了大量的数据,造成leaf block不断分裂,最终导致root block 再次分裂, 索引高度从3再次上升到4,在这个root block split 的过程中可能短期内阻塞索引的DML维护,导致进程等待” enqueue TX: index contention” , 这在OLTP环境中是很常见的问题, 所以一般不推荐在OLTP环境中rebuild index,虽然rebuild index能够减少索引碎片回收空间 ,但由于rebuild index可能导致索引高度降级,所以对于OLTP环境的索引rebuild 需要慎重。

 

 

 

等待事件

 

当索引块分裂发生时, 负责实施分裂 split 的进程会持有 相关的队列锁enqueue TX 锁, 直到该进程完成Split操作才会释放该enqueue Tx,在这个过程中负责split的进程需要找到合适的新块并将对应的数据移动到该新块中。  若在此split过程中,有其他进程INSERT数据到该索引块中,则将进入 enq: TX – index contention等待事件,直到split结束enqueue TX被释放。

 

负责split的进程需要找到一个合适的新块, 其会优先寻找本index 已分配的空间中的 free block, 这些free block应当是100% free的,但是在Oracle 的segment bitmap block 中只区分 0%-25%,25%-50%, 50%-75%, 75%-100% 使用率的数据块, 即无法直接区分 0%-25%使用率的数据块中哪些是100% free的数据块,  Oracle这样做的目的是 为了重用数据块,以避免过度分配空间。   当oracle发现没有可重用的数据块时才会扩展索引空间并移动分裂数据。

这个在split 过程中 寻找可复用的free block的过程称之为failed probes on index block reclamation,在正常的情况下这种找寻可复用块的过程是很快的 ,但是如果 恰好遇到 物理读缓慢或者 全局的数据块争用时,该过程可能变得很慢,这将直接导致split 变慢, 进而导致大量INSERT进程长时间等待enq: TX – index contention。

 

 

 

 

RAC中的索引块分裂

 

在RAC环境中 由于全局缓冲块的争用 以及 全局队列的争用, 在RAC private network 心跳网络传输存在性能瓶颈的情况下可能导致 负责split工作的进程在分裂过程中遇到 例如 gc buffer busy、gc current block busy、gc current split等等待事件,这将极大拖慢 split的速度,导致大量INSERT进程长时间等待enq: TX – index contention。

 

 

 

 

SQL> select name from v$sysstat where name like '%split%' order by name;

NAME
------------------------------------------------------------------------
branch node splits
index compression (ADVANCED HIGH) leaf block 90_10 splits faile
index compression (ADVANCED HIGH) leaf block splits avoided
index compression (ADVANCED LOW) reorg avoid split
leaf node 90-10 splits
leaf node splits
queue splits
root node splits

 

 

相关Event 

 

 

10224 “index block split/delete trace”

 

drop table t1;
create table t1 (name varchar2(2000),nr number) pctfree 0;
create index i1 on t1(name);

alter session set events '10224 trace name context forever,level 1';

declare
i number;
begin
for i in 1..35
loop
insert into t1 values(rpad('Maclean',200,'I'),i);
end loop;
end;
/

insert into t1 values(rpad('9999999',200,'1'),9999);
insert into t1 values(rpad('9999999',200,'1'),9999);
insert into t1 values(rpad('9999999',200,'1'),9999);

oradebug setmypid
oradebug tracefile_name

*** 2013-09-02 12:07:42.632
splitting leaf,dba 0x00419669,time 12:07:42.632
kdisnew first,dba 0x0041966a,time 12:07:42.667
kdisnew using block,dba 0x0041966a,time 12:07:42.667
kdisnew first,dba 0x0041966a,time 12:07:42.667
kdisnew reject block unlink,dba 0x0041966a,time 12:07:42.667
kdisnew loop trying,dba 0x0041966b,time 12:07:42.667
kdisnew using block,dba 0x0041966b,time 12:07:42.667

 

 

 

 

Reference : <Oracle B-Tree Index Internals: Rebuilding The Truth> Richard Foote

Index block split等待事件

When a request for rows from a table is issued, Oracle may determine through the cost-based optimizer, which index access path is best for finding rows in a table. During this index lookup process, if another session is inserting or updating data, which causes updates to that index and an index block split, the first session must wait on that index block split until finished. After which the first session must retry the index lookup request again to get the appropriate index keys for the rows required… more …

Analysis

Finding the splitting index:

When an index block split causes a session to wait, an event will be seen in the V$SESSION_WAIT view. The wait time associated with the event that holds up the session from selecting a row is important, but often just determining the splitting index object is key. The block splitting can then be limited by modifying the structure of the index. There are essentially three steps to this process of finding the offending index:

Find the data block addresses (dba) of the splitting index from the V$SESSION_WAIT view. Two different dbas are given plus the level of the index block:

P1: rootdba: The root of the index
P2: level: The level of the index where the block is being split
P3: childdba: The actual block of the index being split

SELECT sid, event, p1, p2, p3 FROM v$session_wait;

Find the physical location of the splitting index by using the DBMS_UTILITY package. Two functions will help zero in on the physical location of the index block using the rootdba value from step 1:

DATA_BLOCK_ADDRESS_FILE: Returns the file number of the dba
DATA_BLOCK_ADDRESS_BLOCK: Returns the block number the dba

SELECT DBMS_UTILITY.DATA_BLOCK _ADDRESS_FILE(<rootdba>) FILE_ID,
DBMS_UTILITY.DATA_BLOCK_ADDRESS _BLOCK(<rootdba>) BLOCK_ID
FROM dual;

Find the offending index object from DBA_EXTENTS using the FILE_ID and BLOCK_ID values determined from step 2:

SELECT owner, segment_name
FROM dba_extents
WHERE file_id = <FILE_ID>
AND <BLOCK_ID> BETWEEN block_id AND block_id + blocks -1;

Solutions

Re-evaluate the setting of PCTFREE for problematic indexes. Giving a higher PCTFREE will allow more index entries to be inserted into existing index blocks and thus prolong the need for an index block split.

Check the indexed columns to make sure they are valid. An improperly formed index key can cause excessive splitting by the nature and order of the columns it contains.

Check application logic. Many ill-formed applications have been known to perform excessive updates to indexes when not required. Review application code to verify all data manipulations are required, especially if some tables are treated as temporary objects when in fact they are permanent tables.

Expanded Definition

Indexes are made up of a root block, branch blocks, and leaf blocks. Each of which can go through a block split. As index entries are created, and because index structures are inherently ordered, if the block required to hold the new index key is full, room must be made by performing a block split. These block splits can occur in two different flavors.

The first case splitting the block 50/50 where a new block is created and half the entries are contained in each of the blocks after the split. The second case is a 99/1 split that accommodates indexes where there are ever increasing values and the new key value is the highest key. In this case the original block is left intact and the new block contains only the new entry.

Oracle Compressed Indexes

Key compressed indexes are a way to index a low cardinality column without talking up as much space storing the repetitive values. Please view the Oracle Manuals for a full description on implementing compressed indexes.

 

Benefits

  • Fewer index leaf blocks need to be read since more rows fit into fewer blocks
  • Insert statements are faster since fewer leaf blocks need to be read to determine where the new row is inserted
  • Faster full index scans due to fewer leaf blocks
  • Space savings

SQL调优:Clustering Factor影响数据删除速度一例

事情是这样的,客户有一套核心的10g业务数据库,需要针对个别大表删除2年前的归档数据,这些表都是普通的堆表(heap table),没有使用分区或其他技术。因为考虑到不能影响在线业务,所以不能使用insert append/rename的方式来加速删除,只能老老实实地在匿名PL/SQL块里通过rowid批量删除数据,虽然慢一些但还是能接受的,具体的PL/SQL块如下:

DECLARE
   CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from table_name a  where time_column<required_date
   table_name_rec table_name_cur%ROWTYPE;
   row_number number;
BEGIN
   row_number :=0;
   OPEN table_name_cur;
   LOOP
      FETCH table_name_cur INTO table_name_rec;
      IF table_name_cur%NOTFOUND
      THEN
      commit;
         EXIT;
      END IF;
      delete from table_name WHERE rowid = table_name_rec.rowid;
      row_number := row_number + 1;
           if (mod (row_number,1000) =0) then
           insert into delete_rows values (row_number);
           commit;
           end if;
   END LOOP;
  insert into delete_rows values (row_number);
   commit;
   CLOSE table_name_cur;
END;
/

可以看到以上使用一个游标FULL SCAN目标数据表取出所需删除行的rowid,之后在循环中不断fetch出rowid并实际删除数据。

问题出在一张不是非常大的LG表上(不超过10GB),删除这张LG表消耗的时间超过10个小时,而其他更大的表删除也仅用2-3个小时。

针对这一反常现象,客户对删除操作做了10046 level8的跟踪,因为整个删除过程比较长,所以仅 trace了一小段时间,因为这个trace并不完整所以没有办法使用tkprof工具分析该trace。没办法,就分析裸trace信息吧。

从trace内容来看,该时段内主要的等待是db file sequence read(简称DFSR)即数据文件单块读事件,一开始以为是表上有链式行/迁移行造成了大量的DFSR,但客户日常有对该表执行chained rows analyze,没有发现该表上有明显的chained/migrated rows问题。

具体观察该DFSR事件的p1/p2 obj#参数发现这些数据文件单块读主要是针对该LG表的2个索引的,而且最为奇怪的是其中一个索引单块读的频率远多于另外一个索引,比例大约为60:1。这2个索引的差异表现,让我意识到得问题的所在,查看dba_indexes索引视图发现最近一次分析是在4/18日,而2个索引统计信息间最大的差异不在于索引大小,而在于clustering_factor也就是我们说的聚集因子, LG表上大约有6000万条数据,索引A的clustering_factor为170万,而索引B的clustering_factor达到了3400万,即2个索引的聚集因子差20倍,显然这时因为索引A上column更为有序(可能是sequence)而索引B上的字段较为随机造成了这种反差。

因为一开始使用FULL SCAN目标数据表来获取所需的ROWID,所以在实际删除前相关数据行所在的表数据块已经被FULL SCAN读取到buffer cache中了,FULL SCAN使用scattered read多块读,在这里不是主要的性能瓶颈。最主要的问题在于,假设一个table data block中有20行数据,对于clustering_factor较低的索引A而言可能这20行数据都映射到索引的一个leaf block中,而对于clustering_factor很高的索引B而言可能这20行数据需要映射到20个leaf block中,那么如果我们要删除这个数据块中的20行数据,就需要访问索引A上的1个leaf块和索引B上的20个leaf块,因为这些都是历史归档数据,所以日常已经没有业务访问这些old leaf block了,所以这部分的叶子块几乎不会在buffer cache中,服务进程需要把它们”一一”(这里真的是一一,一次读一块)从disk上读取到缓存中;最糟糕的还不止于此,因为实例的buffer cache有限,索引B上的leaf block在读入后可能很快被踢出buffer cache,而因为table与索引B间数据的无序性,这些leaf block可能需要在后续的删除中再次访问,这将对索引B的物理读取代价大大放大了。这种代价反映在Oracle wait interface上就是用户频繁地看到针对某个索引的”db file sequential read”等待事件。

我们通过还原现场,来深入了解一下clustering factor对于删除的影响:

首先要构建一张有一列极端有序,而一列极端无序的数据表
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000; 
Table created. 
SQL> alter table sample add t2 number;
Table altered.
SQL> update sample set t2=dbms_random.value(1,999999999999999);
900000 rows updated.
SQL> commit;
Commit complete.
SQL> create index ind_t1 on sample(t1) nologging;
Index created.
SQL> create index ind_t2 on sample(t2) nologging;
Index created.
SQL> exec dbms_stats.gather_table_stats('MACLEAN','SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';
BLOCKS   NUM_ROWS
---------- ----------
13213     900000
SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';
CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME
----------------- ----------- ------------- ------------------------------
899782        4148        896896 IND_T2
14975        2004        900000 IND_T1
/* 以上构建了一张90万行的数据表,共13213个block
T1列通过序列产生,较为有序
T2列通过随机数产生,无序
ind_t1索引构建在t1列上,clustering_factor较低14975,接近表上数据块的总数
ind_t2索引构建在t2列上,clustering_factor为899782,接近表上数据行的总数
*/  
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> set timing on;
DECLARE
CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from sample a  where t1<=900000;
table_name_rec table_name_cur%ROWTYPE;
row_number number;
BEGIN
row_number :=0;
OPEN table_name_cur;
LOOP
FETCH table_name_cur INTO table_name_rec;
IF table_name_cur%NOTFOUND
THEN
commit;
EXIT;
END IF;
delete from sample WHERE rowid = table_name_rec.rowid;
row_number := row_number + 1;
if (mod (row_number,1000) =0) then
insert into delete_rows values (row_number);
commit;
end if;
END LOOP;
insert into delete_rows values (row_number);
commit;
CLOSE table_name_cur;
END;
/
Elapsed: 00:03:28.52 
观察其trace文件,可以发现在多次EXEC/FETCH后就会紧跟一个db file sequential read等待事件 
众所周知db file sequential read等待事件在如v$session/V$session/v$active_session_history
等动态性能视图中的p1代表file号,p2为block号,p3为读取block总数,一般为1
而在10046 trace中可以直接看到file#,block#,blocks和obj#,为了分辨单块读的对象,可以直接从obj#了解
SQL> select object_id,object_name,object_type from dba_objects where object_name in ('SAMPLE','IND_T1','IND_T2');
OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
1307548 IND_T1               INDEX
1307549 IND_T2               INDEX
1307547 SAMPLE               TABLE
WAIT #3: nam='db file sequential read' ela= 283 file#=6 block#=3311 blocks=1 obj#=1307549 tim=1275797217728516
EXEC #3:c=999,e=349,p=1,cr=2,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728552
FETCH #2:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728578
EXEC #3:c=0,e=49,p=0,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728641
FETCH #2:c=0,e=4,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728663
EXEC #3:c=0,e=36,p=0,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728712
FETCH #2:c=0,e=3,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728732
WAIT #3: nam='db file sequential read' ela= 205 file#=6 block#=3956 blocks=1 obj#=1307549 tim=1275797217728979
EXEC #3:c=0,e=265,p=1,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217729010
FETCH #2:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217729036
[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|wc -l
72395
[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|grep 1307549|wc -l
67721
[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|grep 1307548|wc -l
3878
/* 以上object_id 1307549对应为较高clustering_factor的索引IND_T2,
该索引发生了绝大多数db file sequential read等待
而object_id 1307548对应为较低clustering_factor的索引IND_T1,
该索引发生了较少量的db file sequential read等待
*/
SQL> select sql_id,executions,disk_reads,user_io_wait_time/1000000,elapsed_time/1000000
2  from v$sql
3  where sql_text='DELETE FROM SAMPLE WHERE ROWID = :B1 ';
SQL_ID        EXECUTIONS DISK_READS USER_IO_WAIT_TIME/1000000 ELAPSED_TIME/1000000
------------- ---------- ---------- ------------------------- --------------------
31m4m2drt2t5m     900000      74936                 67.862581           147.743482
[oracle@rh2 udump]$ tkprof g10r2_ora_5190.trc 5190.tkf sys=no
DELETE FROM SAMPLE
WHERE
ROWID = :B1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 900000     78.67     147.73      74936     916440    6401613      900000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   900001     78.67     147.73      74936     916440    6401613      900000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64     (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
SQL*Net message to client                       1        0.00          0.00
SQL*Net message from client                     1       21.99         21.99
 db file sequential read                     72362        0.05         67.60
db file scattered read                        543        0.00          0.25
log file switch completion                     29        0.97          5.81
free buffer waits                             268        0.01          2.83
latch: cache buffers lru chain                  3        0.00          0.00
latch: object queue header operation            5        0.00          0.00
log file switch (checkpoint incomplete)        22        0.97          8.46
latch: In memory undo latch                     2        0.00          0.00
latch: cache buffers chains                     1        0.00          0.00

可能是受限于固化的思维,在我的潜意识中总是觉得clustering_factor聚集因子只会影响select查询语句,而忽略了其对update/delete操作的影响;事实是clustering_factor(注意它只是一个统计信息指标,而非参数)反映了数据在表中的随机分布程度,当表上的数据分布无序时表和索引间的交叉访问将显得很糟糕,这种交叉访问并不局限于查询语句(一个典型可能是INDEX RANGE SCAN-TABLE ACCESS BY INDEX ROWID),也可能发生在DML操作所隐含的维护索引操作中。

显然除了通过以某些列的顺序整理表外没有太好的方法来降低clustering_factor,但实际上这样做是不可能的。首先定期有序化整理的成本过高了,其次如果表上有多个单列上的不同索引,如我们上述演示中的t1、t2列,如果以t2列的顺序整理表那么一个很可能的结果是t1列上的索引的clustering factor猛增,如:

SQL> create table ordered_sample nologging tablespace users as select * From sample order by t2;
Table created.
SQL> truncate table sample;
Table truncated.
SQL> insert /*+ append */ into sample select * from ordered_sample;
900000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('MACLEAN','SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select clustering_factor,index_name from dba_indexes where table_name='SAMPLE';
CLUSTERING_FACTOR INDEX_NAME
----------------- ------------------------------
899782 IND_T1
3983 IND_T2
/* 可以看到ind_t2所以的clustering_factor的确下降了,换得的是ind_t1对应的增长 */

针对由该clustering_factor引起的低效率批量delete/update操作,我们可以通过以下措施减少”db file sequential read”等待的出现:

  1. 通过keep cache保留池等技术将clustering_factor过高的索引缓存住,以避免频繁地单块物理读,从而提高性能
  2. 如果你正在执行一个大作业,那么可以暂时将clustering_factor过高的索引drop掉,在完成操作后再重建该索引,这样起到加速作业的目的

rebuild重建索引在以上案例的情景中获益并不大。

另外”db file sequential read”单块读等待是一种最为常见的物理IO等待事件,这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space),而不是指所读取的数据块是连续的。该wait event可能在以下情景中发生:

  1. 最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的,一般不需要我们去特别关注
  2. 当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”),服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table,每次物理读取都会进入”db file sequential read”等待,且每次读取的都是一个数据块;这种情况下clustering_factor将发挥其作用,需要我们特别去关注,本例中提及的解决方法对这种情景也有效
  3. Extent boundary,假设一个Extent区间中有33个数据块,而一次”db file scattered read”多块读所读取的块数为8,那么在读取这个区间时经过4次多块读取后,还剩下一个数据块,但是请记住多块读scattered read是不能跨越一个区间的(span an extent),此时就会单块读取并出现”db file scattered read”。这是一种正常现象,一般不需要额外关注
  4. 假设某个区间内有8个数据块,它们可以是块a,b,c,d,e,f,g,h,恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了,而这时候恰好要访问这个区间中的数据,那么此时就会单块读取d这个数据块,并出现”db file sequential read”等待。注意这种情况不仅于表,也可能发生在索引上。这是一种正常现象,一般不需要额外关注
  5. chained/migrated rows即链式或迁移行,这里我们不介绍链式行的形成原因,chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取,从而出现”db file sequential read”。这种现象需要我们特别去关注,因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表,在出现大量链式行后,全表扫描需要数个小时),同时也会对其他操作造成不那么明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况,还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况,当然这要求定期收集表上的统计信息;如果没有定期收集的习惯,那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息
  6. 创建Index entry,显然当对表上执行INSERT操作插入数据时,虽然在执行计划中你看不到过多的细节,但实际上我们需要利用索引来快速验证表上的某些约束是否合理,还需要在索引的叶子块中插入相关的记录,此时也可能出现”db file sequential read”等待事件,当然这还和具体的插入的方式有关系。这是一种正常现象,一般不需要额外关注
  7. 针对表上的UPDATE/DELETE,不同于之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”,如果我们使用rowid去更新或删除数据时,服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据,之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里,所以这里同样要如range-scan/unique-scan那样去访问index branch block),这些访问都将会是单块读取,并会出现’db file sequential read’,完成必要的读取后才会执行更新或删除的实际EXEC操作,如下例:
以下trace中,obj#=1307547为sample表,而obj#=1307549为sample表上的唯一一个索引 
PARSING IN CURSOR #10 len=58 dep=0 uid=64 oct=6 lid=64 tim=1275805024007795 hv=505118268 ad='d387e470'
update sample set t2=t2+1 where rowid='AAE/OzAAEAAANUEAAQ'
END OF STMT
PARSE #10:c=1999,e=3016,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=1275805024007787
WAIT #10: nam='db file sequential read' ela= 314 file#=4 block#=54532 blocks=1 obj#=1307547 tim=1275805024008308
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=20 blocks=1 obj#=1307549 tim=1275805024009235
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=742 blocks=1 obj#=1307549 tim=1275805024009496
WAIT #10: nam='db file sequential read' ela= 207 file#=6 block#=24 blocks=1 obj#=1307549 tim=1275805024009750
EXEC #10:c=2000,e=2297,p=6,cr=2,cu=8,mis=0,r=1,dep=0,og=1,tim=1275805024010210   --实际的UPDATE发生在这里

当大量执行这类UPDATE/DELETE操作时将需要频繁地交叉访问表和索引,如果恰好表上的某个索引有较高的clustering_factor的话,那么就会形成本例中的这种性能问题了。实际上当表上有较多索引时,使用rowid来批量update/delete数据这种方式是不被推荐的,仅当表上没有索引时才可能十分高效。如果你坚持要这样做,那么可以参照上面提到的建议。

 

8.BUG!BUG!已知在9i RAC及10g中使用ASM的情况下,存在引发在适用情况下不使用”scattered read”多块读而去使用”sequential read”的BUG。如果你的问题和上述情景都不匹配,但又有大量的”db file sequential read”等待事件,那么你有可能遇到bug了。在这里列出部分已知bug:

Bug# Version Affected
Bug 7243560 – High “db file sequential read” IO times when using ASM 10.2.0.4/11.1.0.7
Bug 7243560: RAPID INCREASE IN DB FILE SEQUENTIAL READ AFTER MOVING TO ASM 10.2.0.3
Bug 9711810: EXCESSIVE DB FILE SEQUENTIAL READS WITH NON COMPLIANT BUFFER CACHE ON RAC 9.2.0.8
Bug 9276739: INSERT STATEMENT HAS SLOW PERFORMANCE WITH DB FILE SEQUENTIAL READ 10.2.0.4
Bug 8625100: EXCESSIVE DB FILE SEQUENTIAL READ ON UNDO 10.2.0.4
Bug 8669544: HIGH DB FILE SEQUENTIAL READ AND GC CR DISK READ WAIT EVENTS DURING FULL SCAN 10.2.0.4
Bug 7427133: AN INSERT CAUSES LOTS OF ‘DB FILE SEQUENTIAL READ’ WAITS FOR THE INDEX BLOCKS 9.2.0.8
Bug 8493139: INCREASE IN DB FILE SEQUENTIAL READ WAITEVENT AFTER MIGRATING TO 10 RAC/ASM 10.2.0.4
Bug 5882268: PERFORMANCE ISSUE WITH ‘DB FILE SEQUENTIAL READ’ 10.2.0.2
Bug 7415702: LOTS OF ‘DB FILE SEQUENTIAL READ’ ON UNDO 10.2.0.3
Bug 5607724: 10202 DB FILE SEQUENTIAL READ THRICE AFTER UPGRADE FROM 9I 10.2.0.2

Alter index coalesce VS shrink space

10g中引入了对索引的shrink功能,索引shrink操作会扫描索引的页块,并且通过归并当前存在的数据将先前已删除记录的空间重新利用;很多书籍亦或者MOS的Note中都会提及SHRINK命令与早期版本中就存在的COALESCE(合并)命令具有完全相同的功能,或者说2者是完全等价的-” alter index shrink space is equivalent to coalesce”,事实是这样的吗?

SQL> conn maclean/maclean
Connected.
/* 测试使用版本10.2.0.4 * /
SQL> select * from v$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/* 建立测试用表YOUYUS,高度为3 */
SQL> drop table YOUYUS;
Table dropped.
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
SQL> analyze  index IND_YOUYUS validate  structure;
Index analyzed.
/*
大家因该很熟悉 analyze index .. validate structure 命令 ,实际上该命令存在一个兄弟: 
analyze  index IND_YOUYUS validate  structure online,
加上online子句后validate structure可以在线操作,但该命令不会填充index_stats临时视图
*/
SQL> set linesize 200;
SQL> set linesize 200;
SQL> select height,
2         blocks,
3         lf_blks,
4         lf_rows_len,
5         lf_blk_len,
6         br_blks,
7         br_rows,
8         br_rows_len,
9         br_blk_len,
10         btree_space,
11         used_space,
12         pct_used
13    from index_stats;
HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3       5376       5154    36979767       7996          9       5153       61784       8028    41283636   37041551         90
/*  可以看到IND_YOUYUS索引的基本结构,在初始状态下其block总数为5376,其中页块共5154  */
/*  我们在表上执行删除操作,均匀删除三分之一的数据 */
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
SQL> commit;
Commit complete.
SQL> conn maclean/maclean
Connected.
SQL> select vs.name, ms.value
2    from v$mystat ms, v$sysstat vs
3   where vs.statistic# = ms.statistic#
4     and vs.name in ('redo size','consistent gets');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          45
redo size                                                                 0
SQL> alter index ind_youyus coalesce;
Index altered.
SQL> select vs.name, ms.value
2    from v$mystat ms, v$sysstat vs
3   where vs.statistic# = ms.statistic#
4     and vs.name in ('redo size','consistent gets');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         788
redo size                                                          70649500
/* coalesce 操作产生了大约67MB的redo数据  */
SQL> analyze  index IND_YOUYUS validate  structure;
Index analyzed.
SQL> set linesize 200;
SQL> select height,
2         blocks,
3         lf_blks,
4         lf_rows_len,
5         lf_blk_len,
6         br_blks,
7         br_rows,
8         br_rows_len,
9         br_blk_len,
10         btree_space,
11         used_space,
12         pct_used
13    from index_stats;
HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3       5376       3439    24653178       7996          9       3438       41188       8028    27570496   24694366         90
/* 可以看到执行coalesce(合并)操作后页块数量下降到3439,
而branch枝块和root根块的结构是不会变化的,同时coalesc命令并不释放索引上的多余空间,
但索引结构实际占用的空间BTREE_SPACE下降到了27570496 bytes */
/* 以下为此时ind_youyus索引的treedump * /
[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5104.trc| \
grep "level:";cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5104.trc|grep leaf|wc -l
branch: 0x130787c 19953788 (0: nrow: 8, level: 2)
branch: 0x1308c41 19958849 (-1: nrow: 450, level: 1)
branch: 0x1308eea 19959530 (0: nrow: 447, level: 1)
branch: 0x1309195 19960213 (1: nrow: 447, level: 1)
branch: 0x130943e 19960894 (2: nrow: 447, level: 1)
branch: 0x13096e7 19961575 (3: nrow: 447, level: 1)
branch: 0x1309992 19962258 (4: nrow: 447, level: 1)
branch: 0x1309c3b 19962939 (5: nrow: 447, level: 1)
branch: 0x1309e0f 19963407 (6: nrow: 307, level: 1)
3439
/* 清理测试现场 */
SQL> drop table YOUYUS;
Table dropped.
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999; 
Table created. 		  
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
SQL> commit;
Commit complete.
SQL> conn maclean/maclean
Connected.
SQL> select vs.name, ms.value
2    from v$mystat ms, v$sysstat vs
3   where vs.statistic# = ms.statistic#
4     and vs.name in ('redo size','consistent gets');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          45
redo size                                                                 0
SQL> alter index ind_youyus shrink space;
Index altered.
SQL> select vs.name, ms.value
2    from v$mystat ms, v$sysstat vs
3   where vs.statistic# = ms.statistic#
4     and vs.name in ('redo size','consistent gets');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                        2951
redo size                                                          90963340
/* SHRINK SPACE操作产生了86MB的redo数据,多出coalesce时的28% */
SQL> analyze  index IND_YOUYUS validate  structure;
Index analyzed.
SQL> set linesize 200;
SQL> select height,
2         blocks,
3         lf_blks,
4         lf_rows_len,
5         lf_blk_len,
6         br_blks,
7         br_rows,
8         br_rows_len,
9         br_blk_len,
10         btree_space,
11         used_space,
12         pct_used
13    from index_stats;
HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3       3520       3439    24653178       7996          9       3438       41188       8028    27570496   24694366         90
/* 以下为此时ind_youyus索引的treedump * /
[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5125.trc|grep "level:"; \
cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5125.trc|grep leaf|wc -l
branch: 0x1309efc 19963644 (0: nrow: 8, level: 2)
branch: 0x130b2c1 19968705 (-1: nrow: 450, level: 1)
branch: 0x130b56a 19969386 (0: nrow: 447, level: 1)
branch: 0x130b815 19970069 (1: nrow: 447, level: 1)
branch: 0x130babe 19970750 (2: nrow: 447, level: 1)
branch: 0x130bd67 19971431 (3: nrow: 447, level: 1)
branch: 0x130b919 19970329 (4: nrow: 447, level: 1)
branch: 0x130b3bf 19968959 (5: nrow: 447, level: 1)
branch: 0x1309efe 19963646 (6: nrow: 307, level: 1)
3439
/* 索引结构与coalesce命令维护后相同,但shrink space操作释放了索引上的空闲空间 */
/* 再次清理测试现场 */
SQL> drop table YOUYUS;
Table dropped.
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999; 
Table created. 
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
SQL>  delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
SQL> commit;
Commit complete.
SQL> conn maclean/maclean
Connected.
SQL> select vs.name, ms.value
2    from v$mystat ms, v$sysstat vs
3   where vs.statistic# = ms.statistic#
4     and vs.name in ('redo size','consistent gets');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          45
redo size                                                                 0
SQL> alter index ind_youyus shrink space compact;
Index altered.
SQL> select vs.name, ms.value
2    from v$mystat ms, v$sysstat vs
3   where vs.statistic# = ms.statistic#
4     and vs.name in ('redo size','consistent gets');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                        3208
redo size                                                          90915424
SQL> analyze  index IND_YOUYUS validate  structure;
Index analyzed.
SQL> set linesize 200;
SQL> select height,
2         blocks,
3         lf_blks,
4         lf_rows_len,
5         lf_blk_len,
6         br_blks,
7         br_rows,
8         br_rows_len,
9         br_blk_len,
10         btree_space,
11         used_space,
12         pct_used
13    from index_stats;
HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3       5376       3439    24653178       7996          9       3438       41188       8028    27570496   24694366         90
/* shrink space compact 起到了和coalesce完全相同的作用,但其产生的redo仍要多于coalesce于28% */

coalesce与shrink space命令对比重建索引(rebuild index)有一个显著的优点:不会导致索引降级。从以上测试可以看到coalesce与shrink space compact功能完全相同;在OLTP环境中,大多数情况下我们并不希望回收索引上的空闲空间,那么coalesce或者shrink space compact(not shrink space)可以成为我们很好的选择,虽然实际操作过程中2者消耗的资源有不少差别。

并不是说coalesce就一定会消耗更少的资源,这需要在您的实际环境中具体测试,合适的才是最好的!

Script:Lists Text index and it’s dependent objects

Script:Lists Text index and it’s dependent objects

 

set linesize 85;
set verify off;
spool bde_chk_imt.lst
accept idx_name prompt 'Enter the Text index name: '
accept tbl_name prompt 'Enter the base table name: '
prompt '-- Printing Object Information'
set pagesize 20
column segment_name format a25     heading 'Object|Name'
column tablespace_name format a10  heading 'Tablespace'
column segment_type format a10     heading 'Object|Type'
column owner format a10            heading 'Owner'       
select /*+ FIRST_ROWS */ unique s.segment_name, s.segment_type,s.tablespace_name,s.owner
from dba_segments s
where 
s.segment_name in (
(select /*+ FIRST_ROWS */ unique ic.index_name
from dba_ind_columns IC
where 
ic.table_name like upper('%&&tbl_name%')) 
union
(select /*+ FIRST_ROWS */ unique t.table_name 
from dba_tables T
where 
t.table_name like upper('%&&tbl_name%')) 
union 
(select /*+ FIRST_ROWS */ unique l.segment_name 
from dba_lobs L
where 
l.table_name like upper('%&&tbl_name%'))
union
(select /*+ FIRST_ROWS */ unique ic.table_name
from dba_ind_columns IC
where 
ic.index_name like upper('%&&tbl_name%')));
prompt '-- Printing Index Information'
prompt '-- $X index should be created with compress2 (i.e. Comp=ENAB)'
prompt '-- alter index  rebuild compress 2;'
prompt '-- Example: alter index DR$FND_LOBS_CTX$X rebuild compress 2;'
column index_name  format a35      heading 'Index|Name'
column index_type  format a10      heading 'Index|Type'
column compression format a4       heading 'Comp'
column table_name  format a30      heading 'Table|Name'
select unique index_name,index_type,substr(compression,1,4) compression, table_name 
from dba_indexes
where table_name like upper('%&&tbl_name%')
or index_name like upper('%&&idx_name%')
group by index_name,index_type, compression,table_name;
prompt'--Printing LOB Information'
prompt'--$R Table should be cached (i.e. Cached = YES)'
prompt'--ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );'
prompt'--tabname = R_TABLE name'
prompt'--lobname = lob column of R_TABLE, which is the 'DATA' column'
prompt'--(example: alter table DR$FND_LOBS_CTX$R modify lob (DATA) (CACHE);' 
column index_name format a25      heading 'Index|Name'
column segment_name format a25      heading 'LOB|Name'
column table_name format a20      heading 'Table|Name'
column cache format a6            heading 'Cached'
select segment_name,table_name, index_name, cache 
from dba_lobs
where table_name like upper('%&&tbl_name%')
order by segment_name,table_name, index_name, cache;
spool off;

沪ICP备14014813号

沪公网安备 31010802001379号