Performance and Tuning in Oracle Standard Edition
- Vijayganesh Tirupattur Sivaprakasam
- Mar 15
- 6 min read
Updated: Mar 18
When Oracle 10g was released, Oracle introduced the Automatic Manageability feature for the first time. This helped address performance-related questions by using the automatic workload repository (AWR). The data is collected and available inside the database itself, and AWR reports help us interpret this data in a much easier and human-readable way and as a nice HTML report. When I say human readable, it means that even to read this report, a sound database administration and good understanding of Oracle architecture and associated processes and events are required.
Before we delve deeper into this topic, we need to understand the licensing, as this would ensure that you are not breaching any licensing agreements. AWR is a great tool, but to use it, you need to buy a “Diagnostic Pack”, which is available only for the enterprise edition.
To determine whether you are allowed to use this, you need to check with your procurement department to confirm whether Enterprise Edition was bought with a diagnostic pack and tuning pack.
To confirm the use of this feature, you can check the parameter “CONTROL_MANAGEMENT_PACK_ACCESS” .
This parameter has three values
DIAGNOSTIC+TUNING → This means the tuning and diagnostic pack has been enabled, and you can use AWR and all the features listed in the table below.
DIAGNOSTIC → Only diagnostic pack is available. You can still use AWR, but cannot use any tuning recommendations or information like SQL Profile, SQL Tuning and Access advisors
NONE → this is default; you cannot use any of these features and are disabled.
PS: It's very important to note that this license is required for all methods of accessing the data through OEM consoles, desktop widgets, CLI APIs, or even directly accessing underlying data.
To learn more about this, please refer to the licensing link. It's important to note that it is always better to check your licenses and the availability of these packs before accessing them than breaching the licensing agreements.
Now, how do I tune my database that runs on standard edition 2? First and foremost, check your database's management pack setting. For 21c standard edition, it should look like the one below.
The diagnostic and tuning pack is unavailable for the standard edition, so this cannot be added separately and the parameters should not be changed.
To check the performance of my standard edition database, statspack is a tool that can be used, and then you can tune the database based on the report you get. Lets look at the steps to create the statspack. This tool has enough performance data for us to understand what is going on with the database.
The first step is determining if statspack has already been installed on your system. Run the below query. If you get the below result, this means the statspack is not installed.
If it's already installed, you will get a similar result.
Now let's look at how to install statspack and collect data.
#1. Create a separate tablespace to store the performance data. This will ensure that the data is stored separately.
#2. Install statspack package by running spcreate.sql under $ORACLE_HOME/rdbms/admin
SQL> @?/rdbms/admin/spcreate.sql
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: xxxx
xxxx
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
----------------------------
PERF_DATA PERMANENT
SYSAUX PERMANENT
*
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: PERF_DATA
Using tablespace PERF_DATA 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.
.....................
......................
Session altered.Also ensure that the parameter TIMED_STATISTICS is set to TRUE
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ -----------
timed_statistics boolean TRUE#3. Take the snapshot of the statistics manually
SQL> conn perfstat/xxxx
Connected.
SQL> sho user
USER is "PERFSTAT"
SQL>
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.#4. The easiest way to run the snapshots is to automate them using dbms_jobs. Oracle provides a script for this. This job runs snapshots every hour, but the interval procedure from the dbms_job package can be changed.
SQL> @?/rdbms/admin/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
----------
2
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 40
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
2 14-MAR-25 12:00:00
#5. The next step is to create a report of the performance metric collected with the statspack. To create a report we need a minimum of two snapshots so that metrics can be analyzed and figure out what is happening on the database between these two timestamps. Let me generate some data in my system and see if it makes the report interesting.
SQL> @?/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2294483228 ORCL21C 1 orcl21c
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
2294483228 1 ORCL21C orcl21c dbvel71.orac
lekiwi.co.nz
Using 2294483228 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
------------ ------------ --------- ----------------- ----- --------------------
orcl21c ORCL21C 1 13 Mar 2025 18:50 5
2 14 Mar 2025 11:11 5
3 14 Mar 2025 12:00 5
4 14 Mar 2025 12:00 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2
Enter value for end_snap: 4
End Snapshot Id specified: 4The report is generated as a text file in the local directory. Below is a screenshot of a sample report.
#6. Next step is to ensure that the data is maintained and old data is purged. This should be done to avoid the tablespace filling up and causing unnecessary issues to the database. The SQL sppurge.sql helps in removing old snapshots of the statspack.
You can also truncate all the data in the underlying statspack tables directly by using sptrunc.sql and uninstall using spdrop.sql. All the SQL files are under $ORACLE_HOME/rdbms/admin directory.
The data captured by statspack are below.
Basic database and host information (hostname, DB Id, startup time, platform cpu etc.)
The cache sizes (buffer and sharedpool) and also load profile of the database.
Instance efficiency how much was buffer waits, library hits and soft parses etc.
The top five timed events. This data and events will provide a overall indicator what is happening in the database what your database is waiting on. The list of events are listed here
Next section has Time model system stats based on different conditions and criteria and is arranged based on specific conditions.
SQL statements run on the database ordering by cpu,elapsed time, gets, executions etc.
Instance activity statistics and OS statistics.
In conclusion, AWR might provide us reports in a nice html format that can be viewed in a browser, but at the same time statspack for standard edition provides us a report that equally good and gives us a great insight into the performance of the database and also lead us to resolving pressing problems in the database.