Detecting Oracle Execution Plan Flip Flops:
Just how many of your SQL operations are flip flopping between good and poor execution plans.
​
A poor execution plan can bring query performance to its knees. There are a few reasons why the Oracle optimiser generates suboptimal execution plans:
​
-
Missing Statistics
-
Stale Statistics
-
Unusable Indexes
-
Column\Datatype mismatch in query body
-
Query HINT used inappropriately
-
SQL Plan stability used inappropriately
​
All of the above situations will prevent the optimiser from choosing the optimum execution plan. It's important to know when suboptimal plans are being chosen for execution. You can quite easily determine the extent of your plan flip flops by allowing "Speedway" to sift through you SQL execution history as per below.
Take advantage of the health check facility in Speedway to obtain information about plan flip flops at the click of a button.
Speedway can also allow you to interrogate the performance profile of each execution plan associated with the SQL ID. From here the most optimal execution plan is easily determined.
