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 (11.1.0.7)
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
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.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
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.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP 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.
NOTE:
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;

SNAP_LEVEL
----------
7
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;

INSTANCE_NUMBER SNAP_LEVEL
--------------- ----------
              1          7
              2          7
To take snapshots simply run
declare
snap_no number;
begin
snap_no := statspack.snap;
dbms_output.put_line('snapshot number '||snap_no);
end;
/
To take snapshot automatically set aq_tm_processes and job_queue_processes to > 0 and
@?/rdbms/admin/spauto
will take snapshot every hour on the hour. In a RAC environment run the spauto script on all nodes to add instnace specific jobs.
JOB   INSTANCE INTERVAL
---- ---------- ------------------------------
30          1 trunc(SYSDATE+1/24,'HH')
50          2 trunc(SYSDATE+1/24,'HH')
Change the snapshot interval with
dbms_job.interval(jobid,interval);
Create statspack report with
@?/rdbms/admin/spreport
For best performance when running statspack report gather statistics on perfstat schema with
dbms_stats.gather_schema_stats('PERFSTAT');
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);
or 
statspack.make_baseline(to_date(begin_date),to_date(end_date));
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.
statspack.purge(begin_snapid,end_snapid);
could be used for this.
@?/rdbms/admin/sppurge
could also be used to purge the snapshot. It will purge snapshot of the currently connected instance.

Drop statspack with
@?/rdbms/admin/spdrop
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;
begin
open delsnaps;
loop
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;
commit;
End;
/
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.
Declare
  Job_Id Number;
Begin
  Dbms_job.submit(job => job_id,
  what => 'Delete_Snaps;',
  next_date => trunc(SYSDATE + 1,'DD') + 15/1444 ,
  Interval => 'trunc(SYSDATE + 1,''DD'') +15/1444');
end;
/
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;

SQL> create index perfstat.STATS$MUTEX_SLEEP_PK on STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
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]



Update on 07 October 2020
As of 12.2 DBMS_JOB package is depreciated. Use DBMS_SCHEDULER to create a snap job and statspack delete job as below.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_SNAP_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
start_date => TO_TIMESTAMP('0' ,'MI'),
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'Delete_Snaps_job',
job_type => 'STORED_PROCEDURE',
job_action => 'Delete_Snaps',
start_date => trunc(SYSDATE + 1,'DD') + 30/1444,
repeat_interval => 'FREQ=DAILY',
enabled => TRUE);
END;
/