ORACLE中的执行计划Word下载.docx
《ORACLE中的执行计划Word下载.docx》由会员分享，可在线阅读，更多相关《ORACLE中的执行计划Word下载.docx（18页珍藏版）》请在冰豆网上搜索。
2 1 INDEX（FULLSCAN）OF'
T_INDEX'
（NON-UNIQUE）（Cost=26Card=28180）
这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加;
第二个表示根据表中索引，把
整个索引读进内存来逐条累加，而不用去读表中的数据。
但是这两种方式到底哪种快呢?
通常来说可能二比一快，但也不是绝对的。
这是一
个很简单的例子演示执行计划的差异。
对于复杂的SQL（表连接、嵌套子查询等），执行计划可能几十种甚至上百种，但是到底那种最好呢?
我们事前并不知道，数据库本身也不知道，但是数据库会根据一定的规则或者统计信息（statistics）去选择一个执行计划，通常来说选择的是
比较优的，但也有选择失误的时候，这就是这次讨论的价值所在。
Oracle优化器模式
Oracle优化器有两大类，基于规则的和基于代价的，在SQLPLUS中我们可以查看init文件中定义的缺省的优化器模式。
showparametersoptimizer_mode
NAME TYPE VALUE
optimizer_mode string CHOOSE
这是Oracle8.1.7企业版，我们可以看出，默认安装后数据库优化器模式为CHOOSE,我们还可以设置为RULE、
FIRST_ROWS,ALL_ROWS。
可以在init文件中对整个instance的所有会话设置，也可以单独对某个会话设置：
ALTERSESSIONSEToptimizer_mode =RULE;
会话已更改。
ALTERSESSIONSEToptimizer_mode =FIRST_ROWS;
ALTERSESSIONSEToptimizer_mode =ALL_ROWS;
基于规则的查询，数据库根据表和索引等定义信息，按照一定的规则来产生执行计划;
基于代价的查询，数据库根据搜集的表和索引的
数据的统计信息（通过analyze命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划（实际上不一定最优）。
RULE是基于规则的，CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行（在CHOOSE模式下Oracle采用的是FIRST_ROWS）
，否则基于规则来执行。
在基于代价的两种方式中，FIRST_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端，对于排序分页
页显示这种查询尤其适用，ALL_ROWS指以总体消耗资源最少的方式返回结果给客户端。
基于规则的模式下，数据库的执行计划通常比较稳定。
但在基于代价的模式下，我们才有更大的机会选择最优的执行计划。
也由于
Oracle的很多查询方面的特性必须在基于代价的模式下才能体现出来，所以我们通常不选择RULE（并且Oracle宣称从Oracle10i版本数据库
开始将不再支持RULE）。
既然是基于代价的模式，也就是说执行计划的选择是根据表、索引等定义和数据的统计信息来决定的，这个统计
信息是根据analyze命令或者dbms_stats包来定期搜集的。
首先存在着一种可能，就是由于搜集信息是一个很消耗资源和时间的动作，尤
其当表数据量很大的时候，因为搜集信息是对整个表数据进行重新的完全统计，所以这是我们必须慎重考虑的问题。
我们只能在服务器空
闲的时候定期的进行信息搜集。
这说明我们在一段时期内，统计信息可能和数据库本身的数据并不吻合;
另外就是Oracle的统计数据本身也
存在着不精确部分（详细参考OracleDOCUMENT），更重要的一个问题就是及时统计数据相对已经比较准确，但是Oracle的优化器的选择也
并不是始终是最优的方案。
这也倚赖于Oracle对不同执行计划的代价的计算规则（我们通常是无法知道具体的计算规则的）。
这好比我们决定
从香港还是从北京去英国，车票、机票等实际价格到底是怎么核算出来的我们并不知道，或者说我们现在了解的价格信息，在我们乘车前
往的时候，真实价格跟我们的预算已经发生了变化。
所有的因素，都将影响我们的整个开销。
执行计划稳定性能带给我们什么
Oracle存在着执行计划选择失误的可能。
这也是我们经常遇见的一些现象，比如总有人说我的程序在测试数据库中跑的很好，但在产
品数据库上就是跑的很差，甚至后者硬件条件比前者还好，这到底是为什么?
硬件资源、统计信息、参数设置都可能对执行计划产生影响。
由于因素太多，我们总是对未来怀着一种莫名的恐惧，我的产品数据库上线后到底跑的好不好?
于是Oracle提供了一种稳定执行计划的能力
，也就是把在测试环境中的运行良好的执行计划所产生的OUTLINES移植到产品数据库，使得执行计划不会随着其他因素的变化而变化。
那么OUTLINES是什么呢?
先要介绍一个内容，Oracle提供了在SQL中使用HINTS来引导优化器产生我们想要的执行计划的能力。
这在
多表连接、复杂查询中特别有效。
HINTS的类型很多，可以设置优化器目标（RULE、CHOOSE、FIRST_ROWS、ALL_ROWS），可以指定表
连接的顺序，可以指定使用哪个表的哪个索引等等，可以对SQL进行很多精细的控制。
通过这种方式产生我们想要的执行计划的这些
HINTS,Oracle可以存储这些HINTS，我们称之为OUTLINES。
通过STOREOUTLINES可以使得我们拥有以后产生相同执行计划的能力，也
就是使我们拥有了稳定执行计划的能力。
这里想给出一个附加的说明就是，实际上，我们通过工具改写SQL，比如使用SQL EXPERT改写后的SQL，这些不仅仅是加了HINTS
而且文本都已经发生了变化的SQL，也可以存储OUTLINES，并可被应用到应用中。
但这不是一定生效，我们必须测试检查是否生效。
但由
于就算给了错误的OUTLINES,数据库在执行的时候，也只是忽略过去重新生成执行计划而不会返回错误，所以我们才敢放心的这么使用。
当然在Oracle文档中并没有指明可以这样做，文档中只是说明，如果存在OUTLINES的同时又在SQL中加了HINTS，则会使用OUTLINES而
忽略HINTS。
这个功能在LECCO将发布的产品中会使用这一功能，这样可以将SQLEXPERT的改写SQL的能力和稳定执行计划的能力结合起
来，那么我们就对不能更改源代码的应用具有了相当强大的SQL优化能力。
也许我们会有疑问，假如稳定了执行计划，那还搜集统计信息干吗?
这是因为几个原因造成的，首先，现在的执行计划对于未来发生了
变化的数据未必就是合适的，存在着当前的执行计划不满足未来数据的变化后的效率，而新的统计信息的情况下所产生的执行计划也并不
是全部都合理的。
那这个时候，我们可以采用新搜集的统计信息，但是却对新统计信息下不良的执行计划采用Oracle提供的执行计划稳定
性这个能力固定执行计划，这样结合起来我们可以建立满意的高效的数据库运行环境。
我们还需要关注的一个东西，Oracle提供的dbms_stats包除了具有搜集统计信息的能力，还具有把数据库中统计信息（statistics）
export/import的能力，还具有只搜集统计信息而使得统计信息不应用于数据库的能力（把统计信息搜集到一个特定的表中而不是立即生效）
，在这个基础上我们就可以把统计信息export出来再import到一个测试环境中，再运行我们的应用，在测试环境中我们观察最新的统计信
息会导致哪些执行计划发生变化（DBEXPERT的PlanVersionTracer是模拟不同环境并自动检查不同环境中执行计划变化的工具），是变好了
还是变差了。
我们可以把变差的这一部分在测试环境中使用hints或者利用工具（SQLEXPERT是在重写SQL这一领域目前最强有力的工具）产
生良好的执行计划的SQL，利用这些SQL可以产生OUTLINES,然后在产品数据库应用最新的统计信息的同时移植进这些OUTLINES。
最后说一下我们不得不使用执行计划稳定性能力的场合。
我们假定Oracle的优化器的选择都是准确的，但是优化器选择的基础就是我
们的SQL,这些SQL才从根本上决定了运行效率，这是更重要的一个优化的环节。
SQL是基础（当然数据库的设计是基础的基础），一个SQL写
的好不好，就相当于我们同样是要想去英国，但是我的起点在珠海，你的起点却在西藏的最边缘偏僻的一个地方，那不管你做怎样的最优
路线选择，你都不如我在珠海去英国所花费的代价小。
2，怎么生成的
1.Explainplan
explainplanfor
select*fromaa;
查看结果：
select*fromtable（dbms_xplan.display（））;
2.AutotraceSettimingon--记录所用时间
Setautottrace--自动记录执行计划
3.SQL_TRACE
ORACLESQL_TRACE
“SQLTRACE”是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。
在日常的数据库问题诊断和解决中，“SQLTRACE”是
非常常用的方法。
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。
此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
本文就“SQLTRACE”的这些使用作简单探讨，并通过具体案例对SQL_TRACE的使用进行说明。
3，怎么查看执行计划
从Oracle10g开始，可以通过EXPLAINPLANFOR查看DDL语句的执行计划了。
在9i及以前版本，Oracle只能看到DML的执行计划，不过从10g开始，通过EXPLAINPLANFOR的方式，已经可以看到DDL语句的执行计划
了。
这对于研究CREATETABLEASSELECT、CREATEMATERIALIZEDVIEWASSELECT以及CREATEINDEX，ALTERINDEXREBUILD等语
句有很大的帮助。
举个简单的例子，Oracle的文档上对于索引的建立有如下描述：
Theoptimizercanuseanexistingindextobuildanotherindex.Thisresultsinamuchfasterindexbuild.
如果看不到DDL的执行计划，只能根据执行时间的长短去猜测Oracle的具体执行计划，但是这种方法没有足够的说服力。
但是通过DDL的执
行计划，就使得结果一目了然了。
SQL>
CREATETABLETASSELECT*FROMDBA_OBJECTS;
表已创建。
EXPLAINPLANFOR
2CREATEINDEXIND_T_NAMEONT（OBJECT_NAME）;
已解释。
SELECT*FROMTABLE（DBMS_XPLAN.DISPLAY）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Planhashvalue:
3035241083
-------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
|0|CREATEINDEXSTATEMENT||57915|3732K|75
（2）|00:
00:
01|
|1|INDEXBUILDNONUNIQUE|IND_T_NAME|||||
|2|SORTCREATEINDEX||57915|3732K|||
|3|TABLEACCESSFULL|T|57915|3732K|41（3）|00:
Note
-----
-estimatedindexsize:
5242Kbytes
已选择14行。
CREATEINDEXIND_T_OWNER_NAMEONT（OWNER,OBJECT_NAME）;
索引已创建。
-------------------------------------------------------------------------------------------
517242163
|3|INDEXFASTFULLSCAN|IND_T_OWNER_NAME|||||
SETAUTOTON
CREATEINDEXIND_T_NAMEONT（OBJECT_NAME）;
注意，查看DDL的执行计划需要使用EXPLAINPLANFOR，AUTOTRACE对于DDL是无效的。
4，如何读懂执行计划：
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTptimizer=CHOOSE
10SORT（AGGREGATE）
21TABLEACCESS（BYINDEXROWID）OF'
USER_INFO'
32NESTEDLOOPS
43TABLEACCESS（FULL）OF'
USER_NUM_TABLE'
53INDEX（RANGESCAN）OF'
PK_USER_INFO'
（UNIQUE）
请问以上执行计划语句是如何看的？
语句的执行顺序是什么？
让我们来解释一下怎么看吧,左边的两列数字，第一列表示这条计划的编号，第二列是这条计划的父计划的编号；
如果一条计划有子计划，
那么先要执行其子计划;
在这个例子中：
从第一条编号为0的（SELECTSTATEMENTptimizer=CHOOSE）开始，他有个子计划1（SORT
（AGGREGATE）），然后1有个子计划2，2有子计划3，3有子计划4和5，4是3的第一个子计划，所以先执行4（TABLEACCESS（FULL）
OF'
），再执行5（INDEX（RANGESCAN）OF'
（UNIQUE）），4和5执行完返回到其父计划3（NESTED
LOOPS），3把4和5取到的rows进行nestedloops，结果再返回到2，再到1排序，再到0select.
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总次数，提高
升级会员 