Oracle SQL性能优化及案例分析

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页RBORBO内置优先顺序1.使用ROWID读取一行数据2.依据聚簇连接读取一行数据3.依据UniqueHASHCluster读取一行数据4.依据UniqueIndex读取一行数据5.CLUSTER连接6.UniqueHASHClusterKey7.UniqueClusterKey8.组合索引9.单一列索引10.依据索引的范围查询11.依据索引的整体范围查询12.SortMerge连接13.索引列的MIN、MAX计算14.索引列的Orderby15.全表扫描SQL的执行顺序是由上到下，表的读取和过滤条件下是从右向左。第5页CBO成本优化器由查询转换器（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页2.使用Explainplan查询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页3.使用sqlplus包来查看执行计划和运行开销--运行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;SQLsetautotracetraceonly--运行需要查询的脚本SQLSELECT*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)32INDEX(FULLSCAN)OF'PK_DEPT'(INDEX(UNIQUE))(Cost=1Card=4)41SORT(JOIN)(Cost=4Card=14Bytes=518)54TABLEACCESS(FULL)OF'EMP'(TABLE)(Cost=3Card=14Bytes=518)统计信息----------------------------------------------------------0recursivecalls0dbblockgets11consistentgets0physicalreads0redosize1597bytessentviaSQL*Nettoclient512bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)14rowsprocessed第10页autotrace运行方式：开始跟踪语句Setautotraceon隐藏SQL语句执行结果（执行SQL语句）Setautotracetraceonly只查看执行统计数据（执行SQL语句）Setautotracetraceonlystatistics只查看执行计划（不执行SQL语句）Setautotracetraceonlyexplain注：PL/SQLt和Explainplan命令并不真正执行SQL语句第11页数据库开发调优三板斧SQL优化创建索引修改表结构第12页SQL优化基本技能:1）基本SQL优化常识.如使用Exists、用=和=替换Between…等.详细请看Oracle+SQL优化.pdf文档.2)找出性能瓶颈，针对性能瓶颈优化.分段执行第13页创建索引Oracle索引有B*树索引、位图索引、函数索引、反向索引、分区索引…等.详细请看此页备注.名词解释：集群因子：指按照索引列值进行了索引排序和对应表中数据行序的相似程度.字段值离散度：是字段不同值总数与行总数的比值.二元高度：是在索引里查询一条记录所需读取的节点数(块数)，级别3是树叶块驻留的级别.反向索引：分散、减少竞争、不适Like和Between操作位图索引：离散度低、位存储按ROWID(StartROWID~EndROWID)、降低存储空间、不适Like和Between操作分区索引：全局分区（Global）、局部分区（Local）需要分区前缀第14页创建索引建议1）只对特定脚本添加索引来优化性能的方法非常不可取。因为添加的索引可能对其它脚本没有帮助，给数据维护带来很大的大压力。2）如果有大量的通过父表关联子表查询，则子表的外键一定要创建索引。反之则需要根据业务数据来确定是否需要创建索引。第15页外键字段索引讨论例1以下两个脚本那个执行效果比较好？ims.spm_user_role表login有没有必要建立索引？select*fromims.spm_user_roler,ims.stafffwherer.login=f.staff_noandr.login='PCITYZH';与select*fromims.spm_user_roler,ims.stafffwherer.login=f.staff_noandf.staff_no='PCITYZH';例2ims.spm_user_role表role_id有没有必要建立索引？select*fromims.spm_user_roler,ims.spm_rolelwherer.role_id=l.role_idandr.login='PCITYZH';例3强依赖父子关系，申请单与申请明细单表子spm_perm_applydetail的父ID(applybillid)有没有必要建立索引？select*fromims.spm_perm_applyp,ims.spm_perm_applydetaildwherep.id='5cc543a9-1b80-44df-aae4-d7b6f6b1dc45'andp.id=d.applybillid;第16页组合索引中列序的决定准则1.是否经常使用?2.是否经常为列使用“=”比较查询条件?3.哪个列具有更好的离散度?4.经常按照何种顺序进行排序?5.何种列将作为附加性列被添加?例：C1(=),C2(between),C3(=)C2(=),C3(between),C4()C1(=),C2(=),C3(Like)C1(=),C2(=),C4()从右边比较结果得知C1+C2创建组合比较好字段次数=值比较离散度查询顺序C1333C2431C3310C4200第17页修改表结构一般是表重构，数据排序后重新插入到表、修改表的类型、分区表。对项目里的SQL不会产生影响，这也是一般优化的前提.详细请看此页备注第18页确定优化目标优化是个没有终点的工作，不管理怎样总有优化空间，所以在优化前先确定优化的目标，预计需要达到什么效果。优化目标有以下参数：1）响应时间(5秒以内)2）减少运算复杂度3）CUP、内存的使用量(使用多少MB)4）吞吐量5）数据冗余量第19页单条SQL优化单条SQL优化只针对脚本级优化，调整脚本的顺序或算法等，一般不要涉及结构和索引.举例:例1.查看用户[林小华]是否存在,前提staff_name字段没有索引declarennumber;beginselectcount(*)intonfromims.staffwherestaff_name='林小华';ifn0...end;例2.查询最大值,前提是根据staff_ID已创建索引selectmax(staff_ID)fromims.staff;例3.进度测量_