Why we need statspack? However for some non critical database where we don’t have license of tuning pack. Hence we need to survive on statspack.

1) Take export backup of exiting perftstat user

2) Drop statspack

 @?/rdbms/admin/spdrop.sql;

3) Create stastpack

 @?/rdbms/admin/spcreate.sql;

4) Change stastpack snap level to level 6

BEGIN
statspack.modify_statspack_parameter(i_snap_level=>6, i_modify_parameter=>'true');
END;
/

select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

5) Take manual snap

 exec PERFSTAT.statspack.snap;

6) Schedule statspack auto jobs for statspack snap ( Please note job id)

 @?/rdbms/admin/spauto.sql

7) Change snap interval to 30 min

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=826;

exec dbms_job.interval(826,'sysdate+(1/48)');

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT , 
to_char(next_date ,'DD-MON-YYYY:HH24:MI:SS') "next date" , 
failures from dba_jobs where JOB=826;

select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" 
from stats$snapshot,v$database;

8) After 30 minutes verify snap interval working fine with 30 min and level 6

@?/rdbms/admin/spreport.sql

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8