A performance problem is reported to you, that occurred 2 days ago, at 5:00 pm, in the HR database. What do you do?

If you have Diagnostic and Tuning Pack you know exactly what to do:

Review AWR reports, either in Enterprise Manager or generate them with the script, or review the dba_hist* views to identify the problematic sql.

But, if you don’t have Diagnostic and Tuning Pack, (let’s face it, this is pretty common), you are facing a bigger challenge.

Not all DBAs work for companies that license Diagnostic and Tuning Pack. Some companies see the value for the extra licensing costs, some don’t.

During your DBA training, you are taught all the cool tools for DBAs, which help you diagnose any performance problem quickly. This makes you believe that you will always have these tools available in your job.

Then reality hits, you get hired, and all of a sudden AWR becomes a dream only. You are not allowed to use AWR, because you have no license for it!

If you are one of those DBAs that don’t have AWR available, I recommend you being pro-active to performance problems, rather then reactive.

What can you do to have some kind of data for future performance problems?

I came up with 2 options. There might be more.

-create your own tool/script that samples the database every few seconds, and saves the data somewhere
-setup Statspack, which is FREE!

Some of you might think I am joking, but I am serious. Statspack is still alive and useful, even in 12c. But, Statspack is outdated, and has no GUI, and is not so detailed as AWR…

Yes, I can hear you! However, it is better than nothing! And it can save you when needed.

Am I using Statspack in my current job? Yes, I am, in the test environment. It has helped me many times, troubleshooting performance problems, or actually proving that the performance problem is not in the database.

There are lots of great, useful articles that cover Statspack. I am not going to duplicate information here.

I am giving you some ideas that will help you troubleshoot performance problems that occurred in the past. If setup correctly, the Statspack reports will be waiting for you to be used. Automated correctly, these reports can be available for months or even years.

1. Most Important: Setup Statspack When AWR Is Not Available
2. How To Automate Snapshots?
3. How To Automate Purging Old Statspack Data?
4. Why Is It Important To Generate Reports?
5. What Do I Do With All The Generated Reports?
6. What Do I Look At In A Statspack Report?

1. Most Important: Setup Statspack When AWR Is Not Available

If you have AWR at your finger tips, then don’t worry about Statspack.
If you don’t, then make it a priority to install Statspack in the database.

Automating the installation for larger environments (many databases) saves time.

Things to consider during installation:

  • the tablespace where you want all the Statspack data stored. I like to call the tablespace STATSPACK, however you can name it as you whish.
  • the PERFSTAT user’s password. Depending on the security requirements, you can make this password as complex as needed.

To install statspack run the script:
@?/rdbms/admin/spcreate.sql

2. How To Automate Snapshots?

To automate snapshots creation you need either a scheduled script to run at certain hours, or a scheduled job.
I prefer setting up a scheduled job in the database. When database is available, the job runs, when the database is not available, the job doesn’t run.

One thing to take into consideration is the RAC environment. If you are in a RAC environment, I recommend you read up on this post, and set it up just the way it is mentioned there

Things to consider for automation:

  • the frequency of the snapshots. I prefer every hour, however, you might need to take snapshots more often.
  • RAC environment, the post mentioned above, has detailed explanation on setting up Statspack in a RAC environment.
  • In a RAC environment a job is needed to run in each instance specifically to take a snapshot for that instance. You cannot create a snapshot for instance 2, if you are connected to instance 1.
?View Code ENGLISH
 
connect perfstat/***
 
begin
dbms_scheduler.create_job(
job_name => 'STATSPACK_SNAPSHOT',
job_type => 'PLSQL_BLOCK',
job_action => 'begin statspack.snap; end;',
start_date => TO_TIMESTAMP_TZ('2016/11/22 10:00:00.000000 AMERICA/DENVER','yyyy/mm/dd hh24:mi:ss.ff tzr'),
repeat_interval => 'FREQ=hourly; INTERVAL=1;',
comments => 'statspack snapshot snap',
enabled => TRUE);
end;
/

3. How To Automate Purging Old Statspack Data?

First task that comes to my mind after taking snapshots, is to schedule a job that will also purge the data. You want to keep the snapshots nice and tidy.
It is up to you how long you want to keep Statspack data around. AWR default retention is 8 days, you can have the same retention too.

?View Code ENGLISH
 
connect perfstat/***
 
begin
dbms_scheduler.create_job(
job_name => 'STATSPACK_PURGE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin statspack.purge(8); end;',
start_date => TO_TIMESTAMP_TZ('2016/11/22 10:00:00.000000 AMERICA/DENVER','yyyy/mm/dd hh24:mi:ss.ff tzr'),
repeat_interval => 'FREQ=daily; byhour=23;',
comments => 'statspack snapshot purge',
enabled => TRUE);
end;
/

4. Why Is It Important To Generate Reports?

Once you capture all this data, what do you do with it? Is this data just sitting in the database waiting to be purged? Yes, unless you take some action.

Do you always hear about performance problems right away? Sometimes yes, sometimes no.
When you hear about the problem a week later, you start troubleshooting, only to find the meaningful Statspack data, purged the night before. Now you have nothing to analyze.

This is why, I recommend generating Statspack reports for each 2 snapshots for the previous day. If today is Nov 24, generate all the reports for Nov 23.

Automate the report generation with a shell script scheduled in the crontab or as a database job.

Things to consider for naming convention of these reports:

  • include the database name
  • include the start snap_id, end snap_id
  • include the start snap_id time
  • in a RAC environment, include the instance number

5. What Do I Do With All The Generated Reports?

I keep them! How long? As long as I want them to.

I have a job that gzips these reports every week, and compresses them. The compressed files are named in a manner that it is easy to identify a database and a specific period of time.

Keep these reports as long as you can.

6. What Do I Look At In A Statspack Report?

You can look at many things, I am not going to explain each and every single part of it. There are many blogs out there on this subject.

What I look at mostly, is High Consumers either by CPU or Elapsed Time. Each sql statement is identified by an “Old Hash Value”, which you will need if you want to generate the execution plan for that particular sql statement.

Write down the “Old Hash Value”, then invoke the following script. Pass the “Old Hash Value” as a parameter, together with the start snap_id and end snap_id:

@?/rdbms/admin/sprepsql.sql

You’ll get the execution plan for that sql statement during the time of the snap_ids provided.

There are many other uses of the Statspack, this is one of the things I use the most.

If you remember one thing from today’s post, this would be it:

If you don’t have AWR, then Statspack is your friend!!!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana


2 thoughts on “Statspack, For Solving Problems Like A PRO

  1. Hi Diana,
    Nice post, short and informative (and important as well), I have a couple of comments:
    1. Statspack is not outdated. It gets updated with Oracle versions (although, as you said, it’s not as comprehensive as AWR)
    2. When creating snapshot, I would recommend using “statspack.snap(i_snap_level=>7)”. Level 7 provides more information, including segments statistics which I find very very useful in many cases.

    Thanks,
    Liron

Leave a Reply

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