Generating Statspack, AWR and ADDM reports

Generating Statspack, AWR and ADDM reports

 

STATSPACK

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis.

The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

TO GENERATE STATSPACK REPORT , FOLLOW THE BELOW STEPS :

Step 1:-Connect to the db as sys user and check out for the value of the parameter timed_statistics as true

SQL> show parameter timed_statistics;

If the value  false set as true

SQL> alter system set timed_statistics=true scope=both;

 

Step 2;-Check whether the user perfstat user already exists. If the user is frequently used, don’t drop it.

SQL>select username from dba_users;

Step 3:- Otherwise run the script spdrop ($ORACLE_HOME\rdbms\admin\spdrop.sql) to drop the existing perfstat user.

SQL>@$ORACLE_HOME/rdbms/admin/spdrop.sql

Step 4:- Run the script spcreate to create the perfstat user.($ORACLE_HOME\rdbms\admin\spcreate.sql)

 

Step 5:- It will ask for the user’s password, default and temp tablespace names

SQL>@$ORACLE_HOME/rdbms/admin/spcreate.sql

SQL> set echo off verify off showmode off feedback off;

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: oracle

oracle

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data.  Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS

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

STATSPACK DEFAULT TABLESPACE

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

SYSAUX                         PERMANENT

*

USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: users

Using tablespace USERS as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE

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

TEMP                           TEMPORARY             *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.

SQL> set echo off;

Creating Package STATSPACK...

Package created.

No errors.

Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>

SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database

SQL> alter session set "_oracle_script" = FALSE;

Session altered.

Step 6:- Connect to the database as the perfstat user

SQL>conn perfstat/oracle

Step 7:- Run the PL/SQL procedure:

SQL>execute statspack.snap

Step 9:-Wait for 20 min’s before taking the second snapshot, i.e step-7 again.

SQL> conn perfstat/oracle

Connected.

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

SQL>  execute statspack.snap

PL/SQL procedure successfully completed.

SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql

Step 9:- Finally run the script spreport ($ORACLE_HOME\rdbms\admin\spreport.sql) to generate the report, where it will be asking for the snapshot ids (choose the appropriate ids and create the report).

SQL>@$ORACLE_HOME/rdbms/admin/spreport.sql

Listing all Completed Snapshots

                                                       Snap

Instance     DB Name        Snap Id   Snap Started    Level Comment

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

livedb       LIVEDB               1 24 Sep 2021 23:49     5

                                  2 25 Sep 2021 00:04     5

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

Example of snap 1 & 2 differences

SQL Memory Statistics  DB/Inst: LIVEDB/livedb  Snaps: 1-2

                                   Begin            End         % Diff

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

   Avg Cursor Size (KB):           23.16          22.97           -.82

 Cursor to Parent ratio:            1.24           1.25            .73

          Total Cursors:           1,080          1,063          -1.60

          Total Parents:             872            852          -2.35

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

init.ora Parameters  DB/Inst: LIVEDB/livedb  Snaps: 1-2

***************************************************************************

TO GENERATE AWR REPORT , FOLLOW THE BELOW STEPS :

 

Step 1:- Login to the database as sys user

Step 2:-  Run the script : $ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Step 3:- Give the type of the report as HTML or TEXT

Specify the Report Type

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

AWR reports can be generated in the following formats.      Please enter the

name of the format at the prompt.  Default value is 'html'.

'html'               HTML format (default)

'text'                Text format

'active-html'     Includes Performance Hub active report

Enter value for report_type: text

Type Specified:  text

Step 5:- From the list of snap IDs shown, enter value of the required begin ID and end ID. This should be depending on the peak hours of the application.

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 100

Begin Snapshot Id specified: 100

Enter value for end_snap: 105

Specify the Report Name

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

The default report file name is awrrpt_1_100_105.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_100_105.txt

 

***********************************************************************

 

TO GENERATE ADDM REPORT , FOLLOW THE BELOW STEPS :

 

Step 1:- Login to the database as sys user

Step 2:- Run the script

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Step 3:- From the list of snap IDs shown, enter value of the required begin ID and end ID. This should be depending on the peak hours of the application.

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance

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

   DB Id    DB Name      Inst Num Instance

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

 3038906043 LIVEDB              1 livedb

Instances in this Workload Repository schema

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

  DB Id      Inst Num   DB Name      Instance     Host

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

* 3038906043     1      LIVEDB       livedb       vignesh.loca

Using 3038906043 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

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

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

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

livedb       LIVEDB             110  24 Sep 2021 22:23    1

                                111  24 Sep 2021 23:30    1

                                112  25 Sep 2021 00:30    1

                                113  25 Sep 2021 01:30    1

                                114  25 Sep 2021 02:30    1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 110

Begin Snapshot Id specified: 110

Enter value for end_snap: 112

End   Snapshot Id specified: 112

Specify the Report Name

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

The default report file name is addmrpt_1_110_112.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name addmrpt_1_110_112.txt

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

          ADDM Report for Task 'TASK_163'

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

Analysis Period

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

AWR snapshot range from 110 to 112.

Time period starts at 24-SEP-21 10.23.30 PM

Time period ends at 25-SEP-21 12.30.58 AM

Analysis Target

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

Database 'LIVEDB' with DB ID 3038906043.

Database version 19.0.0.0.0.

ADDM performed an analysis of instance livedb, numbered 1 and hosted at

vignesh.localdomain.

Activity During the Analysis Period

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

Total database time was 22 seconds.

The average number of active sessions was 0.

There are no findings to report.

          Additional Information

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

Miscellaneous Information

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

There was no significant database activity to run the ADDM.

The database's maintenance windows were active during 100% of the analysis

period.

End of Report

Report written to addmrpt_1_110_112.txt

SQL>

 *************************************************************************