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:
Database and Instance name
Time at which the snapshots were taken
Current size of the caches
Load profile
Efficiency percentages of the instance
Top five wait events
Complete list of wait events
Information on SQL statements currently in the pool
Instance activity statistics
Tablespace and file I/O
Buffer pool statistics
Rollback or undo segment statistics
Latch activity
Dictionary cache statistics
Library cache statistics
System Global Area(SGA) statistics
Startup values for initialization parameters