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 .. PressingConfiguring statspack refer metalink note 149121.1 which lists default values.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.
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 ---------- 7On 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 7To take snapshots simply rundeclare
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/spautowill 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/spreportFor 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.1There 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/sppurgecould also be used to purge the snapshot. It will purge snapshot of the currently connected instance.
Drop statspack with
@?/rdbms/admin/spdropTuning 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 1This 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;
/
