DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Copy SQL Execution Plan from One Database to Another in Oracle 19c
  • Exploring the New Boolean Data Type in Oracle 23c AI
  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server

Trending

  • A Guide to Container Runtimes
  • Docker Model Runner: Streamlining AI Deployment for Developers
  • Internal Developer Portals: Modern DevOps's Missing Piece
  • Unlocking AI Coding Assistants Part 2: Generating Code
  1. DZone
  2. Data Engineering
  3. Databases
  4. Oracle SQL: Understanding Execution Plan and Performance Tuning

Oracle SQL: Understanding Execution Plan and Performance Tuning

In this article, we will explore how each database generates and utilizes "explain" plans, highlighting the strengths and potential pitfalls of their approaches.

By 
Raosaheb Ghule user avatar
Raosaheb Ghule
·
Jul. 16, 24 · Tutorial
Likes (8)
Comment
Save
Tweet
Share
10.7K Views

Join the DZone community and get the full member experience.

Join For Free

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

explain plans

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);

welcome page

Using Oracle SQL Developer Tool

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

Using Oracle SQL Developer Tool

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.

problem

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.

add 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
Database Oracle Database sql

Opinions expressed by DZone contributors are their own.

Related

  • Copy SQL Execution Plan from One Database to Another in Oracle 19c
  • Exploring the New Boolean Data Type in Oracle 23c AI
  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!