[计算机]oracle数据库高级技术培训-性能优化.ppt

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