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

用户至上 用心服务 Customer First Service Foremost广东省电信有限公司OracleOracle数据库高级技术培训数据库高级技术培训主主 讲：张志伟讲：张志伟时时 间：间：20072007年年6 6月月用户至上 用心服务 Customer First Service Foremost广东省电信有限公司培训目的和内容培训目的和内容对象：主要系统设计、应用开发和技术支持人员主要内容：针对大型IT系统的设计、开发和维护工作，介绍：Oracle性能优化 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司OracleOracle数据库性能优化数据库性能优化用户至上 用心服务 Customer First Service Foremost广东省电信有限公司主要内容主要内容数据库性能优化原理Oracle SQL性能优化交易系统数据库应用分析全表扫描 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司误区误区调系统参数“你调了哪些参数？”系统管理员、DBA不是魔术师。调参数是必要条件，但不是充分条件数据库性能主要是系统问题，是系统管理员工作，与软件开发人员关系不大。性能问题与所有人相关。数据库性能主要在系统上线，压力增加之后再进行考虑。开发阶段无需太多考虑。性能问题与软件工程所有时间周期相关。用户至上 用心服务 Customer First Service Foremost广东省电信有限公司误区误区“告诉我如何把SQL语句改一改，能执行得更快点。”性能与语法没有直接关联。多表连接性能太差，应该通过应用分步骤做。请记住，我们用的是关系数据库。CPU利用率越低越好，CPU利用率高我就紧张开了十个门，我们都走一个门，吞吐量高吗？我的数据处理都在内存完成，没有什么磁盘I/O，应该不会再有性能问题？在内存做，同样会消耗CPU资源。一定要深入了解各种事件、Latch、缓冲池的具体情况这些都是表象，不是原因。用户至上 用心服务 Customer First Service Foremost广东省电信有限公司数据库优化的过程化数据库优化的过程化效益效益设计设计开发开发上线上线 设计和开发阶段的优化设计和开发阶段的优化 80%80%以上以上时间时间代价代价用户至上 用心服务 Customer First Service Foremost广东省电信有限公司设计阶段的优化工作设计阶段的优化工作业务规则的优化设计 数据库体系结构的优化设计 数据库逻辑结构设计数据库物理结构设计应用系统的设计用户至上 用心服务 Customer First Service Foremost广东省电信有限公司开发阶段的优化工作开发阶段的优化工作合理的索引策略 合理的访问路径优化SQL语句的分析数 充分使用PL/SQL 减少锁冲突用户至上 用心服务 Customer First Service Foremost广东省电信有限公司产品阶段的优化工作产品阶段的优化工作内存和CPU的优化和调整 磁盘I/O和物理结构的设计和调整 系统资源竞争的监测和调整 操作系统平台的优化用户至上 用心服务 Customer First Service Foremost广东省电信有限公司20/8020/80定律定律应用设计开发-80%系统参数设置-20%80%的性能问题是由20%的应用导致的80%的性能问题可以由20%的优化技术所解决用户至上 用心服务 Customer First Service Foremost广东省电信有限公司上线后的优化过程（自底向上）上线后的优化过程（自底向上）1.检查系统的利用率2.检查等待事件3.检查物理I/O4.确定范围 系统级,模块级,用户级?5.确定最消耗资源的SQLSTATSPACK报告Top Sessions in OEM6.分析执行计划 explain plan7.分析访问的对象(size/cardinality)8.分析连接、访问方式 9.找出相关问题10.确定解决方案11.测试解决方案12.生产环境实施解决方案13.分析结果用户至上 用心服务 Customer First Service Foremost广东省电信有限公司交易系统和查询统计系统交易系统和查询统计系统交易系统主要特征:日常业务操作，尤其是包含大量前台操作 优先级最高，要求反应速度非常高 并发访问量非常大 单笔事务的资源消耗小 CURSOR问题主要是插入和修改操作（DML）用户至上 用心服务 Customer First Service Foremost广东省电信有限公司交易系统交易系统调试目标高可用性相应速度高并发性可恢复性用户至上 用心服务 Customer First Service Foremost广东省电信有限公司交易系统交易系统主要适用技术 预先分配空间(使用uniform而不是auto allocation)尽量使用BIND变量，提高语句的共享性 CURSOR_SHARING参数设置为SIMILAR/FORCE不采用并行处理技术 分区技术 物化视图技术 普通B*树索引 访问方式主要是按索引进行操作 索引不要太多定期重建索引多表连接主要采用嵌套-循环方式（Nested-Loop）用户至上 用心服务 Customer First Service Foremost广东省电信有限公司数据仓库系统数据仓库系统主要特征 单笔事务的资源消耗大大规模数据的查询并发量不高后台操作 要求速度高 有一定的周期性。例如按月产生会计报表 主要是大量查询操作 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司数据仓库系统数据仓库系统主要适用技术 索引多，除普通B*树索引外，还有Bitmap和Bitmap Join索引 多表连接主要采用HASH连接和排序-匹配方式（Sort-Merge）定期进行统计信息的采集不使用BIND变量，保证语句执行路径的最优化 采用并行处理技术 分区技术 物化视图技术 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司差异差异生产系统生产系统查询系统查询系统索引类型索引类型B*B*索引索引BitmapBitmap、Bitmap JoinBitmap Join索引索引索引量索引量适量适量多多访问方式访问方式按索引访问按索引访问全表扫描全表扫描连接方式连接方式Nested_loopNested_loopHash JoinHash JoinBINDBIND变量变量使用或强制使用使用或强制使用不使用不使用并行技术并行技术不使用不使用使用使用分区技术分区技术使用，但目标不同使用，但目标不同使用，但目标不同使用，但目标不同物化视图使用物化视图使用少量使用少量使用大量使用大量使用用户至上 用心服务 Customer First Service Foremost广东省电信有限公司全表扫描全表扫描什么叫全表扫描?(Full table scan)全表扫描问题简单吗？有的原因简单 有的原因很复杂 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司全表扫描的危害全表扫描的危害大量吞噬硬件尤其是I/O资源 全表扫描语句全表扫描语句非全表扫描语句非全表扫描语句响应速度响应速度00:06:10.0700:00:07.06内存消耗内存消耗3,917M0.9MI/O消耗消耗4,411M2.5M用户至上 用心服务 Customer First Service Foremost广东省电信有限公司全表扫描的危害全表扫描的危害被迫在硬件上进行大量投入 降低系统的使用寿命 导致技术运用的复杂化 当前表，历史表？大业务表按年度分别设计表 历史数据迁移？生产机、查询机？导致其它相关联的系统问题全表扫描看似简单，其实后患无穷，是侵蚀整个系统健康的毒瘤！用户至上 用心服务 Customer First Service Foremost广东省电信有限公司全表扫描的原因全表扫描的原因1:1:缺乏索引缺乏索引最简单因素：就是缺乏相关约束条件字段的索引。解决步骤：按消耗资源的高低定位有问题的SQL语句 在这些SQL语句的执行路径中，分析全表扫描的表 在SQL语句中分析该表的约束条件字段 检查各个约束条件字段的索引是否存在 选择可选性最高的字段建立索引 评估效果用户至上 用心服务 Customer First Service Foremost广东省电信有限公司全表扫描原因全表扫描原因2: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=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 7秒I/O消耗：4,411M-2.5M内存消耗:3,917M -0.9M用户至上 用心服务 Customer First Service Foremost广东省电信有限公司新语句新语句SELECT /*+ORDERED USE_NL(TMP_LSSWJG,SB_ZZS_YBNSR,DJ_NSRXX)/*+ORDERED USE_NL(TMP_LSSWJG,SB_ZZS_YBNSR,DJ_NSRXX)INDEX(SB_ZZS_YBNSR IDX_LM)*/INDEX(SB_ZZS_YBNSR IDX_LM)*/.FROM TMP_LSSWJG,SB_ZZS_YBNSR,DJ_NSRXX TMP_LSSWJG,SB_ZZS_YBNSR,DJ_NSRXX where SBRQ=TO_DATE(2006-04-01,YYYY-MM-DD)SBRQ=TO_DATE(2006-04-01,YYYY-MM-DD)and SBRQ=TO_DATE(2006-04-10,YYYY-MM-DD)SBRQ=TO_DATE(2006-04-10,YYYY-MM-DD)AND SB_ZZS_YBNSR.NSR_SWJG_DM=TMP_LSSWJG.SWJG_DM SB_ZZS_YBNSR.NSR_SWJG_DM=TMP_LSSWJG.SWJG_DM AND SB_ZZS_YBNSR.NSRSBH=DJ_NSRXX.NSRSBH SB_ZZS_YBNSR.NSRSBH=DJ_NSRXX.NSRSBH and SB_ZZS_YBNSR.XXSE_HJ SB_ZZS_YBNSR.JXSE_HJ SB_ZZS_YBNSR.XXSE_HJ SB_ZZS_YBNSR.JXSE_HJ AND用户至上 用心服务 Customer First Service Foremost广东省电信有限公司新执行计划新执行计划用户至上 用心服务 Customer First Service Foremost广东省电信有限公司新语句执行过程新语句执行过程DJ_NSRXXSB_ZZS_YBNSRTMP_LSSWJG IDX_LM PK_DJ_NSRXX 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司分析分析将原来对TMP_LSSWJG的子查询修改为多表连接。新语句需要强制指定表连接顺序、连接方式、使用索引查询架构所导致。即在PB中预先定义了SB_ZZS_YBNSR与DJ_NSRXX先进行连接操作的Data Window，再根据其它条件进行过滤操作复合索引的第一字段原则用户至上 用心服务 Customer First Service Foremost广东省电信有限公司驱动表驱动表驱动表的概念:表做关联时，要将返回记录少的表作为驱动表，即写在from子句的最后，注意，是返回记录少的表，不是记录少的表。如表a,b,其中表a返回记录数少,表b返回记录数多:select a.*from b,a where a.id=b.id 大表放再前面，小表放在后面作为驱动表 如果是三个表join同一个字段,如t1.c1=t2.c1 and t2.c1=t3.c1,那么交叉表就是t2,因为t2出现了两次,这个交叉表的选择是个关键,ORACLE会把交叉表作为驱动表,所以我们优化的原则的交叉表尽量的小,在交叉表中约束条件尽量的多,以使该交叉表返回的记录集尽量的小用户至上 用心服务 Customer First Service Foremost广东省电信有限公司驱动表驱动表附加条件(除去连接本身),在连接相同的列,附加条件有些时候列上的索引会被限制住 比如 where t1.id=t2.id and t2.id=1111.那么附加条件在t2表上,并将其作为驱动表你加了ordered，那驱动表就是from后的第一个表RBO不一定选择最后一个表作为驱动表的,但会优先选用最后一个表作为驱动表.例如遇上 要full table scan 的表,RBO会用它作为驱动表.又例如用到 DBLINK的表,会先处理 DBLINK的表用户至上 用心服务 Customer First Service Foremost广东省电信有限公司WHEREWHERE顺序优化顺序优化规则: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 -该字段用到索引 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司分析分析将原来对TMP_LSSWJG的子查询修改为多表连接。新语句需要强制指定表连接顺序、连接方式、使用索引查询架构所导致。即在PB中预先定义了SB_ZZS_YBNSR与DJ_NSRXX先进行连接操作的Data Window，再根据其它条件进行过滤操作复合索引的第一字段原则