How to Read PGA Memory Advisory Section in AWR and Statspack Reports

作者: David, 于 August 29th,2009

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 [...]

August 29th, 2009 | Category: Oracle RDBMS | 2 comments

How to Tune log File Sync？

作者: David, 于 August 29th,2009

当会话等待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. [...]

August 29th, 2009 | Tags: | Category: Oracle RDBMS | One comment

What do OS stats section of AWR report really tell us?

作者: David, 于 August 29th,2009

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 [...]

August 29th, 2009 | Category: Oracle RDBMS | 留言