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:

  1. source set(tab button)
  2. sqlplus (db username/password)
  3. > exec statspack.snap;  –(start snap)
  4. Run your procedure or set of transactions.
  5. >exec statspack.snap; –(end snap)
  6. 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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

What do you think?

400 Points
Upvote Downvote

How to generate trace reports in oracle?

Sqlite pkworlz

Create Sqlite Insert Query For JSON Object (Javascript/typescript)