sql优化经验总结V1.5.ppt

ORACLE SQL优化经验交流 系统支撑部 董建忠 第一章:概述 数据库优化的几个环节 什么样的SQL需要优化 常见的问题 发现问题的方法 数据库优化的几个环节 物理存储IO均衡 数据库规划合理（参数、表空间分布） 数据表和索引的设计 SQL语句的优化 什么样的SQL需要优化 引发严重的等待事件（IO、锁） 消耗大量的系统资源（CPU/IO/MEM） 运行时间超长（排序段、回滚段占用太大） 不能满足压力测试指标 常见的问题 没有恰当的索引（全表扫描问题） 索引不适当（索引低效、无用） 重编译问题（程序开发问题） 多表关联条件不当或关联太多 分区表没有分析，未能使用索引（维护问题） 死锁（应用逻辑问题） 发现问题的方法 从v$session_wait查看等待事件 SQLPLUS使用AUTOTRACE查看执行计划 在TOAD中直接查看执行计划 从STATSPACK查看资源(CPU、I/O)消耗状况 生成SESSION TRACE文件（一般为DBA使用） 用命令tkprof对TRACE文件进行分析 第二章： 从等待事件中发现问题 查看SESSION WAIT的语句 典型事件： Db File Sequential Read 典型事件： Db File Sequential Read 典型事件： Latch Free（latch 释放） 查看SESSION WAIT 的语句 set pagesize 2000 set linesize 110 col event format a25 col program format a20 select a.event,substr(gram,1,20) program ,b.sid,a.p1,a.p2,a.p3 from gv$session_wait a,v$session b where a.sid=b.sid and a.event not like %SQL% and a.event not like %message% and a.event not like %time% 典型事件：Db File Scattered Read 数据文件分散读取 这种情况通常显示与全表扫描相关的等待。 一般表明该表找不到索引，或者只能找到有限的索引。 特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。 建议将小而常用的表CACHE到内存中，以避免一次又一次地重复读取它们 典型事件： Db File Sequential Read数据文件顺序读取 这一事件通常显示单个块的读取(如索引读取) 表示表的连接顺序不佳，或者使用了不恰当的索引 检查每个扫描是否必要的，并检查多表连接的连接顺序 一般会消耗大量PGA内存，从而在顺序读取时导致大量等待。 典型事件： Latch Free（latch 释放） latch 是一种低级排队机制，用于保护系统全局区域（SGA）中共享内存结构。latch 就像是一种快速地被获取和释放的内存锁。latch 用于防止共享内存结构被多个用户同时访问。如果latch 不可用，就会记录latch 释放失败。 大多数latch 问题都与以下操作相关：不能使用绑定变量（库缓存latch）、重复生成问题（重复分配latch）、缓冲存储器竞争问题（缓冲器存储LRU 链），以及缓冲存储器中的“热”块（缓冲存储器链）。也有一些latch 等待与bug（程序错误）有关 当latch不命中率大于0.5%时，就应当研究这一问题 第三章：SQL语句的执行计划 SQL语句的执行步骤 ORACLE的优化器 在SQLPLUS 配置AUTOTRACE 使用QUEST TOAD 查看执行计划 安装AUTOTRACE环境 使用QUEST TOAD 查看执行计划 查看执行计划 SQL 语句的执行步骤 语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的意义。 语义分析 ，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。 表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。 选择优化器，不同的优化器一般产生不同的“执行计划” 选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。 选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。 选择数据的搜索路径， 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。 运行“执行计划” ORACLE