The EXPLAIN PLAN in Oracle (and other relational databases) is a powerful tool that helps analyze and optimize SQL queries. It provides insight into how the database engine executes a SQL query by detailing the execution plan. The plan shows the steps taken by the optimizer to fetch data, join tables, sort data, and apply filters, all in an effort to improve the query's performance.

Components of an EXPLAIN PLAN

When you execute EXPLAIN PLAN FOR , the database produces an execution plan. This plan consists of several rows, each representing an operation performed during query execution, such as table scans, index scans, joins, and sorting. Here’s a breakdown of the key components involved in understanding the EXPLAIN PLAN.

1. Operation (or Step Type)

Each row in the execution plan represents a specific operation the optimizer will perform. Common operations include:

TABLE ACCESS (FULL): A full scan of a table, reading all rows.

TABLE ACCESS (BY INDEX ROWID): Fetching rows using an index.

INDEX SCAN (FULL): Scanning an index to find matching rows.

SORT: Sorting data (usually after joins or in the ORDER BY clause).

JOIN: Types of joins (e.g., NESTED LOOPS, HASH JOIN).

FILTER: Applying a filter condition (like WHERE).

Each operation indicates how the database will access or process the data.

1. Object Name

This column shows the table, index, or other object involved in the operation. For instance, if a full table scan is performed, the object name will be the table name.

1. Cardinality

Cardinality refers to the number of rows estimated by the optimizer to be returned by a specific operation in the plan.

Cardinality helps estimate how many rows will be processed at each step of the query. A high cardinality indicates many rows, while a low cardinality indicates few rows.

Example: If a query is filtering a table with a WHERE clause, the cardinality represents how many rows the optimizer expects to pass through that filter.

The cardinality is important for understanding how much data is being processed at each step of the query, and it impacts the query’s overall cost.

1. Cost

Cost is an estimated value indicating the computational resources required to execute a query. It’s a relative measure (not an absolute time) based on factors like I/O operations, CPU usage, and memory.

The cost is calculated by the Oracle optimizer and is displayed for each operation. The cost helps determine the overall efficiency of the query plan.

Lower cost values are preferred because they indicate a more efficient execution plan, but it's important to note that cost is an estimate, not an actual measurement.

The total cost of a query is the sum of the costs of all the operations in the plan. It’s an indicator of how expensive the query will be in terms of system resources.

1. Bytes

The Bytes column shows the estimated size (in bytes) of the result set for each operation.

For example, if a table scan is performed, this shows the estimated number of bytes that will be read from the table.

It helps to understand the amount of data being processed and is often correlated with cardinality.

1. Start-up Cost

Start-up Cost represents the initial cost required to begin executing a specific step.

It is the fixed cost involved in initiating the operation before any actual processing begins (e.g., the cost to open a table or index).

This metric is important for understanding how much effort is needed to begin processing the data.

1. Total Cost

Total Cost is the cumulative cost of the operation, including both the start-up cost and the ongoing cost as the operation proceeds.

It’s calculated based on the number of rows being processed, the number of I/O operations, CPU usage, and more.

The total cost can help identify which parts of the query might be too expensive and need optimization.

1. Time

Some EXPLAIN PLAN outputs will also include estimated time, though this is not always present in every database.

The estimated time is an approximation of how long the operation is expected to take, based on the current system resources and query complexity.

The actual time taken to execute may vary depending on the database load and system performance at runtime.

1. Rows

The Rows column shows the number of rows expected to be returned by each step in the query.

This helps understand the data flow from one operation to the next.

For example, if you're joining two tables, the rows column indicates how many rows are expected to be returned after the join.

1. Additional Metrics:

Access Predicates: These are conditions used to filter data (typically found in the WHERE clause or as part of an index scan).

Join Predicates: These are the conditions for joins, indicating how tables are being joined (e.g., using ON clauses).

Filter Predicates: These show filtering conditions applied to the rows at different stages of query execution.

Breakdown of a Sample EXPLAIN PLAN

Here’s an example of an EXPLAIN PLAN output with explanations for each part:

EXPLAIN PLAN FOR
SELECT emp_id, emp_name, department
FROM employees
WHERE department = 'Sales'
ORDER BY emp_name;

After executing EXPLAIN PLAN FOR ..., you might see an output like:

Operation Breakdown:

SORT ORDER BY: The result set will be sorted by emp_name. This step will cost 5 and will process 10 rows. It involves sorting data after the filtering operation.

TABLE ACCESS FULL: A full table scan is performed on the EMPLOYEES table. The optimizer estimates that 1000 rows will be accessed, with a total cost of 3.

FILTER: A filter is applied to return only rows where department = 'Sales'. This filter affects 1000 rows and has a cost of 2.

Key Observations:

Cardinality: The number of rows at each step (e.g., 1000 rows processed in the table scan, 10 rows after sorting).

Cost: The estimated cost for each step (e.g., sorting has a higher cost than filtering).

Bytes: The estimated amount of data processed (e.g., 30000 bytes for the full table scan).

Time: The estimated time for each step.

How to Interpret EXPLAIN PLAN

1. Look for High-Cost Operations: High-cost operations should be optimized. For example, full table scans can often be avoided with indexes, especially if the cardinality is high.

2. Focus on Cardinality: Compare the cardinality between operations. If an operation expects more rows than anticipated, it may be inefficient.

3. Identify Expensive Joins: Operations like HASH JOIN or NESTED LOOPS may indicate expensive joins. Consider optimizing them using indexing or changing join strategies.

Conclusion

The EXPLAIN PLAN is an essential tool for understanding the performance of SQL queries. By analyzing components like cardinality, cost, and operation types, you can identify performance bottlenecks and areas for optimization. Here’s a summary of what each part of the EXPLAIN PLAN represents:

Cardinality: Number of rows estimated at each step.

Cost: Estimated computational resources required to execute the query.

Bytes: Estimated size of data processed.

Operations: Specific steps in executing the query (e.g., table scans, joins).

Time: Estimated execution time for each step (if available).

By analyzing these components carefully, you can improve query performance, reduce resource usage, and ensure that your database operates efficiently.