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 b.name, 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 是一堆存放在你的
$ORACLE_HOME/rdbms/admin目录下的SQL脚本，他们对于捕获系统范围的数据库性能统计的快照非常有用。
UTLESTAT 创建这些视图的第二个快照，并将两个快照之间的差异报告到文件 'report.txt'中。 Utlbstat.sql 在你的
sys用户下创建一系列的表和视图，其中包括开始时数据库性能统计的快照。Utlesta.sql在你的sys用户下创建一系
列的表，其中包括结束时数据库性能统计的快照和一个叫 'report.txt'的文件。

使用方法：
o
确保你已经将TIMED_STATSTICS设为TRUE (这仅仅给数据库操作增加了一点非常小的开销)。

o
确保数据库在运行utlbstat前，已经启动并且运行了一段时间。

o
从svrmgrl 中而不是sql*plus中运行utbstat.sql和utlestat.sql。

o
确保在脚本utlbstat/estat运行时，数据库不被Down掉，否则产生的统计就不正确。

o
至少运行utlbstat/estat 1-3个小时，才好调试你的数据库。


Checkpoint Performance Tuning（检查点性能调整）：检查点（ Checkpoint）是一个数据库事件，用来同步内存和

磁盘上的数据文件中的数据块。检查点的目的有两个：
o
建立数据的一致性

o
是数据库恢复更快。

检查点调整方法如下：
o
如果LOG_CHECKPOINT_INTERVAL的值比重写日志（ redo log）的大小大，那么 checkpoint只在

ORACLE进行日志从一个组到另一组切换的时候才发生。这正是我们希望的。这个行为在 Oracle 8i中有
了变化。
o
当把LOG_CHECKPOINTS_TO_ALERT设为TRUE时，将把checkpoint启动和停止的时间记录在alert log

日志里。这对于你确定checkpoint是否正以最佳的频率发生很有帮助。
o
理想的情况是，checkpoint在仅在日志切换时发生。


Import Performance Tuning（数据载入性能调整）：没有什么固定的信息是关于如何加速那些慢得让人难以忍受的

import的。显然import要花费它完成所需要的时间，但是作一些事情可以缩短他们所花的时间。方法很简单，并
行操作