Oracle Scratchpad

March 7, 2007

Analysing Statspack (4)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 4:38 am GMT Mar 7,2007

[Further Reading on Statspack]

One of the ways to use statspack is to extract trending information from the data. I published some sample SQL on my website a couple of years ago to show how this could be done – but there are alternatives.

One possibility is simply to run the spreport.sql script as soon as you’ve taken a snapshot, and then use grep (Unix) or find (Windows) to extract some of the data from a series of the output files. Imagine, for example, that I have a problem with excessive I/O and spiking CPU, I might consider checking what’s been going on with tablescans or scatttered reads. If I have accumulated an archive of statspack reports, I could get a trend report very easily with the command:

grep "table scan rows gotten" sp*.txt

                Statistic                      Total  per Second  per Trans
sp_17011430.txt:table scan rows gotten    67,522,182    75,024.7      166.0
sp_17011445.txt:table scan rows gotten    60,288,793    66,987.6      168.3
sp_17011500.txt:table scan rows gotten    68,181,066    75,672.7      154.8
sp_17011515.txt:table scan rows gotten    68,845,922    76,580.6      207.2
sp_17011530.txt:table scan rows gotten   132,836,448   147,432.2      422.4
sp_17011545.txt:table scan rows gotten   134,103,072   149,003.4      430.5
sp_17011600.txt:table scan rows gotten   454,254,201   504,726.9    1,381.8
sp_17011615.txt:table scan rows gotten   218,650,324   243,215.0      577.1
sp_17011630.txt:table scan rows gotten    76,841,570    85,379.5      208.6
sp_17011645.txt:table scan rows gotten    66,468,544    73,853.9      199.4

I’ve inserted the header line on this report just to remind you what the numbers mean – obviously the titles wouldn’t appear when you used that particular call to grep.

I’ve set up this system to report the filename as month/day/hour/minute, and you can see that the output comes fromo a set of 15 minute snapshots. With my file-naming convention, the grep command has automatically reported the results in the right order to be easily readable.

In this example, you can see that something nasty has happened for about an hour from about 3:15 pm to 4:15 pm, peaking around 4:00 pm. It seems that, on average, the volume of tablescan activity per transaction has escalated sharply. We can now ask ourselves if this is consistent with other parts of the statspack information, and investigate the full statspack report for 4:00 pm (particularly) to see if it will tell us where the extra work came from.

To automate the runs of spreport.sql and get suitable file names for the outputs, you could start with a script something like the following.

execute statspack.snap(5)

column	begin_snap	new_value	begin_snap
column	end_snap	new_value	end_snap
column	report_name	new_value	report_name

column	instance_number	new_value	instance_number
column	dbid		new_value	dbid

select dbid from v$database;
select instance_number from v$instance;

select	begin_snap, end_snap, 'sp_' || snap_time || '.txt' report_name
from	(
		lag(snap_id,1) over(order by snap_id)	begin_snap,
		snap_id					end_snap,
		lead(snap_id,1) over(order by snap_id)	next_snap,
		to_char(snap_time,'mmddhh24mi')		snap_time
	 from	stats$snapshot
	 where	dbid		= &dbid
	 and	instance_number	= &instance_number
	next_snap is null

start ?/rdbms/admin/spreport

This script executes a snapshot, then finds the snapshot ids of the two most recent snapshots for the database and instance, builds a suitable report name based on the timestamp of the last snapshot, and calls spreport.sql.

Obviously you can’t run this script from dbms_job, so you’ll need another mechanism for automating it – and that could be the dbms_scheduler if you are running 10g.

[Further Reading on Statspack]

Footnote: Any advice about reading Statspack reports is almost always relevant when reading AWR reports (and vice versa).


  1. Thanks Jonathan.

    Just one small point, you say:

    Obviously you can’t run this script from dbms_job…

    Not strictly true: in Unix, for example, in conjunction with Java (Java source & PL/SQL wrapper), OS scripts can indeed be set up in dbms_job, though it’s a good deal more work than it is with dbms_scheduler (askTom has an example).

    All the best.

    Comment by SeanMacGC — March 8, 2007 @ 9:03 am GMT Mar 8,2007 | Reply

  2. SeanMcGC, thanks – this looks likes one of the threads that discusses the method.

    Comment by Jonathan Lewis — March 8, 2007 @ 11:31 am GMT Mar 8,2007 | Reply

  3. Sorry Jonathan,

    I could (and should) have left the thread in the first instance, and you’re right, that’s exactly the one.


    Comment by SeanMacGC — March 8, 2007 @ 10:21 pm GMT Mar 8,2007 | Reply

  4. Jonathan,

    Have you investigated or thought of advocating the use of statistical methods for automatically highlighting time periods that are anomalous? I’m thinking that the first problem sample in the “table scan rows gotten” example would have been highlighted immediately by virtue of the 132 million number falling well outside the third standard deviation for the previous set. On a system where stable performance is critical it might be worth investigating a proactive method for alerting in such situations.

    Comment by David Aldridge — March 9, 2007 @ 8:01 pm GMT Mar 9,2007 | Reply

  5. Jonathan ,

    I am trying to use dbms_scheduler to automate the script provided by you.The script works fine from Unix Prompt but it is not generating any output even after the scheduler_logs say that it ran sucessfully.
    DBMS_SCHEDULER.create_job (
    job_name => ‘PerfStat’,
    job_type => ‘EXECUTABLE’,
    job_action => ‘/home/oracle/scripts/’,
    start_date => SYSTIMESTAMP,
    repeat_interval => ‘freq=hourly; byhour=21;byminute=0,15,30,45’,
    enabled => TRUE,
    comments => ‘Temporary Job’);

    Any help will be highly appreciated .


    Comment by Rita — March 16, 2007 @ 10:33 pm GMT Mar 16,2007 | Reply

  6. Rita, Sorry about the delay – I lost this one while travelling. I’m not very familiar with dbms_scheduler, so can’t spot any obvious error beyond the thought that it’s not obvious where the output is going to arrive.

    You might have a look at Tim Hall’s website – he’s written a book on Oracle job scheduling, and although it’s had fairly mixed reviews it was written pre-10.2 and there are some further material about scheduling on his web site.

    Comment by Jonathan Lewis — April 9, 2007 @ 10:28 pm BST Apr 9,2007 | Reply

  7. Hi Jonathan,

    I have 1 question on statspack.snap , customer request us to snap on 9:00am , but my statspack alway 9:03pm or something different.

    Is there any way i can take exact snap ?

    Comment by Raymond — April 16, 2007 @ 10:48 am BST Apr 16,2007 | Reply

  8. Raymond, It should be possible. Presumably you’ve used dbms_job to schedule a call to the snap procedure. What does your dbms_job call look like. (Check the notes about spaces in the opening menu to make the code readable.)

    Comment by Jonathan Lewis — April 19, 2007 @ 9:30 pm BST Apr 19,2007 | Reply

  9. Hello Jonathan,

    How to check the statspack instance whether it is running or not? are there any kind of checks to do that.


    Comment by Ranjith — May 8, 2007 @ 1:24 pm BST May 8,2007 | Reply

  10. Ranjith, not sure quite what you mean by the statspack instance – but if you simply want to check on the whether the automatic job is running, then you could query view dba_jobs:

            to_char(next_date,'dd-mon-yy hh24:mi:ss') when, 
    WHAT               WHEN               B
    ------------------ ------------------ -
    statspack.snap;    08-may-07 22:00:00 N

    Comment by Jonathan Lewis — May 8, 2007 @ 8:53 pm BST May 8,2007 | Reply

  11. Hello Jonathan,

    Thanks for the reply.

    When we run the statspack.snap manually, its taking snapshots, my doubt is whether we can manually check the STATSPACK is running or not through some variable or by some parameter or by some flag.


    Comment by Ranjith — May 9, 2007 @ 3:46 pm BST May 9,2007 | Reply

  12. What you gave information Regarding statspack is very very nice…..

    With regards,

    Comment by MURALI — July 13, 2007 @ 6:37 am BST Jul 13,2007 | Reply

  13. Hi,

    Firstly thanks for taking the time and effort to share your knowledge with the oracle community, it’s very much appreciated.

    Your series of statspack articles has given me some great pointers and in particular this posting has helped me in exactly the area i’m currently working on i.e. trend analysis from statspack reports as part of an overall review of site performance for a customer.

    Rgarding automating the runs of spreport.sql i’ve borrowed your excellent file name format and come up with the following script which in turn generates a script which produces all the statspack reports for a certain day.

    — generateReportScript.sql

    spool DayReports.sql

    set heading off pages 9999

    select ‘define begin_snap=’||snap_id||’;’||chr(10)||
    ‘define end_snap=’||to_number(snap_id + 1)||’;’||chr(10)||
    ‘define report_name=sp_’||to_char(snap_time,’ddmmhh24mi’)||’.txt’||chr(10)||
    from stats$snapshot
    where trunc(snap_time) = trunc(sysdate -1)
    order by snap_time asc;

    spool off

    I don’t have an understanding of analytic functions yet so the above script crudely just adds on 1 to the snap_id to get the end_snap for a report but if you’ve set up your site to take snapshots every 15mins then the script works fine producing the following script:

    — DayReports.sql

    define begin_snap=1621;
    define end_snap=1622;
    define report_name=sp_03060600.txt


    which when run as the PERFSTAT user produces the required files


    which can then be grepped as required!

    Hope someone finds this useful. :)

    George Johnston

    Comment by George Johnston — June 4, 2008 @ 1:02 pm BST Jun 4,2008 | Reply

  14. George,

    Thanks for the comment, and thanks for making the contribution.

    A detail which you may find useful – and which I think I’ve mentioned elsewhere – if you’re going to depend on the statspack snapshot ids increasing by one all the time, then make sure you set the cache size on the statspack sequence (STATS$SNAPSHOT_ID) to zero (i.e. nocache).

    Comment by Jonathan Lewis — June 10, 2008 @ 10:04 am BST Jun 10,2008 | Reply

  15. Hi Jonathan,

    Thanks for sharing your knowledge and experience with oracle community. I really enjoy reading your articles.

    For statspack trending, I use the same method as you use. This is really helpful. I also use following query as well on 10g for trending of Load Profile statistics.

    set lines 130 pages 100
    col value for 999999999999
    col stat_name for a25
    col BEGIN_INTERVAL_TIME for a25
    col END_INTERVAL_TIME for a25
    col per_scond for 9999999999
        b.value-a.value "DIFF" ,
        round((b.value-a.value)  /(extract( day from (end_interval_time-begin_interval_time) )*24*60*60+
                   extract( hour from (end_interval_time-begin_interval_time) )*60*60+
                   extract( minute from (end_interval_time-begin_interval_time) )*60+
                   extract( second from (end_interval_time-begin_interval_time)) ),2) per_scecond
        dba_hist_sysstat a,
        dba_hist_sysstat b,
        dba_hist_snapshot sn 
        a.stat_name='redo size' and
        b.stat_name='redo size' and 
        a.instance_number=(select instance_number from v$instance) and 
        b.instance_number=(select instance_number from v$instance) and 
        sn.instance_number=(select instance_number from v$instance) and 
        a.snap_id=b.snap_id-1 and
    order by 1;

    “redo size” can be replaced by following:
    ‘session logical reads’,
    ‘db block changes’,
    ‘physical reads’,
    ‘physical writes’,
    ‘user calls’,
    ‘parse count (total)’,
    ‘parse count (hard)’,
    ‘sorts (disk)’,
    ‘execute count’,
    ‘user commits’,
    ‘user rollbacks’

    I hope, it will be helpful for someone.


    Comment by Kapil — December 14, 2008 @ 5:05 pm GMT Dec 14,2008 | Reply

    • Kapil,

      Thank you for your contribution. I’ve tried to edit the text to make it a little more readable within the restrictions of WordPress comments.

      A couple of points you might want to check. First, the outer join on snap_id will be eliminated because of earlier predicates that aren’t outer joins. Secondly, this may work on your system, but others may have managed to get data from multiple databases into their AWR tables; technically you should have a join on the database_id column as well. Finally, if you read the article from my website, you’ll notice that it was about using analytic functions instead of a self-join. AWR discards data after seven days (by default) so the overhead using your self-join may not be significant – but some sites will choose to retain much larger ranges of data, at which point the benefits of the analytic approach may become more significant.

      Comment by Jonathan Lewis — December 21, 2008 @ 8:31 pm GMT Dec 21,2008 | Reply

  16. Thanks for pointing snap_id and database_id. I would definitely consider using analytical function.


    Comment by Kapil — December 30, 2008 @ 4:45 am GMT Dec 30,2008 | Reply

  17. […] 原文: […]

    Pingback by xpchild » Analysing Statspack 4 — June 27, 2011 @ 9:15 am BST Jun 27,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by

%d bloggers like this: