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

用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 Oracle数据库高级技术培训 主 讲：张志伟 时 间：2007年6月 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 培训目的和内容 对象：主要系统设计、应用开发和技术支持人员 主要内容：针对大型IT系统的设计、开发和维护工作 ，介绍： Oracle性能优化 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 Oracle数据库性能优化 用户至上 用心服务 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%以上 时间 代价 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 设计阶段的优化工作 业务规则的优化设计 数据库体系结构的优化设计 数据库逻辑结构设计 数据库物理结构设计 应用系统的设计 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 开发阶段的优化工作 合理的索引策略 合理的访问路径 优化SQL语句的分析数 充分使用PL/SQL 减少锁冲突 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 产品阶段的优化工作 内存和CPU的优化和调整 磁盘I/O和物理结构的设计和调整 系统资源竞争的监测和调整 操作系统平台的优化 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 20/80定律 应用设计开发 - 80% 系统参数设置 - 20% 80%的性能问题是由20%的应用导致的 80%的性能问题可以由20%的优化技术所解决 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 上线后的优化过程（自底向上） 检查系统的利用率 检查等待事件 检查物理I/O 确定范围 系统级, 模块级, 用户级? 确定最消耗资源的SQL STATSPACK报告 Top Sessions in OEM 分析执行计划 explain plan 分析访问的对象 (size/cardinality) 分析连接、访问方式 找出相关问题 确定解决方案 测试解决方案 生产环境实施解决方案 分析结果 用户至上 用心服务 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*索引Bitmap、 Bitmap Join索 引 索引量适量多 访问方式按索引访问全表扫描 连接方式Nested_loopHash Join BIND变量使用或强制使 用 不使用 并行技术不使用使用 分区技术使用，但目标 不同 使用，但目标 不同 物化视图使用少量使用大量使用 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 全表扫描 什么叫全表扫描?(Full table scan) 全表扫描问题简单吗？ 有的原因简单 有的原因很复杂 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 全表扫描的危害 大量吞噬硬件尤其是I/O资源 全表扫描语句非全表扫描语句 响应速度 00:06:10.0700:00:07.06 内存消耗 3,917M0.9M I/O消耗4,411M2.5M 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 全表扫描的危害 被迫在硬件上进行大量投入 降低系统的使用寿命 导致技术运用的复杂化 当前表，历史表？ 大业务表按年度分别设计表 历史数据迁移？ 生产机、查询机？ 导致其它相关联的系统问题 全表扫描看似简单，其实后患无穷，是侵蚀整个系统 健康的毒瘤！ 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 全表扫描的原因1:缺乏索引 最简单因素：就是缺乏相关约束条件字段的索引。 解决步骤： 按消耗资源的高低定位有问题的SQL语句 在这些SQL语句的执行路径中，分析全表扫描的表 在SQL语句中分析该表的约束条件字段 检查各个约束条件字段的索引是否存在 选择可选性最高的字段建立索引 评估效果 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 全表扫描原因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 = TO_DATE(2006-04-01, YYYY-MM-DD) and SBRQ 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) INDEX(SB_ZZS_YBNSR IDX_LM)*/ . . FROM TMP_LSSWJG, 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.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 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 新执行计划 用户至上 用心服务 Customer First Service Foremost广东省电信有限公司 新语句执行过程 DJ_NSRXX SB_ZZS_YBNSR TMP_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广东省电信有限公司 WHERE顺序优化 规则: 1、如果是多表连接，表连接的条件放在普通条件前 2、如果使用到了索引，该条件靠后 3、根据条件中筛选得到的记录百分比，越小的越靠后 4、数据量大的时候尽量不使用in 例如: where a.id=b.id -表连接条件 and a.month=