oracle数据库高级技术培训性能优化.ppt

1、Oracle数据库高级技术培训,主 讲：张志伟时 间：2007年6月,培训目的和内容,对象：主要系统设计、应用开发和技术支持人员主要内容：针对大型IT系统的设计、开发和维护工作，介绍：Oracle性能优化,Oracle数据库性能优化,主要内容,数据库性能优化原理Oracle SQL性能优化交易系统数据库应用分析全表扫描,误区,调系统参数“你调了哪些参数？”系统管理员、DBA不是魔术师。调参数是必要条件，但不是充分条件数据库性能主要是系统问题，是系统管理员工作，与软件开发人员关系不大。性能问题与所有人相关。数据库性能主要在系统上线，压力增加之后再进行考虑。开发阶段无需太多考虑。性能问题与软件工程

2、所有时间周期相关。,误区,“告诉我如何把SQL语句改一改，能执行得更快点。”性能与语法没有直接关联。多表连接性能太差，应该通过应用分步骤做。请记住，我们用的是关系数据库。CPU利用率越低越好，CPU利用率高我就紧张开了十个门，我们都走一个门，吞吐量高吗？我的数据处理都在内存完成，没有什么磁盘I/O，应该不会再有性能问题？在内存做，同样会消耗CPU资源。一定要深入了解各种事件、Latch、缓冲池的具体情况这些都是表象，不是原因。,数据库优化的过程化,效益,设计,开发,上线,设计和开发阶段的优化 80%以上,时间,代价,设计阶段的优化工作,业务规则的优化设计 数据库体系结构的优化设计 数据库逻辑结

3、构设计数据库物理结构设计应用系统的设计,开发阶段的优化工作,合理的索引策略 合理的访问路径优化SQL语句的分析数 充分使用PL/SQL 减少锁冲突,产品阶段的优化工作,内存和CPU的优化和调整 磁盘I/O和物理结构的设计和调整 系统资源竞争的监测和调整 操作系统平台的优化,20/80定律,应用设计开发-80%系统参数设置-20%80%的性能问题是由20%的应用导致的80%的性能问题可以由20%的优化技术所解决,上线后的优化过程（自底向上）,检查系统的利用率检查等待事件检查物理I/O确定范围 系统级,模块级,用户级?确定最消耗资源的SQLSTATSPACK报告Top Sessions in OE

4、M分析执行计划 explain plan分析访问的对象(size/cardinality)分析连接、访问方式 找出相关问题确定解决方案测试解决方案生产环境实施解决方案分析结果,交易系统和查询统计系统,交易系统主要特征:日常业务操作，尤其是包含大量前台操作 优先级最高，要求反应速度非常高 并发访问量非常大 单笔事务的资源消耗小 CURSOR问题主要是插入和修改操作（DML）,交易系统,调试目标高可用性相应速度高并发性可恢复性,交易系统,主要适用技术 预先分配空间(使用uniform而不是auto allocation)尽量使用BIND变量，提高语句的共享性 CURSOR_SHARING参数设置为

5、SIMILAR/FORCE不采用并行处理技术 分区技术 物化视图技术 普通B*树索引 访问方式主要是按索引进行操作 索引不要太多定期重建索引多表连接主要采用嵌套-循环方式（Nested-Loop）,数据仓库系统,主要特征 单笔事务的资源消耗大大规模数据的查询并发量不高后台操作 要求速度高 有一定的周期性。例如按月产生会计报表 主要是大量查询操作,数据仓库系统,主要适用技术 索引多，除普通B*树索引外，还有Bitmap和Bitmap Join索引 多表连接主要采用HASH连接和排序-匹配方式（Sort-Merge）定期进行统计信息的采集不使用BIND变量，保证语句执行路径的最优化 采用并行处理技

6、术 分区技术 物化视图技术,差异,全表扫描,什么叫全表扫描?(Full table scan)全表扫描问题简单吗？有的原因简单 有的原因很复杂,全表扫描的危害,大量吞噬硬件尤其是I/O资源,全表扫描的危害,被迫在硬件上进行大量投入 降低系统的使用寿命 导致技术运用的复杂化 当前表，历史表？大业务表按年度分别设计表 历史数据迁移？生产机、查询机？导致其它相关联的系统问题全表扫描看似简单，其实后患无穷，是侵蚀整个系统健康的毒瘤！,全表扫描的原因1:缺乏索引,最简单因素：就是缺乏相关约束条件字段的索引。解决步骤：按消耗资源的高低定位有问题的SQL语句 在这些SQL语句的执行路径中，分析全表扫描的表

7、在SQL语句中分析该表的约束条件字段 检查各个约束条件字段的索引是否存在 选择可选性最高的字段建立索引 评估效果,全表扫描原因2:索引被抑止,在字段前增加了函数 to_char(DJ_SZ.JDRQ,YYYY.MM.DD)BETWEEN 2006.04.01 AND 2006.04.17 修改为：DJ_SZ.JDRQ BETWEEN to_date(2006.04.01,YYYY.MM.DD)AND to_date(2006.04.17,YYYY.MM.DD)字段嵌入表达式之中 DJ_NSRZT_BG.YXQ_Z+7 sysdate 修改为：DJ_NSRZT_BG.YXQ_Z sysdate-

8、7,执行路径不正确,SELECT SB_ZZS_YBNSR.NSRSBH,.FROM SB_ZZS_YBNSR,DJ_NSRXX where SBRQ=TO_DATE(2006-04-01,YYYY-MM-DD)and SBRQ=TO_DATE(2006-04-10,YYYY-MM-DD)AND SB_ZZS_YBNSR.NSRSBH=DJ_NSRXX.NSRSBH and SB_ZZS_YBNSR.XXSE_HJ SB_ZZS_YBNSR.JXSE_HJ AND EXISTS(SELECT 1 FROM TMP_LSSWJG WHERE SB_ZZS_YBNSR.NSR_SWJG_DM=TM

9、P_LSSWJG.SWJG_DM)涉及三个表的操作 SB_ZZS_YBNSR,DJ_NSRXX,TMP_LSSWJG,执行计划,执行步骤,DJ_NSRXX,SB_ZZS_YBNSR,TMP_LSSWJG,PK_DJ_NSRXX,优化前后比较,响应速度：370秒-7秒I/O消耗：4,411M-2.5M内存消耗:3,917M-0.9M,新语句,SELECT/*+ORDERED USE_NL(TMP_LSSWJG,SB_ZZS_YBNSR,DJ_NSRXX)INDEX(SB_ZZS_YBNSR IDX_LM)*/.FROM TMP_LSSWJG,SB_ZZS_YBNSR,DJ_NSRXX where

10、 SBRQ=TO_DATE(2006-04-01,YYYY-MM-DD)and SBRQ=TO_DATE(2006-04-10,YYYY-MM-DD)AND SB_ZZS_YBNSR.NSR_SWJG_DM=TMP_LSSWJG.SWJG_DM AND SB_ZZS_YBNSR.NSRSBH=DJ_NSRXX.NSRSBH and SB_ZZS_YBNSR.XXSE_HJ SB_ZZS_YBNSR.JXSE_HJ AND,新执行计划,新语句执行过程,DJ_NSRXX,SB_ZZS_YBNSR,TMP_LSSWJG,IDX_LM,PK_DJ_NSRXX,分析,将原来对TMP_LSSWJG的子查询

11、修改为多表连接。新语句需要强制指定表连接顺序、连接方式、使用索引查询架构所导致。即在PB中预先定义了SB_ZZS_YBNSR与DJ_NSRXX先进行连接操作的Data Window，再根据其它条件进行过滤操作复合索引的第一字段原则,驱动表,驱动表的概念:表做关联时，要将返回记录少的表作为驱动表，即写在from子句的最后，注意，是返回记录少的表，不是记录少的表。如表a,b,其中表a返回记录数少,表b返回记录数多:select a.*from b,a where a.id=b.id 大表放再前面，小表放在后面作为驱动表 如果是三个表join同一个字段,如t1.c1=t2.c1 and t2.c1=

12、t3.c1,那么交叉表就是t2,因为t2出现了两次,这个交叉表的选择是个关键,ORACLE会把交叉表作为驱动表,所以我们优化的原则的交叉表尽量的小,在交叉表中约束条件尽量的多,以使该交叉表返回的记录集尽量的小,驱动表,附加条件(除去连接本身),在连接相同的列,附加条件有些时候列上的索引会被限制住 比如 where t1.id=t2.id and t2.id=1111.那么附加条件在t2表上,并将其作为驱动表你加了ordered，那驱动表就是from后的第一个表RBO不一定选择最后一个表作为驱动表的,但会优先选用最后一个表作为驱动表.例如遇上 要full table scan 的表,RBO会用它

13、作为驱动表.又例如用到 DBLINK的表,会先处理 DBLINK的表,WHERE顺序优化,规则:1、如果是多表连接，表连接的条件放在普通条件前 2、如果使用到了索引，该条件靠后 3、根据条件中筛选得到的记录百分比，越小的越靠后 4、数据量大的时候尽量不使用in例如:where a.id=b.id-表连接条件and a.month=200505-筛选出记录为总记录的60%and b.state=B-筛选出记录为总记录的25%and a.serv_id=123456-该字段用到索引,分析,将原来对TMP_LSSWJG的子查询修改为多表连接。新语句需要强制指定表连接顺序、连接方式、使用索引查询架构所导致。即在PB中预先定义了SB_ZZS_YBNSR与DJ_NSRXX先进行连接操作的Data Window，再根据其它条件进行过滤操作复合索引的第一字段原则,