Rick,
This is not surprising. Different database environments may have a huge impact on execution plans.
The CBO is a process that accepts a sql statement, retrieves system/object information and relevant parameter settings, applies an internal set of rules and calculation formulas and produces an execution plan. If all of the input information and internal code stays the same, it will produce the same execution plan every time (barring the ocassional bug).
In your case, let's skip the sql statement issue for the sake of expediency. If the developers are on Windows and the other environments are unix, there are parameter differences and perhaps even different internal code (not having access to the code, I can't guarantee it).
The next item to look at is the object information. The CBO will make some base decisions on the # of blocks in a table, the depth (level) and breadth (leaf blocks) of an index and the index's clustering factor (describes the order relationship between entries in the index and blocks in the table and CANNOT be changed with an index rebuild), the # of distinct values in an index, etc. If there are differences (perhaps even 1 more block below the hwm), the CBO can change from planA to planB. If you don't have valid (not the same as up-to-date) statistics on all the objects (and corollary objects) referenced in the query, you might get some defaults that are unrealistic or it may perform dynamic sampling.
Continuing on, examine the relevant parameters for the system and, most importantly, the session. Is optimizer_mode set the same? db_file_multiblock_read_count impacts full table scans and full index scans. For a definitive list of parameters that influence the CBO, read Wolfgang's paper at www.centrexcc.com titled "What's New in the Cost-Based Optimizer in 9i" (or variation thereof, my memory is not as good as it used to be). BTW, according to 10053 trace, the number of CBO parameters increases to over 150 in 10g!
Why are changing execution plans a concern? I would expect changes between development -> qa -> production unless the environments (including size and distribution of data) are identical and this is usually not the case. The advantage of the CBO is that it can change execution plans based on changes in data and/or the database environment. I would not expect a query that performs blindingly fast in development to have the same response time in a production environment. In this case, preserving the development execution plan may cause the query to be so slow as to be unusable. If you have a performance test environment, then you can benchmark the query and address performance problems at that point.
When it comes to hints, I am a bit of a contrarian. Hints are bandaids. If query1 gets acceptable performance only when it is hinted to use an index scan instead of a table scan on tableC, you have to ask "How many other queries are improperly choosing a table scan on tableC instead of an index scan?". I'd venture to say that there is a significant number of queries that have the same problem and the root cause may be statistics, session parameters, system parameters, object structural problems, even i/o subsystem issues. I do not consider hints to be part of a valid development method. Sure, there are exceptions, but essentially the developer is saying "I know the best execution plan and the CBO does not." Yes, this does happen and there are developers who know the production database well enough to be correct. Call me a cynical old dba, but I've never met one in person, though I know of a few folks (less than a dozen) that I would not argue with if they said they needed a hint. And, yes, I know the data dictionary uses hints all over the place, but even Oracle is trying to wean themself off these.
I am in favor of stored outlines in code you cannot touch (Siebel, Peoplesoft, etc.). I know some of the list members have had success getting better performance with these systems using stored outlines. My opinion of stored outlines in other cases mirrors my opinion of hints, they are a bandaid that addresses a symptom. To quote a line from Disclosure "Solve the problem."
For a more detailed explanation of the CBO and it's care and feeding, check Wolfgang's paper library and Jonathan Lewis's site (http://www.jlcomp.demon.co.uk). Both gentleman are amongst the elite of Oracle scientists, especially in the area of the CBO.
Regards, Daniel "Long-winded" Fink
The CBO has been nothing short of a pain in the butt to me. Going from Development to QA to a live environment achieves unexpected results. It seems that you never know what you are going to get when it comes to an execution plan. The developers run Oracle on their Windows box and the execution path is one way, but when it gets moved to a QA environment it chooses another way. At least with the RULE base optimizer you know what you are going to get.
Sometimes I think I am the only one with this problem. How do you work this? Do you always use hints, do you use stored outlines….?
Thanks,
Rick Stephenson
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
