"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, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
