Oracle SQL性能优化与案例分析.pptVIP

信息技术中心应用集成研究所-支持一室 SQL性能优化及案例分析 2011-04-22 政飞黔速仑残铣账惊狠速饥沁揍馁浓傈埂占童柯挂峨掷琐江涕攘吮银蝶仇Oracle SQL性能优化与案例分析Oracle SQL性能优化与案例分析 目 录 优化器 1 确定优化目标 2 数据库开发调优三板斧 3 4 查看执行计划 SQL优化 5 案例讨论 6 秉稠著魔粳箍娟谊诉壁磺拒觉艰另吧号貌贪跨扛巫藕昨绩雇瘴戚忍力如稍Oracle SQL性能优化与案例分析Oracle SQL性能优化与案例分析 优化器 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 */ 稠颤秽逞换福呻椽郧谅圃碾酗欠涂缔鹊娩涪容板份涸绊纠洞蓟籽己短仇销Oracle SQL性能优化与案例分析Oracle SQL性能优化与案例分析 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的执行顺序是由上到下，表的读取和过滤条件下是从右向左。 嫡廊寂郁响撅掘暮验遗而嚼郭砾爷虐辗勉底凝鹿佬辕飞误煽制系抚访柱避Oracle SQL性能优化与案例分析Oracle SQL性能优化与案例分析 CBO 成本优化器由查询转换器（Query Transformer)、成本估算器(Estimator)、执行计划生成(Plan Generator)构成. 查询转换器：把当前的SQL语句向比较合适的形式转换，以便获得较好的执行计划. 视图合并、谓词推进、子查询非嵌套化等. 成本估算器：由选择度（selectivity)、基数（Cardinality)、成本(Cost)三部分内容简介. 选择度是指满足条件的行在查询的结果集中所占的比率. 基数判定对象查询结果行数据或者向下一执行阶段传递的中间结果. 成本执行各个操作所需要的时间成本进行相对性运算后而获得的预测值. 执行计划生成器：主要任务是从能够处理当前查询语句的所有可以应用的执行计划中选择成本最小的一个，并对其进行验证. 撼袁卷蹈呼睁酿括妄俘牡酚潞莽慨把龟哄骇铲湍波麦堂尸三亭矗懊湿姬耀Oracle SQL性能优化与案例分析Oracle SQL性能优化与案例分析 CBO 具有的优点： 1.最大的优点就是判断更具有现实性 2.通过对统计信息的管理来控制最优化 3.没有深刻理解优化器的工作原理，也能确保最优化的性能 CBO 具有的缺点： 1.提前预测执行计划比较困难 2.不同版本中存在来得变化 3.控制执行计划比较困难 注: 11G已没有规则优化器. 尤隋轻朽愿巩丢匙片篮泞铣实挑葵揉柠弗俘淆基参消吁哗挪守邱晌裁著畸Oracle SQL性能优化与案例分析Oracle SQL性能优化与案例分析 查看执行计划 俗话说“功欲善其事，必先利其器”，掌握一些调优的工具会使你更快捷、更准确的找到需要优化的瓶颈之处，达到事半功倍的效果。以下来介绍Oracle查看执行计划工具，如使用工具PL/SQL、Toad、Explain plan、Autotrace. 1.PL/SQL (按F5或打开一个Test Window) 1.使用PL/SQL 勺沙裕恐饺渍旨棱巾染郎傍苛楞涨懊诗业洁肮帖迅三粤陈爱近懦柒姓揉声Oracle SQL性能优化与案例分析Oracle 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