Database Reference
In-Depth Information
All snapshots created prior to a specific date (parameter
i_purge_before_date )
All snapshots older than a specific number of days (parameter
i_num_days )
Note that by default, data about SQL statements and execution plans isn't purged. To purge it, an extended purge
must be activated by setting the i_extended_purge parameter to TRUE . For example, the following call purges all
snapshots (including SQL statements and execution plans) taken before April 2014:
statspack.purge(
i_purge_before_date => to_date('2014-04-01','YYYY-MM-DD'),
i_extended_purge => TRUE
);
Since, out of the box, snapshots are neither automatically taken nor purged after a specific period of time, you
should schedule two jobs that carry out those tasks. The following are examples (note that both jobs should be created
with the perfstat user):
Take a snapshot every 15 minutes.
dbms_scheduler.create_job(
job_name => 'TAKE_STATSPACK_SNAPSHOT',
job_type => 'PLSQL_BLOCK',
job_action => 'perfstat.statspack.snap();',
start_date => sysdate,
repeat_interval => 'FREQ = HOURLY; BYMINUTE = 0,15,30,45',
enabled => TRUE,
comments => 'take STATSPACK shapshot'
);
Purge snapshots created more than 35 days ago.
dbms_scheduler.create_job(
job_name => 'PURGE_STATSPACK_SNAPSHOTS',
job_type => 'PLSQL_BLOCK',
job_action => 'statspack.purge(i_num_days => 35, i_extended_purge => TRUE);',
start_date => sysdate,
repeat_interval => 'FREQ = HOURLY; BYMINUTE = 50',
enabled => TRUE,
comments => 'purge STATSPACK shapshots'
);
You can find other examples in two scripts distributed by Oracle: spauto.sql and sppurge.sql . Both are
available in the $ORACLE_HOME/rdbms/admin directory.
Note
in a real application Clusters environment, it's necessary to separately schedule the jobs on every database
instance.
 
Search WWH ::




Custom Search