Query Optimization: EXPLAIN and Execution Plan

Have you ever wondered why your SQL queries are running slowly, even with optimized code?Well, say
hello to your new debugging path:
EXPLAIN Plan and Execution Plan! These are the crystal balls of database queries-one predicts the future, the other analyzes the past.

What’s the Difference?

• Execution Plan: What actually happened when your query ran.
• EXPLAIN Plan: What might happen if you run the query.

When to Use Each?

EXPLAIN Plan

• When: Before running your query.
• Why: To optimize your query and avoid surprises like full table scans or skewed workloads.
• How: Use it to predict the logical steps, expected data size, and how work is distributed.

Execution Plan

• When: After your query finishes.
• Why: To debug performance issues, identify bottlenecks, and see actual resource usage.
• How: Check runtime metrics like rows processed, execution time, and node distribution.

Exploring Execution Plan Representations

Execution plans provide a blueprint for SQL statement execution, revealing database operations and their order. While commonly displayed in tabular form, the plan structure is inherently tree-like. Understanding the tree representation helps DBAs and developers analyze performance more effectively.

For instance, in a query that joins sales and products tables and groups by prod_category, the tree representation starts from the leaf nodes (table access operators). These operators, such as full table scans, produce rows for higher-level operators like joins and group-bys.

The query optimizer evaluates multiple execution plans, selecting the one with the lowest estimated cost based on resource usage metrics (e.g., I/O, CPU). Tools like the EXPLAIN PLAN command or the DBMS_XPLAN package can be employed to generate and interpret these plans. The chosen representation—tree or tabular—offers unique insights, aiding efficient SQL tuning and troubleshooting.

EXPLAIN PLAN FOR 
SELECT cust_name, SUM(order_total) 
FROM orders o, customers c 
WHERE o.cust_id = c.cust_id 
GROUP BY cust_name;

SELECT plan_table_output 
FROM TABLE(dbms_xplan.display('plan_table', NULL, 'BASIC'));

Output:

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   CUSTOMERS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  ORDERS  
------------------------------------------

Real-World Example

Let’s say you’re using Impala to analyze logs. Here’s how both plans can help:

Step 1: EXPLAIN Plan (Predict the query behavior)

EXPLAIN SELECT user_id, COUNT(*) 
FROM logs 
WHERE event = 'login' 
GROUP BY user_id;

Sample Output:

+---------------------------------------------------+
| Operator                  | Estimated Rows | Cost |
+---------------------------------------------------+
| 04:EXCHANGE               | 1.2M           | 50   |
| 03:AGGREGATE (FINALIZE)   | 1.2M           | 40   |
| 02:SCAN HDFS logs         | 10M            | 30   |
+---------------------------------------------------+

What it tells you:

• Impala will scan 10M rows.
• Aggregation happens in parallel across nodes.
• Data is shuffled and combined at the EXCHANGE step.

Step 2: Execution Plan (Analyze the actual behavior)

After running the query:

SELECT user_id, COUNT(*) 
FROM logs 
WHERE event = 'login' 
GROUP BY user_id;

Execution Plan Output:

+----------------------------------------------------+
| Operator                   | Actual Rows | Time(s) |
+----------------------------------------------------+
| 04:EXCHANGE                | 1.1M        | 12.5    |
| 03:AGGREGATE (FINALIZE)    | 1.1M        | 9.8     |
| 02:SCAN HDFS logs          | 9.9M        | 7.2     |
+----------------------------------------------------+

What it tells you:

• Actual Rows match estimates, so the query behaves as expected.
• Time at each step shows that scanning took the longest.
• If times were unexpectedly high, you’d know where to optimize.

Quick Tips

1. Use EXPLAIN to prevent problems.
2. Use Execution Plan to fix problems.
3. Always optimize partitioning and indexes for large datasets.
4. For Impala, use the EXPLAIN_LEVEL query option to adjust plan details.

Pro Tip: Save both plans during query optimization — it’s like having before-and-after snapshots to track improvements.

REF:

https://blogs.oracle.com/optimizer/post/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement