Skip to Main Content
  • Questions
  • Performance tunining in 10g using STATSPACK utility

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Krish.

Asked: October 26, 2008 - 5:27 am UTC

Answered by: Tom Kyte - Last updated: October 27, 2008 - 8:50 pm UTC

Category: Database - Version: 10.2.0

Viewed 1000+ times

You Asked

Hi tom,
i'm tried to install statspack utility in 10gdatabase by running the following scripts.

@ORACLE_HOME/rdbms/admin/spcreate.sql

installed STATSPACK in sysaux tablespace. and dynamic performance views are created in perfstat user schema. PERFSTAT users default tablespace is SYSAUX TS.

@ORACLE_HOME/rdbms/admin/spauto.sql for taking a snapshot
and at the end to view the report

@ORACLE_HOME/rdbms/admin/spreport.sql

Now i come across an error like ORA 20200: DATABASE/INSTANCE 1414392414 does not exit in STATS$DATABASE_INSTANCE

But when i checked the V$DATABASE view the above instance ID is present.

my doubt is

1) Can we install STATSPACK utility in 10g?

2) wht abt the remaining utilities like utlbstat,utlestat

3) if yes is this the right way to install these utilities?

and we said...

1) yes, it is fully documented as well

http://www.oracle.com/pls/db102/search?remark=quick_search&word=statspack&tab_id=&format=ranked

2) those two are very obsolete, ignore them

3) spcreate was correct, yes.

however, spauto just sets up a job - it did not actually capture statistics right then and there - and you need TWO observations.

So, either

a) make sure job queues are configured and wait 3 hours for at least two observations
b) use statspack.snap to take your own observations.

but I'll suggest you click on the link above and read about statspack and how to use it first...

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

AWR is better

October 27, 2008 - 7:12 pm UTC

Reviewer: Zahir M from Monroe , NJ

In 10g , you may want to use AWR .

( awrrpt.sql / awrrpti.sql or dbms_workload_repositry )
Tom Kyte

Followup  

October 27, 2008 - 8:50 pm UTC

presuming you are on enterprise edition and have licensed it, yes.

but then don't bother with the scripts, just use the browser.

and it is already installed.