Friday, July 23, 2010

Statspack setup in Brief

It seems statspack is the only option if the database is standard edition. Diagnostic and Tuning packs are not an option on standard edition.

Installing statspack metalink ntoe 149113.1
Blog is for 11gR1 (
SQL> @?/rdbms/admin/spcreate

Choose the PERFSTAT user's password
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: pefstat

Choose the Default tablespace for the PERFSTAT user
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

------------------------------ --------- ----------------------------
TOOLS                          PERMANENT
USERS                          PERMANENT
WSTBS                          PERMANENT
Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: tools

Using tablespace TOOLS as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views     
SQL> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
SPCPKG complete. Please check spcpkg.lis for any errors.
Configuring statspack refer metalink note 149121.1 which lists default values.
Change the default snap level 5 to 7 (Good for taking snapshots every hour or level 0 and every 15 minutes)
exec statspack.modify_statspack_parameter(i_snap_level => 7, i_modify_parameter=>'true');
Verify the changes in
select snap_level from stats$statspack_parameter;

On RAC systems run the parameter modify command on each instance and check snap level has been set on all nodes
SQL> select instance_number,snap_level from stats$statspack_parameter;

--------------- ----------
              1          7
              2          7
To take snapshots simply run
snap_no number;
snap_no := statspack.snap;
dbms_output.put_line('snapshot number '||snap_no);
To take snapshot automatically set aq_tm_processes and job_queue_processes to > 0 and
will take snapshot every hour on the hour. In a RAC environment run the spauto script on all nodes to add instnace specific jobs.
---- ---------- ------------------------------
30          1 trunc(SYSDATE+1/24,'HH')
50          2 trunc(SYSDATE+1/24,'HH')
Change the snapshot interval with
Create statspack report with
For best performance when running statspack report gather statistics on perfstat schema with
Making baseline explained on metalink note 394937.1
There are two ways to make baselines using snapshots, which include snap_shot ranges, date range. Make a baseline with
exec Statspack.make_baseline(begin_snapid,end_snapid);
Snapshots making the baselines are not affected by snapshot purge.

There are number of ways to purge snapshots by specifying range of snapshots, range of dates, purge all snapshots before a certain date or delete all snapshots taken number of days before the current date.
could be used for this.
could also be used to purge the snapshot. It will purge snapshot of the currently connected instance.

Drop statspack with
Tuning with statspack on metalink note 228913.1
Deleting snapshots at regular intervals, this is not part of the oracle statspack setup files.
create table to store deleted snap information.
create table deleted_snaps(delete_time timestamp primary key,
min_snapid number, 
max_snapid number, 
dbid number, 
instance_number number);
Procedure for keeping only the snapshots taken two weeks ago
create or replace
Procedure Delete_Snaps As
min_snap_id stats$snapshot.snap_id%type;
max_snap_id stats$snapshot.snap_id%type;
i_dbid stats$snapshot.snap_id%type;
i_instance_num stats$snapshot.snap_id%type;
snapshots_purged pls_integer;
cursor delsnaps is select dbid,instance_number,min(snap_id) as min,
max(snap_id) as max from stats$snapshot 
where to_char(snap_time,'YYYY-MM-DD') = to_char(sysdate-15,'YYYY-MM-DD') 
group by dbid,instance_number;
open delsnaps;
fetch delsnaps into i_dbid,i_instance_num,min_snap_id,max_snap_id;
Exit When Delsnaps%Notfound;
snapshots_purged := statspack.purge( i_begin_snap      => min_snap_id
, i_end_snap        => max_snap_id
, i_snap_range      => true
, i_extended_purge  => true
, i_dbid            => i_dbid
, i_instance_number => i_instance_num);

Insert Into Deleted_Snaps values(Systimestamp,Min_Snap_Id,Max_Snap_Id,i_dbid,i_instance_num);  
end loop;
Close Delsnaps;
Schedule a job to delete at 12.15 am on a Rac environment it will delete snapshots of all instances that meet the delete criteria, no need to run on all instances.
  Job_Id Number;
  Dbms_job.submit(job => job_id,
  what => 'Delete_Snaps;',
  next_date => trunc(SYSDATE + 1,'DD') + 15/1444 ,
  Interval => 'trunc(SYSDATE + 1,''DD'') +15/1444');
Update on 07 December 2011
At times it is possible to see the following error message on the alert log
ORA-12012: error on auto execute of job 50
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5661
ORA-06512: at "PERFSTAT.STATSPACK", line 105
ORA-06512: at line 1
This is because there could be same values for columns (MUTEX_TYPE, LOCATION) in v$mutex_sleep view. As a workaround disable the unique constraint and create a non-unique index on affected columns.
SQL> alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

This has been tracked as the BUG:5443234 - STATSPACK.SNAP CAUSING UNIQUE CONSTRAINT (STATS$MUTEX_SLEEP_PK) VIOLATED mentioned in the metalink note ORA-1 on PERFSTAT.STATS$MUTEX_SLEEP_PK when executing statspack.snap [ID 382993.1]