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
Above query created a explain plan that can be queried, using DBMS_XPLAN.DISPLAY to give the full view. You can also use a shortcut to generate the explain plan using SQL Developer – Press F10
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Using Oracle SQL Developer Tool
To obtain the actual execution plan in SQL Developer tool, you can press “F10” to generate the actual execution plan for the query on which cursor is pointing and it will show the explain plan.
Please refer the below diagram for details.
Figure: How to see the explain using SQL Developer Tool
Figure: Explain plan of the above query.
Practical Examples
Let’s take a look at 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 & 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, specially DML’s. 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 the query performance. This, in turn, would enhances 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 by 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
- https://docs.oracle.com/cd/A97385_01/server.920/a96533/ex_plan.htm#838
- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXPLAIN-PLAN.html
- https://www.oracle.com/docs/tech/database/technical-brief-explain-the-explain-plan-052011.pdf