Installing and Using Standby Statspack in 11g (by active Data Guard)

Statspack cannot be executed on a standby Database due to its read-only nature therefore tuning the performance of the apply process involves manually collecting statistics.

In the new Active Data Guard option, users can now use statspack from the primary database to collect data from a standby database that is opened read-only and performing recovery. The standby statspack is installed in a separate schema on the Primary database, STDBYPERF which is then propagated to the standby. This user "STDBYPERF" user does not have DBA privileges and has no access to local V$ tables.

Installing and Using Standby
Statspack in 11gR1 

1. Standby Statspack Usage

1.1 Statspack Installation

The perfstat schema and statspack related objects must be present on the primary and standby prior to installing standby statspack. The standby statspack installation script (sbcreate.sql) creates the standby statspack schema to hold the standby snapshots. The script asks for:

1. A password for stdbyperf user
2. Default tablespace
3. Temporary tablespace

The script creates the ‘stdbyperf’ user and assigns it the selected default and temporary table space. After the user has been created it calls sbaddins.sql to add the first standby instance to the configuration.

1.2 Add an Instance to Statspack Configuration

Log in to the primary as the 'stdbyperf' user and run the script sbaddins.sql to add a standby instance to the configuration. The script asks for:

1. The TNS alias of the standby database instance
2. The password of the perfstat user on the standby site

The script then creates a private database link to the perfstat schema on the standby site and a separate gathering package for each standby instance. Example:

connect stdbyperf/your_password 
@sbaddins
Input inst2_alias as the tns alias

1.3 Collect Performance Data from a Standby Instance

The script sbaddins.sql creates a separate PL SQL package for each standby instance. Run the snap procedure of those packages to collect the performance data. The statspack_<instance_name>.snap procedure accesses the data dictionary and stats$ views on the standby database via database link connected to the original perfstat user und stores the data to stats$ tables on the primary instance. For example, while the standby is opened read only, login to the primary database and create the snap:

connect stdbyperf/your_password
exec statspack_.snap

1.4 Generate Standby Statistics Report

The script sbreport.sql generates the standby statistics report. The script asks for: database id, instance number, high and low snapshots id to create the report. Example:

sql> @sbreport

1.5 Purge a Set of Snapshots

The script sbpurge.sql purges a set of snapshots. The script asks for database id, instance number, low and high snapshots ids. The script purges all snapshots between the low and high snapshot ids for the given instance. Example:

@sbreport

1.6 Delete an Instance from the Configuration

The script sbdelins.sql deletes an instance from the configuration, and deletes the associated PL SQL package. The scripts asks for instance name. The snapshots are not automatically purged when the instance is deleted. After deleting the instance, you are not able to generate reports for that instance. Example:

@sbdelins

1.7 Drop Statspack Schema

The script sbdrop.sql drops the stdbyperf user and tables. The script must be run when connected to SYS (or internal). Example:

connect / as sysdba
@sbdrop.sql 

Documentation :
Metalink  "Installing and Using Standby Statspack in 11gR1" note 454848.1