OracleSQL性能优化及案例分析VIP免费

2009年9月信息技术中心应用集成研究所-支持一室SQL性能优化及案例分析2011-04-22第2页目录优化器1确定优化目标2数据库开发调优三板斧34查看执行计划SQL优化5案例讨论6第3页优化器优化器Oracle有以下两种优化器：规则优化器RBO（RuleBasedOptimizer）成本优化CBO（CostBasedOptimizer）优化器的设置：•Instance级别-Optimizer_mode=first_rows•Session级别-Altersessionsetoptimizer_mode=first_rows;•SQL语句级别-Hint－/*+first_rows*/第4页RBORBORBO内置优先顺序1.使用ROWID读取一行数据2.依据聚簇连接读取一行数据3.依据UniqueHASHCluster读取一行数据4.依据UniqueIndex读取一行数据5.CLUSTER连接6.UniqueHASHClusterKey7.UniqueClusterKey8.组合索引9.单一列索引10.依据索引的范围查询11.依据索引的整体范围查询12.SortMerge连接13.索引列的MIN、MAX计算14.索引列的Orderby15.全表扫描SQL的执行顺序是由上到下，表的读取和过滤条件下是从右向左。第5页CBOCBO成本优化器由查询转换器（QueryTransformer)、成本估算器(Estimator)、执行计划生成(PlanGenerator)构成.查询转换器：把当前的SQL语句向比较合适的形式转换，以便获得较好的执行计划.视图合并、谓词推进、子查询非嵌套化等.成本估算器：由选择度（selectivity)、基数（Cardinality)、成本(Cost)三部分内容简介.选择度是指满足条件的行在查询的结果集中所占的比率.基数判定对象查询结果行数据或者向下一执行阶段传递的中间结果.成本执行各个操作所需要的时间成本进行相对性运算后而获得的预测值.执行计划生成器：主要任务是从能够处理当前查询语句的所有可以应用的执行计划中选择成本最小的一个，并对其进行验证.第6页CBO具有的优点：1.最大的优点就是判断更具有现实性2.通过对统计信息的管理来控制最优化3.没有深刻理解优化器的工作原理，也能确保最优化的性能CBO具有的缺点：1.提前预测执行计划比较困难2.不同版本中存在来得变化3.控制执行计划比较困难注:11G已没有规则优化器.第7页查看执行计划查看执行计划俗话说“功欲善其事，必先利其器”，掌握一些调优的工具会使你更快捷、更准确的找到需要优化的瓶颈之处，达到事半功倍的效果。以下来介绍Oracle查看执行计划工具，如使用工具PL/SQL、Toad、Explainplan、Autotrace.1.PL/SQL(按F5或打开一个TestWindow)1.使用PL/SQL第8页EXPLAINPLANSETSTATEMENT_ID='QUERY1'FORSELECT*FROMSCOTT.DEPT,SCOTT.EMPWHEREDEPT.DEPTNO=EMP.DEPTNO;显示执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY());Planhashvalue:1858509404------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------|0|SELECTSTATEMENT||14|798|6(17)|00:00:01||1|MERGEJOIN||14|798|6(17)|00:00:01||2|TABLEACCESSBYINDEXROWID|DEPT|4|80|2(0)|00:00:01||3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01||*4|SORTJOIN||14|518|4(25)|00:00:01||5|TABLEACCESSFULL|EMP|14|518|3(0)|00:00:01|------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("DEPT"."DEPTNO"="EMP"."DEPTNO")filter("DEPT"."DEPTNO"="EMP"."DEPTNO")也可是通过下面语句查询SELECT*FROMDBA_HIST_SQL_PLANWHERESQL_ID='QUERY1'ORDERBYID;第9页--运行sqlplus包SQL>@D:\oracle\product\10.1.0\Db_1\sqlplus\admin\plustrce;SQL>@D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\utlxplan;SQL>setautotracetraceonly--运行需要查询的脚本SQL>SELECT*FROMSCOTT.DEPT,SCOTT.EMPWHEREDEPT.DEPTNO=EMP.DEPTNO;执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=6Card=14Bytes=798)10MERGEJOIN(Cost=6Card=14Bytes=798)21TABLEACCESS(BYINDEXROWID)OF'DEPT'(TABLE)(Cost=2Card=4Bytes=80)32IN...