• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

i need free oracle statspack analysing tool .please give me a location

i need free oracle statspack analysing tool .please give me a location i have oracle 8i(8.1.7.4), oracle 9i( 9.0.2)
0
Roshanw4
Asked:
Roshanw4
  • 2
1 Solution
 
pettmansCommented:
The statspack utilities come with Oracle 8i and 9i and can be installed from
$ORACLE_HOME/rdbms/admin/spcreate.sql

Metalink (metalink.oracle.com) has some very useful scripts to assist with analysis. In particular http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT&p_id=1019592.6 (SCRIPT TO PROVIDE DATABASE PERFORMANCE HEALTHCHECK and RECOMMENDATIONS) is comprehensive providing analysis of most areas of interest, with explanation of the implications.

Metalink requires registration and you may also need a support contract number.

There are many GUI tools out there to visually present trends and analysis.  
http://www.dbainfopower.com/ provides Performance Explorer-I and while registration is required, I haven't been asked for any money. - note that I haven't attempted to use the tool as I generally use SQL scritps instead.

Other tools are not free but often include a handy evaluation period. For example, http://www.geocities.com/alexdabr/sppro.html provides a tool that is not free but does allow 30 days free evaluation.

Regards,
Scott Pettman
0
 
Roshanw4Author Commented:
Hi   pettmans


can you give me steps how to use the startspack which coming with oracle

$ORACLE_HOME/rdbms/admin/spcreate.sql


Roshan
0
 
pettmansCommented:
Roshan,

Step 1. Install statspack,
Oracle recommend installing the components into a separate tablespace (such as TOOLS).

SQLPLUS/nolog

connect / as sysdba (Or specify an username/password that has sysdba role)
CREATE TABLESPACE Tools
DATAFILE '/data/diskx/tools01.dbf' SIZE 100M
MINIMUM EXTENT 500K
DEFAULT STORAGE (
  INITIAL 500K
  NEXT 500K
  MINEXTENTS 1
  MAXEXTENTS 200
  PCTINCREASE 0 )
PERMANENT
ONLINE ;

I have arbitrarily specifed the size - I haven't checked the actual space required.

Then install statspack:
@%ORACLE_HOME%/rdbms/admin/spcreate.sql

You'll be prompted for:
Default TableSpace -- supply the name of the new tablespace you created
Temporary Tablespace - supply the name of your existing temporart tablespace.

2. Use statspack to generate some performance data.
Connect  perfstat/perfstat
execute statspack.snap

This will generate a snapshot of performance statistics at that time. Analysis of performance data will compare two snaphots. So you need to
a) establish some baseline snapshots - either find a time when you consider response is good/typical and create a snapshot at that time, or perhaps schedule some regular snapshots (eg hourly) to provide a profile of performance at particular times of the day.

b) For periods where performance problems are being detected, create more snaphots to capture what is going on, then:

3. Perform analysis.
This will usually involve comparing two snapshots, one from your baseline, and one from the period being investigated.

I have already provided you with a link to an excellent article in Metalink that provides some great scripts to analyse statspack data. Do you have access to metalink? If not I can look for some scripts elsewhere on the net. Gotta love Google.

Additionally, I can recommend Oracle's performance tuning course that forms part of their DBA stream of education products. I'm not sure there's any point in trying to tune Oracle unless you take some time to learn the physical implementation of the product as taught in a course such as that.

Regards,
Scott Pettman
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now