Install

Check if already installed

select * from all_users where username like '%PERF%';
select TABLESPACE_NAME, STATUS from dba_tablespaces where tablespace_name like '%PERF%';
select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like '%PERF%';
select JOB_NAME, JOB_CREATOR, JOB_ACTION from user_scheduler_jobs where JOB_ACTION  like '%statspack%';

Installation

CREATE TABLESPACE perfstat
DATAFILE '/database/dbs1/perfstat.ora' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
@?/rdbms/admin/catdbsyn.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/spcreate.sql
--- Prompt : perfstat, (default sysaux), (default tmp)
--- Scriptable via define default_tablespace='TOOLS'; define temporary_tablespace='TEMP'; define perfstat_password='my_perfstat_password'

If bug, remove and reinstall

Remove

@?/rdbms/admin/spdrop.sql
DROP TABLESPACE perfstat INCLUDING CONTENTS AND DATAFILES;

Usage

Manual snapshot

sqlplus perfstat/perfstat
exec statspack.snap;

Collect levels list

SELECT * FROM statslevel_description ORDER BY snap_level;

Snap with a specific level, i_modify_parameter for permanent change. Level 6 is required for explain plan.

exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

Snapshots listing

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')\"Date/Time\" from statssnapshot,vdatabase;

Snapshots purge

@?/rdbms/admin/sppurge;

Create a report, it'll ask for beginning snap and last snap, and an output file path.

@?/rdbms/admin/spreport.sql

Execution plan for a fiven request, identified by its "Old Hash Value" from the report. Remenber, lelvel 6 snaps and above.

@?/rdbms/admin/sprepsql.sql

Schedule snapshots

Grant perfstat user job creation

sqlplus / as sysdba
grant create job to perfstat;

Then create schedule

connect perfstat/perfstat
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_type => 'STORED_PROCEDURE',
job_name => 'statspack_snap',
job_action => 'statspack.snap',
repeat_interval => 'FREQ=HOURLY; BYHOUR=9,12,15; BYMINUTE=5,15', --- 9h05 9h15 12h05 12h15 15h05 15h15
stop_date => sysdate +1/24/59, --- sysdate + 1 minute
auto_drop => FALSE,
enabled => TRUE,
comments => 'Statspack automated snap');
END;

Disable or enable schedule

BEGIN
dbms_scheduler.disable('statspack_snap');
--- dbms_scheduler.enable('statspack_snap');
END;

Informations about schedules

select * from user_scheduler_jobs;
select * from user_scheduler_job_log order by log_date desc;
select * from user_scheduler_job_run_details;