STATSPACK

SQL> create tablespace spack

           datafile 'C:\oracle\product\10.2.0\oradata\dba20\SPAAC01.DBF' size 2M;

Tablespace created.

 

SQL> show parameter timed_statistics;

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

timed_statistics                     boolean     TRUE

 

If False:

SQL> alter system set timed_statistics=true;

 System altered.

 

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spcreate.sql

Enter value for perfstat_password: perfstat

 

Enter value for default_tablespace: spack

 

Enter value for temporary_tablespace: temp

 

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

 

Pass long amount of time.

 

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

 

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

 

SQL> conn perfstat/perfstat

Connected.

 

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spreport.sql

Enter value for begin_snap: 1

Enter value for end_snap: 2

Enter value for report_name: d:\SPreport

 

Statspack Output:

  1. Database and Instance name
  2. Time at which the snapshots were taken
  3. Current size of the caches
  4. Load profile
  5. Efficiency percentages of the instance
  6. Top five wait events
  7. Complete list of wait events
  8. Information on SQL statements currently in the pool
  9. Instance activity statistics
  10. Tablespace and file I/O
  11. Buffer pool statistics
  12. Rollback or undo segment statistics
  13. Latch activity
  14. Dictionary cache statistics
  15. Library cache statistics
  16. System Global Area(SGA) statistics
  17. Startup values for initialization parameters
Comments