This content originally appeared on HackerNoon and was authored by Arvind Toorpu
\
Optimizing Oracle Database Queries Using Execution Plans: A Step-by-Step Guide
\ Abstract: Execution plans are indispensable tools for diagnosing and improving SQL query performance in Oracle databases. This guide provides a comprehensive workflow to generate, interpret, and optimize execution plans, with practical examples, actionable insights, and advanced tuning strategies for large-scale datasets.
Table of Contents
- Introduction
- Generating Execution Plans
- Interpreting Execution Plans
- Identifying Performance Bottlenecks
- Query Tuning Strategies
- Practical Optimization Example
- Advanced Tips and Best Practices
- Conclusion
Introduction
Execution plans in Oracle databases reveal how the SQL optimizer processes queries, exposing inefficiencies like full table scans, expensive joins, or sorting operations. With the rise of big data, optimizing these plans is critical for reducing latency, improving scalability, and minimizing resource consumption. This guide bridges theory and practice, offering a systematic approach to tuning queries using execution plans.
Generating Execution Plans
Method 1: Using EXPLAIN PLAN
Generate an estimated plan without executing the query:
EXPLAIN PLAN FOR
SELECT c.customer_id, c.city, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
GROUP BY c.customer_id, c.city
ORDER BY SUM(o.amount) DESC;
-- Retrieve the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Method 2: Oracle SQL Developer (GUI)
- Open SQL Developer.
- Write your query and click Explain Plan (or press
F10). - Use the visual plan tree to analyze operations.
Method 3: Capture Actual Execution Statistics
Include runtime metrics using GATHER_PLAN_STATISTICS:
```sql
SELECT /*+ GATHERPLANSTATISTICS */
c.customerid, c.city, SUM(o.amount) AS totalamount
FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE c.city = 'New York'
GROUP BY c.customerid, c.city
ORDER BY totalamount DESC;
-- Display actual execution data
SELECT * FROM TABLE(DBMSXPLAN.DISPLAYCURSOR(format => 'ALLSTATS LAST'));
---
## Interpreting Execution Plans
### Plan Structure and Hierarchy
Execution plans are **tree structures** where child operations feed data to parent operations.
* **Reading Direction**: **Right-to-left** and **bottom-to-top**.
* **Indentation** = Depth in the tree.
* **Root Node**: The top-left operation (`SELECT STATEMENT`).
#### Sample Execution Plan
For the query:
sql
SELECT c.customerid, c.city, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE c.city = 'New York'
GROUP BY c.customerid, c.city
ORDER BY SUM(o.amount) DESC;
**Resulting Plan**:
sql
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 10000 | 450K | 25468 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 10000 | 450K | 25468 (2)| 00:00:01 |
| 2 | HASH GROUP BY | | 10000 | 450K | 25467 (2)| 00:00:01 |
|* 3 | HASH JOIN | | 5000K | 214M | 24472 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 10000 | 300K | 423 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL| ORDERS | 5000K | 71M | 19742 (1)| 00:00:01 |
#### Visual Representation of Data Flow:
sql
SELECT STATEMENT (Id 0)
└── SORT ORDER BY (Id 1)
└── HASH GROUP BY (Id 2)
└── HASH JOIN (Id 3)
├── TABLE ACCESS FULL CUSTOMERS (Id 4)
└── TABLE ACCESS FULL ORDERS (Id 5)
#### Execution Order:
1. **Leaf Nodes First** (Deepest/Rightmost Operations):
* Id 4: Full scan of `CUSTOMERS` (filtered by `city = 'New York'`).
* Id 5: Full scan of `ORDERS`.
2. **Parent Operations**:
* Id 3: Combines results from Id 4 and Id 5 via a `HASH JOIN`.
* Id 2: Groups data by `customer_id` and `city`.
* Id 1: Sorts the grouped results by `total_amount DESC`.
3. **Root Node**:
* Id 0: Returns the final sorted result.
---
### Key Metrics and Cost Model
| Column | Description |
|----|----|
| **Id** | Step identifier. Child operations execute before parents (e.g., Id 4 → Id 3). |
| **Operation** | Action performed (e.g., `HASH JOIN`, `TABLE ACCESS FULL`). |
| **Name** | Object involved (table/index name). |
| **Rows** | Estimated rows processed at this step (cardinality). |
| **Bytes** | Estimated data size processed. |
| **Cost (%CPU)** | Optimizer’s cost estimate (lower = better). Includes CPU and I/O overhead. |
| **Time** | Estimated runtime for the operation. |
#### How Oracle Calculates Cost
The optimizer estimates cost based on:
* **I/O**: Reading data from disk (e.g., full table scans).
* **CPU**: Processing data (joins, sorting, aggregations).
* **Memory**: Storing intermediate results (e.g., hash tables for joins).
#### Critical Red Flags in the Sample Plan
1. **Full Table Scans** (`TABLE ACCESS FULL`):
* Id 4 and Id 5 scan entire tables. For large tables, this is slow.
* **Fix**: Add indexes on filtered (`city`) or joined (`customer_id`) columns.
2. **High Cost Operations**:
* The `HASH JOIN` (Id 3) contributes 24,472 to the total cost (96% of total).
* **Why?** Joining 5 million rows from `ORDERS` with 10,000 rows from `CUSTOMERS`.
3. **Expensive Sorting**:
* `SORT ORDER BY` (Id 1) adds overhead.
* **Fix**: Indexes on `SUM(o.amount)` or materialized views.
---
## Identifying Performance Bottlenecks
1. **Full Table Scans**
* **Cause**: Missing indexes on filtered or joined columns.
* **Fix**: Create composite or function-based indexes.
2. **High Join Costs**
* **Example**: `HASH JOIN` on tables with 10M+ rows.
* **Fix**: Use `NESTED LOOPS` for indexed small tables.
3. **Sorting Overheads**
* **Indicators**: `SORT GROUP BY`, `SORT ORDER BY`.
* **Fix**: Add indexes on `GROUP BY`/`ORDER BY` columns.
4. **Cardinality Mismatches**
* **Example**: Optimizer estimates 100 rows, but 100K are processed.
* **Fix**: Refresh statistics with `DBMS_STATS.GATHER_TABLE_STATS`.
---
## Query Tuning Strategies
### 1. Index Optimization
* **Covering Indexes**: Include frequently accessed columns.
sql CREATE INDEX idxcustomerscity ON customers(city) INCLUDE (customer_id, name);
* **Partitioning**: Split tables by range, hash, or list (e.g., by `order_date`).
### 2. SQL Rewrites
* Replace `SELECT *` with explicit columns.
* Use `EXISTS` instead of `IN` for subqueries.
* Avoid functions on indexed columns (e.g., `WHERE UPPER(name) = 'JOHN'`).
### 3. Optimizer Hints
Force specific behaviors (use sparingly):
sql
SELECT /*+ INDEX(orders idxorderscustomerid) */
customerid, SUM(amount)
FROM orders
GROUP BY customer_id;
---
## Practical Optimization Example
### Step 1: Create Sample Tables
sql
-- Customers table with 1M rows
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
city VARCHAR2(50)
);
-- Orders table with 5M rows
CREATE TABLE orders (
orderid NUMBER PRIMARY KEY,
customerid NUMBER,
order_date DATE,
amount NUMBER
);
### Step 2: Baseline Execution Plan
sql
SELECT /*+ GATHERPLANSTATISTICS */
c.customerid, c.city, SUM(o.amount) AS totalamount
FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE c.city = 'New York'
GROUP BY c.customerid, c.city
ORDER BY totalamount DESC;
#### Initial Plan Analysis
**Initial Execution Plan**
sql
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 10000 | 450K | 25468 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 10000 | 450K | 25468 (2)| 00:00:01 |
| 2 | HASH GROUP BY | | 10000 | 450K | 25467 (2)| 00:00:01 |
|* 3 | HASH JOIN | | 5000K | 214M | 24472 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 10000 | 300K | 423 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL| ORDERS | 5000K | 71M | 19742 (1)| 00:00:01 |
* **FULL TABLE SCAN** on both tables.
* **Cost**: 25,468.
* **Bottlenecks**: No indexes on `city` or `customer_id`.
### Step 3: Apply Optimizations
1. **Add Indexes**:
sql
CREATE INDEX idxcustomerscity ON customers(city);
CREATE INDEX idxorderscustomerid ON orders(customerid, amount);
2. **Refresh Statistics**:
sql
-- Update statistics for CUSTOMERS
EXEC DBMSSTATS.GATHERTABLE_STATS(USER, 'CUSTOMERS');
-- Update statistics for ORDERS
EXEC DBMSSTATS.GATHERTABLE_STATS(USER, 'ORDERS');
\
### Step 4: Optimized Execution Plan
**Re-run the Query**:
sql
SELECT /*+ GATHERPLANSTATISTICS */
c.customerid, c.city, SUM(o.amount) AS totalamount
FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE c.city = 'New York'
GROUP BY c.customerid, c.city
ORDER BY totalamount DESC;
\
#### Optimized Execution Plan Output:
sql
| Id | Operation | Name | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1502 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 1502 (3)| 00:00:01 |
| 2 | HASH GROUP BY | | 1498 (3)| 00:00:01 |
|* 3 | HASH JOIN | | 1412 (1)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDXCUSTOMERSCITY | 40 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IDXORDERSCUSTOMER_ID | 1363 (1)| 00:00:01 |
```
Optimized Plan Results
- INDEX RANGE SCAN on CUSTOMERS:
Uses idxcustomerscity to filter city = 'New York' efficiently.
INDEX FAST FULL SCAN on
ORDERS:Leverages
idx_orders_customer_idto retrievecustomer_idandamountwithout scanning the entire table.Cost Reduction:
Before: 25,468
After: 1,502 (94% reduction).
Advanced Tips and Best Practices
- Adaptive Query Optimization (Oracle 12c+):
- Let Oracle dynamically adjust execution plans.
- SQL Tuning Advisor:
- Use Oracle’s built-in tool for automated recommendations.
- Monitor Historical Plans:
- Query
DBA_HIST_SQL_PLANfor plan regressions.
Conclusion
Execution plans are the cornerstone of Oracle query optimization. By systematically analyzing operations like full scans, inefficient joins, and sorting steps, developers can achieve dramatic performance improvements. Key takeaways:
- Index Strategically: Align indexes with query predicates.
- Update Statistics Regularly: Ensure accurate cardinality estimates.
- Test Incrementally: Validate changes with
A/Bplan comparisons.
\
Further Reading
- Oracle Documentation: Using EXPLAIN PLAN
- Oracle SQL Tuning Guide
- "Cost-Based Oracle Fundamentals" by Jonathan Lewis
\
This content originally appeared on HackerNoon and was authored by Arvind Toorpu
Arvind Toorpu | Sciencx (2025-02-03T21:26:15+00:00) Optimizing Oracle Database Queries Using Execution Plans: A Step-by-Step Guide. Retrieved from https://www.scien.cx/2025/02/03/optimizing-oracle-database-queries-using-execution-plans-a-step-by-step-guide/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.