RE: Query tuning exercise: what to look for in a 10053 trace

 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.

Other related posts: