EXPLAIN PLAN   «Prev  Next»

Lesson 6 Table joins
Objective Use the EXPLAIN PLAN to diagnose Table Joins in Oracle

Utilizing the EXPLAIN PLAN for Diagnosing Table Joins in Oracle

Oracle databases, like many relational databases, rely heavily on the efficiency of SQL query execution. One of the core tools to assess and optimize this efficiency is the `EXPLAIN PLAN` command. This command unveils the Oracle optimizer's decision-making process, offering insights into the execution path chosen for a SQL query. When it comes to table joins—a common but potentially costly operation—the `EXPLAIN PLAN` becomes an invaluable diagnostic tool. To effectively use the `EXPLAIN PLAN` to diagnose table joins in Oracle, follow the steps outlined below:
  1. Generating the EXPLAIN PLAN: Before you can interpret the plan, you need to generate it. Here's how: 
    EXPLAIN PLAN FOR
[your SQL query goes here];

    This command will place the plan in the `PLAN_TABLE`, a default table provided by Oracle for this purpose.
  2. Viewing the EXPLAIN PLAN: After generating the plan, you can retrieve and view it using: 
    SELCT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    This displays the plan with its steps and the associated costs.
  3. Interpreting Table Joins in the Plan: Pay particular attention to the following components when diagnosing table joins:
    • Operation Column: This column provides insight into the join method used by Oracle. The common join methods include `NESTED LOOPS`, `HASH JOIN`, and `SORT MERGE JOIN`. The chosen method can significantly affect performance.
    • Object Name Column: This reveals the tables or indexes involved in each step. When diagnosing joins, focus on entries where two or more tables are listed.
    • Cardinality Column: Represents an estimate of the number of rows retrieved at each step. Discrepancies between the estimated cardinality and actual row count can point to statistics that may need updating.
    • Bytes Column: Provides an estimate of the amount of data Oracle needs to process. A significant amount of data in a join operation might indicate areas to optimize.
    • Cost Column: Indicates the estimated cost of each operation, guiding the optimizer's decisions. An unexpectedly high cost for a join operation may suggest inefficiencies.
  4. Recommendations for Optimizing Joins: Based on the insights from the `EXPLAIN PLAN`, consider the following strategies:
    • Update Statistics: Oracle's decisions are influenced by the statistics of the tables and indexes. Ensure these are up to date using the `DBMS_STATS` package.
    • Appropriate Indexing: If Oracle isn't using available indexes for join operations, consider the suitability of those indexes. Sometimes, creating a composite index or rearranging columns in an index can lead to more efficient joins.
    • SQL Query Refactoring: Occasionally, rewriting the SQL query or restructuring joins can lead to better optimization choices by Oracle.
In conclusion, the `EXPLAIN PLAN` is a critical tool for anyone tuning Oracle databases. By providing visibility into Oracle's optimization choices for table joins, it allows database administrators and developers to identify inefficiencies and implement performance-enhancing modifications. Proper use of this tool, combined with an understanding of Oracle's optimization strategies, will facilitate optimal database performance.

Joining Oracle Tables

While the EXPLAIN PLAN utility is commonly used to view access paths, it can also be used to view a nested loop (as well as correlated subqueries). A nested loop is a type of execution plan used to join two or more Oracle tables. It scans one table, and for each row of that table probes for the corresponding rows in the other table.

Establishing table-order for efficiency

When Oracle joins two or more tables with the rule-based optimizer, the order that the tables are specified in the FROM clause affects execution time for the nested loop query. The table that returns the fewest number of rows should be the driving table[1], and the driving table should always be specified last in the FROM clause. In short, tables should be listed in the FROM clause in order from largest to smallest because the driving table is accessed first in the query. 

    
Wrong: select * from tiny_table, small_table, large_table;

Right: select * from large_table, small_table, tiny_table;
The driving table is the table that returns the smallest number of rows in your query, not necessarily the table with the smallest number of table rows. You must carefully analyze your WHERE clause to determine the approximate number of rows returned by your query. To illustrate, consider the following table join. 

select  
   student_name,
   course_number
from
   student s,
   registration  c
where
   s.student_id = c.student_id;

In this case, we have determined that the registration table will have fewer rows than the student table, and it is made the driving table for this query. 
SELECT STATEMENT [CHOOSE] Cost=5 
NESTED LOOPS
   TABLE ACCESS FULL STUDENT [ANALYZED]
   TABLE ACCESS FULL REGISTRATION [ANALYZED]

Here we see the EXPLAIN PLAN for this query, showing the student table as the driving table and the registration table as the probed table. Sometime the use_hash hint with the cost-based optimizer will run far faster than nested loop joins. We will be covering this technique in detail in a later lesson. Even though we use the rule-based optimizer for these examples, the cost-based optimizer will also use nested loops. However, with the cost-based optimizer Oracle will use table statistics (rather than the position of the table name in the FROM clause) to determine the driving table. Now that we understand the tuning of basic table joins, let us explore a more complicated concept, the idea of Oracle subqueries.

[1]Driving table: This is the table used by the SQL optimizer in the initial step of execution.