04 oracle优化器之执行计划.docxVIP

分类： Oracle什么是执行计划执行计划显示了执行一个sql语句所需步骤的详细信息.这些步骤代表了一组数据库操作它们会消费和生产行数据.这些操作的顺序以及它们的实现取决于查询优化器对查询转换和物理优化技术的联合使用.执行计划通常是以表格形式来显示,这个执行计划实际上是一个树形结构.例如下面是一个基于sh方案的查询:SELECT prod_category, AVG(amount_sold)FROM sales s, products pWHERE p.prod_id = s.prod_idGROUP BY prod_category;下面的表格是上面语句的执行计划:树形结构形式的执行计划如下:上面的表格代表了一个自上而下,从左到右遍历的执行树.当读取一个执行计划树时你应该从底部的左边开始然后自下而上.在上面的这个例子中从查看树的叶子块开始.在这种情况下树的叶子块是对products和sales表进行全表扫描来实现的.由这些表扫描产生的行数据将被连接操作来消费使用.这里连接操作是一个哈希连接.最后group-by操作使用哈希来实现的它将消费由连接操作产生的行数据并将最终结果返回给终端用户. 显示执行计划有两种常用的方法来显示一个sql语句的执行计划:Explain plan命令—它能不用实际执行sql语句就能显示出其执行计划 V$sql_plan—在oracle9i中引入的一个动态性能视图它显示一个sql语句已经被编译成游标并存储在游标缓存中的执行计划.在特定条件下使用explain plan显示的执行计划可能不同于使用v$sql_plan所显示的执行计划.例如,当sql语句包含绑定变量时使用explain plan在显示执行计划时会忽略掉绑定变量的值,当使用v$sql_plan显示执行计划时会在生成执行计划的过程中考虑绑定变量的值.在oracle9i中引入了dbms_xplan包使得显示执行计划变得更加容易,而且这个dbmx_xplan包在后续的版本中功能更加增强了.这个dbms_xplan包提供了一些pl/sql接口来显示不得来源的执行计划:Explain plan命令V$sql_planAutomatic workload repostitory(Awr)Sql tuning set(STS)Sql plan baseline(spm) 使用explain plan命令与dbms_xplan.display函数下面的例子将说明使用dbms_xplan包所提供的不同函数来怎样生成和显示执行计划Dbms_xplan.display函数的参数如下:Plan table name(缺省值是’plan_table’)Statement_id(缺省值是null意味着最后一个被插入plan table的语句)Format 控制着显示信息的总量(缺省值是’typical’) 为了利用explain plan的功能你需要有合适的权限来运行你要试图explain plan的语句.一个缺省的plan_table对于每一个用户都是存在的不需要提前创建. 使用dbms_xplan.display_cursor函数一种替代的方法是真实执行sql语句来生产执行计划并使用dbms_xplan.display_cursor函数来显示执行计划.Dbms_xplan.display_cursor函数的参数值如下:Sql_id(缺省值是null,意味着在这个会话中最后一个执行的sql语句)Child number(default 0),Format 控制着显示信息的数量(缺省值是’typical’)除了要有实际运行这个sql语句的权限之外还要有对v$sql_plan,v$sql_plan_detail和select_catalog_role的select权限. 格式化执行计划Dbms_xplan包中函数的格式化参数是高度可定制的在执行计划输出中可以根据需要来显示少量或大量的详细信息.这里有三个预先定义的格式变量:Basic 在执行计划中只会显示ID,operation和name列Typical 在执行计划中显示了在basic级别的信息之外还包括了额外优化相关的内部信息比如,cost,cardinality,estimates等等.在执行计划中这些信息显示了每一个操作优化器所认为的操作成本,生成的行记录数等等.也显示了每一个操作的谓词评估.有两种类型的谓词:access和filter.access谓词对于索引来说将通过对合适的列应用搜索条件来检索相关的数据块.filter谓词在检索数据块后来进行评估. All 在执行计划中显示了在typical级别的信息之外还包括了每一个操作产生的表达式(列)列表.提示别名和查询块名字属于outline信息.最后两个片段的信息可