《Oracle数据库性能优化.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库性能优化.ppt（42页珍藏版）》请在金锄头文库上搜索。
1、 2011年年09月月数据数据库调优专题培培训培训目标2通通过本次培本次培训你将了解你将了解数据数据库优化基本原化基本原则Oracle性能性能诊断工具的使用断工具的使用SQL优化主要手段及原化主要手段及原则数据数据库设计原原则及其重要性及其重要性也很重要但本次不涉及也很重要但本次不涉及第三方第三方优化工具的使用化工具的使用数据数据库参数参数调优数据库优化总体原则v为什么要什么要优化？化？v优化的范化的范围？v谁来来优化？化？系系统设计人人员程序开程序开发人人员数据数据库管理管理员系系统管理管理员v有哪些限制？有哪些限制？数据库优化总体原则技技术逻辑正确正确实践践第一第一把握把握重点重点沟通沟通
2、让用用户参参与与简化化业务逻辑持持续改改进优化一般过程定位问题提出优化方案验证优化方案跟踪实际应用发现问题STATSPACK报告v安装安装Statspack的准的准备单独表空独表空间（90M)设置参数置参数timed_statistics=true在在init.ora中中设置置该参数，并参数，并让其生效其生效SQLalter system set timed_statistics=true;vSTATSPACK的安装的安装SQL ?/rdbms/admin/spcreate.sql;脚本将自脚本将自动创建建PERFSTAT用用户，并提示保存，并提示保存STATSPACK统计信息的表空信息的表空
3、间以及以及临时表空表空间STATSPACK报告v性能快照的采集性能快照的采集SQLexec statspack.snap; v统计报告的生成告的生成SQL ?/rdbms/admin/spreport.sql;v性能快照的自性能快照的自动采集采集SQL ?/rdbms/admin/spauto.sql;AWR报告报告中关注哪些内容Cache sizeLoad profileInstance efficiencyPercentagesTop timed eventTop SQLInstance Activity statistictablespace IO stats for DBfile IO
4、 stats for DBbuffer pool statistics for DBPGA Aggr Target Stats for DBEnqueue activity for DBrollback segment stats for DBrollback segment storage for DBUndo Segment Stats for DBlatch activity for DBdictionary Cache stats for DBSGA memory summary for DBinit.ora parameters for DBTop5timedevents数据数据库主
5、要都在干什么主要都在干什么Instanceefficiency内存命中率内存命中率TopSQL80/20原原则优化一般过程定位问题提出优化方案验证优化方案跟踪实际应用发现问题Oracle 如何处理 SQL语句Stage 1: Create a Cursor Stage 2: Parse the StatementStage 3: Bind Any Variables Stage 4: Run the StatementStage 5: Fetch Rows of a QueryStage 6: Close the Cursor PARSERSQLOptimizerCBORBODictionar
6、yCost-baseOptimizerRule-baseOptimizerRow SourceGeneratorExecution Planexecute ResultUser执行计划优化器决定SQL语句的执行路径，对性能至关重要。基于规则的优化器 RBO (Rule Based Optimizer)No change since oracle 7.3,desupported in oracle 10Stable, generate the execution plan according to fixed rulesNot necessary goodDo not support new o
7、racle features(like IOT, partition, materialized view,)基于成本的优化器 CBO (Cost based optimizer)Since oracle 7.3Support all oracle featuresBetter than RBO in most case, Can be bad in some caseChoose the best execution plan according to internal algorithmNeed to analyze schema to get the best execution pla
8、nRBO和CBOOracle操作优先级vRBOPath1:SingleRowbyRowid(等等级最高最高)vRBOPath2:SingleRowbyClusterJoinvRBOPath3:SingleRowbyHashClusterKeywithUniqueorPrimaryKeyvRBOPath4:SingleRowbyUniqueorPrimaryKeyvRBOPath5:ClusteredJoinvRBOPath6:HashClusterKeyvRBOPath7:IndexedClusterKeyvRBOPath8:CompositeIndexvRBOPath9:Single-Col
9、umnIndexesvRBOPath10:BoundedRangeSearchonIndexedColumnsvRBOPath11:UnboundedRangeSearchonIndexedColumnsvRBOPath12:SortMergeJoinvRBOPath13:MAXorMINofIndexedColumnvRBOPath14:ORDERBYonIndexedColumnvRBOPath15:FullTableScan(等等级最低最低)14SQL书写原则v同同样的的SQLSQL只写一次。只写一次。v使用表的使用表的别名，降低解析名，降低解析时间。vSELECTSELECT子句中避免
10、使用子句中避免使用 * * v减少减少访问访问数据数据库库的次数的次数v用用WhereWhere子句替子句替换换HAVINGHAVING子句子句v尽量多使用尽量多使用COMMIT COMMIT v用用=替代替代v用用Unionall代替代替unionv尽量用尽量用EXISTSEXISTS替代替代ININ、用、用NOT EXISTSNOT EXISTS替代替代NOT INNOT INv用用EXISTSEXISTS替替换DISTINCTDISTINCTSQL书写原则v少用少用in、Or关关键词v避免在索引列上避免在索引列上进行行计算算v在条件上多加引号，避免改在条件上多加引号，避免改变数据数据类型
11、型v避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT NULL IS NOT NULL v使用复合索引的第一个列使用复合索引的第一个列进行行查询v在索引列上慎用在索引列上慎用LIKE字句字句v在存在存储过程中慎用打开超程中慎用打开超过一万行数据的游一万行数据的游标v优先使用先使用INT而不是而不是Varchar2v优先使用先使用Varchar2，而不是，而不是CHAR。v避免不必要的排序：避免不必要的排序：distinct、orderby索引创建原则v提高提高查询语句的效率，减慢了句的效率，减慢了DML语句的速度句的速度v在全表在全表扫描和索引之描和索引之间权衡
12、衡v在哪些列建立索引在哪些列建立索引Where字句中引用的列字句中引用的列Join中引用的列中引用的列在子表的在子表的FK上建立索引上建立索引防止防止对父表操作父表操作时锁住子表住子表v在哪些列上不要建立索引在哪些列上不要建立索引经常有常有DML操作操作排它性小排它性小Selectcount(1),count(distinctcol_name)fromtable_name索引创建原则v不建不建议表上的索引超表上的索引超过5个个v能能够用用组合索引合索引则尽量用尽量用组合索引合索引v组合索引的靠左原合索引的靠左原则v对于大表，尽量利用于大表，尽量利用local的分区索引的分区索引例子1优化前se
13、lect * from lccont where contno=9025000001602988;例子1优化后select * from lccont where contno=9025000001602988;例子2优化前 select * from v_bc_lcinsured where insuredno = (select distinct CustomerNo from LLSubReport where SubRptNo in (select SubRptNo from LLReportRela where RptNo = 9050000000766388)例子2优化后Selec
14、t * from v_bc_lcinsured i, LLSubReport t, LLReportRela a where i.insuredno = t.customerno and t.SubRptNo = a.subrptno and a.rptno = 9050000000766388例子3优化前select b.insuredname, b.insuredidno from llclaimpedor a, lccont b where trim(a.contno) = trim(b.contno) and a.caseno = 9055000006018488例子3优化后 sele
15、ct b.insuredname, b.insuredidno from llclaimpedor a, lccont b where a.contno = b.contno and a.caseno = 9055000006018488例子4优化前SELECT r.* FROM LOPRTManager r, LCCont t WHERE r.StateFlag in (1) and (patchflag is null or patchflag 1) AND r.PrtType = 0 and t.ContNo = r.OtherNo and r.Code 70 and r.ManageC
16、om like 86070101% and r.PrtSeq = 1019010000132388unionSELECT r.* FROM LOPRTManager, LCCont WHERE r.StateFlag in (1) and (patchflag is null or patchflag 1) AND r.PrtType = 0 and r.Code = 70 and r.ManageCom like 86070101% and r.PrtSeq = 1019010000132388例子4优化后SELECT r.* FROM LOPRTManager r, LCCont t WH
17、ERE r.StateFlag in (1) and (patchflag is null or patchflag 1) AND r.PrtType = 0 and t.ContNo = r.OtherNo and r.Code 70 and r.ManageCom like 86070101% and r.PrtSeq = 1019010000132388union allSELECT r.* FROM LOPRTManager r WHERE r.StateFlag in (1) and (patchflag is null or patchflag 1) AND r.PrtType =
18、 0 and r.Code = 70 and r.ManageCom like 86070101% and r.PrtSeq = 1019010000132388例子5优化前Select* from lccont where 1=1例子5优化后引导用户录入充分的条件后进行查询。例子6优化前select * from v_bc_lcinsured a where 1 = 1 and a.name = 吕兵例子6优化后create index IND_LBINSURED_NAMESEXBIRTHDAY on LBINSURED (NAME, BIRTHDAY, SEX);例子7优化前select
19、count(distinct(a.contno), sum(c.prem) from lccont a, lcfirstpol cwhere a.contno = c.contno and a.conttype = 1and a.inputdate = 2008-05-09 and a.inputdate = 2008-05-09 and a.inputdate = 2008-05-09 group by a.managecom优化一般过程定位问题提出优化方案验证优化方案跟踪实际应用发现问题影响执行计划的因素在与生产尽可能接近的环境测试硬件资源统计信息Oracle参数索引持续改进定位问题提出优
20、化方案验证优化方案跟踪实际应用发现问题数据库设计的三大范式第一范式v第一范式第一范式(1NF)v定定义：如果关系模式：如果关系模式R的每个关系的每个关系r的属性都是不的属性都是不可分的数据可分的数据项，那么就称，那么就称R是第一范式的模式。是第一范式的模式。v简单的的说，每一个属性都是原子，每一个属性都是原子项，不可分割。，不可分割。数据库设计的三大范式第二范式v第二范式（第二范式（2NF）v定定义：如果关系模式：如果关系模式R是是1NF，且每个非主属性，且每个非主属性完全函数依完全函数依赖于候于候选键，那么就称，那么就称R是第二范式。是第二范式。v简单的的说，第二范式要，第二范式要满足以下的
21、条件：首先要足以下的条件：首先要满足第一范式，其次每个非主属性要完全函数依足第一范式，其次每个非主属性要完全函数依赖与候与候选键，或者是主，或者是主键。也就是。也就是说，每个非主，每个非主属性是由整个主属性是由整个主键函数决定的，而不能由主函数决定的，而不能由主键的的一部分来决定。一部分来决定。数据库设计的三大范式第三范式v第三范式（第三范式（3NF）v定定义：如果关系模式：如果关系模式R是是2NF，且关系模式，且关系模式R（U，F）中的所有非主属性）中的所有非主属性对任何候任何候选关关键字都不存字都不存在在传递依依赖，则称关系称关系R是属于第三范式。是属于第三范式。v简单的的说，第三范式要，第三范式要满足以下的条件：首先要足以下的条件：首先要满足第二范式，其次非主属性之足第二范式，其次非主属性之间不存在函数依不存在函数依赖。由于。由于满足了第二范式，表示每个非主属性都足了第二范式，表示每个非主属性都函数依函数依赖于主于主键。如果非主属性之。如果非主属性之间存在了函数存在了函数依依赖，就会存在，就会存在传递依依赖，这样就不就不满足第三范足第三范式。式。数据库设计的三大范式三大范式第一范式第二范式第三范式三大特性原子性唯一性必须性解决的问题字段意义清晰完全依赖传递依赖
