Using Oracle statspack

STATSPACK has two adjustable parameters: level and threshold. Level is the type of data
collected and threshold acts as a filter for collecting SQL statements. You can use this query
to view all levels:

SQL> SELECT * FROM stats$level_description ORDER BY snap_level;

Level 0		This level captures general statistics, including rollback segment, row cache, SGA,
system events, 
		background events, session events, system statistics, wait statistics, lock statistics, and
Latch 
		information.

Level 5		This level includes capturing high resource usage SQL Statements, along with all data
captured by 
		lower levels.

Level 6		This level includes capturing SQL plan and SQL plan usage information for high
resource usage SQL 
		Statements, along with all data captured by lower levels.

Level 7		This level captures segment level statistics, including logical and physical reads,
row lock, itl 
		and buffer busy waits, along with all data captured by lower levels.

Level 10	This level includes capturing Child Latch statistics, along with all data captured by
lower levels. 


To change the default level for statspack snapshots use this function:

SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

To create a snapshot:

sqlplus perfstat/perfstat

SQL> exec statspack.snap;

To view snapshots:

sqlplus perfstat/perfstat

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from
stats$snapshot, v$database;

To delete a snapshot:

sqlplus perfstat/perfstat

SQL> @?/rdbms/admin/sppurge;
Enter the Lower and Upper Snapshot ID

To create statspack report:

sqlplus perfstat/perfstat

SQL> @?/rdbms/admin/spreport.sql