TIP:  Click help for a detailed explanation of this page.

Bookmark

Go to End

Subject:	How To Integrate Statspack with EM 10G
	Doc ID:	274436.1	Type:	HOWTO
	Modified Date :	15-JUN-2008	Status:	MODERATED

In this Document
  Goal
  Solution
  References

@ (AuthWiz 2.5.3) Created from SR 3742743.996
@ Click here to edit in wizard.  

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Goal

Solution

To integrate the statspack with EM, some Enterprise Manager Packages have to be installed in the target databases.

The Enterprise Manager Packages include the following:
* Statspack: Top SQL Report metrics, Bad SQL metrics,
Setting Snapshot Collection Level, Setting Statspack Collection Level
* mgmt_response : SQL Response Time metrics
* mgmt_reuse : Duplicate SQL Report metrics

If you have not installed these packages, "not configured" status will
appear next to SQL Response, Top SQL Report, Bad SQL, or Duplicate SQL
under the 'Active Sessions' on the Database home page.

Steps to install these packages:

1. Login to the Grid Console.

2. Go to Targets -> Databases.

3. Choose the target database and click on 'Configure'.

4. This will take you to the 'Configure Database' wizard.
Review the information in the 'Properties' page and click on 'Next'.

5. In the 'Install Packages' page, choose the following option :
"Install database objects and configure the database to enable these metrics"

6. In the 'Credentials' page, specify the password of the 'SYS' user,
'Connect As' : SYSDBA and click on 'Next'.

7. In the 'Parameters' page, the statspack information is stored in the
PERFSTAT schema, created by this wizard.
You can change the 'Default Tablespace' and 'Temporary Tablespace',
for this user.
Enter a valid OS username and password for the ' Host Credentials' .

8. 'Review' the information and click on 'Submit'.

9. This will submit a job to the target database to create the perfstat user and corresponding views.
Click on the 'View job' and ensure that the job completes without any errors.

10. Once these packages are installed, the metrics - SQL Response Time,
Bad SQL, Top SQL Report
under the 'Active Sessions' on the Database home page
should not have 'Not configured' value.
If the "Unavailable" status appears next to the fields, it is because
the data has not yet been collected from the Management Agent;
either the collection has not been scheduled or there is a collection error.

Note:

- The EM job will create a PERFSTAT user in the target database, whose
   account is locked by default.
   Unlock the account and re-set the password.

- Once the database is configured, there is a DBMS_JOB executed every hour which collects a 
   database snapshot. This can be verified by querying the DBA_JOBS view under 
   the schema DBSNMP against the target 9.2.0.x database.

- To ensure that STATSPACK works fine in GC, wait for a couple of hours as 
   we need at least two snapshots to be collected to view historical data. Then, 
   go in GC -> Targets -> Database -> <Your 9.2.0.x Database> -> Performance 
   tab. Choose Top SQL and select Historical Data. As soon as you have more than 
   two snapshots collected, historical data should be visible.

- When monitoring 8.1.7.x and 9.2.0.x databases, GC is using STATSPACK. When 
   monitoring 10gR1 / 10gR2 databases, GC is using AWR (Automatic Workload 
   Repository). As such, the "Snapshots" link gets displayed in the 10G database 
   performance page.
   However, this displays AWR Snapshots and not STATSPACK snapshots. 
   "Snapshots" link will not be created in the 9.2.0.x database -> performance page ,
   as there is no AWR concept in 9.2.0.x RDBMS.

Note: For 10g databases, the Configure Database wizard's "Install Packages", "Credentials", and "Parameters" steps are not required. For this reason, the pages disabled. [From the online help]

For 8i, refer to Note 337556.1 : Unable To View Historical Data Information From 10g Grid
Control For 8.1.7.4 Db

References

Keywords