Oracle notes: Statspack Installation and Use

As the saying goes, to do a good job, you must first sharpen it. A good tool is also required for database performance analysis. Statspack is a powerful and free performance analysis tool provided by oracle.
To install statspack, you must have sysdba permissions. First, log in with sysdba to create a separate tablespace for statspack.
SQL code
1. SQL> create tablespace perfstat datafile 'd: \ oracle \ oradata \ epcit \ data_file \ PERFSTAT. dbf' size 2G;
2.
3. Tablespace created.
SQL> create tablespace perfstat datafile 'd: \ oracle \ oradata \ epcit \ data_file \ PERFSTAT. dbf'size 2G;
Tablespace created.
Then run spcreate. SQL. The SQL files required to install statspack are all located in the % oracle_home % \ RDBMS \ ADMIN directory. For ease of use, you can add this path to the environment variable sqlpath (set sqlpath = % oracle_home \ RDBMS \ ADMIN ).
SQL code
1. SQL> @ spcreate
2.
3. Choose the PERFSTAT user's password
4 .-----------------------------------
5. Not specifying a password will result in the installation FAILING
6.
7. Enter value for perfstat_password: password
8.
9. Enter value for default_tablespace: perfstat
10. Using tablespace PERFSTAT as PERFSTAT default tablespace.
11.
12. Enter value for temporary_tablespace: temp
13. Using tablespace temp as PERFSTAT temporary tablespace.
14.
15... Creating PERFSTAT user
16... Installing required packages
17... Creating views
18... Granting privileges
19.
20. NOTE:
21. SPCUSR complete. Please check spcusr. lis for any errors.
22.
23. SQL> -- Build the tables and synonyms
24. SQL> connect perfstat/& perfstat_password
25. Connected.
26. SQL> @ spctl
27.
28. Using perfstat tablespace to store Statspack objects
29.
30... Creating STATS $ SNAPSHOT_ID Sequence
31... Creating STATS $... Tables
32.
33. NOTE:
34. spalb complete. Please check spctl. lis for any errors.
35.
36. SQL> -- Create the statistics Package
37. SQL >@spcpkg
38.
39. Creating Package STATSPACK...
40. Package created.
41.
42. Creating Package Body STATSPACK...
43. Package body created.
44.
45. NOTE:
46. SPCPKG complete. Please check spcpkg. lis for any errors.
SQL> @ spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: password
Enter value for default_tablespace: perfstat
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Enter value for temporary_tablespace: temp
Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr. lis for any errors.
SQL> -- Build the tables and synonyms
SQL> connect perfstat/& perfstat_password
Connected.
SQL> @ spctl
Using perfstat tablespace to store Statspack objects
... Creating STATS $ SNAPSHOT_ID Sequence
... Creating STATS $... Tables
NOTE:
Spctl complete. Please check spctl. lis for any errors.
SQL> -- Create the statistics Package
SQL> @ spcpkg
Creating Package STATSPACK...
Package created.
Creating Package Body STATSPACK...
Package body created.
NOTE:
SPCPKG complete. Please check spcpkg. lis for any errors.
During the installation process, statspack will prompt you to enter the user password, default tablespace and temporary tablespace. If you want to install it in silent mode, you can set these variables in advance.
SQL code
1. SQL> define perfstat_password = 'Password'
2. SQL> define default_tablespace = 'perfstat'
3. SQL> define temporary_tablespace = 'temp'
SQL> define perfstat_password = 'Password'
SQL> define default_tablespace = 'perfstat'
SQL> define temporary_tablespace = 'temp'
The installation is complete.

 

This article comes from "oracle certification value"