Oracle 数据库性能调优的一些经验与体会.doc

Oracle 数据库性能调优的一些经验与体会 摘要 本文主要介绍在HP8500服务器上实现Oracle 9i数据库管理工作中性能调优的方法与系统参数调整的原则。关键字 共享池，缓存，栓锁，重写日志，回滚段，临时表空间，检查点，I/O 从事数据库系统维护工作已经有一段时间了，多少有一些工作工作经验，想与大家作个交流。数据库性能调节与优化是DBA（数据库管理员）的一项重要工作。DBA如何才能把这项工作做好哪？首先，购买功能和性能更强的硬件是性能问题的一种解决办法，我们把Oracle 数据库安装运行于HP8500服务器，应用服务器采用HP ML570。但是，硬件往往非常昂贵，且随着软件的升级和复杂化，数据的增加，再好的硬件也会过时。DBA 必须最好地利用现有的硬件资源，想其他办法提高系统的性能。 一 Oracle 数据库性能调优方法Oracle 给出了DBA 应当采用的性能调节的5大步骤。一般而言，在所有的情况下，都应从第1步开始，以避免在解决问题的过程中又产生新的问题。在此同时，也应注意到，随着步骤的深入，调节所影响的范围和深度也在加大。下面是调节的步骤：1. 优化设计：调节系统结构设计和应用系统的设计。2. 优化应用程序：在很多情况下，写的很差的查询语句是性能问题的根源。DBA 应当在 鼓励开发人员调整 SQL 查询语句的性能。 （前两个步骤通常是系统体系结构设计者和应用程序开发人员的责任；但是，DBA也可能参与应用程序的优化。）3.优化内存结构。在应用程序调节后，恰当的配置和调节内存结构会对应用和数据库有极 大的性能影响。Oracle 应当有足够的空间分配给 SQL/PLSQL、数据字典缓冲区、数据缓冲区、重做日志缓冲区以获取高的性能。这些体现在以下几个方面： A对已存在于内存中的数据库数据更快的查询。 B减少 RDBMS 对 SQL 不必要的分析。 C消除操作系统的页面交换，特别是将SGA交换到磁盘。 4. 优化I/O：Oracle 设计成防止 I/O 负面地影响应用系统的性能。Oracle 服务器、DBWR、LGWR、CKPT 以及 SMON 的特性均可对磁盘的使用进行有效的管理，并设计成减少应用对磁盘快速写的依赖来提高性能。调节磁盘使用一般意味着将 I/O 分布到多个磁盘上以避免竞争、将数据存储在块上以方便读取、以及产生恰当尺寸的盘区来存储数据。5. 优化资源竞争：与调节磁盘I/O使用一样，Oracle 设计成尽量减少资源竞争。例如，Oracle 可以检测和消除死锁。然而，有很多情况用户会竞争使用资源，例如回退段、信使（dispatchers）、重做日志缓冲区拴（latches）等等。虽然不常出现，但这些竟争会对应用的性能极其不利。6. 优化操作系统：Oracle 在操作系统之上工作。操作系统性能的好坏会直接影响到 Oracle。即使 Oracle 本身已经调节的很好，很差的操作系统设置会令数据库的性能表现的很差。上面是在性能出现问题的情况下进行调节的6个步骤。即使一切运转良好，DBA 也应预调节数据库，这样做的好处是可以减少遇到问题时的调整时间。二、Oracle 系统参数调整和优化原则粗略来讲，系统调整一般包括下面几个方面：Shared Pool and Library Cache Performance Tuning(共享池和Library Cache)：Oracle将SQL语句、存储包、对象信息和很多其他的项目保存在SGA中一个叫共享池（shared pool）的地方。它有3个基本的问题要克服： 1）内存分配的单元不是个常量。从池中分配的内存单元可能是从几个字节到几千个字节。 在用户完成工作时，不是所有的内存都能够释放出来，因为共享池的目标是使信息最大程度的共享。 2）没有一个象其他常规的高速缓存的文件做后备的存储那样磁盘空间供整页的导出。 3）只有可重新创建的信息可以从Cache中丢弃，在他被再次需要的时候再重新创建。共享池调整的技巧有：1）当共享池的碎片过多时，刷共享池可以使小块的内存合并为大块的内存, 这能够暂时恢复性能。刷共享池可以使用语句：alter system flush shared_pool; 注意执行这个语句将会造成性能的暂时尖峰，因为对象都要重新加载。所以应当在数据库的负载不是很大的情况下进行。 2）确保联机事务处理（ OLTP）应用使用绑定变量 （bind variables）.3）确保library cache 的命中率 95% 参数调节： 调节shared_pool_size相应地调节了库缓存和数据字典缓存的大小。可通过查询视图V$SGASTAT来监控共享缓冲池的使用。查询视图V$LIBRATYCACHE可得出Reloads_to_Pins的比率，如果此比率大于1%，增加shared_pool_size的大小。 SHARED_POOL_RESERVED_SIZE: 为大需求保留的空闲空间。当shared_pool产生大量碎片，oracle为当前需求寻找shared_pool时，它避免了性能的降低。Buffer Cache Performance Tuning(数据库缓存调整)：数据库缓存保持了从磁盘上读去的数据块的备份。因为缓存通常受到内存约束的限制，不是磁盘上所有的数据都可以放到缓存里。从缓存调整的角度看，应力求避免以下的问题：缓存的最近最少使用(LRU)链（cache buffers LRU chain ）的加锁竞争 平均写队列（Average Write Queue ）长度过大 过多时间花在等待写完毕等待上（write complete waits ） 过多时间花在等待缓冲释放等待上 （free buffer waits ） 参数调节：数据缓存的大小 = db_block_size*db_block_buffers查询视图V$SYSSTAT可得出数据缓存的命中率，如果命中率低于90%，增加db_block_buffers的大小。Latch Contention加锁（插销）竞争：插销加锁是SGA中保护共享数据结构的低层的串行化机制。插销latch是一类可以非常快的获得和释放的锁。插销锁的实现是依赖于操作系统的，尤其在关于一个进程是否会等待一个锁，和等多久方面。 有如下的锁（插销）需要调整： Redo Copy/Allocation Latch：重写日志的复制/分配插销 Shared Pool Latch：共享池的插销 Library Cache Latch：Library Cache插销 Redo Log Buffer Performance Tuning（重写日志缓冲的调整）：LGWR 将重写日志缓冲中的重写项写到重写日志文件中。一旦LGWR将这些项复制到重写日志文件中，用户进程就可以重写这些项。统计项目redo log space requests反映了用户进程等待重写日志缓冲中空间的时间的数字。 设置重写日志大小的一些提示： 1.) redo log space requests的值应该接近0。 2.) 设定合适的重写日志的大小，建议每15-30分钟进行一次重写日志的切换。 3.) Query Performance Tuning（查询效率的调整）：如果查询运行得很慢，请考虑这些方面： 你希望这个查询运行的有多快以及有理由这样要求吗？ 优化模式OPTIMIZER_MODE 设为何值？ 查询涉及的索引都是有效的吗？ 在数据库中有没有其他的长时间运行的查询（大查询） 统计信息是被计算出来的还是被估计出来的？ 对于查询的性能调整由两个主要的调试工具： TKPROF AUTOTRACE 如果系统的事务处理比较繁忙，建议log_buffer取64k或以上。查询视图V$SYSSTAT，如果redo buffer allocation retries/redo entries 1%，则增加log_buffer的大小。Rollback Segment Performance Tuning（回滚段的调整）：Oracle数据库提供了任何数据库对象上的SELECT, INSERT, UPDATE, 和DELETE 操作的读一致性。回滚段用于保存由那些要回滚的动作或系统需要产生一个和前面某一时间读一致的影像所产生的可取消事务。 设置回滚段大小的技巧如下：建议最少每4个事务一个回滚段 建议为长时间运行的大查询提供一个大回滚段。 v$rollstat中的wrap数接近0。否则增大扩展大小（extent size）。 SELECT , a.wraps FROM V$ROLLSTAT a, V$ROLLNAME b;参数调节： TRANSACTIONS此参数说明了oracle 服务器允许的最大并发事务数。TRANSACTIONS_PER_ROLLBACK_SEGMENT此参数说明了每一回滚段允许的最大并发事务数。Transactions/transactions_per_rollback_segment的结果取整，即为此数据库服务器所需的回滚段数。Temporary Tablespace Performance Tuning（临时表空间的调整）：临时表空间用于保存临时对象，如排序段。排序段采用它所在的表空间的缺省存储参数（DEFAULT STORAGE (NEXT) 子句）作为自己的存储参数。 临时表空间的调整的技巧如下:如果即使在稳定的状态下也存在很多的排序扩展锁（Sort Extent Pool latch）的竞争，你应该通过修改临时表空间的DEFAULT STORAGE 子句的NEXT值来增大扩展块的大小。 如果存在很多的排序扩展锁（Sort Extent Pool latch）的竞争并且这种等待是由于过多的并发的排序造成的，你应该增大SORT_AREA_SIZE参数的大小，以使更多的排序能保存在内存中。 建议让扩展块的大小和SORT_AREA_SIZE参数相同。 Checkpoint Performance Tuning（检查点性能调整）：检查点（ Checkpoint）是一个数据库事件，用来同步内存和磁盘上的数据文件中的数据块。检查点的目的有两个： 建立数据的一致性 使数据库恢复更快。 检查点调整方法如下： 如果LOG_CHECKPOINT_INTERVAL的值比重写日志（ redo log）的大小大，那么 checkpoint只在ORACLE进行日志从一个组到另一组切换的时候才发生。这正是我们希望的。这个行为在 Oracle 8i中有了变化。 当把LOG_CHECKPOINTS_TO_ALERT设为TRUE时，将把checkpoint启动和停止的时间记录在alert log日志里。这对于你确定checkpoint是否正以最佳的频率发生很有帮助。 理想的情况是，checkpoint在仅在日志切换时发生。 I/O的调整:DB_FILE_MULTIBLOCK_READ_COUNT( Default value: 8 )此参数表示在全表扫描中，每一