« Oracle Database 11g回滚段命名的变化 | Blog首页 | 2009回家过年及香港之行-纵横四海篇 »
使用spawrrac.sql收集RAC数据库对比信息
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2009/02/spawrrac.html
在Oracle Database 11g中，Oracle引入了一个新的脚本工具spawrrac.sql用于收集RAC环境下的数据库对比信息，在某些情况下可以清晰的展现RAC环境中的一些问题，值得了解一试。
这个脚本同样可以用于Oracle Database 10g，我在10.2.0.4中使用一切正常，其他版本请测试后使用。
该脚本的文件说明信息如下：
Rem $Header: spawrrac.sql 23-apr-2007.11:13:39 cgervasi Exp $
Rem
Rem spawrrac.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem spawrrac.sql - Server Performance AWR RAC report
Rem
Rem DESCRIPTION
Rem This scripts generates a global AWR report to report
Rem performance statistics on all nodes of a cluster.
Rem
Rem NOTES
Rem Usually run as SYSDBA
运行和使用awrrpt.sql脚本类似：
SQL> @?/rdbms/admin/spawrrac.sql
Instances in this AWR schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Instance
DB Id DB Name Count
----------- ------------ --------
4266683088 SMSDB 2
Enter value for dbid: 4266683088
Using 4266683088 for database Id
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 31 days of Completed Snapshots
Snap Instance
DB Name Snap Id End Interval Time Level Count
------------ --------- ----------------- ----- --------
SMSDB 9398 26 Jan 2009 00:00 1 2
......
SMSDB 9566 02 Feb 2009 00:00 1 2
9567 02 Feb 2009 01:00 1 2
9568 02 Feb 2009 02:00 1 2
9569 02 Feb 2009 03:00 1 2
9570 02 Feb 2009 04:00 1 2
9571 02 Feb 2009 05:00 1 2
9572 02 Feb 2009 06:00 1 2
9573 02 Feb 2009 07:00 1 2
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 9572
Begin Snapshot Id specified: 9572
Enter value for end_snap: 9573
End Snapshot Id specified: 9573
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is spawrrac_9572_9573. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name spawrrac_9572_9573
生成的报告摘要。
比如第一部分我们就能看到，实例2的繁忙程度要远远大于实例1：
OS Stat
~~~~~~~
Num Load Load
Inst # CPUs Begin End % Busy % Usr % Sys % Idl Busy Time (s) Idle Time (s) Total time (s)
------ ---- ------ ------ ------ ------ ------ ------ ------------- ------------- --------------
1 4 .3 .5 2.6 1.1 1.5 97.4 378.61 13,974.33 14,352.94
2 4 1.2 1.2 26.9 10.2 16.7 73.1 3,861.20 10,492.55 14,353.75
------------- ------------- --------------
sum 4,239.81 24,466.88 28,706.69
而根据RAC的GC Current Blocks Received和GC Current Blocks Served，也可以看出两个节点之间的不均衡，这个RAC环境需要检查：
SysStat and GE Misc - RAC
~~~~~~~~~~~~~~~~~~~~~~~~~
GC Current GC CR GC Current GC CR
Blocks Blocks Blocks Blocks GC Messages GE Messages Msgs Rcvd
I# Received Received Served Served Sent Sent Actual
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3,322 1,736 2,089,116 608 27,744 4,621 309,873
2 2,091,838 612 3,603 1,764 2,767,283 4,629 28,000
----------- ----------- ----------- ----------- ----------- ----------- -----------
sum 2,095,160 2,348 2,092,719 2,372 2,795,027 9,250 337,873
SysStat and GE Misc (per Sec) - RAC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
GC Current GC CR GC Current GC CR
Blocks Blocks Blocks Blocks GC Messages GE Messages Msgs Rcvd
I# Received/s Received/s Served/s Served/s Sent/s Sent/s Actual/s
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 .93 .48 581.91 .17 7.73 1.29 86.31
2 582.62 .17 1.00 .49 770.74 1.29 7.80
----------- ----------- ----------- ----------- ----------- ----------- -----------
sum 583.54 .65 582.92 .66 778.47 2.58 94.11
-The End-
历史上的今天...
>> 2008-02-02文章:
>> 2006-02-02文章:
>> 2005-02-02文章:
------
这篇 【使用spawrrac.sql收集RAC数据库对比信息】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2009-02-02 08:54 | Comments (4) | Posted to Statspack | Tools | Edit |
搜索本站:
留言 (4)
呵呵，eygle最近在研究11g？
Posted by: 小荷 at February 2, 2009 4:03 PM
在研究RAC，发现很多有意思的东西！
Posted by: eygle at February 2, 2009 4:36 PM
GC Current Blocks Received和GC Current Blocks Served
这两个字段 什么意思呢？
Posted by: wangliang at February 3, 2009 2:30 PM
GC Current Blocks Received和GC Current Blocks Served
你可以理解为一个是收到的，一个是发出的 Blocks
Posted by: eygle at February 4, 2009 9:54 AM