Brad, you're absolutely correct. Explain plan is what is intended, but isn't what always happens. It probably is most of the time, but not always. I recently had a circumstance in which a long running job (the explain plan of) was telling me it was reading from a big partitioned table and using the index that it should have been using, but a 10046 trace showed reads from a different index and no reads from the index identified by explain plan. A full analyze compute (using dbms_stats) later, this job works fine. I suspect in my case the problem was with statistics. The job was originally set up by the developer to add data each night, then analyze the current partition. The last time global stats were gathered on this table was last October. I suspect that over time with data loads and updates the data distribution, etc. got skewed as compared to the global stats, so the optimizer didn't have enough valid information to make a good choice. This query was reading data from 5 or 6 partitions of a 54 partition table. That's important information because a couple of weeks ago I was reading the "Oracle 8i Designing and Tuning for Performance" document and came across this statement: "Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics." Pretty interesting to think about. It's gather stats global and local from now on for me.
>>> [EMAIL PROTECTED] 03/20/03 02:24PM >>> 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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).
