Explain Plan
An Oracle White Paper May 2011
The Oracle Optimizer Explain the Explain Plan
Oracle Optimizer: Explain the Explain Plan
Introduction ....................................................................................... 1 The Execution Plan ........................................................................... 2 Displaying the Execution plan ........................................................ 3 What is Cost .................................................................................. 8 Understanding the execution plan ..................................................... 8 Cardinality ..................................................................................... 9 Access Method ............................................................................ 12 Join method ................................................................................. 15 Join Order.................................................................................... 18 Partitioning .................................................................................. 20 Parallel Execution ........................................................................ 22 Conclusion ...................................................................................... 27
Oracle Optimizer: Explain the Explain Plan
Introduction
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries. It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, partitioning, etc. Still it is expected that the optimizer will generate sub-optimal plans for some SQL statements now and then. In cases where there is an alternative plan that performed better than the plan generated by the optimizer, the first step in diagnosing why the Optimizer picked the sub-optimal plan is to visually inspect both of the execution plans. Examining the
The Oracle Optimizer Explain the Explain Plan
Oracle Optimizer: Explain the Explain Plan
Introduction ....................................................................................... 1 The Execution Plan ........................................................................... 2 Displaying the Execution plan ........................................................ 3 What is Cost .................................................................................. 8 Understanding the execution plan ..................................................... 8 Cardinality ..................................................................................... 9 Access Method ............................................................................ 12 Join method ................................................................................. 15 Join Order.................................................................................... 18 Partitioning .................................................................................. 20 Parallel Execution ........................................................................ 22 Conclusion ...................................................................................... 27
Oracle Optimizer: Explain the Explain Plan
Introduction
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries. It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, partitioning, etc. Still it is expected that the optimizer will generate sub-optimal plans for some SQL statements now and then. In cases where there is an alternative plan that performed better than the plan generated by the optimizer, the first step in diagnosing why the Optimizer picked the sub-optimal plan is to visually inspect both of the execution plans. Examining the