Oracle Statspack Installation Guide

Introduction:
---
Oracle Statspack was introduced from Oracle8.1.6 and immediately became a powerful tool used by DBAs and Oracle experts to diagnose database performance. Through Statspack, we can easily determine all the bottlenecks of the Oracle database, record the database performance status, and allow remote technicians to quickly understand the database running status. Therefore, it is important for DBA to understand and use Statspack.

Before Statspack was released, we often used two scripts utlbstat. SQL and utlestat. SQL to diagnose the database. BSTAT/ESTAT is a very simple performance diagnostic tool. At the beginning, UTLBSTAT obtains many V $ view snapshots. UTLESTST generates a report through the previous snapshots and the current view. In fact, this report is equivalent to two sampling points of statspack.
Through continuous sampling, Statspack can provide very important trend analysis data, which is a huge improvement.

Therefore, if you can use the Statspack environment, try not to use the BSTAT/ESTAT method to diagnose database problems.
(Statspack is used in versions earlier than Oracle816. You need to use the statscbps. SQL script to create a V $ buffer_pool_statistics view. Can be downloaded from the oracle website .)

++
Principle:
---
1. Run the built-in oracle script to generate a series of statistical tables.
2. Generate snapshots and take samples (run statspack. snap to generate snapshots. Generally, snapshots are generated through automatic tasks)
3. generate a report based on the snapshot

++
I. Preparation
---

1. check some parameter values.
-- Job_queue_process: To create an automatic task and perform data collection, this parameter must be greater than 0.
Alter system set job_queue_processess = 6;

--- Timed_statistics, which is set to true, stores the collected time information in dynamic performance views such as V $ sessstats and V $ sysstats, but consumes resources, you can set this parameter to true before using Satspack. After sampling, modify this parameter to false dynamically.
Alter system set timed_statistics = true;
2. Script
To use this function, you need to run an oracle script to generate a series of tables and views in the database for collecting various information.
The script is located in the directory % oracle_home % \ rdbms \ admin, and oracle816 is a group of files starting with stat, and later versions are a group of files starting with sp.

++
Ii. Installation
---

1. Log On As sysdba.
8 I can log on with an internal user: sqlplus internal
For 9i and later versions, you can use the sys user to log on as sysdba: sqlplus/as sysdba
(It is best to go to all the scripts directory % oracle_home % \ rdbms \ admin to facilitate script execution)

2. Create a tablespace to save the sample data.
Create tablespace perfstat datafile 'e: \ hs01 \ dat \ perstat. ora'
Size 100 m
Extent management local;
The Statspack report data still occupies a considerable amount of space, especially when multiple consecutive sampling times are performed, so it cannot be too small, with a minimum of 100 MB; otherwise, the object creation will fail.

3. Run the script and install statspack.
Oracle816: % oracle_home % \ rdbms \ admin \ statscre. SQL
After 816: % oracle_home % \ rdbms \ admin \ spcreate. SQL

-- The script will create the user perfstat. You must specify this user password.
Enter the perfstat_password value: perfstat

-- Enter the tablespace used by perfstat: Specify the new tablespace.
Enter the value of default_tablespace: perfstat

-- You must specify the temporary tablespace used by perfstat.
Enter the value of temporary_tablespace: tmp10

-- The installation is successful. The following information is displayed:
Creating Package STATSPACK...

The package has been created.

No error.
Creating Package Body STATSPACK...

The package body has been created.

No error.

NOTE:
SPCPKG complete. Please check spcpkg. lis for any errors.

-- View error information
NT:
Host find "ORA-" *. LIS
Host find "err-" *. LIS

In Unix:
Grep ORA-*. lis
Grep err *. lis

If an error occurs, run the script to delete the related content: @ % oracle_home % \ rdbms \ admin \ spdrop. SQL
(Note: run a script under sysdba to delete related objects)
Then run the script again.

This installation process creates a series of tables for storing sample data.
View the new table:
Select dt. table_name from dba_tables dt where dt. owner = 'perfstat'
Conn perfstat/perfstat
Select table_name from user_tables;

++

1 2 3 4 5 6 Next Page