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)
Roshanw4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.