— Go to Directory location for Statspack Install scripts on the VMDB host —
cd /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin
— From cdb$root —
sqlplus “/as sysdba”
@spcreate.sql
Enter value for perfstat_password: P@ssword1234#_
— Check Level of Statistics —
SELECT * FROM stats$level_description ORDER BY snap_level;
— Gather the Stats of PERFSTAT schema before we begin —
exec dbms_stats.gather_schema_stats(‘PERFSTAT’);
— Connect with the PERFSTAT user and generate a sample snapshot —
sqlplus perfstat/Statspack1234#_
SQL> exec statspack.snap;
— Create the Statspack Auto job (it creates a snapshot every one hour) —
SQL> @?/rdbms/admin/spauto.sql
— Verify the jobs —
SQL> alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;
SQL> select job, what, LAST_DATE, NEXT_DATE, TOTAL_TIME, BROKEN, FAILURES from dba_jobs where SCHEMA_USER=’PERFSTAT’;
Take another snapshot to get the two snapshots to generate the statspack report:
SQL> exec statspack.snap;
— Check the snapshots in system view stats$snapshot —
SQL> select name, snap_id, to_char(snap_time, ‘DD/MM/YYYY HH24:MI:SS’) “Snapshot Time” from stats$snapshot,v$database;
NAME SNAP_ID Snapshot Time
ABC 3 08/02/2021 17:00:18
ABC 1 08/02/2021 16:52:55
ABC 2 08/02/2021 16:54:07
— Generate the report between the created snapshots run the script ––
SQL> @?/rdbms/admin/spreport.sql