Oracle SQL: Understanding Execution Plan and Performance Tuning

Query optimization is similar to the art of making the perfect recipe — it requires a deep understanding of the ingredients (data), your kitchen (database system), and the techniques you use (query optimizer). Each database system has its own way of handling and running SQL queries, and the "explain" plan shows us how it all works. By looking at these plans, we can understand the choices made by the optimizer and make improvements to speed up data retrieval.

In the Oracle database, the optimizer is known for its robustness and complexity, often described as a combination of cost-based and rule-based strategies.

In this article, we will explore how each database generates and utilizes "explain" plans, highlighting the strengths and potential pitfalls of their approaches. Whether you are a database developer, a database administrator, or a data analyst, understanding these mechanisms will empower you to optimize queries effectively, ensuring faster and more reliable data retrieval.

Importance of Explain Plans in Query Optimization

Explain plans are very instrumental in making select queries faster and more efficient. Getting insight and interpretation from them will also give you a hand in optimizing your query speed and resource usage. 

Here's why they are so important:

• Performance insight: Explain plans show the path a query follows to run. This tells us which parts are really slow and where to make improvements.
• Cost analysis: Every operation within an explain plan has a corresponding cost; this cost is an estimate and it is used as a relative metric among the various ways that the query might be executed. The lower the query cost, the faster the query performance.
• Index utilization: Explain plans tell us if the indexes are being used and how they are being used. Proper utilization of indexes may make the processing of a query very fast.
• Join strategies: The explain plans show how to join across tables for queries that involve multiple tables. Knowing this helps in optimizing relationships between tables.
• Troubleshooting: When a query is slow, explain plans are very important for knowing why. In fact, they show precisely where the problem is, making it easier to fix.
• Optimization techniques: In explain plans, we learn different techniques for query optimization that sometimes require rewriting, database structure changes, or even configuration changes.

Definition and Purpose

Basically, an execution plan is a step-by-step explanation of how a database engine will execute the query. It outlines the sequence of operations, the indexes that will be used, and the methods for joining tables. Since generating an explain plan can be a resource-intensive process, it’s crucial to understand its significance.

The primary purpose of an explain plan is to give you insights into the query’s performance. By analyzing the explain plan, you can see where the database might run efficiently or where it might encounter performance bottlenecks. This understanding allows you to identify and address potential issues, helping to optimize your query for better performance.

Key Concepts and Terminology

• Execution Plan (Access Path): The path followed by the database to execute a query. 
• Cost: An estimate of the resources (like CPU and I/O) needed to perform a query. 
• Operation: The specific database action, such as reading from a table or performing an index scan.
• Rows: The estimated number of rows that each operation will handle.
• Filter: Conditions that rows must meet to proceed to the next step in the query processing. 
• Join Method: The type of join used, such as nested loop or hash join.
• Bytes: Provides an estimate of the amount of data (in bytes) that will be processed by each operation in the execution plan.
• Ex. Figure: An example of an explain plan output for a query: DB - Oracle: Tool - PLSQL Developer

In conclusion, lowering the cost of a query typically translates to faster execution times due to reduced resource consumption and more efficient query processing. Similarly, higher selectivity leads to better query performance by enabling more efficient use of indexes and reducing the number of rows processed. 

Generating Explain Plans In Oracle

Using EXPLAIN PLAN

The EXPLAIN PLAN statement in Oracle creates an execution plan for a query.

EXPLAIN PLAN FOR SELECT * FROM employees;

This creates a plan that can be queried, using DBMS_XPLAN.DISPLAY to give the full view.

Using DBMS_XPLAN.DISPLAY

The above query created an explain plan that can be queried, using DBMS_XPLAN.DISPLAY to give the full view. 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Using Oracle SQL Developer Tool

You can also use a shortcut to generate the explain plan using SQL Developer – Press F10.

Practical Examples

Let’s take a look at the customer-order query.

Scenario

A query joining two tables, orders and customers, is running slowly due to missing indexes and suboptimal join methods. We will optimize the query by adding indexes and rewriting the join.

Identify the Slow Query

SELECT o.order_id, c.full_name

FROM orders o JOIN customers c

ON o.customer_id = c.customer_id

WHERE o.order_status = 'COMPLETE';

Step-By-Step Plan Interpretation

Let’s start with the explain plan of the query.

Problem

The query performs a full table scan and uses hash joins, leading to slow performance.

Solution

When you identify full table scans in your query, consider replacing them with index scans. First, check if the necessary indexes already exist; if not, create new indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.

Let’s first try to create the index on join conditions i.e. columns which are part of the join and where clause. 

CREATE INDEX customers_idx0 ON customers (customer_id, full_name);

CREATE INDEX orders_idx1 ON orders (customer_id, order_id, order_status);

Gathering table statistics after creating indexes is crucial for the database optimizer to make informed decisions on query execution plans. Let’s gather table stats.

EXEC DBMS_STATS.gather_table_stats(SYS, 'CUSTOMERS');

EXEC DBMS_STATS.gather_table_stats(SYS, ORDERS);

Now let’s re-check the execution plan after adding these indexes.

Cost is reduced from 8 to 6, which means there is a 25% improvement in cost by replacing the full table scans with index fast full scan and range scans.

General Optimization Strategies

Indexing

Creating indexes will generally make queries run much faster by avoiding data scanning. Proper indexing will make many queries run by seeking only the proper rows.

Query Rewriting

Sometimes, query performance can be improved by rewriting queries to be more effective. Frequently, it is possible to rewrite a query involving complex joins or sometimes subqueries to obtain a more efficient execution plan.

Execution Plan Caching

It then caches the plan for re-execution, thus avoiding the overhead of creating an execution plan every time. In particular, this allows many of the queries to use previously computed plans.

Best Practices

Indexing Strategy

• Identify and create indexes: Identify slow-running queries. Create appropriate indexes to speed up data retrieval. For queries filtering on multiple columns, consider composite indexes to enhance performance.
• Avoid over-indexing: Excessive indexes can degrade performance, especially DMLs. Regularly review and remove unused or redundant indexes.

Query Design

• Simplify queries:  Break down complex queries into simpler parts whenever possible. Use subqueries and temporary tables to manage intermediate results.
• Avoid SELECT *: Explicitly specify required columns in the SELECT statement to reduce data retrieval load.
• Use joins appropriately: Choose INNER JOINs for matching rows and OUTER JOINs only when necessary. Ensure join conditions are based on indexed columns.

Execution Plan Analysis

• Regularly review execution plans: Use the EXPLAIN command (or equivalent tool) to analyze and understand query execution plans. Identify bottlenecks and inefficiencies.
• Look for full table scans: Identify and optimize queries causing full table scans by adding indexes or restructuring the query.
• Monitor cost and cardinality: Pay attention to the estimated cost and cardinality in execution plans to ensure efficient query paths.

 Use Query Hints Wisely

• Direct the optimizer: Use query hints to influence the optimizer's choice when you have better knowledge of the data and workload patterns. Regularly test and validate the impact of hints on query performance, as they may lead to suboptimal plans if data or workload changes.

Conclusion

Understanding and utilizing explain plans is very important for optimizing database queries. By mastering the tips and techniques explained in this article, both database developers and administrators can greatly improve query performance. This, in turn, would enhance the overall efficiency of the database, leading to faster response times and more efficient database management. By focusing on the explain plans, you can easily identify and resolve potential performance bottlenecks, ensuring that your database operates at its best.

For example, consider the case when a database developer/administrator experiences performance problems with some critical report. They could easily identify an expensive full table scan in the execution plan and replace it with an indexed search. As a result, the query execution time was reduced from several hours to just a few minutes. This not only improved the system's responsiveness but also freed up resources for other tasks, demonstrating the practical benefits of leveraging execution plans.

References

1. Oracle Docs
2. Explain Plan
3. Technical br