860高性能SQL优化.ppt

Oracle 高性能SQL优化 什么是Oracle性能管理 Oracle性能管理是一种利用已证实过的方法，反复试验、判断和实现优化方案的循序渐进的过程。 SQL语句优化是性能管理的重要组成部分。 Oracle性能管理的类型（1） 主动的性能管理？ 　　以一种高性能的体系结构思想指导设计和开发完善的系统。 　　　它也指定期监控系统的性能，注意各种苗头，在它们成为实际问题前加以解决。 Oracle性能管理的类型（2） 被动的性能管理？ 　　涉及性能评估、故障排除、优化以及在现有硬件和软件体系结构内对环境进行的优化。它在出现问题时进行处理。 　　相对于所获得的性能收益，其代价一般较高。 什么是优化 　　优化是有目的地更改系统的一个或多个组件，使其满足一个或多个目标的过程。 　　　对Oracle来说，优化是进行有目的的调整组件以改善性能，即增加吞吐量，减少响应时间。 SQL优化的目标 去掉不必要的大型表的全表扫描 缓存小型表的全表扫描 检验优化索引的使用 检验优化的连接技术 检查子查询 不同优化阶段性能收益 SQL调整的障碍 对特定的SQL生成器的调整 调整不可再用的SQL语句 来自管理方面的抵制 来自SQL程序员的抵制 SQL语句处理过程 SQL语句的解析过程 调入库缓存 (内存) 检验安全性 检验语法 重新格式化查询 (查询重写) 减少SQL解析的技术 将所有的SQL语句放置在存储过程中 在SQL语句中避免使用直接量 生成执行计划 对Oracle来说查询速度和查询效率是两个完全不同的概念。Oracle有两个优化目标，这两个目标分别反映了为了满足SQL的要求而使用的各自不同的通用手段。 最大速度 以最短的时间返回结果集 (Oracle的first_rows优化器模式) 最小资源占用 使用少量的机器和磁盘资源 (all_rows优化器模式) SQL语句性能评估的方法 SQL语句的执行计划 执行SQL语句所需要的时间 执行SQL语句所需要的时间 查看SQL语句的执行计划 SQL语句的执行计划 表访问方式 全表扫描 散列获取 ROWID访问 SQL语句的优化器 优化器种类: 基于规则的优化器-RBO 基于成本的优化器-CBO 优化器模式: Rule Choose First_rows All_rows 基于规则的优化器-RBO RBO不使用任何表或索引的统计数据,而是用试探法来确定到达数据的最佳访问路径。RBO根据迭代过程来生成执行计划，并且检查from子句中的每个表，以及在该查询中每个表与其他表的所有连接方式。每一个可行的路径根据它们所要消耗的成本进行排序，并选择成本最低的路径。 RBO选择低成本的依据 01）ROWID读取 02）使用簇连接的单独记录 03）使用散列簇主键的单独记录 04）使用主键的单独记录 05）簇连接 06）散列簇主键 07）索引簇主键 08）复合主键 09）单字段索引 10）索引字段的结合范围查找 11）排序合并连接 12）索引字段上的MAX或MIN 13）索引字段上的ORDER BY 14）全表扫描 基于规则优化器的特性 总是使用索引。 总是从驱动表开始。 只有在不可避免的情况下，才使用全表扫描。 任何索引都可以 有时会选择一个并非最理想的索引来对查询服务。 基于规则的SQL语句优化 更改基于规则的驱动表。 如果基于规则的优化器没有使用正确的索引，可通过索引提示强制使用我们想要使用的索引，或者在索引中混入数据类型的方式禁用那些不想的查询中使用的索引。 基于成本的优化器-CBO 　　　Oracle基于成本的优化器的创建目的是为基于规则的优化提供更加复杂的替代方式。 　　　CBO 优化器需要了解表和索引统计数据的细节, SQL优化根据成本进行排序,并选择成本最低的路径。 表和索引的统计数据 表数据 记录的数目 物理数据块的数目 索引数据 索引中惟一值的数目 索引中值的分布 索引的可选择性 索引簇因素 全表扫描 索引访问 快速完全索引扫描 基于规则的优化器如果探测到 可用的索引，总是选择使用索引，否则使用全表扫描。 基于成本的优化器拥有有关表中数据的信息，当估计到全表扫描的成本低于索引访问的成本时，即使存在索引，它也选择全表扫描。 ALL_ROWS 优化器模式更倾向于全表扫描，更适用于批量处理的查询。 FIRST_ROWS 优化器模式确保以最快的速度返回记录。如果目标表中不存在可用索引，那么它将执行全表扫描。 使用NULL条件的查询 对没有索引字段的查询 带有like条件且掩码开始端使用%字