OracleSQL语法和性能优化.doc

Oracle SQL的性能优化 2012-6-2 史服宇 目录 Oracle SQL的性能优化 1 1. 术语 1 2. 性能优化的策略和原则 2 3. 常见的诊断和性能优化工具 3 3.1. 操作系统参数的监控： 3 3.2. 数据库参数调整 3 3.3. 应用系统的通用优化工具 4 3.4. Sql的优化工具 5 4. SQL优化的基本原则 5 5. 一些有用的sql语句 8 6. 思考 9 术语 RBO和CBO：Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO)。 A、RBO方式优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。 B、CBO方式依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。 内存分配（SGA和PGA） SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。 性能优化的策略和原则 性能优化的策略和原则 Oracle数据库的性能优化策略一般包括服务器操作系统参数调整、数据库参数调整、网络性能调整、应用程序SQL语句分析及设计等几个方面，而应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。 优化调整主要集中在以下几个方面 设计调整—应用的设计是ORACLE性能中最重要的因素。 实例调整—调整PGA、SGA等初始化参数。 对象调整—对表及索引的存储参数如pctfree、pctuse、freelists等。这些参数能对性能参数比较大的影响。 SQL调整—因为对数据库的访问是通过SQL来实现的所以这部分的调整最花时间。 服务器、网络及磁盘调整—如果出现诸如CPU忙、内存交换频繁、磁盘I/O瓶颈等情况，我们需检查服务器、网络及磁盘配置。 一般性能优化的基本方法 设立合理的性能优化目标 我们做任何事情都会有个目标，没有了目标就会茫然，不知所措，性能优化也不例外。有了目标还是不够的，就像我们平时说的要优化系统，要优化到什么程度呢？所以说设立目标的最重要的一点是它们必须是可量化和可达到的。 测量并记录当前性能 利用相关工具进行量化 确定当前oracle的性能瓶颈（等待什么） 从Oracle v$system_event、v$session_event和v$session_wait中获得等待事件，进而找出影响性能的对象和sql语句 确定瓶颈原因 优化所需的部分（应用程序、数据库、i/o、争用、os、存储、网络等） 跟踪并实施更改过程 测量并记录当前性能 重复3-7，直到满足优化目标 常见的诊断和性能优化工具 操作系统参数的监控： 主要是监测OS的状况 （1）Windows 上的监控使用控制面板-〉管理工具-〉性能即可 （2）UNIX上的监控使用通用性的工具，包括sar、iostat、cpustat、mpstat、netstat、top、osview等。 打开enterprise manager console 查看数据库的众多参数，每个值的意思 打开工具菜单，里面有众多的工具. 如top sql Oracle SQL性能检测的命令和工具 用下列SQL工具找出低效SQL: SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND( ( BUFFER_GETS-DISK_READS)/ BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS0 AND BUFFER_GETS 0 AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS 0.8 ORDER BY 4 DESC; 使用TKPROF 工具来查询SQL性能状态 SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的