Oracle RAC AWR指标含义

RAC相关指标

Global Cache Load Profile

Per SecondPer Transaction

Global Cache blocks received:12.062.23

Global Cache blocks served:8.181.51

GCS/GES messages received:391.1972.37

GCS/GES messages sent:368.7668.22

DBWR Fusion writes:0.100.02

Estd Interconnect traffic (KB)310.31

指标指标说明

Global Cache blocks received通过硬件连接收到远程实例的数据块的数量。发生在一个进程请求一致性读一个数据块不是在本地缓存中。Oracle发送一个请求到另外的实例。一旦缓冲区收到，这个统计值就会增加。这个统计值是另两个统计值的和：Global Cache blocks received = gc current blocks received + gc cr blocks received

Global Cache blocks served通过硬件连接发送到远程实例的数据块的数量。这个统计值是另外两个统计值的和：Global Cache blocks served = gc current blocks served + gc cr blocks served

GCS/GES messages received通过硬件连接收到远程实例的消息的数量。这个统计值通常代表RAC服务引起的开销。这个统计值是另外两个统计值的和：GCS/GES messages received = gcs msgs received + ges msgs received

GCS/GES messages sent通过硬件连接发送到远程实例的消息的数量。这个统计值通常代表RAC服务引起的开销。这个统计值是另外两个统计值的和：GCS/GES messages sent = gcs messages sent + ges messages sent

DBWR Fusion writes这个统计值显示融合写入的次数。在RAC中，单实例Oracle数据库，数据块只被写入磁盘因为数据过期，缓冲替换或者发生检查点。当一个数据块在缓存中被替换因为数据过期或发生检查点但在另外的实例没有写入磁盘，Global Cache Service会请求实例将数据块写入磁盘。因此融合写入不包括在第一个实例中的额外写入磁盘。大量的融合写入表明一个持续的问题。实例产生的融合写入请求占总的写入请求的比率用于性能分析。高比率表明DB cache大小不合适或者检查点效率低。

Estd Interconnect traffic (KB)连接传输的KB大小。计算公式如下：Estd Interconnect traffic (KB) = ((‘gc cr blocks received’+ ‘gc current blocks received’ + ‘gc cr blocks

served’+ ‘gc current blocks served’) * Block size)

+ ((‘gcs messages sent’ + ‘ges messages sent’ + ‘gcs msgs received’+ ‘gcs msgs

received’)*200)/1024/Elapsed Time

Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access – local cache %:91.05

Buffer access – remote cache %:0.03

Buffer access – disk %:8.92

指标指标说明

Buffer access – local cache %数据块从本地缓存命中占会话总的数据库请求次数的比例。在OLTP应用中最希望的是尽可能维持这个比率较高，因为这是最低成本和最快速的获得数据库数据块的方法。计算公式：Local Cache Buffer Access Ratio = 1 – ( physical reads cache + Global Cache blocks received ) / Logical Reads

Buffer access – remote cache %数据块从远程实例缓存命中占会话总的数据块请求的比例。在OLTP应用中这个比率和Buffer access – local cache的和应该尽可能的高因为这两种方法访问数据库数据块是最快速最低成本的。这个比率的计算方法：Remote Cache Buffer Access Ratio = Global Cache blocks received / Logical Reads

Buffer access – disk %从磁盘上读数据块到缓存占会话总的数据块请求次数的比例。在OLTP应用中希望维持这个比例低因为物理读是最慢的访问数据库数据块的方式。这个比率计算方法：

1 – physical reads cache / Logical Reads

Global Cache and Enqueue Services – Workload Characteristics

Avg global enqueue get time (ms):0.0

Avg global cache cr block receive time (ms):0.3

Avg global cache current block receive time (ms):0.2

Avg global cache cr block build time (ms):0.0

Avg global cache cr block send time (ms):0.0

Global cache log flushes for cr blocks served %:1.2

Avg global cache cr block flush time (ms):1.8

Avg global cache current block pin time (ms):1,021.7

Avg global cache current block send time (ms):0.0

Global cache log flushes for current blocks served %:6.9

Avg global cache current block flush time (ms):0.9

本文永久地址http://www.askmaclean.com/archives/rac-awr-statistics.html

指标指标说明

Avg global enqueue get time (ms)通过interconnect发送消息，为争夺资源开启一个新的全局队列或者对已经开启的队列转换访问模式所花费的时间。如果大于20ms，你的系统可能会出现超时。

Avg global cache cr block receive time (ms)从请求实例发送消息到mastering instance（2-way get）和一些到holding instance (3-way get)花费的时间。这个时间包括在holding instance生成数据块一致性读映像的时间。CR数据块获取耗费的时间不应该大于15ms。

Avg global cache current block receive time (ms)从请求实例发送消息到mastering instance（2-way get）和一些到holding instance (3-way get)花费的时间。这个时间包括holding instance日志刷新花费的时间。Current Block获取耗费的时间不大于30ms

Avg global cache cr block build time (ms)CR数据块创建耗费的时间

Avg global cache cr block send time (ms)CR数据块发送耗费的时间

Global cache log flushes for cr blocks served %需要日志刷新的CR数据块占总的需要服务的CR数据块的比例。

Avg global cache cr block flush time (ms)CR数据块刷新耗费的时间

Avg global cache current block pin time (ms)Current数据块pin耗费的时间

Avg global cache current block send time (ms)Current数据块发送耗费的时间

Global cache log flushes for current blocks served %需要日志刷新的Current数据块占总的需要服务的Current数据块的比例

Avg global cache current block flush time (ms)Current数据块刷新耗费的时间

Global Cache and Enqueue Services – Messaging Statistics

Avg message sent queue time (ms):2,367.6

Avg message sent queue time on ksxp (ms):0.1

Avg message received queue time (ms):0.3

Avg GCS message process time (ms):0.0

Avg GES message process time (ms):0.0

% of direct sent messages:54.00

% of indirect sent messages:44.96

% of flow controlled messages:1.03

指标指标说明

Avg message sent queue time (ms)一条信息进入队列到发送它的时间

Avg message sent queue time on ksxp (ms)对端收到该信息并返回ACK的时间，这个指标很重要，直接反应了网络延迟，一般小于1ms

Avg message received queue time (ms)一条信息进入队列到收到它的时间

Avg GCS message process time (ms)

Avg GES message process time (ms)

% of direct sent messages直接发送信息占的比率

% of indirect sent messages间接发送信息占的比率，一般是排序或大的信息，流控制也可能引起

% of flow controlled messages流控制信息占的比率，流控制最常见的原因是网络状况不佳， % of flow

controlled messages应当小于1%

Wait Event Histogram

% of Waits

EventTotal Waits<1ms<2ms<4ms<8ms<16ms<32ms<=1s>1s

ADR block file read20838.03.444.713.9

ADR block file write40100.0

ADR file lock48100.0

ARCH wait for archivelog lock3100.0

ASM file metadata operation12.8K99.7.1.0.0.0.2.0

Backup: MML write backup piece310.5K7.6.1.11.310.430.250.2.0

CGS wait for IPC msg141.7K100.0

CSS initialization3450.047.12.9

CSS operation: action11048.220.928.22.7

CSS operation: query10288.23.97.8

DFS lock handle660793.9.5.2.0.05.3.0

Disk file operations I/O1474100.0

IPC send completion sync21.9K99.5.1.1.1.0.2

KJC: Wait for msg sends to complete13100.0

LGWR wait for redo copy16.3K100.0.0

Log archive I/O333.366.7

PX Deq: Signal ACK EXT225699.8.1.1

PX Deq: Signal ACK RSG212499.9.1.0

PX Deq: Slave Session Stats799794.6.9.92.5.8.4

PX Deq: Table Q qref235599.9.1

PX Deq: reap credit1215.7K100.0.0.0

PX qref latch1366100.0

Parameter File I/O19494.81.01.01.01.5.5

Wait Event Histogram：等待时间直方图

Event：等待事件名字

Total Waits：该等待事件在快照时间内等待的次数

%of Waits < 1ms ：小于1ms的等待次数

%of Waits < 2ms ：小于2ms的等待次数

%of Waits < 4ms ：小于4ms的等待次数

%of Waits < 8ms ：小于8ms的等待次数

%of Waits < 16ms ：小于16ms的等待次数

%of Waits < 32ms ：小于32ms的等待次数

%of Waits < =1s ：小于等于1s的等待次数

%of Waits > 1s ：大于1s的等待次数

Parent Latch Statistics

only latches with sleeps are shown

ordered by name

Latch NameGet RequestsMissesSleepsSpin & Sleeps 1->3+

Real-time plan statistics latch77,84013620116/0/0/0

active checkpoint queue latch321,02320,5287720451/0/0/0

active service list339,641546132424/0/0/0

call allocation328,283550148440/0/0/0

enqueues1,503,52521714203/0/0/0

ksuosstats global area2,605110/0/0/0

messages2,608,863141,38029141351/0/0/0

name-service request queue155,047431528/0/0/0

qmn task queue latch2,368907812/0/0/0

query server process26830300/0/0/0

redo writing910,70311,6235011573/0/0/0

resmgr:free threads list14,4541904186/0/0/0

space background task latch11,2091578/0/0/0

Latch Name：闩名称

Get Requests：申请获得父闩的次数

本文永久地址http://www.askmaclean.com/archives/rac-awr-statistics.html

Child Latch Statistics

only latches with sleeps/gets > 1/100000 are shown

ordered by name, gets desc

Latch NameChild NumGet RequestsMissesSleepsSpin & Sleeps 1->3+

KJC message pool free list196,136822062/0/0/0

Lsod array latch102,22215311858/0/0/0

Lsod array latch132,151431429/0/0/0

Lsod array latch42,06615412459/0/0/0

Lsod array latch51,9881054463/0/0/0

Lsod array latch91,734953264/0/0/0

Lsod array latch21,707883855/0/0/0

Lsod array latch111,695883257/0/0/0

Lsod array latch61,68015812664/0/0/0

Lsod array latch121,65715511165/0/0/0

Lsod array latch71,640903459/0/0/0

Lsod array latch11,62716915346/0/0/0

Lsod array latch31,555873654/0/0/0

Lsod array latch81,4871278857/0/0/0

cache buffers chains47418354,3133914387/0/0/0

cache buffers chains8031337,1352508242/0/0/0

cache buffers chains78358305,0225289519/0/0/0

cache buffers chains6927241,8081294125/0/0/0

Latch Name：闩名称

Child Num：

Get Requests：

Misses：

Sleeps：

Spin&Sleeps 1->3+：

Dictionary Cache Stats (RAC)

CacheGES RequestsGES ConflictsGES Releases

dc_awr_control1150

dc_global_oids500

dc_histogram_defs2151707

dc_objects9090

dc_segments791073

dc_sequences35,738370

dc_table_scns600

dc_tablespace_quotas907770

dc_users1000

outstanding_alerts5762880

Cache：字典缓存类名

GES Requests：

GES Conflicts：

GES Releases：

Library Cache Activity (RAC)

NamespaceGES Lock RequestsGES Pin RequestsGES Pin ReleasesGES Inval RequestsGES Invali- dations

ACCOUNT_STATUS2420000

BODY01,530,0131,530,01300

CLUSTER74747400

DBLINK2460000

EDITION31131131100

HINTSET OBJECT18618618600

INDEX152,360152,360152,36000

QUEUE2239,7179,71700

SCHEMA2550000

SUBSCRIPTION0262600

TABLE/PROCEDURE275,2153,023,0833,023,08300

TRIGGER0384,493384,49300

Namespace：library cache 的命名空间

GES Lock Requests：

GES Pin Requests：

GES Inval Requests：

GES Invali-dations：

Interconnect Ping Latency Stats

Ping latency of the roundtrip of a message from this instance to

target instances.

The target instance is identified by an instance number.

Average and standard deviation of ping latency is given in miliseconds

for message sizes of 500 bytes and 8K.

Note that latency of a message from the instance to itself is used as

control, since message latency can include wait for CPU

Target Instance500B Ping CountAvg Latency 500B msgStddev 500B msg8K Ping CountAvg Latency 8K msgStddev 8K msg

11,1380.200.031,1380.200.03

21,1380.170.041,1380.200.05

31,1380.190.221,1380.230.22

41,1380.180.041,1380.210.04

Target Instance：目标实例

500B Ping Count：

Avg Latency 500B msg：

Stddev 500B msg：

8K Ping Count：

Avg Latency 8K msg：

Stddev 8K msg：

Interconnect Throughput by Client

Throughput of interconnect usage by major consumers

All throughput numbers are megabytes per second

Used BySend Mbytes/secReceive Mbytes/sec

Global Cache0.100.20

Parallel Query0.020.06

DB Locks0.090.09

DB Streams0.000.00

Other0.020.01

Used By：主要消费者

Send Mbytes/sec：发送Mb/每秒

Receive Mbytes/sec：接收Mb/每秒

Interconnect Device Statistics

Throughput and errors of interconnect devices (at OS level)

All throughput numbers are megabytes per second

Device NameIP AddressPublicSourceSend Mbytes/secSend ErrorsSend DroppedSend Buffer OverrunSend Carrier LostReceive Mbytes/secReceive ErrorsReceive DroppedReceive Buffer OverrunReceive Frame Errors

bondib0192.168.10.8NOcluster_interconnects parameter0.0000000.00000

Device Name：设备名称

IP Address：IP地址

Public：是否为公用网络

Source：来源

Send Mbytes/sec：发送MB/每秒

Send Errors：发送错误

Send Dropped：

Send Buffer Overrun：

Send Carrier Lost：

Receive Mbytes/sec：

Receive Errors：

Receive Dropped：

Receive Buffer Overrun：

Receive Frame Errors：

Dynamic Remastering Stats

times are in seconds

Affinity objects – objects mastered due to affinity at begin/end snap

NameTotalper Remaster OpBegin SnapEnd Snap

remaster ops291.00

remastered objects401.38

replayed locks received1,99068.62

replayed locks sent87730.24

resources cleaned00.00

remaster time (s)5.00.17

quiesce time (s)1.70.06

freeze time (s)0.60.02

cleanup time (s)0.70.02

replay time (s)0.20.01

fixwrite time (s)1.30.04

sync time (s)0.50.02

affinity objects365367