What is meant by STATSPACK in relation to Oracle Tuning!

[amitstora]

Hi all!

Query:

1.What is meant by STATSPACK in relation to Oracle Tuning!

How could it be useful!

What book is recommended for the above!

Thanks&Regards,

Amit.

[Sureshy]

Read This ..

http://www.oracle.com/oramag/oracle/...ml?o20tun.html

[noor]

Hello,

I few word, STATSPACK is a tool (set of scripts) used to gather information about the state (health) of the database (new version of UTL[B|E]stat script).

For the detail, the URL given above is a good reference.

Hope this helps

[ligang]

Oracle provides many PL/SQL packages, including a good number of SQL*Plus scripts that support instance tuning. Examples include UTLBSTAT.SQL, UTLESTAT.SQL, UTLCHN1.SQL, UTLDTREE.SQL, and UTLLOCKT.SQL. Release 8.1.6 also contains the STATSPACK set of scripts.

These statistical scripts support instance management, allowing you to develop performance history. You can use them to:

w Remove the need to issue DDL each time statistics are gathered.

w Separate data gathering from reporting, and let a range of observations be taken at intervals during a period of representative system operation, and then allow the statistics to be reported from any start point to any end point.

w Report a number of indicative ratios that you can use to determine whether the instance is adequately tuned.

w Present LRU statistics from the buffer cache in a usable form.

STATSPACK differs from the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:

w They collect more data, including high resource SQL.

w Many of the manual calculations which were required with BSTAT/ESTAT are now provided; for example, the first page contains a summary of instance performance and load.

w Permanent tables are created. Each time a new "snapshot" of data is taken, it is added to these tables, with keys which allow comparison between snapshots.

w A new user, PERFSTAT, is automatically created. All objects created by this package are owned by PERFSTAT. This user has limited query-only privileges.

w Written in PL/SQL and uses SQL*Plus as the reporting tool.

Like UTLBSTAT.SQL and UTLESTAT.SQL, STATSPACK can be found in the ORACLE_HOME/rdbms/admin/ directory on UNIX and in the ORACLE_HOME/rdbms81/admin directory on NT.

[omegamark]

One another Main advantage is You can compare the snapshot details when the database performance is good with the snapshot when the database performance is poor .

So one can easily identify where the bottle neck is .

That's the reason one should take use the statspack eventhough the performance is good .You can use them when the performance degrades and one can easily identify the bottleneck .