oracle-执行计划-详解

1在此处插入图片ExplainPlan命令说明NancyGuo郭颖忠SeniorSalesConsultant免责声明本讲座旨在为您提供有关如何阅读SQL执行计划的说•本讲座旨在为您提供有关如何阅读SQL执行计划的说明，并帮助您确定该计划是否满足您的要求。•本讲座并不能使您一举成为优化器专家，也无法使您具备轻松调整SQL语句的能力！具备轻松调整SQL语句的能力！议题什么是执行计划如何生成执行计划？•什么是执行计划，如何生成执行计划？•一个优秀的优化器计划是什么样的？•理解执行计划•理解执行计划•基数•访问方法访问方法•联接顺序•联接类型•分区修剪•并行度•执行计划示例•执行计划示例在此处插入图片在此处插入图片什么是执行计划，如何生什么是执行计划，如何生成执行计划？什么是执行计划？执行计划显示在执行条SQL语句时必须执行的详细•执行计划显示在执行一条SQL语句时必须执行的详细步骤这些步骤表示为组数据库运算符这些运算符将使•这些步骤表示为一组数据库运算符，这些运算符将使用和生成行•这些运算符及其实施的顺序由优化器使用查询转换及•这些运算符及其实施的顺序由优化器使用查询转换及物理优化技术的组合来确定•执行计划通常以表格的形式显示，但它实际上为树形执行计划通常以表格的形式显示，但它实际上为树形什么是执行计划？查询SELECTprodcategoryavg(amountsold)SELECTprod_category,avg(amount_sold)FROMsaless,productspWHEREp.prod_id=s.prod_idGROUPBYprod_category;GB执行计划的表格表示-----------------------------------------------------------IdOperationName执行计划的树形表示GroupByHASHJOINIdOperationName-----------------------------------------------------------0SELECTSTATEMENT1HASHGROUPBY2HASHJOINTABLEACCESSTABLEACCESS2HASHJOIN3TABLEACCESSFULLPRODUCTS4PARTITIONRANGEALL5TABLEACCESSFULLSALES----------------------------------------------------------SALESPRODUCTS----------------------------------------------------------如何获取执行计划可以使用两种方法查看执行计划可以使用两种方法查看执行计划1EXPLAINPLAN命令1.EXPLAINPLAN命令•显示一条SQL语句的执行计划，而不实际执行此语句2V$SQLPLAN2.V$SQL_PLAN•在Oracle9i中引入的字典视图，它可显示已编译到游标缓存中一个游标的一条SQL语句的执行计划使用DBMS_XPLAN包来显示执行计划在某些情况下，使用EXPLAINPLAN显示的计划可能与使用V$SQLPLAN显示的计划不同V$SQL_PLAN显示的计划不同如何获取执行计划示例1EXPLAINPLAN命令和dbms_xplan.display函数SQLEXPLAINPLANFORSELECTprod_category,avg(amount_sold)FROMsaless,productspWHEREp.prod_id=s.prod_idpp_p_GROUPBYprod_category;ExplainedSQLSELECTplantableoutputSQLSELECTplan_table_outputFROMtable(dbms_xplan.display('plan_table',null,'basic'));------------------------------------------IdOtiNIdOperationName------------------------------------------0SELECTSTATEMENT1HASHGROUPBY2HASHJOIN3TABLEACCESSFULLPRODUCTS4PARTITIONRANGEALL5TABLEACCESSFULLSALES-------------------------------------------如何获取执行计划示例2生成并显示在会话中昀后执行的SQL语句的执行计划SQLSELECTprod_category,avg(amount_sold)FROMldtFROMsaless,productspWHEREp.prod_id=s.prod_idGROUPBYprod_category;norowsselectednorowsselectedSQLSELECTplan_table_outputFROMtable(dbms_xplan.display_cursor(null,null,'basic'));------------------------------------------IdOperationName------------------------------------------0SELECTSTATEMENT0SELECTSTATEMENT1HASHGROUPBY2HASHJOIN3TABLEACCESSFULLPRODUCTS4PARTITIONRANGEALL4PARTITIONRANGEALL5TABLEACCESSFULLSALES-------------------------------------------如何获取执行计划示例3显示V$SQLPLAN中的任何其他语句的执行计划示例3显示V$SQL_PLAN中的任何其他语句的执行计划1直接：1.直接：SQLSELECTplan_table_outputFROMtable(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));2.间接：SQLSELECTplan_table_outputFROM$lTABLE(dbldil(lidhildbFROMv$sqls,TABLE(dbms_xplan.display_cursor(s.sql_id,s.child_number,'basic'))tWHEREs.sql_textlike'selectPROD_CATEGORY%';注：有关详情，请访问个参数•DBMS_XPLAN.DISPLAY接受3个参数•计划表的名称（默认为“PLAN_TABLE”），•statement_id（默认为null）•格式（默认为“TYPICAL”）•DBMS_XPLAN.DISPLAY_CURSOR接受3个参数SQLID（默认为此会话中昀后个执行的语句）•SQL_ID（默认为此会话中昀后一个执行的语句），•子编号（默认为0），•格式（默认为“TYPICAL”）•格式是高度可定制的•Basic•Typical•Typical•All•其他低级别参数可显示更多的详细信息在此处插入图片在此处插入图片一个优秀的优化器计划个优秀的优化器计划是什么样的？一个优秀的优化器计划是什么样的？优化器有两个不同的目标优化器有两个不同的目标•串行执行：其关注的是开销开销越低越好•开销越低越好•并行执行：其关注的是性能•速度越快越好•速度越快越好两个基本问题：•什么是开销？•什么是性能？什么是性能什么是开销？•优化器生成的神奇数字？执行句所需的资•执行SQL语句所需的资源？•复杂计算的结果？•执行语句所需时间的估计？实际定义实际定义•开销指的是所使用的工作单元或资源的数量优化器用CPU内存使用和IO作为工作单元•优化器用CPU、内存使用和IO作为工作单元•开销是对执行操作时要使用的CPU和内存量以及磁盘I/O数的估计开销是Oracle的一个内部量度性能是什么？完成尽可能多的查询？•完成尽可能多的查询？•使用昀少的资源获得尽可能快的运行速度？获得昀佳的并发率？•获得昀佳的并发率？实际定义•性能指的是对查询的昀快响应时间•目标是尽可能快地完成查询操作•优化器不关注执行计划所需的资源在此处插入图片在此处插入图片理解执行计划理解执行计划SQL执行计划您在查看计划时能否确定以下项是否正确？•基数•每个对象是否生成正确的行数？•访问方法•访问方法•是否以昀好的方式访问数据？扫描？索引查找？•联接顺序•是否以正确的顺序联接各表以便尽早尽多地消除数据？•联接类型•是否使用了正确的联接类型？•是否使用了正确的联接类型？•分区修剪•我执行过分区修剪吗？是否消除了足够多的数据？•并行度基数什么是基数？•估算将返回的行数•单值谓词的基数=行的总数/不同值的总数•例如：共100行，共10个不同值=基数=10行•或者，如果为柱状图表示，则是行数*密度或者，如果为柱状图表示，则是行数密度为什么要关注？•它将影响所有方面！访问方法、联接类型、联接顺序等哪些因素会导致基数出错？统计信息陈缺少•统计信息陈旧/缺少•数据偏差•一个表有多个单列谓词•个表有多个单列谓词•where子句谓词中包含函数•复杂表达式，其中包含来自不同表的列基数或选择度估算返回行数的基数估算返回行数的基数使用简单的SELECTCOUNT(*)从每个表应用任何属于该表的WHERE子句谓词确定正确的基数使用以下代码查看基数SELECT/*+gather_plan_statistics*/p.prod_nameasproduct,sum(s.quantity_sold)asunits,FROMsalessproductspFROMsaless,productspWHEREs.prod_id=p.prod_idGROUPBYp.prod_name;SELECT*FROMtable(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT='ALLSTATSLAST'));比较计划中每个操作的估算返回行数与实际返回行数使用SQL监视器查看基数利用SQL监视器您可以比较计划中每个操作的利用SQL监视器，您可以比较计划中每个操作的估算返回行数与实际返回行数有关解决基数问题的建议原因解决方法统计信息陈旧/缺少DBMS_STATS数据偏差创建一个柱状图*数据偏创建个柱状图一个表有多个单列谓词使用DBMS_STATS.CREATE_EXTENDED_STATS创建一个列组在一个联接中使用多个列使用DBMSSTATS.CREATEEXTENDEDSTATS创___创建一个列组包含函数的列使用DBMSSTATSCREATEEXTENDEDSTATS创DBMS_STATS.CREATE_EXTENDED_STATS创建有关包含函数的列的统计信息复杂表达式，其中包含来自多个表的列使用4级或更高的动态抽样级别的列*柱状图会对具有11g之前的绑定的语句产生令人注目的副作用请谨慎使用访问方法—获取数据访问方法解释完整表扫描读取表中所有行并过滤掉那些不符合WHERE子句谓词的行。用于索引、DOP集等集等按ROWID访问表ROWID指定含有所需行的数据文件和数据块以及该行在该块中的位置。当在索引或WHERE子句中提供rowid时使用索引唯一扫描将只返回一行。当语句中包含UNIQUE或PRIMARYKEY约束条件时使用，这些约束条件用于保证只访问行些约束条件用于保证只访问一行索引范围扫描访问相邻索引项，可返回多个ROWID值。与等式一起用于非唯一索引，或与范围谓词一起用于唯一索引（.、between等）索引跳过扫描如果前导列中只有很少的