Optimizing Oracle Database Queries Using Execution Plans: A Step-by-Step Guide

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: SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; -- Display actual execution data SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(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: 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; Resulting Plan: ------------------------------------------------------------------------------- | 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: 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: Leaf Nodes First (Deepest/Rightmost Operations): Id 4: Full scan of CUSTOMERS (filtered by city = 'New York'). Id 5: Full scan of ORDERS. 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. 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 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. 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. Expensive Sorting: SORT ORDER BY (Id 1) adds overhead. Fix: Indexes on SUM(o.amount) or materialized views. Identifying Performance Bottlenecks Full Table Scans Cause: Missing indexes on filtered or joined columns. Fix: Create composite or function-based indexes. High Join Costs Example: HASH JOIN on tables with 10M+ rows. Fix: Use NESTED LOOPS for indexed small tables. Sorting Overheads Indicators: SORT GROUP BY, SORT ORDER BY. Fix: Add indexes on GROUP BY/ORDER BY columns. 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. CREATE INDEX idx_customers_city 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): SELECT /*+ INDEX(orders idx_orders_customer_id) */ customer_id, SUM(amount) FROM orders GROUP BY customer_id; Practical Optimization Example Step 1: Create Sample Tables -- 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 ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, amount NUMBER ); Step 2: Baseline Execution Plan SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; Initial Plan Analysis Initial Execution Plan ------------------------------------------------------------------------------- | 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 Add Indexes: CREATE INDEX idx_customers_city ON customers(city); CREATE INDEX idx_orders_customer_id ON orders(customer_id, amount); Refresh Statistics: -- Update statistics for CUSTOMERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS'); -- Update statistics for ORDERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); Step 4: Optimized Execution Plan Re-run the Query: SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; Optimized Execution Plan Output: ------------------------------------------------------------------------------- | 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 | IDX_CUSTOMERS_CITY | 40 (0)| 00:00:01 | | 5 | INDEX FAST FULL SCAN| IDX_ORDERS_CUSTOMER_ID | 1363 (1)| 00:00:01 | ------------------------------------------------------------------------------- Optimized Plan Results INDEX RANGE SCAN on CUSTOMERS: Uses idx_customers_city to filter city = 'New York' efficiently. INDEX FAST FULL SCAN on ORDERS: Leverages idx_orders_customer_id to retrieve customer_id and amount without 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_PLAN for 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/B plan comparisons. Further Reading Oracle Documentation: Using EXPLAIN PLAN Oracle SQL Tuning Guide "Cost-Based Oracle Fundamentals" by Jonathan Lewis 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. Abstract: 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 Introduction Generating Execution Plans Generating Execution Plans Interpreting Execution Plans Interpreting Execution Plans Identifying Performance Bottlenecks Identifying Performance Bottlenecks Query Tuning Strategies Query Tuning Strategies Practical Optimization Example Practical Optimization Example Advanced Tips and Best Practices Advanced Tips and Best Practices Conclusion 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 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); 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. Open SQL Developer. Write your query and click Explain Plan (or press F10 ). Explain Plan F10 Use the visual plan tree to analyze operations. Method 3: Capture Actual Execution Statistics Include runtime metrics using GATHER_PLAN_STATISTICS : GATHER_PLAN_STATISTICS SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; -- Display actual execution data SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST')); SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; -- Display actual execution data SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST')); Interpreting Execution Plans Plan Structure and Hierarchy Execution plans are tree structures where child operations feed data to parent operations. tree structures Reading Direction: Right-to-left and bottom-to-top. Indentation = Depth in the tree. Root Node: The top-left operation (SELECT STATEMENT). Reading Direction : Right-to-left and bottom-to-top . Reading Direction Right-to-left bottom-to-top Indentation = Depth in the tree. Indentation Root Node : The top-left operation ( SELECT STATEMENT ). Root Node SELECT STATEMENT Sample Execution Plan For the query: 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; 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; Resulting Plan : Resulting Plan ------------------------------------------------------------------------------- | 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 | ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- | 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: 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) 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: Leaf Nodes First (Deepest/Rightmost Operations): Id 4: Full scan of CUSTOMERS (filtered by city = 'New York'). Id 5: Full scan of ORDERS. 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. Root Node: Id 0: Returns the final sorted result. Leaf Nodes First (Deepest/Rightmost Operations): Id 4: Full scan of CUSTOMERS (filtered by city = 'New York'). Id 5: Full scan of ORDERS. Leaf Nodes First Id 4: Full scan of CUSTOMERS (filtered by city = 'New York'). Id 5: Full scan of ORDERS. Id 4: Full scan of CUSTOMERS (filtered by city = 'New York' ). CUSTOMERS city = 'New York' Id 5: Full scan of ORDERS . ORDERS 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. 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. Id 3: Combines results from Id 4 and Id 5 via a HASH JOIN . HASH JOIN Id 2: Groups data by customer_id and city . customer_id city Id 1: Sorts the grouped results by total_amount DESC . total_amount DESC Root Node : Id 0: Returns the final sorted result. Root Node Id 0: Returns the final sorted result. 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. 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. Column Description Column Column Description Description Id Step identifier. Child operations execute before parents (e.g., Id 4 → Id 3). Id Id Id Step identifier. Child operations execute before parents (e.g., Id 4 → Id 3). Step identifier. Child operations execute before parents (e.g., Id 4 → Id 3). Operation Action performed (e.g., HASH JOIN, TABLE ACCESS FULL). Operation Operation Operation Action performed (e.g., HASH JOIN, TABLE ACCESS FULL). Action performed (e.g., HASH JOIN , TABLE ACCESS FULL ). HASH JOIN TABLE ACCESS FULL Name Object involved (table/index name). Name Name Name Object involved (table/index name). Object involved (table/index name). Rows Estimated rows processed at this step (cardinality). Rows Rows Rows Estimated rows processed at this step (cardinality). Estimated rows processed at this step (cardinality). Bytes Estimated data size processed. Bytes Bytes Bytes Estimated data size processed. Estimated data size processed. Cost (%CPU) Optimizer’s cost estimate (lower = better). Includes CPU and I/O overhead. Cost (%CPU) Cost (%CPU) Cost (%CPU) Optimizer’s cost estimate (lower = better). Includes CPU and I/O overhead. Optimizer’s cost estimate (lower = better). Includes CPU and I/O overhead. Time Estimated runtime for the operation. Time Time Time Estimated runtime for the operation. 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). I/O : Reading data from disk (e.g., full table scans). I/O CPU : Processing data (joins, sorting, aggregations). CPU Memory : Storing intermediate results (e.g., hash tables for joins). Memory Critical Red Flags in the Sample Plan 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. 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. Expensive Sorting: SORT ORDER BY (Id 1) adds overhead. Fix: Indexes on SUM(o.amount) or materialized views. 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. 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. 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. Fix city customer_id 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. 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. The HASH JOIN (Id 3) contributes 24,472 to the total cost (96% of total). HASH JOIN Why? Joining 5 million rows from ORDERS with 10,000 rows from CUSTOMERS . Why? ORDERS CUSTOMERS Expensive Sorting : SORT ORDER BY (Id 1) adds overhead. Fix: Indexes on SUM(o.amount) or materialized views. Expensive Sorting SORT ORDER BY (Id 1) adds overhead. Fix: Indexes on SUM(o.amount) or materialized views. SORT ORDER BY (Id 1) adds overhead. SORT ORDER BY Fix : Indexes on SUM(o.amount) or materialized views. Fix SUM(o.amount) Identifying Performance Bottlenecks Full Table Scans Cause: Missing indexes on filtered or joined columns. Fix: Create composite or function-based indexes. High Join Costs Example: HASH JOIN on tables with 10M+ rows. Fix: Use NESTED LOOPS for indexed small tables. Sorting Overheads Indicators: SORT GROUP BY, SORT ORDER BY. Fix: Add indexes on GROUP BY/ORDER BY columns. Cardinality Mismatches Example: Optimizer estimates 100 rows, but 100K are processed. Fix: Refresh statistics with DBMS_STATS.GATHER_TABLE_STATS. Full Table Scans Cause: Missing indexes on filtered or joined columns. Fix: Create composite or function-based indexes. Full Table Scans Cause: Missing indexes on filtered or joined columns. Fix: Create composite or function-based indexes. Cause : Missing indexes on filtered or joined columns. Cause Fix : Create composite or function-based indexes. Fix High Join Costs Example: HASH JOIN on tables with 10M+ rows. Fix: Use NESTED LOOPS for indexed small tables. High Join Costs Example: HASH JOIN on tables with 10M+ rows. Fix: Use NESTED LOOPS for indexed small tables. Example : HASH JOIN on tables with 10M+ rows. Example HASH JOIN Fix : Use NESTED LOOPS for indexed small tables. Fix NESTED LOOPS Sorting Overheads Indicators: SORT GROUP BY, SORT ORDER BY. Fix: Add indexes on GROUP BY/ORDER BY columns. Sorting Overheads Indicators: SORT GROUP BY, SORT ORDER BY. Fix: Add indexes on GROUP BY/ORDER BY columns. Indicators : SORT GROUP BY , SORT ORDER BY . Indicators SORT GROUP BY SORT ORDER BY Fix : Add indexes on GROUP BY / ORDER BY columns. Fix GROUP BY ORDER BY Cardinality Mismatches Example: Optimizer estimates 100 rows, but 100K are processed. Fix: Refresh statistics with DBMS_STATS.GATHER_TABLE_STATS. Cardinality Mismatches Example: Optimizer estimates 100 rows, but 100K are processed. Fix: Refresh statistics with DBMS_STATS.GATHER_TABLE_STATS. Example : Optimizer estimates 100 rows, but 100K are processed. Example Fix : Refresh statistics with DBMS_STATS.GATHER_TABLE_STATS . Fix DBMS_STATS.GATHER_TABLE_STATS Query Tuning Strategies 1. Index Optimization Covering Indexes: Include frequently accessed columns. CREATE INDEX idx_customers_city ON customers(city) INCLUDE (customer_id, name); Partitioning: Split tables by range, hash, or list (e.g., by order_date). Covering Indexes: Include frequently accessed columns. CREATE INDEX idx_customers_city ON customers(city) INCLUDE (customer_id, name); Covering Indexes : Include frequently accessed columns. Covering Indexes CREATE INDEX idx_customers_city ON customers(city) INCLUDE (customer_id, name); CREATE INDEX idx_customers_city ON customers(city) INCLUDE (customer_id, name); Partitioning: Split tables by range, hash, or list (e.g., by order_date). Partitioning : Split tables by range, hash, or list (e.g., by order_date ). Partitioning 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'). Replace SELECT * with explicit columns. SELECT * Use EXISTS instead of IN for subqueries. EXISTS IN Avoid functions on indexed columns (e.g., WHERE UPPER(name) = 'JOHN' ). WHERE UPPER(name) = 'JOHN' 3. Optimizer Hints Force specific behaviors (use sparingly): SELECT /*+ INDEX(orders idx_orders_customer_id) */ customer_id, SUM(amount) FROM orders GROUP BY customer_id; SELECT /*+ INDEX(orders idx_orders_customer_id) */ customer_id, SUM(amount) FROM orders GROUP BY customer_id; Practical Optimization Example Step 1: Create Sample Tables -- 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 ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, amount NUMBER ); -- 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 ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, amount NUMBER ); Step 2: Baseline Execution Plan SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; Initial Plan Analysis Initial Execution Plan Initial Execution Plan ------------------------------------------------------------------------------- | 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 | ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- | 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. FULL TABLE SCAN on both tables. FULL TABLE SCAN Cost : 25,468. Cost Bottlenecks : No indexes on city or customer_id . Bottlenecks city customer_id Step 3: Apply Optimizations Add Indexes: CREATE INDEX idx_customers_city ON customers(city); CREATE INDEX idx_orders_customer_id ON orders(customer_id, amount); Refresh Statistics: -- Update statistics for CUSTOMERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS'); -- Update statistics for ORDERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); Add Indexes: CREATE INDEX idx_customers_city ON customers(city); CREATE INDEX idx_orders_customer_id ON orders(customer_id, amount); Add Indexes : Add Indexes CREATE INDEX idx_customers_city ON customers(city); CREATE INDEX idx_orders_customer_id ON orders(customer_id, amount); CREATE INDEX idx_customers_city ON customers(city); CREATE INDEX idx_orders_customer_id ON orders(customer_id, amount); Refresh Statistics: -- Update statistics for CUSTOMERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS'); -- Update statistics for ORDERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); Refresh Statistics : Refresh Statistics -- Update statistics for CUSTOMERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS'); -- Update statistics for ORDERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); -- Update statistics for CUSTOMERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS'); -- Update statistics for ORDERS EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); Step 4: Optimized Execution Plan Re-run the Query : Re-run the Query SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, c.city, SUM(o.amount) AS total_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 total_amount DESC; Optimized Execution Plan Output: ------------------------------------------------------------------------------- | 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 | IDX_CUSTOMERS_CITY | 40 (0)| 00:00:01 | | 5 | INDEX FAST FULL SCAN| IDX_ORDERS_CUSTOMER_ID | 1363 (1)| 00:00:01 | ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- | 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 | IDX_CUSTOMERS_CITY | 40 (0)| 00:00:01 | | 5 | INDEX FAST FULL SCAN| IDX_ORDERS_CUSTOMER_ID | 1363 (1)| 00:00:01 | ------------------------------------------------------------------------------- Optimized Plan Results INDEX RANGE SCAN on CUSTOMERS: INDEX RANGE SCAN on CUSTOMERS: INDEX RANGE SCAN on CUSTOMERS: Uses idx_customers_city to filter city = 'New York' efficiently. INDEX FAST FULL SCAN on ORDERS: Leverages idx_orders_customer_id to retrieve customer_id and amount without scanning the entire table. Cost Reduction: Before: 25,468 After: 1,502 (94% reduction). INDEX FAST FULL SCAN on ORDERS: Leverages idx_orders_customer_id to retrieve customer_id and amount without scanning the entire table. INDEX FAST FULL SCAN on ORDERS : INDEX FAST FULL SCAN ORDERS Leverages idx_orders_customer_id to retrieve customer_id and amount without scanning the entire table. Leverages idx_orders_customer_id to retrieve customer_id and amount without scanning the entire table. idx_orders_customer_id customer_id amount Cost Reduction: Before: 25,468 After: 1,502 (94% reduction). Cost Reduction : Cost Reduction Before: 25,468 After: 1,502 (94% reduction). Before : 25,468 Before After : 1,502 ( 94% reduction ). 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_PLAN for plan regressions. Adaptive Query Optimization (Oracle 12c+): Let Oracle dynamically adjust execution plans. Adaptive Query Optimization Let Oracle dynamically adjust execution plans. Let Oracle dynamically adjust execution plans. SQL Tuning Advisor : Use Oracle’s built-in tool for automated recommendations. SQL Tuning Advisor Use Oracle’s built-in tool for automated recommendations. Use Oracle’s built-in tool for automated recommendations. Monitor Historical Plans : Query DBA_HIST_SQL_PLAN for plan regressions. Monitor Historical Plans Query DBA_HIST_SQL_PLAN for plan regressions. Query DBA_HIST_SQL_PLAN for plan regressions. DBA_HIST_SQL_PLAN 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/B plan comparisons. Index Strategically : Align indexes with query predicates. Index Strategically Update Statistics Regularly : Ensure accurate cardinality estimates. Update Statistics Regularly Test Incrementally : Validate changes with A/B plan comparisons. Test Incrementally A/B Further Reading Further Reading Oracle Documentation: Using EXPLAIN PLAN Oracle SQL Tuning Guide "Cost-Based Oracle Fundamentals" by Jonathan Lewis Oracle Documentation: Using EXPLAIN PLAN Oracle Documentation: Using EXPLAIN PLAN Oracle SQL Tuning Guide Oracle SQL Tuning Guide "Cost-Based Oracle Fundamentals" by Jonathan Lewis