28 | 03 | 2024
Latest Articles
Popular Articles

Performance Tuning

Statspack Installation

User Rating:  / 0
PoorBest 

Statspack installation

Since version I don' t know the Oracle Database offers us the v$views, a lot of them contain information about what is going on at the instance level (v$system_event, v$sysstat, v$waitstat, v$rollstat, v$undostat, v$lock, v$transaction , v$filestat, v$segment_statistics .... ) at the session level (v$session_event, v$sesstat, v$session_wait, ... ). However a select from them gives us only info at one particular moment. It can become interesting to know what the performance stats, the wait events are in between a time frame(s). That' s what statspack is about, available since release 8.1.5. Statspack allows us to take, schedule performance  snapshots "pictures" from these v$views stores the info in a tablespace, in some dedicated tables. A statspack report is nothing more then a standardized output of the instance/db events between two snapshots.  Since the output is standardized a statspack report becomes an important source off information which we can use in order to list out the db/intance events in order to discuss toghether what is going on in a particular db. Please note that since Oracle 10G R 1 statspack reports are somewhat outdated and replaced by ADDM reports and AWR Reports. ( but you need a valid licence to use these reports ) Note as well one can use statspack snapshots at a default and at a non default level both system and sessionwide.

Let us install statspack, you will see this is an easy task.

 

 

cd $ORACLE_HOME/rdbms/admin

sqlplus sys/secret_password as sysdba

SQL> show release
release 902000500

SQL> create tablespace perfstat datafile size 200M autoextend on next 10M maxsize 2G extent management local segment space management auto; 

SQL> @spcreate.sql
... Installing Required Packages

Package created.

... Creating PERFSTAT user ...

Choose the PERFSTAT user's password.

Not specifying a password will result in the installation FAILING

Specify PERFSTAT password
Enter value for perfstat_password: perfstat
perfstat

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for performance data is not supported.


TABLESPACE_NAME CONTENTS


COS_IZE_DATA PERMANENT
COS_IZE_INDX PERMANENT
PERFSTAT PERMANENT
TEMPORARY_DATA TEMPORARY
UNDOTBS3 UNDO
USER_DATA PERMANENT

6 rows selected.

Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: PERFSTAT

Enter value for temporary_tablespace: TEMPORARY_DATA

...

...

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

 

That' s it, we are ready to use statspack

Statspack at the instance level

Statspack at the session level

Statspack with non default attributes