How to Use Oracle AWR in Standard Edition Using Statspack

Hi,

I will explain you How to Use Oracle AWR in Standard Edition Using Statspack.

 

 

Performance problems are mostly solved using Oracle AWR ( Automatic Workload Repository ). But Oracle AWR report is available in the Enterprise edition.

If you use Oracle Standart edition database, you need Oracle AWR to analyse Performance Problems.

You can use StatsPack (stands for Statistics Package)  in Standart edition. Statspack is like AWR report and set of Oracle packages. You can take statspack report with  snapshots of performance-related data that occurred between any two snapshots.

 

To install statspack in Standart edition, execute spcreate.sql under $ORACLE_HOME/rdbms/admin directory.

You can store statspack data in the different tablespace. I have create new tablespace for statspack.

 

You can install statspack like following.

 

[oracle@MehmetSalih admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 19 11:15:50 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> CREATE TABLESPACE StatsPack DATAFILE
'/u01/Datafile/Deveci/statspack01.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
2 3 4 5 6 7
Tablespace created.

SQL>

 

 

 

 

 

 

SQL> @spcreate.sql;

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: welcome1
welcome1


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
------------------------------ --------- ----------------------------
BAKIYE_DATA PERMANENT
GECICI PERMANENT
GENEL_TAHAKKUK_DATA PERMANENT
LOG_TABLE_SPACE PERMANENT
MUHASEBE_DATA PERMANENT
ORT_SICIL PERMANENT
ORT_SICIL_INDX1 PERMANENT
STATSPACK PERMANENT
SU_INDEX PERMANENT
SU_TABLE_SPACE PERMANENT
SYSAUX PERMANENT *

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
USERS PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: STATSPACK

Using tablespace STATSPACK as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------

 

 

 

Create a job with scheduled interval (1 hour) using spauto.sql.

 

SQL> @spauto.sql;

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
----------
24


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
---------- ---------- --------
24 19/04/2019 12:00:00

SQL>
SQL>

 

 

To create manual snapshot for statspack, execute following command.

SQL> exec perfstat.statspack.snap;

PL/SQL procedure successfully completed.

 

You can modify snap_level to 7  for more information in the statspack.

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

 

 

 

To create Statspack report from snapshots, use the spreport.sql like following.

SQL> @spreport.sql

Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3210188289 DEVECI 1 DEVECI

SP2-0311: string expected but not found


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3210188289 1 DEVECI DEVECI oracdb

Using 3210188289 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 all Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
DEVECI DEVECI 1 19 Apr 2019 11:18 5
2 19 Apr 2019 12:02 7
3 19 Apr 2019 11:20 7
4 19 Apr 2019 11:25 7
5 19 Apr 2019 13:06 7
6 19 Apr 2019 14:00 7
7 19 Apr 2019 15:00 7

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2

Enter value for end_snap: 7
End Snapshot Id specified: 7

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_2_7. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: test_report.html


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

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

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



End of Report ( test_report.html )

 

 

Then Statspack report is created, you can use it to analyze Oracle database Performance.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *