执行计划详解.docx

执行计划详解.docx

《执行计划详解.docx》由会员分享，可在线阅读，更多相关《执行计划详解.docx（18页珍藏版）》请在冰豆网上搜索。

执行计划详解

Oracle执行计划详解

Oracle执行计划详解

---

作者：

TTTBLOG

本文地址：

---

简介：

   本文全面详细介绍oracle执行计划的相关的概念，访问数据的存取方法，表之间的连接等内容。

   并有总结和概述，便于理解与记忆!

+++

目录

---

   一．相关的概念

   Rowid的概念

   RecursiveSql概念

   Predicate（谓词）

   DRivingTable（驱动表）

   ProbedTable（被探查表）

   组合索引（concatenatedindex）

   可选择性（selectivity）

   二．oracle访问数据的存取方法

   1）全表扫描（FullTableScans，FTS）

   2）通过ROWID的表存取（TableAccessbyROWID或rowidlookup）

   3）索引扫描（IndexScan或indexlookup）有4种类型的索引扫描：

（1）索引唯一扫描（indexuniquescan）

（2）索引范围扫描（indexrangescan）

      在非唯一索引上都使用索引范围扫描。

使用indexrangscan的3种情况：

   　　 （a）在唯一索引列上使用了range操作符（><<>>=<=between）

   　　 （b）在组合索引上，只使用部分列进行查询，导致查询出多行

   　　 （c）对非唯一索引列上进行的任何查询。

   　（3）索引全扫描（indexfullscan）

   　（4）索引快速扫描（indexfastfullscan）

   三、表之间的连接

   1，排序--合并连接（SortMergeJoin，SMJ）

   2，嵌套循环（NestedLoops，NL）

   3，哈希连接（HashJoin，HJ）

   另外，笛卡儿乘积（CartesianProduct）

   总结Oracle连接方法

   Oracle执行计划总结概述

+++

　　一．相关的概念

　　Rowid的概念：

rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。

对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。

不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。

一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。

　　RecursiveSQL概念：

有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为''recursivecalls''或''recursiveSQLstatements''.如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursiveSQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。

当需要的数据字典信息没有在共享内存中时，经常会发生Recursivecalls，这些Recursivecalls会将数据字典信息从硬盘读入内存中。

用户不比关心这些recursiveSQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。

当然DML语句与SELECT都可能引起recursiveSQL.简单的说，我们可以将触发器视为recursiveSQL.

　　RowSource（行源）：

用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个rowsource进行连接操作（如join连接）后得到的行数据集合。

　　Predicate（谓词）：

一个查询中的WHERE限制条件

　　DrivingTable（驱动表）：

该表又称为外层表（OUTERTABLE）。

这个概念用于嵌套与HASH连接中。

如果该rowsource返回较多的行数据，则对所有的后续操作有负面影响。

注意此处虽然翻译为驱动表，但实际上翻译为驱动行源（drivingrowsource）更为确切。

一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件（如等值限制），则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。

在执行计划中，应该为靠上的那个rowsource，后面会给出具体说明。

在我们后面的描述中，一般将该表称为连接操作的rowsource1.

　　ProbedTable（被探查表）：

该表又称为内层表（INNERTABLE）。

在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。

所以该表应当为大表（实际上应该为返回较大rowsource的表）且相应的列上应该有索引。

在我们后面的描述中，一般将该表称为连接操作的rowsource2.

　　组合索引（concatenatedindex）：

由多个列构成的索引，如createindexidx_emponemp（col1，col2，col3，……），则我们称idx_emp索引为组合索引。

在组合索引中有一个重要的概念：

引导列（leadingcolumn），在上面的例子中，col1列为引导列。

当我们进行查询时可以使用“wherecol1=？

”，也可以使用“wherecol1=？

andcol2=？

”，这样的限制条件都会使用索引，但是“wherecol2=？

”查询就不会使用该索引。

所以限制条件中包含先导列时，该限制条件才会使用该组合索引。

　　可选择性（selectivity）：

比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。

如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。

在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。

　　二．oracle访问数据的存取方法

　　1）全表扫描（FullTableScans，FTS）

　　为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件一个多块读操作可以使一次I/O能读取多块数据块（db_block_multiblock_read_count参数设定），而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。

在这种访问模式下，每个数据块只被读一次。

　　使用FTS的前提条件：

在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5%——10%，或你想使用并行查询功能时。

　　使用全表扫描的例子：

　　SQL>explainplanforselect*fromdual;

　　QueryPlan

　　-----------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=

　　TABLEACCESSFULLDUAL

　　2）通过ROWID的表存取（TableAccessbyROWID或rowidlookup）

　　行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。

　　这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。

我们会经常在执行计划中看到该存取方法，如通过索引查询数据。

　　使用ROWID存取的方法：

　　SQL>explainplanforselect*fromdeptwhererowid=''AAAAyGAADAAAAATAAF''；

　　QueryPlan

　　------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　TABLEACCESSBYROWIDDEPT[ANALYZED]

　　3）索引扫描（IndexScan或indexlookup）

　　我们先通过index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找（indexlookup）。

一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。

　　在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。

　　索引扫描可以由2步组成：

（1）扫描索引得到对应的rowid值。

（2）通过找到的rowid从表中读出具体的数据。

　　每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。

但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，是极其费时间的。

所以如果多大表进行索引扫描，取出的数据如果大于总量的5%——10%，使用索引扫描会效率下降很多。

如下列所示：

　　SQL>explainplanforselectempno，enamefromempwhereempno=10；

　　QueryPlan

　　------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　TABLEACCESSBYROWIDEMP[ANALYZED]

　　INDEXUNIQUESCANEMP_I1

　　但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的

　　SQL>explainplanforselectempnofromempwhereempno=10;--只查询empno列值

　　QueryPlan

　　------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　INDEXUNIQUESCANEMP_I1

　　进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序

　　SQL>explainplanforselectempno,enamefromemp

　　whereempno>7876orderbyempno;

　　QueryPlan

　　--------------------------------------------------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　TABLEACCESSBYROWIDEMP[ANALYZED]

　　INDEXRANGESCANEMP_I1[ANALYZED]

　　从这个例子中可以看到：

因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。

　　根据索引的类型与where限制条件的不同，有4种类型的索引扫描：

　　索引唯一扫描（indexuniquescan）

　　索引范围扫描（indexrangescan）

　　索引全扫描（indexfullscan）

　　索引快速扫描（indexfastfullscan）

（1）索引唯一扫描（indexuniquescan）

　　通过唯一索引查找一个数值经常返回单个ROWID.如果存在UNIQUE或PRIMARYKEY约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。

　　使用唯一性约束的例子：

　　SQL>explainplanfor

　　selectempno，enamefromempwhereempno=10；

　　QueryPlan

　　------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　TABLEACCESSBYROWIDEMP[ANALYZED]

　　INDEXUNIQUESCANEMP_I1

（2）索引范围扫描（indexrangescan）

　　使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词（where限制条件）中使用了范围操作符（如>、<、<>、>=、<=、between）

　　使用索引范围扫描的例子：

　　SQL>explainplanforselectempno，enamefromemp

　　whereempno>7876orderbyempno；

　　QueryPlan

　　--------------------------------------------------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　TABLEACCESSBYROWIDEMP[ANALYZED]

　　INDEXRANGESCANEMP_I1[ANALYZED]

　　在非唯一索引上，谓词col=5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

　　使用indexrangscan的3种情况：

　　（a）在唯一索引列上使用了range操作符（><<>>=<=between）

　　（b）在组合索引上，只使用部分列进行查询，导致查询出多行

　　（c）对非唯一索引列上进行的任何查询。

　　（3）索引全扫描（indexfullscan）

　　与全表扫描对应，也有相应的全索引扫描。

而且此时查询出的数据都必须从索引中可以直接得到。

　　全索引扫描的例子：

　　AnIndexfullscanwillnotperformsingleblocki/o''sandsoitmayprovetobeinefficient.

　　e.g.

　　IndexBE_IXisaconcatenatedindexonbig_emp（empno，ename）

　　SQL>explainplanforselectempno，enamefrombig_emporderbyempno，ename；

　　QueryPlan

　　--------------------------------------------------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=26

　　INDEXFULLSCANBE_IX[ANALYZED]

　　（4）索引快速扫描（indexfastfullscan）

　　扫描索引中的所有的数据块，与indexfullscan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。

在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

　　索引快速扫描的例子：

　　BE_IX索引是一个多列索引：

big_emp（empno，ename）

　　SQL>explainplanforselectempno，enamefrombig_emp；

　　QueryPlan

　　------------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　INDEXFASTFULLSCANBE_IX[ANALYZED]

　　只选择多列索引的第2列：

　　SQL>explainplanforselectenamefrombig_emp；

　　QueryPlan

　　------------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=1

　　INDEXFASTFULLSCANBE_IX[ANALYZED]

　　三、表之间的连接

　　Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。

在后面的叙述中，我们将会使用“rowsource”来代替“表”，因为使用rowsource更严谨一些，并且将参与连接的2个rowsource分别称为rowsource1和rowsource2.Join过程的各个步骤经常是串行操作，即使相关的rowsource可以被并行访问，即可以并行的读取做join连接的两个rowsource的数据，但是在将表中符合限制条件的数据读入到内存形成rowsource后，join的其它步骤一般是串行的。

有多种方法可以将2个表连接起来，当然每种方法都有自己的优缺点，每种连接类型只有在特定的条件下才会发挥出其最大优势。

　　rowsource（表）之间的连接顺序对于查询的效率有非常大的影响。

通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限制条件，从而得到一个较小的rowsource，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。

一般是在将表读入内存时，应用where子句中对该表的限制条件。

　　根据2个rowsource的连接条件的中操作符的不同，可以将连接分为等值连接（如WHEREA.COL3=B.COL4）、非等值连接（WHEREA.COL3>B.COL4）、外连接（WHEREA.COL3=B.COL4（+））。

上面的各个连接的连接原理都基本一样，所以为了简单期间，下面以等值连接为例进行介绍。

　　在后面的介绍中，都以以下Sql为例进行说明：

　　SELECTA.COL1，B.COL2

　　FROMA，B

　　WHEREA.COL3=B.COL4；

　　假设A表为RowSoruce1，则其对应的连接操作关联列为COL3；

　　B表为RowSoruce2，则其对应的连接操作关联列为COL4；

　　连接类型：

　　目前为止，无论连接操作符如何，典型的连接类型共有3种：

　　排序--合并连接（SortMergeJoin（SMJ））

　　嵌套循环（NestedLoops（NL））

　　哈希连接（HashJoin）

　　另外，还有一种Cartesianproduct（笛卡尔积），一般情况下，尽量避免使用。

　　1，排序--合并连接（SortMergeJoin，SMJ）

　　内部连接过程：

　　1）首先生成rowsource1需要的数据，然后对这些数据按照连接操作关联列（如A.col3）进行排序。

　　2）随后生成rowsource2需要的数据，然后对这些数据按照与sortsource1对应的连接操作关联列（如B.col4）进行排序。

　　3）最后两边已排序的行被放在一起执行合并操作，即将2个rowsource按照连接条件连接起来

　　下面是连接步骤的图形表示：

　　MERGE

　　/\

　　SORTSORT

　　||

　　RowSource1RowSource2

　　如果rowsource已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操作，特别是对于较大的表。

预先排序的rowsource包括已经被索引的列（如a.col3或b.col4上有索引）或rowsource已经在前面的步骤中被排序了。

尽管合并两个rowsource的过程是串行的，但是可以并行访问这两个rowsource（如并行读入数据，并行排序）。

　　SMJ连接的例子：

　　SQL>explainplanfor

　　select /*+ordered*/ e.deptno，d.deptno

　　fromempe，deptd

　　wheree.deptno=d.deptno

　　orderbye.deptno，d.deptno；

　　QueryPlan

　　-------------------------------------

　　SELECTSTATEMENT[CHOOSE]Cost=17

　　MERGEJOIN

　　SORTJOIN

　　TABLEACCESSFULLEMP[ANALYZED]

　　SORTJOIN

　　TABLEACCESSFULLDEPT[ANALYZED]

　　排序是一个费时、费资源的操作，特别对于大表。

基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个rowsource都已经预先排序，则这种连接方法的效率也是蛮高的。

　　2，嵌套循环（NestedLoops，NL）

　　这个连接方法有驱动表（外部表）的概念。

其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小rowsource的表作为驱动表（用于外层循环）的理论依据。

但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使语句产生的I/O次数最少。

有时不遵守这个理论依据，反而会获得更好的效率。

如果使用这种方法，决定使用哪个表作为驱动表很重要。

有时如果驱动表选择不正确，将会导致语句的性能很差、很差。

　　内部连接过程：

　　Rowsource1的Row1——Probe->Rowsource2

　　Rowsource1的Row2——Probe->Rowsource2

　　Rowsource1的Row3——Probe->Rowsource2

　　……。

　　Rowsource1的Rown——Probe->Rowsource2

　　从内部连接过程来看，需要用rowsource1中的每一行，去匹配rowsource2中的所有行，所以此时保持rowsource1尽可能的小与高效