Statspack is a tool available from Oracle 8. It is still an option for gathering workload statistics, if diagnostics pack is not available. Standard Editions of Oracle do not support Diagnostic pack as it is an addon of Enterprise Edition. Of course when diagnostic pack is available, AWR should be used for this purpose as it provides many more useful tools.
Statspack can run in CDB and PDB so we need to decide where we want to install it.
Let’s see how to enable statspack ina a pluggable database:
First a new tablespace is needed usually it is called PERFSTAT. Connect to the desired PDB and create it:
<mark style="background-color:#000000" class="has-inline-color has-palette-color-7-color">create tablespace PERFSTAT datafile size 512M autoextend on maxsize 3G;</mark>
Then on the same pluggable we execute:
<mark style="background-color:#000000" class="has-inline-color has-palette-color-8-color">@?/rdbms/admin/spcreate</mark>
This creates the necessary objects (PERFSTAT user, tables, package). It will ask for perfstat’s user password, the default tablespace and TEMP tablespace. If you want it installed in each own tablespace, create it first.
It is a good idea to gather statistics on the new schema:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’PERFSTAT’,CASCADE=>TRUE);
To take a Perfstat Snapshot connect as perfstat and:
EXECUTE statspack.snap;
To create Statspack Report connect as perfstat and provide two snapshots:
@?/rdbms/admin/spreport
A report in text format is created in the directory where it was called.
You can create a job to take snapshots every 30 minutes:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘SP_AUTO_SNAPSHOT’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN statspack.snap; END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=MINUTELY; INTERVAL=30’,
enabled => TRUE,
comments => ‘Take STATSPACK snapshot every 30 minutes’);
END;
/
Check job creation:
SELECT job_name, enabled, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = ‘SP_AUTO_SNAPSHOT’;
To drop the job:
BEGIN
DBMS_SCHEDULER.drop_job(‘SP_AUTO_SNAPSHOT’);
END;
/
And to drop the perfstat package:
<mark style="background-color:#000000" class="has-inline-color has-palette-color-8-color">@?/rdbms/admin/spdrop</mark>