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


Question and Answer

Tom Kyte

Thanks for the question, Krish.

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

Last updated: October 27, 2008 - 8:50 pm UTC

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.


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


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 Tom said...

1) yes, it is fully documented as well

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...


  (1 rating)

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


AWR is better

Zahir M, October 27, 2008 - 7:12 pm UTC

In 10g , you may want to use AWR .

( awrrpt.sql / awrrpti.sql or dbms_workload_repositry )
Tom Kyte
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.