Have you ever encountered critical issues while operating an Oracle Database, such as “a specific process is slow” or “the screen suddenly stopped responding”? To eliminate system bottlenecks and improve response times, it is essential to conduct an accurate analysis of the current state and follow proper tuning procedures.
This article provides beginner to intermediate Oracle DBAs and application developers with a clear explanation of the fundamental knowledge of Oracle performance tuning, five actionable steps that can be implemented immediately in the field, and how to choose between the essential analysis tools, AWR and Statspack, accompanied by text diagrams.
- Conclusion: The Shortest Procedure for Performance Improvement (To-Do List)
- What is Performance Tuning?
- How to Proceed with Performance Tuning (5 Steps)
- Troubleshooting (Typical ORA Errors and Countermeasures)
- Operations, Monitoring, and Security Notes
- FAQ
- Summary: 5 Golden Rules of Performance Improvement
Conclusion: The Shortest Procedure for Performance Improvement (To-Do List)
When performance issues occur, do not change settings blindly. Narrow down the bottleneck step-by-step according to the following procedure:
- Step 1: Understand the Symptoms – Quantitatively identify the delay timeframe and the scope of impact using AWR, Statspack, or V$ views.
- Step 2: Identify the Bottleneck – Identify high-load SQL statements (Top SQL) that consume excessive resources from views such as V$SQL.
- Step 3: Analyze the Cause – Obtain the execution plan (EXPLAIN PLAN) of the target SQL and check for full table scans or inefficient joins.
- Step 4: Implement Improvements – Create optimal indexes, gather the latest optimizer statistics, and rewrite the SQL statements themselves.
- Step 5: Verify the Effects – Use SQL Trace (tkprof) or compare execution times to prove the numerical improvement effects before and after the implementation.
What is Performance Tuning?
Mechanism and Definition
Performance tuning in Oracle refers to a series of optimization processes aimed at shortening system response times and improving throughput (the amount of processing per unit of time) by conducting a “current state analysis” of the database operation, followed by “cause identification,” and applying the “optimal improvement.”
Approach Based on a 3-Layer Structure
Database system loads and bottlenecks are categorized and isolated into the following three layers:
+-----------------------------------------------------------------------+
| 1. Application Layer : SQL, PL/SQL, Application Logic |
+-----------------------------------------------------------------------+
| 2. Database Layer : Optimizer Statistics, Execution Plan, |
| Index, Memory |
+-----------------------------------------------------------------------+
| 3. OS / Infrastructure / I/O Layer : CPU, Physical Memory, |
| Disk I/O, Network |
+-----------------------------------------------------------------------+
Quick Note for Beginners
It is said that approximately 80% of the overall tuning effect originates from improvements in the “Application Layer (SQL)” and the “Database Layer.” Since modifying OS parameters or hardware involves high risks and often yields limited effects, the golden rule is to start by reviewing individual SQL statements and index designs.
How to Proceed with Performance Tuning (5 Steps)
Performance improvement in Oracle can be implemented safely without backtracking by following these five stages in order:
[1. Understand the Symptoms] Quantify which process is slow and by how much.
↓
[2. Identify the Bottleneck] Locate whether the cause lies in SQL, disk I/O, CPU, or locks.
↓
[3. Analyze the Cause] Check the execution plan and identify inefficient access mechanisms.
↓
[4. Implement Countermeasures] Add indexes, gather optimizer statistics, and modify SQL.
↓
[5. Verify the Effects] Compare numerical values before and after tuning to prove effectiveness.
Step 1: Understand the Symptoms — Which Process is Slow?
The first step in tuning is to convert subjective statements like “it is slow” into objective numerical values (seconds, wait times). Select the optimal investigation method below based on your environment (edition and licensing).
Comparison of Main Investigation Methods
| Tool Name | Overview | Supported Editions |
| AWR | A standard feature that automatically performs time-series analysis on performance statistics for the entire database. | Enterprise Edition (*Requires the Oracle Diagnostic Pack option) |
| Statspack | A free, lightweight version of AWR. Analyzes snapshots captured manually or via jobs. | Standard Edition 2 / Enterprise Edition |
| V$ Views | Allows direct monitoring of real-time session and SQL information remaining in memory. | All Editions (Available in both SE2 and EE) |
Detailed Comparison Between AWR and Statspack
| Comparison Item | AWR (Automatic Workload Repository) | Statspack |
| License | Paid (EE + Oracle Diagnostic Pack) | Free (No additional cost) |
| Data Collection | Automatically collected by a background process (MMON) | Executed manually via dedicated scripts or periodically via DBMS_JOB |
| Analysis Targets | Comprehensive information including OS statistics and ASH (Active Session History) | Basic indicators such as major system events, wait events, and Top SQL |
| Output Format | HTML, Text (Strong integration with GUI screens like Enterprise Manager) | Text only (Generated as a file by executing a dedicated SQL script) |
In a Standard Edition 2 environment, Statspack is the only choice. In an Enterprise Edition environment that meets the licensing requirements, using AWR is strongly recommended as it performs more detailed analysis automatically.
Step 2: Identify the Bottleneck
Bottlenecks that delay system processing are mainly classified into the following four types:
- SQL Execution Delays: Large volume data scans caused by inappropriate execution plans.
- Disk I/O Bottlenecks: Excessive reads from physical disks when data cannot fit into memory (buffer cache).
- High CPU Load: Heavy utilization caused by large-scale sort operations, hash joins, or the frequent use of inefficient user-defined functions.
- Lock Contention: Update conflicts on the same data (wait events such as enq: TX – row lock contention).
Utilizing V$ Views to Identify High-Load SQL in Real Time
The following SQL statement identifies the top 10 SQL statements (Top SQL) currently consuming large amounts of resources in the database, based on cumulative execution time (elapsed_time).
Because this extracts data from dynamic performance views (V$SQL), executing it requires the SELECT ANY DICTIONARY privilege or a connection as the SYS or SYSTEM user. Additionally, in a multitenant environment (CDB/PDB), connect to the target PDB before execution.
SELECT sql_id, elapsed_time, executions, sql_text
FROM v$sql
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
SQL Intent and Result:
This retrieves 10 records from the SQL statements remaining in the cache, ordered by total processing time in descending order. This allows you to pinpoint the highest-priority sql_id (a unique identifier for SQL) that should be targeted for tuning.
Step 3: Analyze the Cause — How to Read SQL Execution Plans
Most reasons why a specific SQL statement is slow lie in the inefficiency of its “execution plan,” which is the internal data retrieval procedure.
Basic Steps to Obtain an Execution Plan
Check the current execution plan estimated by the optimizer (Oracle’s access path determination feature).
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SQL Intent and Result:
The EXPLAIN PLAN statement analyzes the access path of the target SQL and stores it in the PLAN_TABLE. The subsequent DBMS_XPLAN.DISPLAY function formats the results into a human-readable table structure and outputs them.
Important Keywords to Watch in Execution Plans
| Internal Operation (Operation) | Processing Mechanism | Tuning Checkpoints |
| TABLE ACCESS FULL | Scans all data in the table from beginning to end (full table scan). | Check whether appropriate indexes matching the search conditions are missing. |
| INDEX RANGE SCAN | An efficient access method that scans only a specific range of an index. | Evaluate whether the index is utilized correctly by the filtering conditions. |
| NESTED LOOPS | Joins tables by looping through one table and looking up the other (nested loops). | Ensure the driving table (outer) is small and the inner table has an index on the join key. |
| HASH JOIN | Hashes the join keys into memory to join large volumes of data at high speed. | Since this consumes memory (PGA), verify if the optimizer statistics are up to date. |
Step 4: Implement Improvements
Based on the cause of the bottleneck, implement one (or a combination) of the following improvement measures:
1. Creating an Index
Create an appropriate B-tree index on the columns specified in the WHERE clause filtering conditions to avoid full table scans.
CREATE INDEX idx_emp_dept ON employees (department_id);
SQL Intent and Result:
This creates an index on the department_id column of the employees table. From then on, searches using this column as a condition switch from a full table scan to an index range scan (INDEX RANGE SCAN), drastically reducing the I/O volume.
2. Gathering Optimizer Statistics
If optimizer statistics are stale, the optimizer may miscalculate data volumes and select an inefficient execution plan. Re-gather statistics manually to reflect the latest data state.
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');
SQL Intent and Result:
This measures the latest record counts and data distribution for the EMPLOYEES table within the SCOTT schema and saves them in the data dictionary. This enables the optimizer to choose a correct execution plan based on the latest actual data.
3. Rewriting the SQL Itself
- Eliminating Unnecessary Columns: Stop using SELECT * and explicitly specify only the column names actually required by the application. This reduces network and memory loads.
- Removing Redundant Nesting: If subqueries, IN clauses, or EXISTS clauses are intricately intertwined, consider whether they can be rewritten using joins (JOIN).
Step 5: Verify the Effects — Tuning is Not “Done Once Implemented”
After implementing countermeasures, confirm how much the actual processing time has been shortened using quantitative values. To track detailed behavior per session, use SQL Trace.
Steps to Obtain and Analyze SQL Trace
Execute the following steps within the session running the target SQL under investigation:
ALTER SESSION SET SQL_TRACE = TRUE;
SELECT * FROM employees WHERE department_id = 10;
ALTER SESSION SET SQL_TRACE = FALSE;
SQL Intent and Result:
This outputs a detailed log containing disk read counts, CPU time, and parse counts associated with the execution of the target SQL into a .trc file (trace file) on the server.
Generated trace files are in text format but are highly difficult to read as they are. Therefore, format them on the OS command line using the standard Oracle utility command tkprof.
tkprof ora_12345.trc output.txt sort=elapsed
Command Intent and Result:
This reads the unformatted log file ora_12345.trc and generates a clean analysis report file output.txt sorted by longest execution time (sort=elapsed).
Troubleshooting (Typical ORA Errors and Countermeasures)
These are the causes and specific resolution procedures for errors that easily occur during tuning operations or on highly loaded Oracle databases.
| Error Code | Main Cause | Verification Method | Reference-based / Safe Countermeasure |
| ORA-01555 snapshot too old | A long-running SQL lost track of past data (UNDO) that was overwritten by updates from other sessions. | Check the alert log and identify the processing time of the SQL where the error occurred. | Run the batch job outside of high-update hours, or extend the value of the UNDO_RETENTION parameter (Requires DBA privileges). |
| ORA-01652 unable to extend temp segment | The capacity of the temporary tablespace (TEMP) was exhausted due to large-scale sort operations or hash joins. | Check V$SORT_USAGE and identify the SQL consuming excessive space. | Reduce sort processing (ORDER BY, DISTINCT) on the SQL side, or configure/add auto-extension (AUTOEXTEND ON) for the temporary files (TEMPfile). |
Operations, Monitoring, and Security Notes
When performing performance tuning, you must anticipate the impact on the entire system in advance.
- The Pitfall of Adding Indexes: Adding an index speeds up search operations (SELECT), but because index reorganization occurs during data additions, updates, and deletions (INSERT/UPDATE/DELETE), the performance of write operations decreases slightly. It also consumes additional disk capacity.
- Risks of Gathering Statistics: Performing large-scale statistics gathering (DBMS_STATS) while online poses risks of lock contention on the data dictionary or sudden slowing of other queries due to abrupt changes in execution plans (plan instability). In principle, this should be executed at night or during maintenance windows.
- How to Revert to the Original State: To drop an index and revert to the original state, execute the following command:
DROP INDEX idx_emp_dept;
FAQ
Q1. Is it acceptable to output AWR reports in a Standard Edition 2 (SE2) environment?
A1. Technically, the features might be invokable, but using AWR requires purchasing a paid option license called “Oracle Diagnostic Pack” in addition to the Enterprise Edition contract. Using it in an SE2 environment violates the license agreement (and is subject to detection). Therefore, always use the free Statspack feature in SE2.
Q2. I gathered optimizer statistics, but the execution plan did not change. Why?
A2. If the data volume in the target table is extremely small, the optimizer may determine that a full table scan (TABLE ACCESS FULL) is faster than using an index. Additionally, initialization parameters like OPTIMIZER_MODE or hint clauses specified inside the SQL might be taking precedence.
Q3. How can I execute Statspack or other scripts from SQL*Plus using a user whose password contains the “@” symbol?
A3. Writing a connection string like scott/tiger@pwd@orcl prevents Oracle from distinguishing where the password ends and where the connect identifier (net service name) begins, resulting in a connection error. If the password contains special characters, escape the entire password section with double quotes, and wrap the entire string in single quotes as follows:
sqlplus ‘scott/”tiger@pwd”@orcl’
Q4. I want to replicate the same table structure as the production environment into a test environment for tuning purposes.
A4. Use the following verification sample DDL to create objects with the identical structure in your verification schema and perform your tests:
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);
INSERT INTO employees VALUES (1, 'Taro', 'Yamada', 10, 500000);
INSERT INTO employees VALUES (2, 'Hanako', 'Suzuki', 20, 550000);
INSERT INTO employees VALUES (3, 'Jiro', 'Tanaka', 10, 480000);
INSERT INTO employees VALUES (4, 'Akira', 'Sato', 30, 620000);
COMMIT;
Summary: 5 Golden Rules of Performance Improvement
- Thoroughly Understand the Current State via Data: Do not change settings based on guesswork; obtain quantitative evidence from AWR, Statspack, or V$ views.
- Start from the SQL (Application Layer): Begin by reviewing individual SQL statements, which offer the largest margin for improvement and have the fewest side effects on the entire system.
- Examine Execution Plans and Statistics Together: Always question why a particular access path was chosen and whether the optimizer’s decision-making data (statistics) is up to date.
- Choose Tools Suited for the Environment: Comply with environmental constraints—use Statspack for SE2 and AWR for EE (with appropriate licensing).
- Always Verify with Numbers After Countermeasures: Measure processing times, block read counts, and CPU time. Tuning is only complete once the improvement effects are “visualized.”
This article is explained based on Oracle Database 19c (screens or default values may vary in other versions).
[reference]
Oracle Database Database Performance Tuning Guide, 19c
コメント