I will explain you How to Use Oracle AWR in Standard Edition Using Statspack. Let’s review the Statspack in Standard Edition vs Oracle AWR.
Statspack in Standard Edition vs Oracle AWR
Performance problems are mostly solved using Oracle AWR ( Automatic Workload Repository ). But Oracle AWR report is available in the Enterprise edition.
If you want to learn more details about Oracle AWR report, then read the following post to learn it.
What is the Oracle AWR ( Automatic Workload Repository ) Report
If you use Oracle Standart edition database, you need Oracle AWR to analyse Performance Problems.
Statspack Report in Oracle
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.
Standart edition AWR Report
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.
Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.
Performance Tuning and SQL Tuning Tutorial in the Oracle Database
Do you want to learn Oracle Database for Beginners, then Click and read the following articles.
Oracle Database Tutorials for Beginners ( Junior Oracle DBA )