Oracle系统参数调整和优化原则(精)

Oracle 系统参数调整和优化原则

(来自时代朝阳数据库技术中心)

粗略来讲，系统调整一般反映在下列方面：

∙Shared Pool and Library Cache Performance Tuning(共享池和Library Cache)：Oracle将SQL语句、存储包、对象信息和很多其他的项目保存在SGA中一个叫共享池（shared pool）的地方。这个可共享的区域由一个成熟的高速缓存和堆管理器管理。它有3个基本的问题要克服：

o内存分配的单元不是个常量。从池中分配的内存单元可能是从几个字节到几千个字节。

o在用户完成工作时，不是所有的内存都能够释放出来，因为共享池的目标是使信息最大程度的共享。

o没有一个象其他常规的高速缓存的文件做后备的存储那样磁盘空间供整页的导出。

只有可重新创建的信息可以从Cache中丢弃，在他被再次需要的时候再重新创建。

共享池调整的技巧有：

o刷（Flush）共享池可以使小块的内存合并为大块的内存。当共享池的碎片过多时，这能够暂时恢复性能。

刷共享池可以使用语句：alter system flush shared_pool;

o注意执行这个语句将会造成性能的暂时尖峰，因为对象都要重新加载。所以应当在数据库的负载不是很大的情况下进行。

o确保联机事务处理（OLTP）应用使用绑定变量（bind variables）. 这一点对于决策支持系统（DSS）并不重要。

o确保library cache 的命中率> 95%

o增大共享池并不总能解决命中率过多的问题。

∙Buffer Cache Performance Tuning(数据库缓存调整)：数据库缓存保持了从磁盘上读去的数据块的备份。因为缓存通常受到内存约束的限制，不是磁盘上所有的数据都可以放到缓存里。当缓存满了的时候，后来的缓存不中使得Oracle 将已经在缓存中的数据写到磁盘上。后续的对写到磁盘上的数据的访问还会造成缓存不中。

从缓存调整的角度看，应力求避免以下的问题：

o'缓存的最近最少使用(LRN)链'（'cache buffers LRU chain' ）的加锁竞争

o'平均写队列'（"Average Write Queue" ）长度过大

o过多时间花在等待‘写完毕等待上’（"write complete waits" ）

o过多时间花在等待‘缓冲释放等待’上（"free buffer waits" ）

∙Latch Contention加锁（插销）竞争：插销加锁是SGA中保护共享数据结构的低层的串行化机制。插销latch是一类可以非常快的获得和释放的锁。插销锁的实现是依赖于操作系统的，尤其在关于一个进程是否会等待一个锁，和等多久方面。

有如下的锁（插销）需要调整：

o Redo Copy/Allocation Latch：重写日志的复制/分配插销

o Shared Pool Latch：共享池的插销

o Library Cache Latch：Library Cache插销

∙Redo Log Buffer Performance Tuning（重写日志缓冲的调整）：LGWR 将重写日志缓冲中的重写项写到重写日志文件中。一旦LGWR将这些项复制到重写日志文件中，用户进程就可以重写这些项。统计项目"redo log space requests"

反映了用户进程等待重写日志缓冲中空间的时间的数字。

设置重写日志大小的一些提示：

o"redo log space requests"的值应该接近0。

o设定合适的重写日志的大小，建议每15-30分钟进行一次重写日志的切换。

∙Query Performance Tuning（查询效率的调整）：如果查询运行得很慢，请考虑这些方面：

o你希望这个查询运行的有多快以及有理由这样要求吗？

o优化模式OPTIMIZER_MODE 设为何值？

o查询涉及的索引都是有效的吗？

o在数据库中有没有其他的长时间运行的查询（大查询）

o对于CBO(基于成本的优化)In case of CBO:

o表和索引上有统计信息吗？

o统计信息是被计算出来的还是被估计出来的？

o对于查询的性能调整由两个主要的调试工具：

▪TKPROF

▪AUTOTRACE

∙Rollback Segment Performance Tuning（回滚段的调整）：Oracle数据库提供了任何数据库对象上的SELECT, INSERT, UPDATE, 和DELETE 操作的读一致性。回滚段用于保存由那些要回滚的动作或系统需要产生一个和前面某一时间读一致的影像所产生的可取消事务。

设置回滚段大小的技巧如下：

o建议最少每4个事务一个回滚段

o建议为长时间运行的大查询提供一个大回滚段。

o v$rollstat中的wrap数接近0。否则增大扩展大小（extent size）。

SELECT , a.wraps FROM V$ROLLSTAT a, V$ROLLNAME b;

∙n Temporary Tablespace Performance Tuning（临时表空间的调整）：从RDBMS release 7.3开始，Oracle引入了临时表空间的概念。这个表空间用于保存临时对象，如排序段。排序段采用它所在的表空间的缺省存储参数

（DEFAULT STORAGE (NEXT) 子句）作为自己的存储参数。

临时表空间的调整的技巧如下:

o如果即使在稳定的状态下也存在很多的排序扩展锁（Sort Extent Pool latch）的竞争，你应该通过修改临时表空间的DEFAULT STORAGE 子句的NEXT值来增大扩展块的大小。

o如果存在很多的排序扩展锁（Sort Extent Pool latch）的竞争并且这种等待是由于过多的并发的排序造成的，你应该增大SORT_AREA_SIZE参数的大小，以使更多的排序能保存在内存中。

o建议让扩展块的大小和SORT_AREA_SIZE参数相同。

∙Utlbstat/Utlestat Performance Tuning（Utlbstat/Utlestat调整）：Utlbstat/Utlestatt 是一堆存放在你的