A doc from Metalink:
Preferred and easiest way of monitoring and setting pga_aggregate_target parameter (PGA) is section ‘PGA Memory Advisory’ in the AWR and Statspack reports.
PGA Memory Advisory
When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
PGA Target Est (MB)
Size Factr
W/A MB Processed
Estd Extra [...]
当会话等待redo log buffer 中的信息被写到磁盘的时候就会发生Log file sync waits 等待事件。
通常这是由LGWR的磁盘写入速度慢或者用户的频繁递交引起的。
下面是一些常用的减少log file sync waits 等待时间的方法：
1)调整LGWR以获得更好的磁盘写入吞吐量。
2)如果在短时间段内有很多事务，看有没有可能把这些事务分批处理以减少commit操作。
每个递交动作oracle都会首先去确认相关的redo log 信息以及被写到了磁盘。
3)看是不是有可能使用COMMIT NOWAIT 选项。在Oracle 10g R2中，Oracle增强了COMMIT命令，
增加了WRITE语句来控制redo log文件写入磁盘的方式以提高性能。这个新特性应该只是被用在以下情况：
(1)大事务量导致的redo log 写。
(2)在实例崩溃的时候，可以接受数据的丢失。
(3)处理的事务会引起严重的redo log writes 等待。
COMMIT语句的一些可用选项：
COMMIT;
COMMIT WRITE WAIT; –> The commit command is synchronous. It doesn’t return until the relevant redo information is written to the online redo log.
COMMIT WRITE NOWAIT; –> The commit command is asynchronous. [...]
NUM_LCPUS
Probably 0 because LPARs not set up
NUM_VCPUS
Same thing
AVG_BUSY_TIME
BUSY_TIME/NUM_CPUS
AVG_IDLE_TIME
IDLE_TIME/NUM_CPUS
AVG_IOWAIT_TIME
IOWAIT_TIME/NUM_CPUS
AVG_SYS_TIME
SYS_TIME/NUM_CPUS
AVG_USER_TIME
USER_TIME/NUM_CPUS
BUSY_TIME
Time equiv of %usr+%sys in sar output
IDLE_TIME
Time equiv of %idle in sar
IOWAIT_TIME
Time equiv of %wio in sar
SYS_TIME
Time equiv of %sys in sar
USER_TIME
Time equiv of %usr in sar
LOAD
OS_CPU_WAIT_TIME
Supposedly time waiting on run queues
RSRC_MGR_CPU_WAIT_TIME
Time waited coz of resource manager
PHYSICAL_MEMORY_BYTES
Total memory in use supposedly
NUM_CPUS
Number of CPUs reported by OS
NUM_CPU_CORES
Number [...]
Recent Comments