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

Oracle数据库高级技术培训 主讲 张志伟时间 2007年6月 培训目的和内容 对象 主要系统设计 应用开发和技术支持人员主要内容 针对大型IT系统的设计 开发和维护工作 介绍 Oracle性能优化 Oracle数据库性能优化 主要内容 数据库性能优化原理OracleSQL性能优化交易系统数据库应用分析全表扫描 误区 调系统参数 你调了哪些参数 系统管理员 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确定范围 系统级 模块级 用户级 确定最消耗资源的SQLSTATSPACK报告TopSessionsinOEM分析执行计划explainplan分析访问的对象 size cardinality 分析连接 访问方式 找出相关问题确定解决方案测试解决方案生产环境实施解决方案分析结果 交易系统和查询统计系统 交易系统主要特征 日常业务操作 尤其是包含大量前台操作优先级最高 要求反应速度非常高并发访问量非常大单笔事务的资源消耗小CURSOR问题主要是插入和修改操作 DML 交易系统 调试目标高可用性相应速度高并发性可恢复性 交易系统 主要适用技术预先分配空间 使用uniform而不是autoallocation 尽量使用BIND变量 提高语句的共享性CURSOR SHARING参数设置为SIMILAR FORCE不采用并行处理技术分区技术物化视图技术普通B 树索引访问方式主要是按索引进行操作索引不要太多定期重建索引多表连接主要采用嵌套 循环方式 Nested Loop 数据仓库系统 主要特征单笔事务的资源消耗大大规模数据的查询并发量不高后台操作要求速度高有一定的周期性 例如按月产生会计报表主要是大量查询操作 数据仓库系统 主要适用技术索引多 除普通B 树索引外 还有Bitmap和BitmapJoin索引多表连接主要采用HASH连接和排序 匹配方式 Sort Merge 定期进行统计信息的采集不使用BIND变量 保证语句执行路径的最优化采用并行处理技术分区技术物化视图技术 差异 全表扫描 什么叫全表扫描 Fulltablescan 全表扫描问题简单吗 有的原因简单有的原因很复杂 全表扫描的危害 大量吞噬硬件尤其是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 JDRQBETWEENto date 2006 04 01 YYYY MM DD ANDto date 2006 04 17 YYYY MM DD 字段嵌入表达式之中DJ NSRZT BG YXQ Z 7 sysdate修改为 DJ NSRZT BG YXQ Z sysdate 7 执行路径不正确 SELECTSB ZZS YBNSR NSRSBH FROMSB ZZS YBNSR DJ NSRXXwhereSBRQ TO DATE 2006 04 01 YYYY MM DD andSBRQ TO DATE 2006 04 10 YYYY MM DD ANDSB ZZS YBNSR NSRSBH DJ NSRXX NSRSBHandSB ZZS YBNSR XXSE HJ SB ZZS YBNSR JXSE HJANDEXISTS SELECT1FROMTMP LSSWJGWHERESB 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 ORDEREDUSE NL TMP LSSWJG SB ZZS YBNSR DJ NSRXX INDEX SB ZZS YBNSRIDX LM FROMTMP LSSWJG SB ZZS YBNSR DJ NSRXXwhereSBRQ TO DATE 2006 04 01 YYYY MM DD andSBRQ TO DATE 2006 04 10 YYYY MM DD ANDSB ZZS YBNSR NSR SWJG DM TMP LSSWJG SWJG DMANDSB ZZS YBNSR NSRSBH DJ NSRXX NSRSBHandSB ZZS YBNSR XXSE HJ SB ZZS YBNSR JXSE HJAND 新执行计划 新语句执行过程 DJ NSRXX SB ZZS YBNSR TMP LSSWJG IDX LM PK DJ NSRXX 分析 将原来对TMP LSSWJG的子查询修改为多表连接 新语句需要强制指定表连接顺序 连接方式 使用索引查询架构所导致 即在PB中预先定义了SB ZZS YBNSR与DJ NSRXX先进行连接操作的DataWindow 再根据其它条件进行过滤操作复合索引的第一字段原则 驱动表 驱动表的概念 表做关联时 要将返回记录少的表作为驱动表 即写在from子句的最后 注意 是返回记录少的表 不是记录少的表 如表a b 其中表a返回记录数少 表b返回记录数多 selecta fromb awherea id b id大表放再前面 小表放在后面作为驱动表如果是三个表join同一个字段 如t1 c1 t2 c1andt2 c1 t3 c1 那么交叉表就是t2 因为t2出现了两次 这个交叉表的选择是个关键 ORACLE会把交叉表作为驱动表 所以我们优化的原则的交叉表尽量的小 在交叉表中约束条件尽量的多 以使该交叉表返回的记录集尽量的小 驱动表 附加条件 除去连接本身 在连接相同的列 附加条件有些时候列上的索引会被限制住比如wheret1 id t2 idandt2 id 1111 那么附加条件在t2表上 并将其作为驱动表你加了ordered 那驱动表就是from后的第一个表RBO不一定选择最后一个表作为驱动表的 但会优先选用最后一个表作为驱动表 例如遇上要fulltablescan的表 RBO会用它作为驱动表 又例如用到DBLINK的表 会先处理DBLINK的表 WHERE顺序优化 规则 1 如果是多表连接 表连接的条件放在普通条件前2 如果使用到了索引 该条件靠后3 根据条件中筛选得到的记录百分比 越小的越靠后4 数据量大的时候尽量不使用in例如 wherea id b id 表连接条件anda month 200505 筛选出记录为总记录的