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


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.


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

and you rated our response

  (1 rating)

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


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


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.