执行计划详解.docx
- 文档编号:9966109
- 上传时间:2023-02-07
- 格式:DOCX
- 页数:18
- 大小:24.10KB
执行计划详解.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尽可能的小与高效
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 执行 计划 详解