Install and use Statspack

Environment: CentOS 6.2 + Oracle 11.2.0.3 1. the Statspack installation and installation process is to create a user perfstat and create corresponding objects under this user. Oracle provides existing scripts to create these objects, we only need to use SYSDBA to run this script. The Statspack creation script spcreate. the SQL statement is located in the $ ORACLE_HOME/rdbms/admin directory. The process for creating Statspack is as follows: SQL> @/u/oracle/product/11.2/rdbms/admin/spcreate. sqlChoose the PERFSTAT user's password does Not specifying a password will result in the installation FAILING input perfstat_password value: perfcreateperfcreate use sys user to connect to the database and run spcreate. for SQL scripts, you must first enter the new user perfstat password. The password we enter is perfcreate. Choose the Default tablespace for the PERFSTAT user --------------------------------------------------- Below is the list of online tablespaces in this database which canstore user data. specifying the SYSTEM tablespace for the user's sdefault tablespace will result in the installation FAILING, asusing SYSTEM for performance data is not supported. choose the PERFSTAT users's default tablespace. this is The tablespacein which the STATSPACK tables and indexes will be created. TABLESPACE_NAME contents statspack default tablespace limit --------- specified CW limit permanentdbapi permanentpetdog permanentseven limit PERMANENT * users permanent Pressing <return> will result in STATSPACK's recommended defa Ulttablespace (identified by *) being used. enter the default_tablespace value: users: Oracle then lists the tablespaces that can be used in the database. We need to specify the default tablespace for the user perfstat, that is, the tablespace that stores Statspack information, here we select the users tablespace. Using tablespace USERS as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
Store temporary data (e.g. for sort workareas). Specifying the SYSTEM
Tablespace for the user's temporary tablespace will result in
Installation FAILING, as using SYSTEM for workareas is not supported. Choose the PERFSTAT user's Temporary tablespace. TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
-----------------------------------------------------------------
Temp temporary * Pressing <return> will result in the database's default Temporary
Tablespace (identified by *) being used. Enter the value of temporary_tablespace: temp. Then, enter the default temporary tablespace. No doubt, we will select temp tablespace. The following is the operation to create a perfstat user object: If this script is automatically called from spcreate (which isthe supported method), all STATSPACK segments will be created inthe PERFSTAT user's default tablespace. using users tablespace to store Statspack objects... the Creating STATS $ SNAPSHOT_ID Sequence has been created. You have created a synonym .... The Creating STATS $... tables table has been created. You have created a synonym. The table has been created. One row has been created. One row has been created. You have created a synonym. You have created a synonym. NOTE: sp2009complete. Please check spctl. lis for any errors... the Package STATSPACK... 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. So far, the Statspack installation is complete. Next, use Statspack for performance collection and performance analysis.

1 2 Next Page