ORACLE原理及查询优化.pptx

ORACLE查询优化 2017年3月29日 培训目标 为什么要优化？ 数据库SQL执行过程和原理 知道什么是执行计划，明白如何进行性能评估 知道什么是硬解码 常见问题分析 系统优化的重要性 系统效率不高的后果 资源浪费 系统效率低下 系统稳定性下降 服务处理积压 发用户满意度下降 维护人员工作量增大 对系统优化迫在眉睫 系统优化的目标 使访问的数据库数据块数量降到最低 把尽量多的数据块缓冲到内存 减小等待时间 提高系统响应速度 增加系统吞吐量 减轻系统负载 增强系统处理能力 提高系统稳定性 提高前台人员的感观体验 提高用户满意度 SQL语句优化是提高性能的重要环节 开发人员不能只注重功能的实现，不管性能如何 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法 必需遵守既定的开发规范 未经过SQL语句优化的模块不要上线 目前被动优化过程 监控系统，找到瓶颈 分析产生瓶颈的原因 定位有问题的语句 检查执行计划 检查执行过程中优化器的统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 讨论实施方案 复查确定已解决 目前有待提高的方面 经过长时间的运行和调整，系统、网络、数据库参数等基本上都已到位，可以优化的余地很少，需要从应用、设计等方面考虑优化提升。 部分业务运行效率不高，程序处理流程、处理方法有待提升 部分程序初始设计不合理，如表参数的设置不合理，没有合理使用表空间，没有合理使用分区技术，索引和数据表建在同一表空间等 大量有问题或低效的SQL语句，浪费了大量系统资源，造成： 主机CPU紧张 主机磁盘IO量大、等待高 多项处理时间长，响应慢 低效SQL语句不仅本身执行时间长，甚至影响了其他业务的正常运行。 造成低效SQL语句的根源一样，很多都是同样的问题，如没有创建合适索引，没有正确使用索引，SQL写法导致没有用上索引等。 程序上线前没有完善的测试流程，造成开发环境快，上线生产后慢的情况（开发、生产数据量不一致），性能差别大。 什么是好的SQL语句？ 尽量简单，模块化 易读、易维护 节省资源 内存 CPU 扫描的数据块要少 少排序 不造成死锁 大家应做的工作 必需遵守既定的开发规范 编写高效的SQL程序 避免程序上线后的被动优化 开发人员不能只注重功能的实现，不管性能如何 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法 SQL 语句的执行步骤 语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的意义。 语义分析 ，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。 表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。 选择优化器，不同的优化器一般产生不同的“执行计划” 选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。 选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表 选择数据的搜索路径， 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。 运行“执行计划” 优化器的优化模式（一） Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的 Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO) 1）RBO方式 优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。 2）CBO方式 优化器会根据成本计算出最优的执行计划，做为SQL程序的执行路径。是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息，很多的时侯过期统计信息会令优化器做出一个错误的执行计划 SQL语句优化的过程 定位有问题的语句 检查执行计划 检查执行过程中优化器的统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 有些SQL语句不具备优化的可能，需要优化处理方式 达到最佳执行计划 SQL程序的性能评估指标 执行计划当中的资源消耗计量单位 Cost　每个执行项的综合操作成本 Card　每个执行项访问的行数(记录数) Bytes 该操作访问的byte数 SQL程序的成本信息