Database.fi

Hello, it’s been a while since last post. Too busy

Here is just a small example of how to perform analysis on a specific time period. Of course with AWR it would be so simple, but if you do not have access to AWR (Standard Edition Oracle for example), you just have to make do with Statspack.

Let’s say that we have a problem which occurs late at night at customer site.
It would be helpful to see what happens between 21 and 23 at night.

Of course we can just make statspack do snaps every 5 minutes and then turn it off later:
BEGIN
DBMS_SCHEDULER.CREATE_JOB ( job_name => 'STATSPACK_COLLECTION',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
repeat_interval => 'FREQ=MINUTELY; BYMINUTE=00,05,10,15,20,25,30,35,40,45,50,55',
enabled => TRUE,
comments => 'Statspack Collection'
);
END;
/

But it is way too easy to forget to turn it off and also you would allocate space from perfstat for no good reason. You’d probably end up filling up perfstat tablespace / quota.

The elegant way is to run snaps only for the time period you are interested in.
For that, we need to add a schedule to dbms_scheduler, create a program and tie them together as a job.

We need to create a schedule:
- Starts at 21:00
- Ends at 23:00
- When the schedule is active, Oracle must run statspack.snap every 5 minutes

Fine. How do we do that?

We need to create a schedule like this:

-- run every 5 minute from 21 PM to 23 PM
begin
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_5_MINUTES_2H',
start_date => trunc(sysdate)+21/24,
repeat_interval => 'freq=MINUTELY;interval=5',
end_date => trunc(sysdate)+23/24,
comments => 'Today from 21 to 23 PM, 5 minute interval');
end;
/


Also we need a program (Oracle scheduler term) to run statspack.snap:
-- Program to run statspack.snap
begin
dbms_scheduler.create_program
(program_name=> 'STATSPACK_SNAP',
program_type=> 'STORED_PROCEDURE',
program_action=> 'statspack.snap',
enabled=>true,
comments=>'Procedure to create statspack snap.'
);
end;
/



begin
-- Create job which will run with the new schedule and program.
dbms_scheduler.create_job
(job_name => 'STATSPACK_COLLECTION',
program_name=> 'STATSPACK_SNAP',
schedule_name=>'INTERVAL_EVERY_5_MINUTES_2H',
enabled=>true,
auto_drop=>false,
comments=>'Run statspack every 5 minutes from 21 to 23 PM today');
end;
/


That should do it.
It will run statspack collection from 21 to 23 PM with 5 minute interval.

Tomorrow you’ll be able to create spreport.sql (statspack reports) from that time period and dig deeper into the problem.

Hope this helps,
Pasi

I was asked to provide instructions on how to install and configure Statspack into Oracle so here goes.

The statspack scripts can be found from under this directory structure:
$Oracle_home/rdbms/admin


The installation needs to be run as SYS user.

Basic installation is done simply by running this script:
@?/rdbms/admin/spcreate.sql


This installs the Statspack engine & tables to database.

You can (if you want) automate the snap creation with this script:
@?/rdbms/admin/spauto.sql

This script takes snaps every hour starting from the next hour.

You can modify this script to make another kind of scheduling.
For example if you want to take snaps every half an hour, you need to modify:
"sysdate+1/24" => "sysdate+1/48"

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

=>
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'HH'), 'trunc(SYSDATE+1/48,''HH'')', TRUE, :instno);


If you need to ie. change the currently running job interval, you need to find out the job number first.
Connect as dba user to Oracle and run this query:

select job, what, interval from dba_jobs;

JOB
----------
WHAT
-----------------------------
INTERVAL
-----------------------------
21
statspack.snap;
trunc(SYSDATE+1/24,'HH')

So the statspack.snap is run every hour and job number is 21. Fine.
Let’s say that we need to run it every half an hour.
We need to change the interval to: 1/48.

You can update the job interval like this. You can set is as the job owner with this syntax:
exec dbms_job.interval(21,'TRUNC(SYSDATE+1/48,''HH'')');

PL/SQL procedure successfully completed.


Or as another user with DBA privileges:
SQL> show user
USER is "SYS"
SQL> exec dbms_ijob.interval(21,'TRUNC(SYSDATE+1/48,''HH'')');

PL/SQL procedure successfully completed.


Please note: there are two single quotes ‘ around HH, not one double-quote “.

Also you could remove the job with:
exec dbms_job.remove(21);

and re-create it with:

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'HH'), 'trunc(SYSDATE+1/48,''HH'')', TRUE, :instno);
commit;
end;
/


You should try that the job runs now.

Run as the job owner:
SQL> exec dbms_job.run(21);

PL/SQL procedure successfully completed.

Run as a DBA user:
SQL> exec dbms_ijob.run(21);

PL/SQL procedure successfully completed.

You can check when the next scheduled job run is:

SQL> select job, what, interval, to_char(next_date, 'DD.MM.YYYY HH24:MI:SS') from dba_jobs where job=21;

JOB
----------
WHAT
-------------------------------------
INTERVAL
-------------------------------------
TO_CHAR(NEXT_DATE,'
-------------------
21
statspack.snap;
TRUNC(SYSDATE+1/48,'HH')
29.03.2010 11:00:00

Statspack was introduced in Oracle 9i and it is still an usable tool in current Oracle version of 11.2.
AWR is superior technology compared to Statspack, but for many purposes Statspack still holds its place.