使用spawrrac.sql收集RAC数据库对比信息

« Oracle Database 11g回滚段命名的变化 | Blog首页 | 2009回家过年及香港之行-纵横四海篇 »

在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