How to generate Statspack reports in Oracle?

Basically statspack report is similar to trace reports.Both have same purpose to find time consuming operations on a database.

Set of commands to generate statspack reports:

source set(tab button)
sqlplus (db username/password)
> exec statspack.snap; –(start snap)
Run your procedure or set of transactions.
>exec statspack.snap; –(end snap)
to view list of all snaps exec below command > @?/rdbms/admin/spreport;

Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2

Specify the Report Name
The default report file name is sp_1_2 To use this name, press <return> to
continue, otherwise enter an alternative. Enter value for report_name: ‘xyz.txt/lst’.

One can auto create snaps of available time intervals eg: 10:00-10:10, 10:10-10:30,10:30-10:50, 10:50-11:00 am.This can be done while configuring statspack on host machine.

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j