Oracle-SQL性能优化及案例分析剖析.ppt

第*页 2009年9月 信息技术中心应用集成研究所-支持一室 SQL性能优化及案例分析 2011-04-22 目 录 优化器 1 确定优化目标 2 数据库开发调优三板斧 3 4 查看执行计划 SQL优化 5 案例讨论 6 优化器 Oracle 有以下两种优化器： 规则优化器RBO（Rule Based Optimizer） 成本优化CBO（Cost Based Optimizer） 优化器的设置： ?Instance级别 -Optimizer_mode = first_rows ?Session级别 -Alter session set optimizer_mode = first_rows; ?SQL语句级别 -Hint －/*+ first_rows */ RBO RBO内置优先顺序 1.使用ROWID 读取一行数据 2.依据聚簇连接读取一行数据 3.依据Unique HASH Cluster 读取一行数据 4.依据Unique Index 读取一行数据 5.CLUSTER 连接 6.Unique HASH Cluster Key 7.Unique Cluster Key 8.组合索引 9.单一列索引 10.依据索引的范围查询 11. 依据索引的整体范围查询 12.Sort Merge 连接 13.索引列的MIN、MAX计算 14.索引列的Order by 15.全表扫描 SQL的执行顺序是由上到下，表的读取和过滤条件下是从右向左。 CBO 成本优化器由查询转换器（Query Transformer)、成本估算器(Estimator)、执行计划生成(Plan Generator)构成. 查询转换器：把当前的SQL语句向比较合适的形式转换，以便获得较好的执行计划. 视图合并、谓词推进、子查询非嵌套化等. 成本估算器：由选择度（selectivity)、基数（Cardinality)、成本(Cost)三部分内容简介. 选择度是指满足条件的行在查询的结果集中所占的比率. 基数判定对象查询结果行数据或者向下一执行阶段传递的中间结果. 成本执行各个操作所需要的时间成本进行相对性运算后而获得的预测值. 执行计划生成器：主要任务是从能够处理当前查询语句的所有可以应用的执行计划中选择成本最小的一个，并对其进行验证. CBO 具有的优点： 1.最大的优点就是判断更具有现实性 2.通过对统计信息的管理来控制最优化 3.没有深刻理解优化器的工作原理，也能确保最优化的性能 CBO 具有的缺点： 1.提前预测执行计划比较困难 2.不同版本中存在来得变化 3.控制执行计划比较困难 注: 11G已没有规则优化器. 查看执行计划 俗话说“功欲善其事，必先利其器”，掌握一些调优的工具会使你更快捷、更准确的找到需要优化的瓶颈之处，达到事半功倍的效果。以下来介绍Oracle查看执行计划工具，如使用工具PL/SQL、Toad、Explain plan、Autotrace. 1.PL/SQL (按F5或打开一个Test Window) 1.使用PL/SQL 2.使用Explain plan查询 EXPLAIN PLAN SET STATEMENT_ID=QUERY1 FOR SELECT * FROM SCOTT.DEPT,SCOTT.EMP WHERE DEPT.DEPTNO = EMP.DEPTNO; 显示执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); Plan hash value: 1858509404 ------------------------------------------------------------------------------ | Id | Operation |Name |Rows|Bytes|Cost (%CPU)|Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 798 | 6 (17)|00:00:01| | 1 | MERGE JOIN | | 14 | 798 | 6 (17)|00:00:01| | 2 | TABLE ACCESS BY INDEX ROWID|DEPT | 4 | 80 |