Install Statspack for Performance Tuning on OCI VMDB Standard Edition Databases

— 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

Category: CloudDatabaseOraclePerformanceScriptTuning

Tags:

Leave a Reply

Article by: Shadab Mohammad