• 1. 技术支持部内部交流 ORACLE数据库 调优
  • 2. 交流内容 数据库性能优化原理 OLTP和OLAP系统的差异分析 Oracle SQL性能优化 论全表扫描2018/10/24 - 2
  • 3. 数据库性能优化过程-自顶向下2018/10/24 - 3效益设计开发上线 设计和开发阶段的优化 – 80%以上时间代价
  • 4. 设计阶段的优化工作业务规则的优化设计 数据库体系结构的优化设计 数据库逻辑结构设计 数据库物理结构设计 应用系统的设计2018/10/24 - 4
  • 5. 开发阶段的优化工作合理的索引策略 优化执行计划 充分使用PL/SQL 减少锁冲突 ………2018/10/24 - 5
  • 6. 开发阶段的优化工作2018/10/24 - 6
  • 7. 产品阶段的优化工作内存和CPU的优化和调整 磁盘I/O和物理结构的设计和调整 系统资源竞争的监测和调整 操作系统平台的优化 … …2018/10/24 - 7
  • 8. 20/80定律应用设计开发 -- 80% 系统参数设置 -- 20% 80%的性能问题是由20%的应用导致的 80%的性能问题可以由20%的优化技术所解决2018/10/24 - 8
  • 9. 基础知识性能调优方法论 SQL语句执行过程 SQL语句分析工具:EXPLAIN ,AUTOTRACE ,SQL Trace ,TKPROF ,10046 ,10053 基于规则优化器(RBO)和基于成本优化器(CBO) 索引及数据访问方式 统计信息的采集 Hint的使用 表连接的优化 优化执行计划的固化 Bitmap等高级索引的使用 物化视图(汇总表)和临时表的使用 数据仓库有关技术的应用 … … 2018/10/24 - 9
  • 10. 上线之后的优化过程(自底向上)检查系统负载 检查物理I/O 检查等待事件 确定范围 – 系统级,用户级? 确定最消耗资源的SQL STATSPACK报告 Top Sessions in OEM 分析执行计划 explain plan 分析访问的对象 (size/cardinality) 分析连接、访问方式… … 找出相关问题 确定解决方案 测试解决方案 生产环境实施解决方案 分析结果2018/10/24 - 10
  • 11. 系统怎么突然变慢了?从第5步开始,向上排查 检查系统负载 检查物理I/O 检查等待事件 确定范围 – 系统级,用户级? 确定最消耗资源的SQL 2018/10/24 - 11
  • 12. OLTP和OLAP系统的差异分析2018/10/24 - 12 OLTP即联机事务处理,就是我们经常说的关系数据库,意即记录即时的增、删、改、查,就是我们经常应用的东西,这是数据库的基础. OLAP即联机分析处理,是数据仓库的核心部心,所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库,用于处理商业智能、决策支持等重要的决策信息;数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析;是处理两种不同用途的工具而已。
  • 13. OLTP主要特征 日常业务操作,尤其是包含大量前台操作 优先级最高,要求反应速度非常高 并发访问量非常大 单笔事务的资源消耗小 CURSOR问题 主要是插入和修改操作(DML)2018/10/24 - 13
  • 14. OLTP调试目标 高可用性 相应速度 高并发性 可恢复性 典型应用2018/10/24 - 14
  • 15. OLTP典型应用 银行ATM 网上商城(TAOBAO) 积分 客户俱乐部 OCS ……2018/10/24 - 15
  • 16. OLTP主要适用技术 普通B*树索引 访问方式主要是按索引进行操作 索引不要太多 为Sequence字段设计Reverse Key 定期重建索引 多表连接主要采用嵌套-循环方式(Nested-Loop) 为固定进行join操作的表使用Cluster技术 IOT表的应用2018/10/24 - 16
  • 17. OLTP主要适用技术 预先分配空间(使用uniform而不是auto allocation) 尽量使用BIND变量,提高语句的共享性 CURSOR_SHARING参数设置为SIMILAR/FORCE 不采用并行处理技术 分区技术 物化视图技术 … … 2018/10/24 - 17
  • 18. OLAP-数据分析/数据仓库系统主要特征 单笔事务的资源消耗大 大规模数据的查询 并发量不高 后台操作 要求速度高 有一定的周期性。例如按月产生会计报表 主要是大量查询操作 2018/10/24 - 18Data
  • 19. 数据分析/数据仓库系统典型应用 大批量数据抽取、转换和转换(ETL) 联机在线分析(OLAP) 历史数据迁移 会计计帐 生成统计报表 … … 2018/10/24 - 19
  • 20. 数据分析/数据仓库系统主要适用技术 索引多,除普通B*树索引外,还有Bitmap和Bitmap Join索引 合理设置db_cache_size和DB_FILE_MULTIBLOCK_READ_COUNT参数 保证extent大小是DB_FILE_MULTIBLOCK_READ_COUNT的倍数 多表连接主要采用HASH连接和排序-匹配方式(Sort-Merge) 定期进行统计信息的采集2018/10/24 - 20
  • 21. 数据分析/数据仓库系统主要适用技术 不使用BIND变量,保证语句执行路径的最优化 采用并行处理技术 分区技术 物化视图技术 2018/10/24 - 21对操作大数据量的SQL语句精心调试,保证最优执行路径
  • 22. OLTP和OLAP系统的主要差异2018/10/24 - 22OLTPOLAP索引类型B-TreeBitmap、B-Tree索引数量适量多常见访问方式按索引访问全表扫描连接方式Nested_loopHash JoinBIND变量使用或强制使用不一定使用并行技术不使用使用分区技术使用,但目标不同使用,但目标不同物化视图使用少量使用大量使用
  • 23. 全表扫描什么叫全表扫描(Full Table Scan) 全表扫描的影响 2018/10/24 - 23在数据库中,对无索引的表进行查询一般称为全表扫描。全表扫描是数据库服务器用来搜寻表的每一条记录的过程,直到所有符合给定条件的记录返回为止。  全表扫描语句 非全表扫描语句 响应速度 00:06:10.07 00:00:07.06 内存消耗 3,917M 0.9M I/O消耗 4,411M 2.5M
  • 24. 全表扫描的危害性被迫在硬件上进行大量投入 降低系统的使用寿命 导致技术运用的复杂化 当前表,历史表? 大业务表按年度分别设计表 历史数据迁移? 读写分离? 导致其它相关联的系统问题 2018/10/24 - 24
  • 25. 全表扫描的原因1:缺乏索引最简单因素:就是缺乏相关约束条件字段的索引。 解决步骤: 按消耗资源的高低定位有问题的SQL语句 在这些SQL语句的执行计划中,分析全表扫描的表 在SQL语句中分析该表的谓词条件字段 检查各个谓词条件字段的索引是否存在 选择可选性最高的字段建立索引 评估效果2018/10/24 - 25
  • 26. 全表扫描的原因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 2018/10/24 - 26
  • 27. 全表扫描的原因3:索引没有启动原因分析 统计信息没有及时更新,导致基于成本的Oracle优化器没有选择合适的按索引访问数据的执行路径 Oracle 老版本的优化器存在不足 解决办法 及时更新统计信息 通过HINT,强制使用索引 升级Oracle版本 2018/10/24 - 27
  • 28. 案例响应速度: 988秒->11秒 2018/10/24 - 28
  • 29. 复合索引的使用建议复合索引效率大大高于单字段索引—优先考虑复合索引 复合索引的前缀性(prefix)的风险 复合索引的设计建议如下: 分析最常见的条件定义操作 如果条件定义模式固定,例如同时涉及到月份、纳税人识别号、税务机关代码三个字段的条件,则可以考虑建立一个复合索引 在复合索引中,按可选性高低,或者条件定义的频度进行排序。例如该索引的的字段排列顺序为:纳税人识别号、税务机关代码、月份 如果条件涉及的字段不固定,组合比较灵活,则分别为月份、纳税人识别号、税务机关代码三个字段建立索引 2018/10/24 - 29
  • 30. 数据库优化的常用技巧总结调整初始化参数 SGA PGA 索引 索引组织表(OLTP) 簇表(OLTP) 调整执行计划 HINT 统计信息 2018/10/24 - 30
  • 31. 2018/10/24 - 31Q&问答 A
  • 32. 2018/10/24 - 32谢谢