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

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.

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

Rating

  (1 rating)

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

Comments

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.