Re: Explain Plan vs Actual Execution Plan
FYI - Following on the point picked up by Darrell Landrum below, and my comment about global histograms in 8i, I decided to test a workaround: Could you export a histogram from a non-partitioned table, then import it to a partitioned table ? If so, would a query against the partitioned table actually use the global histogram that had magically appeared ? The answer to both questions was YES. So if you want Oracle 8i to make use of good histogram information at the global level, you can fake it into the system. However, in the course of my tests, I discovered that the manual's comments that: Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. is not entirely true. If the predicate involves a column with a histogram, and Oracle decides that multiple partitions will be accessed, then the optimizer MAY use a collection of partition-level histograms to synthesize a table level histogram to estimate the cardinality of the result. The conditions governing the optimizer's behaviour seem to be the size of the tables (number of rows, perhaps) and the number of partitions. i.e. balancing the cost of acquiring the partition-level information against the potential saving by doing a better job. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk For one-day tutorials: (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May Estonia___June (provisional) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: [EMAIL PROTECTED] Sent: 20 March 2003 23:52 The really cute thing about the need for global statistics to be reasonable - a few pages further on you'll find the comment that in 8i you can't generate global histograms ! (Fixed in 9i) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk One-day tutorials: (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd The three-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - From: Darrell Landrum [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 20 March 2003 21:28 Subject: Re: Explain Plan vs Actual Execution Plan 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Explain Plan vs Actual Execution Plan
Thanks Jonathon, I would never have thought to try this. Excellent information to know! (I did, however suspect the manual's statement on query predicate, etc. would be found to not be 100% true 100% of the time.) Thanks! [EMAIL PROTECTED] 03/26/03 05:43AM FYI - Following on the point picked up by Darrell Landrum below, and my comment about global histograms in 8i, I decided to test a workaround: Could you export a histogram from a non-partitioned table, then import it to a partitioned table ? If so, would a query against the partitioned table actually use the global histogram that had magically appeared ? The answer to both questions was YES. So if you want Oracle 8i to make use of good histogram information at the global level, you can fake it into the system. However, in the course of my tests, I discovered that the manual's comments that: Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. is not entirely true. If the predicate involves a column with a histogram, and Oracle decides that multiple partitions will be accessed, then the optimizer MAY use a collection of partition-level histograms to synthesize a table level histogram to estimate the cardinality of the result. The conditions governing the optimizer's behaviour seem to be the size of the tables (number of rows, perhaps) and the number of partitions. i.e. balancing the cost of acquiring the partition-level information against the potential saving by doing a better job. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk For one-day tutorials: (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May Estonia___June (provisional) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: [EMAIL PROTECTED] Sent: 20 March 2003 23:52 The really cute thing about the need for global statistics to be reasonable - a few pages further on you'll find the comment that in 8i you can't generate global histograms ! (Fixed in 9i) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk One-day tutorials: (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd The three-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - From: Darrell Landrum [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 20 March 2003 21:28 Subject: Re: Explain Plan vs Actual Execution Plan 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
RE: Explain Plan vs Actual Execution Plan
I have another suggestion (or two) as to why the execution plan may have changed: * Bind variables - these often cause grief. Of course, choosing a different execution plan may be correct behaviour based on what the histograms say. * FGAC (Fine Grained Access Control) - this caught me out once. We had fgac (or rls - row level security - as it was often called on our project) enabled and the execution plan includes the effects of the access control. You could grab a query executed by a different user and ask for an explain plan under your own schema and get a different result. This made tuning difficult since the access control could do all sorts of things to queries, and it is easy to ignore when you don't see it on screen. I guess at the end of the day it's important to remember that the optimisor's job is to choose the best execution path for a given query at a given moment in time. A lot of people get concerned when execution plans change, and whilst this is sometimes with good reason it is also Oracle simply changing to a better approach. If the plan changes and the query takes forever to run, but if the plan changes and performance is still fine then you typically don't have a problem. Regards, Mark. Kevin Toepke [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] racle.com cc: Sent by: Subject: RE: Explain Plan vs Actual Execution Plan [EMAIL PROTECTED] 22/03/2003 05:29 Please respond to ORACLE-L Yes, local statistics are the partition specific statitics. Global stats are on the partitioned object as a whole. Global stats are vitally important when you are accessing more than one partition. If you are accessing 1 partition and you do not have global stats, then the optimizer will use the default statistics. Very bad. Kevin -Original Message- Brad Sent: Friday, March 21, 2003 12:19 PM To: Multiple recipients of list ORACLE-L Please exlain the difference between local and global statistics. Is this statisitics on a partition? Brad Odland -Original Message- Sent: Thursday, March 20, 2003 3:29 PM To: Multiple recipients of list ORACLE-L 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
RE: Explain Plan vs Actual Execution Plan
Please exlain the difference between local and global statistics. Is this statisitics on a partition? Brad Odland -Original Message- Sent: Thursday, March 20, 2003 3:29 PM To: Multiple recipients of list ORACLE-L 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 beginsor 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). -- 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).
RE: Explain Plan vs Actual Execution Plan
Yes, local statistics are the partition specific statitics. Global stats are on the partitioned object as a whole. Global stats are vitally important when you are accessing more than one partition. If you are accessing 1 partition and you do not have global stats, then the optimizer will use the default statistics. Very bad. Kevin -Original Message- Brad Sent: Friday, March 21, 2003 12:19 PM To: Multiple recipients of list ORACLE-L Please exlain the difference between local and global statistics. Is this statisitics on a partition? Brad Odland -Original Message- Sent: Thursday, March 20, 2003 3:29 PM To: Multiple recipients of list ORACLE-L 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 beginsor 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). -- 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
RE: Explain Plan vs Actual Execution Plan
Title: RE: Explain Plan vs Actual Execution Plan I'm open to abuse if I'm wrong, but I think that the intended distinction was: let's assume table T with partitions P1 and P2 analyze table t compute statistics ; --- global statistics analyze table t partition (p1) compute statistics ; --- local statistics on partition p1 analyze table t partition (p2) compute statistics ; --- local statistics on partition p2 -Original Message- From: Odland, Brad [mailto:[EMAIL PROTECTED]] Please exlain the difference between local and global statistics. Is this statisitics on a partition? -Original Message- 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.
RE: Explain Plan vs Actual Execution Plan
Yes, that is specific for a partitioned table. Using dbms_stats.gather_table_stats, there is a parameter called granularity with which you can specify to gather stats for your table at different levels: DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. ALL: Gather all (subpartition, partition, and global) statistics. [EMAIL PROTECTED] 03/21/03 11:19AM Please exlain the difference between local and global statistics. Is this statisitics on a partition? Brad Odland -Original Message- Sent: Thursday, March 20, 2003 3:29 PM To: Multiple recipients of list ORACLE-L 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 beginsor 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). -- 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 -
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 beginsor 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).
RE: Explain Plan vs Actual Execution Plan
I think you can trust the STAT lines in a SQL trace file to give you accurate information about what execution plan *did* take place. The test I would suggest is to compare the information in these lines to what EXPLAIN PLAN shows you. You'll need to look at the raw trace data, though, because tkprof doesn't always convert STAT lines into a correct execution plan. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Thursday, March 20, 2003 2:24 PM To: Multiple recipients of list ORACLE-L 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 beginsor 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: Cary Millsap 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).
Re: Explain Plan vs Actual Execution Plan
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 beginsor 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).
Re: Explain Plan vs Actual Execution Plan
The really cute thing about the need for global statistics to be reasonable - a few pages further on you'll find the comment that in 8i you can't generate global histograms ! (Fixed in 9i) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk One-day tutorials: (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd The three-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 20 March 2003 21:28 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
