Re: Explain Plan vs Actual Execution Plan

"Odland, Brad" wrote:
>
> Hello,
>
> Heres a question to ponder. While tuning a SQL statment for a user I noticed
> that the explain plan from SQL Analyze was not the same plan that was found
> when I used OEM Top Sessions (9.2.0.1) upon executing to collect execution
> stats. Database is on HP/UX 11 version Oracle 8.1.7
>
> The stats were not "stale" yet. Monitoring is on for the tables in the
> query. The query would actually never return. I suspected that the stats
> were a bit off so I ran new ones and then SQL Analyze displayed a different
> explain plan and the plan reteived from top sessions while the SQL was
> running matched.
>
> My question is is the Explain Plan and estimate or is the actual plan. I
> suspect that when an explain plan is created it uses statistics and the
> optimizer to determine the estimated plan and cost. However when the SQL is
> actually executed I suspect that a different plan may be generated as actual
> execution begins....or am I just wacked.
>
> Either way the statistics when run created a proper plan that worked fine.
> But I wonder why the difference in plans...
>
> Brad O.
>

Brad,

  The explained plan is the actual plan Oracle would use if the statement were parsed - but a statement which you have just executed is not necessarily reparsed because you modify (say, through ALTER SESSION) some parameters which affect the plan. It works for some parameters such as optimizer_mode, not for the more obscure ones. I presume that something similar may happen with stats.

-- 
Regards,