Re: Statspack for RAC

"DBA Infopower Support" <support_at_dbainfopower.com> wrote in message news:<bZ6dnYVs1-Q-Gbnd4p2dnA_at_comcast.com>...
> Hello Dias,
>
> Actually Oracle selects instance number internally:
>
> SQL> select VIEW_DEFINITION from SYS.V_$FIXED_VIEW_DEFINITION where
> VIEW_NAME = 'V$FILESTAT';
>

The fact that statspack is instance-number aware is not in doubt. The question here is 'why?'... what does statspack do with that information? Does it permit cluster-wide collection of statistics? Will a given single statspack report show the statistics for multiple instances? Or a cluster-wide aggregation of statistics? To which the answer is no.

From the Statspack doco: "Statspack uses the INSTANCE_NUMBER and the DBID to identify the instance's snapshot preferences, so you could have a different set of levels or thresholds being used when taking snapshots of an instance [because instance number can change following an instance re-start]."

Instance number is a configuration issue, therefore. It is used simply to identify the different statistics collected, separately, on each instance, and to set preferences as to the detail level of your statistics collection. It doesn't mean you can issue one cluster-wide 'execute statspack.snap', and pull out a cluster-wide report.

From the RAC Oracle Course Notes: "In a Real Application Clusters database, the utility [ie, Statspack] only collects and analyses information for individual instances, although much of the information can be used to examine overall cluster performance. You should use Statspack to collect baseline statistics from *each* instance [my emphasis]..."

So, for the original poster's question of 'will a statspack report include statistics from all instances or just one', the answer is just one. However, any statspack report includes statistics on segment behaviour, and since you are sharing segments between instances, those sorts of statistics are of relevance to cluster performance regardless of which instance you happen to run statspack on. But instance-specific statistics (latching, etc) are just that: specific to the instance you ran statspack on.

Regards
HJR Received on Sat Feb 07 2004 - 15:43:54 CST