oracle rac lms,Oracle RAC 的监控和调整 (zt)

Select name,value from v$sysstat where name like ‘%global cache%’;

Global Cache Service Request Latency

在一个完整Block请求中计算平台延时的公式(百分之一秒)

Global Cache CR Block Receive Time

Local Cache CR Blocks Received

1、Global Cache Service请求的延时，构成一个完整读块的平均时间：

a = (global cache cr block build time) / (global cache cr blocks served)

2、等待Log Flush的平均时间

b = (global cache cr block flush time) / (global cache cr blocks served)

3、发送完整块的平均时间

c = (global cache cr block send time) / (global cache cr blocks served)

4、平均的lms服务时间

平均的延时 - a – b – c

5、监控当前的块的处理

当前的快的平均延时 = (global cache current block receive time / global cache current blocks received ) –( global cache current block pin time + global cache current block flush time + global cache current block send time ) / (global cache current blocks served)

如果值比较高，有可能在Buffers、cpu cycles、interconnect访问有可能竞争

6、监控block模式的转换

平均转换时间(毫秒) = 10 * (global cache convert time) / (global cache converts)

平均get时间(毫秒) = 10 * (global cache get time) / (global cache gets)

下面的统计计算了超时global cache convert timeouts，分析global enqueue统计

1. 收集global统计数据

a) 监控statspack ges统计部分

b) 查v$sysstat

2. 计算平均global enqueue时间

3. 计算平均global锁转换时间

4. 确定可能导致文件的资源类型

5. 使用v$librarycache 和v$rowcache视图来进行进一步的分析

select name, value from v$sysstat where name like ‘%global lock%’;

计算平均global enqueue时间(毫秒)

10 * (global lock get time) / (global lock sync gets + global lock async gets)

计算平均global lock convert时间(毫秒)

10 * (global lock convert time) / (global lock sync converts + global lock async converts)

select event,time_waited,average_wait from v$system_event order by time_waited desc;

select namespace, dlm_lock_request, dlm_pin,requests, dlm_pin_releasees, dlm_invalidation_requests, dlm_invalidations from v$librarycache;

select parameter, dlm_requests, dlm_conflicts, dlm_releasees from v$rowcache;

监控global enqueue 服务资源统计

1、加入统计

EVENT = ” 29700 TRACE NAME CONTEXT FOREVER”

2、查统计结果

v$ges_convert_local

v$ges_convert_remote

分析global enqueue服务资源统计

select r.convert_type, r.average_convert_time, l.average_convert_time, r.convert_count, l.convert_count from v$ges_convert_local l, v$ges_convert_remote r where r.convert_count <> 0 or l.convert_count <>0 group by r.convert_type;

其它的视图来查询资源活动

v$lock_activity

v$class_cache_transfer

v$cache_transfer

v$file_cache_transfer

V$system_evnet

事务的响应时间

response time/number of transaction = cpu time /number of transactions + wait time /number of transactions

RAC在v$system_evnet中的事件

Global cache cr request

Library cache pin

Buffer busy due to global cache

Global cache busy

Global cache open x

Global cache open s

Global cache null to x

Global cache s to x

Global cache null to s

调整实例之间性能的观察

select paramater, count, dlm_requests, dlm_conflicts, dlm_releasees from v$rowcache;

select namespace, dlm_lock_requests, dlm_pin_requests, dlm_pin_releases, dlm_invalidation_requests, dlm_invalidations from v$librarycache;

调优战略

收集基线数据

监控统计跟基线数据进行比较

只有在性能降低到不可以接受的水平才去调优

要监控跟cluster db无关的因素

address causes为更高水平的竞争

文章引用自：