How to generate AWR, ADDM, STATSPACK reports.

================================
General
================================
AWR, ADDM, STATSPACK, AWRDDRPT reports.

gen_awr.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrrpt.sql

gen_addm.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/addmrpt.sql

gen_ash.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/ashrpt.sql

gen_statspack.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/spreport.sql

#for specific SQL
#@sqlplus perfstat/iwantawr@igt @/software/oracle/111/rdbms/admin/sprepsql.sql

awrddrpt.sql

sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrddrpt.sql

The awrddrpt.sql script is the AWR "diff" report for comparing two AWR reports.

The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.  

This awrddrpt.sql script compares two time periods.  

To do this, the awrddrpt.sql script accepts four snapshot values.

Start and end for the first AWR report.  

Start and end for the second AWR report.  

Once the four snapshot numbers are collected, the awrddrpt.sql script creates two AWR reports and then compares the two reports.  

The awrddrpt.sql script is very useful for comparing performance during two elapsed time periods. 

======================

To see and change  AWR settings:

======================

To see current settings:

SELECT snap_interval, retention FROM DBA_HIST_WR_CONTROL;

To set interval to 60 minutes an retention to 14 days (60*24*14=43200)

EXECUTE DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(INTERVAL => 60,RETENTION => 20160);

================================
perfstat Purge old intervals data
================================

When checking for big tables, following table occupy a lot of space on WORKAREA Tablespace:

Option A - Delete few of the snapshots

sqlplus perfstat/iwantawr@igt

/software/oracle/112/rdbms/admin/sppurge.sql

Warning

~~~~~~~

sppurge.sql deletes all snapshots ranging between the lower and

upper bound Snapshot Id's specified, for the database instance

you are connected to.  Snapshots identified as Baseline snapshots

which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.

Specify the Lo Snap Id and Hi Snap Id range to purge

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for losnapid: 10030

Using 10030 for lower bound.

Enter value for hisnapid: 11949

Using 11949 for upper bound.

Deleting snapshots 10030 - 11949.

begin

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

ORA-06512: at "PERFSTAT.STATSPACK", line 1330

ORA-06512: at "PERFSTAT.STATSPACK", line 1501

ORA-06512: at line 4

Option B - Truncate statspack tables

@$ORACLE_HOME/rdbms/admin/sptrunc.sql

SQL> @/software/oracle/112/rdbms/admin/sptrunc.sql

Warning

~~~~~~~

Running sptrunc.sql removes ALL data from Statspack tables.  You may

wish to export the data before continuing.

About to Truncate Statspack Tables

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If would like to exit WITHOUT truncating the tables, enter any text at the

begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin

the truncate operation, press <return>

Enter value for begin_or_exit:

Entered at the 'begin_or_exit' prompt

... Starting truncate operation

Table truncated.

Table truncated.

Table truncated.

...

...

... Truncate operation complete

Error ORA-20200: Database/Instance 1278761441/1 does not exist in STATS$DATABASE_INSTANCE

This error means that no statistics were gathered in the past.

To create perfstat schema:

@${ORACLE_HOME}/rdbms/admin/spcreate.sql

Step 1.

sqlplus system/xxxxxxxx

GRANT CREATE JOB TO perfstat;

Step 2.

conn perfstat/xxxxxxxx

@${ORACLE_HOME}/rdbms/admin/spauto.sql

EXECUTE statspack.snap;

To gather interval stats now:

CONNECT perfstat/my_perfstat_password

EXECUTE statspack.snap;

To create the job to gather stats in automated way:

SQL> @/software/oracle/193/rdbms/admin/spauto.sql

SQL> @${ORACLE_HOME}/rdbms/admin/spauto.sql

begin

*

ERROR at line 1:

ORA-27486: insufficient privileges

ORA-06512: at "SYS.DBMS_ISCHED", line 9387

ORA-06512: at "SYS.DBMS_ISCHED", line 9376

ORA-06512: at "SYS.DBMS_ISCHED", line 175

ORA-06512: at "SYS.DBMS_ISCHED", line 9302

ORA-06512: at "SYS.DBMS_IJOB", line 196

ORA-06512: at "SYS.DBMS_JOB", line 168

ORA-06512: at line 3

In Oracle 19, the owner of might not have privilege to execute create job, for collecting stats 

To fix it, as system:

GRANT CREATE JOB TO perfstat;

as perfstat

SQL> @/software/oracle/193/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Now, the job was created

Run statspack report for every 10 minutes

SET LINESIZE 120

COL what FOR A40

COL interval  FOR A30

SELECT job, what, interval 

FROM USER_JOBS 

WHERE what LIKE '%stat%';

JOB WHAT             INTERVAL

--- ---------------- -------------------------

124 statspack.snap;  trunc(SYSDATE+1/24,'HH')

BEGIN

  DBMS_JOB.next_date(124, TO_DATE('20211007 14:00:00','YYYYMMDD hh24:mi:ss'));

 DBMS_JOB.interval(124,'TRUNC(SYSDATE+10/1440,''MI'')');

 commit;

END;

/

================================
Run perfstat from crontab
================================

How to run STATSPACK report from crontab?

1. Copy/paste oracle scripts to the local directory.

2. Create a Linux wrapper script:

- set env variables

- populate the parameters for my_spreport.sql

- clean up old report files

3. Create my_spreport.sql, a copy paste from oracle my_spreport.sql, to holde parameters from script, and pass them to sprepins.sql. Normally these parameters are passed from prompt, but here they are passed from script.

4. Create a crontab entry, to run once every hour.

crontab

5 * * * * /software/oracle/oracle/scripts/statspack/cron_statspack.sh

cron_statspack.sh

#!/bin/bash

BAS_ORACLE_LIST=igt

ORA_VER=1120

ORACLE_SID=igt

ORACLE_BASE=/software/oracle

ORACLE_HOME=/software/oracle/112

WORK_DIR=/software/oracle/oracle/scripts/statspack

cd ${WORK_DIR}

DAYS_TO_KEEP=14

get_last_snap_id () {

    sqlplus -s perfstat/iwantawr@igt <<!

    set heading off

    set feedback off

    set pages 0

    select MAX(snap_id) FROM stats_snapshot;

!

}

max_snap_id=$(get_last_snap_id)

echo max_snap_id = $max_snap_id

prev_snap_id=$((max_snap_id-1))

echo prev_snap_id=$prev_snap_id

RUN_DATE=`date "+%Y%m%d"_"%H%M"`

report_name=perfstat_${RUN_DATE}.rep

#sqlplus perfstat/iwantawr@igt /software/oracle/1910/rdbms/admin/spreport.sql

sqlplus perfstat/iwantawr@igt @my_spreport.sql $prev_snap_id $max_snap_id $report_name

find ${WORK_DIR} -type f -name "perfstat_*rep" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

my_spreport.sql

column inst_num  heading "Inst Num" new_value inst_num  format 99999;

column inst_name heading "Instance" new_value inst_name format a12;

column db_name   heading "DB Name"  new_value db_name   format a12;

column dbid      heading "DB Id"    new_value dbid      format 9999999999 just c;

prompt

prompt Current Instance

prompt ~~~~~~~~~~~~~~~~

select d.dbid            dbid

     , d.name            db_name

     , i.instance_number inst_num

     , i.instance_name   inst_name

  from v$database d,

       v$instance i;

define begin_snap  = &1;

define end_snap    = &2;

define report_name = &3;

@@sprepins.sql

EXIT;

Bug in perfstat - Data Guard is listed as a main wait event
After running spreport.sql Data Guard is listed as a main wait event

Top 5 Timed Events                            Avg %Total

~~~~~~~~~~~~~~~~~~                           wait   Call

Event                      Waits  Time (s)   (ms)   Time

-------------------------- ------ -------- ------ ------

Data Guard: Timer               6      181  30208   30.6

Data Guard: Gap Manager         3      181  60414   30.6

pman timer                     58      174   3008   29.4

CPU time                                15           2.5

log file sync                 653       13     20    2.2

"Data Guard: Timer", "Data Guard: Gap Manager", "pman timer" - are idle events that should not be listed as wait events

Issue is due to a bug #Bug 28523746 – STATSPACK: TOP 5 TIMED EVENTS CONTAINS IDLE WAIT EVENTS

Solution:

Update the table STATS$IDLE_EVENT 

INSERT INTO STATS$IDLE_EVENT

SELECT name FROM V$EVENT_NAME WHERE wait_class='Idle'

MINUS

SELECT event FROM STATS$IDLE_EVENT;

commit;

==========================

Error ORA-20200: Database/Instance does not exist

==========================

ERROR at line 1:

ORA-20200: Database/Instance 1278761441/1 does not exist in

STATS$DATABASE_INSTANCE

ORA-06512: at line 23

This error can occur if spreport.sql is ran before any snapshots have been taken. 

Try creating a snapshot using execute statspack.snap and rerun spreport.sql.

  

sqlplus perfstat/xxxxxx@igt  

select * from stats$database_instance;

no rows selected  

EXECUTE statspack.snap;

select * from stats$database_instance;

--Data is there

==========================

Drop duplicate statspack.snap job

==========================

sqlplus perfstat/xxxxxx@igt

 

COL SCHEMA_USER FOR A30

COL WHAT FOR A50

SELECT job, schema_user 

  FROM DBA_JOBS 

 WHERE UPPER(what) LIKE '%STATSPACK%';

BEGIN

 DBMS_JOB.remove(21);

 commit;

END;

/

==========================

Automate Delete

==========================

Step A. 

cp /software/oracle/1910/rdbms/admin/sppurge.sql /software/oracle/1910/rdbms/admin/sppurge.sql_orig

Edit existing file /software/oracle/1910/rdbms/admin/sppurge.sql

Add the lines in blue
In this example: purge data older than 30 days

prompt

prompt

prompt Snapshots for this database instance

prompt ====================================

select s.snap_id

     , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')    snap_date

     , s.baseline

     , s.snap_level                                      "level"

     , di.host_name                                      host_name

     , s.ucomment

  from stats$snapshot s

     , stats$database_instance di

 where s.dbid              = :dbid

   and di.dbid             = :dbid

   and s.instance_number   = :inst_num

   and di.instance_number  = :inst_num

   and di.startup_time     = s.startup_time

 order by db_name, instance_name, snap_id;

-------------------------------

-- Populate HiSnapId and HiSnapId

-------------------------------

COL min_snap_id new_val LoSnapId

COL max_snap_id new_val HiSnapId

SELECT MIN(SNAPSHOTS.snap_id) min_snap_id,

       MAX(SNAPSHOTS.snap_id) max_snap_id

FROM STATS$SNAPSHOT SNAPSHOTS

WHERE SNAPSHOTS.snap_time < TRUNC(SYSDATE) - 30;

SELECT &&LoSnapId as low_value from DUAL;

SELECT &&HiSnapId as high_value from DUAL;

--------------------------------

Step B.
Create a crontab task 

purge_perfstat.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

ORACLE_HOME=/software/oracle/1910

TNS_ADMIN=${ORACLE_HOME}/network/admin

PATH=${PATH}:${ORACLE_HOME}/bin

sqlplus perfstat/iwantawr@igt @/software/oracle/1910/rdbms/admin/sppurge.sql

crontab

4 6 * * * /software/oracle/oracle/scripts/awr/purge_perfstat.sh