When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan that is used.
The two most common methods used to generate the execution plan for a SQL statement are:
EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.
V$SQL_PLAN – A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the cursor cache.
My preferred method is always to use V$SQL_PLAN (even though it requires the statement to at least begin executing) because under certain conditions the plan shown by the EXPLAIN PLAN command can be different from the plan that will actually be used when the query is executed.
So, what can cause the plans to differ?
Continue reading “Does the Explain Plan command really show the execution plan that will be used?”