Performance Tuning
Statspack Installation
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 21:48
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 18 March 2010 21:48
-
Written by Guy Lambregts
-
Hits: 4256
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