Orace执行计划

• Orace执行计划
  • 相关的概念
  • Oracle访问数据的方法
  • 表之间的连接方式
  • 总结
  • 参考

相关的概念

• Rowid的概念
  rowid是一个伪列，这个列不是用户定义而是系统自己给加上的。每个表都有一个rowid的伪列，表中并不物理存储ROWID的值，但可以像使用其他列那样使用它，只是不能删除修改该列。一旦一行数据掺入数据库，则rowid在该行的生命周期内是唯一的，即使该行产生行迁移，行的rowid也不会改变。
• Recursive SQL概念
  为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为'recursive calls'
• Row Source行源
  用在查询中，由上一操作返回的符合条件的行的集合，既可以是表中全部的行数据的集合，也可以是表的部分行数据的集合；
• Predicate谓词
  一个查询中WHERE限制条件
• Driving Table驱动表
  驱动表又称外层表(OUTER TABLE)。这个概念用于嵌套连接和HASH连接中，如果该row source返回较多的行数据，则对后续操作有负面影响。一般说来，应用查询条件的限制后，返回较少行源的表作为驱动表，所以一个达标在where条件后返回较少数据，则大表也合适作为驱动表，并不是只有较小的表可以作为驱动表。
• Probed Table
  该表通常被称作内层表(INNER TABLE)。从驱动表获取的具体一行的数据，需要在该表中寻找符合连接条件的行。所以数据量较大，且在相应连接列上有索引的表适合做为内层表。
• 组合索引
  顾名思义，组合索引就是由多个列组成的索引。组合索引中的第一个列被称作引导列(LEADING COLUMN)，只有限制条件中包含引导列时，该限制条件才会使用该组合索引

Oracle访问数据的方法

1. 全表扫描(FULL TABLE SCANS,FTS)
   为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的where条件。一个多块读操作可以使一次I/O能读取多块数据(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O的总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效的实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出的数据比较多，超过总量的5%-10%，或者想使用并行查询功能。
2. 通过ROWID(TABLE ACCESS BY ROWID)
   行的ROWID指出了该行所在的数据文件，数据块及该行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。
3. 索引扫描(INDEX SCAN/INDEX LOOKUP)
   通过Index先查找到数据对应的rowid值，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描。在索引中，除了存储每个索引的值外，索引还存储具体此值的行对应的ROWID值。
   索引扫描由两步组成：

st=>start: 扫描索引得到rowid
e=>end: 通过rowid读出数据

st->e

每步都是一次单独的I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第一步的I/O经常是逻辑I/O，即数据可以从内存中得到。但对于第二部来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，极其费时间。所以如果对大表进行索引扫描，取出的数据如果大于总量的5%-10%，使用索引扫描效率会下降很多。但是如果查询的数据能全在索引中找到，就可以避免进行第二步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的。再者，如果sql语句中需要对索引列进行排序，因为索引已经预先排序好了，所以在执行计划索引进行排序。
根据索引的类型和where限制条件的不同，一般有如下4中类型的索引扫描：

• 索引唯一扫描(index unique scan)
  通过唯一索引查找一个数值经常返回单个ROWID，如果存在UNIQUE或PRIMARY KEY约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。

• 索引范围扫描(index range scan)
  使用一个索引存取多行数据。以下三种情况会使用index range scan：

  1. 在唯一索引列上使用了range操作符(> < <> >= <= between and)
  2. 在组合索引上，只使用部分列进行查询，导致查询出多行
  3. 在非唯一索引列上进行查询

• 索引全扫描(index full scan)
  与全表扫描相对应，也有相应的全索引扫描而且此时查询出的数据都必须从索引中可以直接得到。

• 索引快速扫描(index fast full scan)
  扫描索引中的索引数据块，与index full scan很类似，但是一个显著的区别就是它不对查询出的数据排序，即数据不是以排序顺序被返回。

表之间的连接方式

Join是一种试图将两个表结合在一起的谓词，一次只能连接两个表。Join可以并行的读取两个连接的表的数据，但将表中符合限制条件的数据段读入内存形成row source后，join的其它步骤一般都是串行的。
目前为止，典型的join连接类型有3中：

• 排序合并连接(Sort Merge Join(SMJ))
  内部连接过程：

  1. 首先生成row source1需要的数据，然后对这些数据按照连接操作相关列进行排序。
  2. 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列排序。
  3. 最后两边已排序的行被放在一起执行合并操作，即将两个row source按照连接条件连接起来。

如果row source已经在关联列上被排序，则该连结操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其耗费资源的操作。预先排序的row source包括已经被索引的列或row source已经在前面的步骤中被排序了。另外，尽管合并两个row source的过程是串行的，但是可以并行访问这两个row source（如并行读入数据，排序）。
排序是一个费时，费资源的操作，基于这个原因，SMJ通常不是一种有效率的连接方式，但当row source已经排好序的前提下，SMJ的效率还是很可观的。

• 嵌套循环连接(Nested Loops(NL))
  这个连接方法有驱动表（外部表）的概念。该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这就是我们将小表作为驱动表（用于外层循环）的理论依据。但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使语句产生的I/0次数最少。
  在NESTED LOOPS连接中，Oracle读取外部表的每一行，然后再内部表中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理外部表的下一行。如果外部表比较小，并且在内部表中有唯一高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的一个优点：可以先返回已经连接的行，而不必等待所有的连接操作处理完成才返回数据，这可以实现快速的响应时间。
  如果不适用并行操作，最好的驱动表是那些应用了where条件限制后，可以返回较少行数据的表。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作不一定比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否支持并行（多个CPU，多个硬盘控制器）。
• 哈希连接(Hash Join)
  哈希连接是在Oracle7.3以后引入的，从理论上来说比NL和SMJ更高效，而且只用在CBO优化器中。
  较小的row source被用来构建hash table与bitmap，第二个row source用来于第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查再hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中，这种连接方式的效率极高。
  要是哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE,缺省情况下该参数为TRUE,另外还需要设置hash_area_size参数，以使哈希连接高效运行，因为哈希连接会在参数指定的大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式低。

总结

各连接方法试用的场景：

• 排序-合并连接

  1. 对于非等值连接，这种方式效率比较高
  2. 如果在关联列上有索引，效果更好
  3. 对于将两个比较大的row source做连接，该连接方法比NL要好
  4. 如果sort merge返回的row source过大，则又会使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O

• 嵌套循环

  1. 如果外部表比较小，并且在内部表上有唯一索引，或者高选择性索引
  2. 该方法有其它连接方法没有的优点：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这样可以实现快速的响应时间

• 哈希连接

  1. 一般来说，其效率好于其它两种连接，但这种连接只能用在CBO优化中，而且需要设置合适的hash_area_size参数
  2. 只用于等值连接

参考

参考文献