Here we find again the holy trinity : DUC Diagnose-Understand-Cure D : Why is my DB so slow --> Oracle wait interface (Oracle press) U : Why the CBO is doing that --> Cost based optimisation (JL) C : What should I do now --> Dan Tow, SQL tuning (Oreilly) You many not be a fan of SQL diagraming, but it is the only systematic approach that tries to get aways from the try/error scheme while it gives a methodology to join complex queries. I read Garrison book on SQL tuning and was very disapointed. There are little to help you when you are faced with the brute fact of an SQL with 20 tables and 40 joins/filter while with Dan Tow method I still have a general plan of what to do, goal to reach. At this stage, JL book will help me understand why the optimiser ignored my will and correct. B.Polarski http://www.smenu.org From: Schultz, Charles [mailto:sac@xxxxxxxxxxxxx] Sent: Tuesday, 25 July, 2006 9:05 PM To: Allen, Brandon; oracle-l@xxxxxxxxxxxxx Subject: RE: Query tuning exercise: what to look for in a 10053 trace That makes sense. Since the long-running query has not yet completed, it is rather hard to know the "real" number of rows at each step (as opposed to the estimate). The cardinality reported by autotrace (and the 10053 trace) do not differ by much, but I have to assume that this little difference is having a huge impact on the ordering. Perhaps the straw that broke the camel's back. Thanks for the feedback. From: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx] Sent: Tuesday, July 25, 2006 1:34 PM To: Schultz, Charles; oracle-l@xxxxxxxxxxxxx Subject: RE: Query tuning exercise: what to look for in a 10053 trace Yep, read them both - and saw his presentation at Hotsos :-) I'm not sure on this, but I think the join order will be a result of the estd cardinalities as well - with a NL join, it will start with the row source that it expects to return the smallest number of rows, then probe the other table for matching rows. So, if it underestimates the cardinality, then it will end up doing a lot more probing than it expected to do. From: Schultz, Charles [mailto:sac@xxxxxxxxxxxxx] Sent: Tuesday, July 25, 2006 11:25 AM To: Allen, Brandon; oracle-l@xxxxxxxxxxxxx Subject: RE: Query tuning exercise: what to look for in a 10053 trace I see you have been reading his "tuning by cardinality feedback" paper. Or perhaps "under the hood..."? =) I have read them, but it takes a long time for me to absorb the wonderful insights these guys have. From an academic standpoint, I am trying to figure out why the CBO is using a different join order - perhaps this is a vain and useless endeavor, but I am curious. Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.