ࡱ> /( / 00DArialndRomantt0x: 0DTimes New Romantt0x: 0 DWingdingsRomantt0x: 00DGaramondRomantt0x: 0 A .  @n?" dd@  @@`` sy,,HH** >>   () 59:>@CEGHJLNPQRTUVWXefgiklmnopq 0AA@8)*5-ʚ;G%ʚ;g4CdCdbx: 0.ppp@ <4dddd@ 0t0 <4!d!d@ 0t<4BdBd@ 0t80___PPT10 ?  %&=But Mogens, We are STILL Not Tuning the Right SQL Statements!>>6$+ AgendaVTo open communication about the many causes and approaches to performance optimizationUFinding Response Time Consumers QMethod R Reduce the response time Method M Find the troublemaker Method 42 ??????t         RMethod R ( Work first to reduce the biggest response time component of the business most important user action Response Time Analysis Stopwatch Timed Events&~~  ! Method R Are these the same? Find the most time consuming sql statement and tune it Look for the non-idle wait event that has the most elapsed time and try to reduce it NO! But& it is what I often see and hear Misapplication of Method R We try to fix what we knowL(6(6  LThere s No Business Like Slow Business''( '@ business most important user action Do we really know the business? Do we really know user expectations and experience? We don t need no stinkin benchmarks!&{&{&   8It s All About Response Time  Work first to reduce the biggest response time component&  Response time is more than just db time We can t optimize what we can t analyze The most time consuming statement is not always the problem&== !What we can fixYConfiguration issues Optimizer Options Basic SQL tuning Indexes/Table Scans Minor ChangesL""  Intermission Performance Tools Tools can be helpful BUT they show you what the author(s) think is important& not necessarily what you need to see Management Metrics How is the system? Can we automate/report on response time?Lr<r<RThank you, Graham  So which SQL should we be tuning?  Who says it is a sql statement?  G Assertion  We can trace sql, tune sql, make sql run faster& AND STILL NOT HAVE ANY MEASURABLE IMPACT! Because SQL is not the root cause of poor performance:1`1*(666/HIssues Is optimizing response time a solved problem? A broken business cannot be tuned Russian Roulette with Silver Bullets Bad SQL does not just appeard  *$ Solved Problem? f Optimizing response time is for the most part a solved problem Cary Millsap Then why is it still a hot topic? Because it is not solved Too few people know too little informationRA "DA "D  !A broken business cannot be tuned""($If the root cause is a faulty business process or work culture, adding hardware won t help Conflicts between departments and prioritization issues!He Who Lives by the Silver Bullet Dies by the Silver Bullet If you can t explain it, how can you repeat it? J6Bad SQL Just Doesn t HappenIIt has to be written by someone It is released into production by someone$) It is written, so shall it be People write bad sql Lack of knowledge or resource Prioritization of other skills No corporate commitment People write code generators that write bad sql Good news  one fix many statements Bad news  one fix many conditionsLU0GU0G@'EG When good sql goes bad  Invalid testing Non production-like system Changes in business Size of business Focus of business Users defy expectations New queries Using technology for unplanned usest#0#0   See SQL Run; Run SQL Run. Ineffective Quality Assurance and/or Change Management Developers run amuck in production & until performance degrades then it s the DBA s help desk ticket People do what they can& not always what they should&ZuZu $D uhvelopersSo& does this mean that it is all the fault of developers? NO! We all share blame Education is the key6>>'Why Am I Even Here? Are you the right audience? Maybe, Maybe Not& Your attendance indicates you acknowledge there are still performance problems Who is the right audience? ADD (not what you think) Management UsersLa*a*$Follow the Yellow Brick RoadBusiness Must be an active participant Demonstrate the value of good performance Technical Learn as much as you can Demonstrate good troubleshooting, tuning and testing processes Political Be friends with developers Let them take credit ZHZ ZXZ Z0Z H X 0  /"#%&  0` 3` ff>>\fg` J*T333` QYmx~3ft` \ғhEy`` cb^DDf`Y˵W` sg7xGr` K%ޯd{mG/` 33f>?" dd@,?nFd@    @ `  n?" dd@   @@``PR    @ ` `<p>> z(    6 #" `b `  X*   6 #" `` `  Z* xT ~  "~\ {  "{  c BB CDEFd @ bb   H   T W6Vw}\gFQ6<1++11 1L b6xQrq 6\}N   - c    0 A Q g     S    6N KawF bFy0 a*ly7lE;uz  B | a F 0 ! 1 < B < & l L 0  @`"T   c |BC+DEFyd @ ==gL6cI}\eA& w6m!W<! &<W!r6W}F6\}>68QNlX^XH8+Sgg|@`"Jo 5 J   B! CDEF @ ))\*l{FDNF W% W ! !   ! 6X L b x_ /aF6b}SA$|l\\TX@`s"*  T  u4   B C%DEF ss&QW}k6D Q r 8 N c t y y ;t Qi gS }8     T   :0\-Q4*%%v*o(V}5q;  S   < s } l \ g} \, L ;n &  Z}\[6 }WF<! e C }h m W ]  ,t B RxkqA,Rg@`" {b    BCDEFy @ ,,Lh6\}mWG &a*zCxsh-&jG]~5g6&LLZ\@`s"*   u(     BCDE|Fy @ __@% M|f4@{ f0`lA}L\A+h MB6ww FJl{ 0V|{J  *5KlGn a*:Pf\|6 /6Kbf|F kP}5x}VvaP@@@`s"*   =>    c BlCrDEF"d@ rlrl@`"~  6 " `}  B Click to edit Master title style!  6젍 #" ``   Z*   0 " `  RClick to edit Master text styles Second level Third level Fourth level Fifth level!    B  s *޽h ? 3___PPT10i. u%.+D=' = @B +  Stream ) 0 9(  xT ~  "~\ {  "{  c BB CDEFd @ bb   H   T W6Vw}\gFQ6<1++11 1L b6xQrq 6\}N   - c    0 A Q g     S    6N KawF bFy0 a*ly7lE;uz  B | a F 0 ! 1 < B < & l L 0  @`"T   c |BC+DEFyd @ ==gL6cI}\eA& w6m!W<! &<W!r6W}F6\}>68QNlX^XH8+Sgg|@`"Jo 5 J   B! CDEF @ ))\*l{FDNF W% W ! !   ! 6X L b x_ /aF6b}SA$|l\\TX@`s"*  T  u4  B C%DEF ss&QW}k6D Q r 8 N c t y y ;t Qi gS }8     T   :0\-Q4*%%v*o(V}5q;  S   < s } l \ g} \, L ;n &  Z}\[6 }WF<! e C }h m W ]  ,t B RxkqA,Rg@`" {b   BCDEFy @ ,,Lh6\}mWG &a*zCxsh-&jG]~5g6&LLZ\@`s"*   u(     BCDE|Fy @ __@% M|f4@{ f0`lA}L\A+h MB6ww FJl{ 0V|{J  *5KlGn a*:Pf\|6 /6Kbf|F kP}5x}VvaP@@@`s"*   =>    c BlCrDEF"d@ rlrl@`"~  < "F   B Click to edit Master title style!  0< " `    E#Click to edit Master subtitle style$  6 #" `` `  X*   6 #" `b   Z*   6 #" `d `  Z* B  s *޽h ? 3___PPT10i. u%.+D=' = @B +) 0 PX(  X X 0 P    X*  X 0,     Z* d X c $ ?   X 0H  0  RClick to edit Master text styles Second level Third level Fourth level Fifth level!     X 6 _P   X*  X 6% _   Z* H X 0޽h ? 3380___PPT10.@* 0 ' (  x  c $p F     0 0 )KCOUG Conference October 2006 Daniel Fink* *6 H  0޽h ? 33___PPT10i.+D=' = @B + ) 0 P0(  x  c $  `}   x  c $͍ `  H  0޽h ? 3___PPT10i.0 ~+D=' = @B +} ) 0 $(  r  S c  `}   r  S c `  H  0޽h ? 3___PPT10i.-7+D=' = @B +} ) 0 @$(  @r @ S j  `}   r @ S j `  H @ 0޽h ? 3___PPT10i.pocs+D=' = @B +} ) 0  L$(  Lr L S 0r  `}   r L S s `  H L 0޽h ? 3___PPT10i.B+D=' = @B +} ) 0 p$(  r  S }  `}   r  S } `  H  0޽h ? 3___PPT10i.G+D=' = @B +} ) 0  $(   r   S <  `}   r   S  `  H   0޽h ? 3___PPT10i.+D=' = @B +} ) 0 $(  r  S 䓕  `}   r  S  `  H  0޽h ? 3___PPT10i.NX++D=' = @B +} ) 0 ($(  (r ( S $  `}   r ( S  `  H ( 0޽h ? 3___PPT10i.p+D=' = @B +} ) 0 d$(  dr d S   `}   r d S t `  H d 0޽h ? 3___PPT10i.BP'+D=' = @B +} ) 0 @T$(  Tr T S   `}   r T S 諕 `  H T 0޽h ? 3___PPT10i.?+D=' = @B +} ) 0 p`$(  `r ` S   `}   r ` S 趕 `  H ` 0޽h ? 3___PPT10i.Bp_+D=' = @B +} ) 0 0$(  0r 0 S   `}   r 0 S ܼ `  H 0 0޽h ? 3___PPT10i._"+D=' = @B +} ) 0 $(  r  S Ǖ  `}   r  S Ǖ `  H  0޽h ? 3___PPT10i.F`u+D=' = @B +} ) 0 8$(  8r 8 S Hҕ  `}   r 8 S  ӕ `  H 8 0޽h ? 3___PPT10i.гl+D=' = @B +} ) 0 $(  r  S @ٕ  `}   r  S ڕ `  H  0޽h ? 3___PPT10i. +D=' = @B +} ) 0 P$(  r  S ە  `}   r  S  `  H  0޽h ? 3___PPT10i.n&+D=' = @B +} ) 0 0 $(   r   S   `}   r   S  `  H   0޽h ? 3___PPT10i.C?+D=' = @B +} ) 0 D$(  Dr D S t  `}   r D S L `  H D 0޽h ? 3___PPT10i. XX+D=' = @B +$ ) 0 $(  r  S t  `}   r  S L `  H  0޽h ? 380___PPT10.pk ) 0 0(  x  c $(֕  `}   x  c $f `  H  0޽h ? 3___PPT10i.`+D=' = @B +$ ) 0 p$(  r  S `  `}   r  S 8 `  H  0޽h ? 380___PPT10.F`z  0  `\(  \X \ C X    \ S HX 0   SResponse Time != Database Time Actual database time might be a trivial contributor T 5 TH \ 0޽h ? 3380___PPT10.@F 0 ` (  X  C X     S  0X 0   "At RMOUG Training Days this past February, I listened to Mogens s  No, Cary, We are not tuning the right SQL statements . As I thought about his points, I realized that 1) There is a lot of truth in what he is saying and 2) there are things he is not addressing. H  0޽h ? 3380___PPT10.FX3U  0 e(  X  C X     S X 0   gSOkay, these topics are not totally related to the overall message from Mogens, but H  0޽h ? 3380___PPT10.6 Y,4 0 D(  X  C X     S X 0   F2This topic was also submitted to Mogens s Miracle DB Forum and Graham Wood (one of the track coordinators) sent me an email with& To which I responded& H  0޽h ? 3380___PPT10.7 ?,E 0 U(  X  C X     S 8X 0   WCHow many people here are developers? How many are DBAs? How many are Architects/Designers? Managers? I was able to spend some time recently with Mogens and Cary. Part of the discussion was centered around this thread and what each of them said and meant. This is the intent of the presentation, to open communication and H  0޽h ? 3380___PPT10.˹l 0 rj(  X  C X   j  S lX 0   Do not take anything I am saying to justify looking at a developer and saying  It s all your fault! (even if it is). I don t think there is anyone here who intentionally writes bad code, but most people just don t take the time to write good code!H  0޽h ? 3380___PPT10.Gr@/]Oq vk @u9~ ȅ܏0/y{ҊjhMWt<aCp'1Oh+'0U hp  $ 0 <HP<No, Mogens We are STILL Not Tuning the Right SQL StatementsDaniel W. FinkStreamDaniel W. Fink82Microsoft Office PowerPoint@pQ@@N3<GSg  )'    """)))UUUMMMBBB999|PP3f333f3333f3ffffff3f̙3ff333f333333333f33333333f33f3ff3f3f3f3333f33̙33333f333333f3333f3ffffff3f33ff3f3f3f3fff3ffffffffff3ffff̙fff3fffff3fff333f3f3ff3ff33f̙̙3̙ff̙̙̙3f̙3f333f3333f3ffffff3f̙3f3f3f333f3333f3ffffff3f̙3f3ffffffffff!___www4'A x(xKʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___www___e_e____eee_e_e_e_eeee_e_e__e_e_e_e_ee__e_e_eeeeee_e_e_ee___e_eeeeeeeeee_e_e_eeeeee_e_eee___eeee_ee_eeee_e_eeeeee_e_eee_e_eee_e_eeee_e_e_eeeee__e_e_e__eeeeee__ee_e____________________e_e_e_e_e_ee_e_e_e_e_e_eeeeeeeeeeee_e__e__e__e___ee___e___e_e___e__e__e__e__e_e_e_e_e_e_e_e_e___e__e_e_e_e_e_e_e_e_e_e_e_e__e_e_e_e_e_e_e__e_e__ee_e_e_e_e_e_e_e_e_e_eeeeeeeeeeeeeeeeeeeeee_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee___e___e___e___e___e___e___e___e___e___e___e___e_____e___e___e___e___e___eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee_e_e_e_e_e_e_e_e_e_e_e_e__eeeee_e_e_e_e_e_e_e_e_e_e_e_eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee_e__e__e__e__e__e__e__e__e__e__e__e____e__ee__e__e__e__e__e__e__e__e__e__e__e__e_eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_ee_e_eee___e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_eee_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_ee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eeeee_eee_ee_eee__ee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_ee_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e__e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_e_eeee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_eee_But Mogens, We are STILL Not Tuning the Right SQL Statements!Agenda Finding Response Time Consumers Method R ! Method R'Theres No Business Like Slow BusinessIts All About Response TimeWhat we can fix IntermissionThank you, Graham AssertionIssuesSolved Problem?"A broken business cannot be tuned"He Who Lives by the Silver BulletBad SQL Just Doesnt HappenIt is written, so shall it beWhen good sql goes badSee SQL Run; Run SQL Run DuhvelopersWhy Am I Even Here?Follow the Yellow Brick Road  Fonts UsedDesign Template Slide Titles&_㗼0Daniel W. FinkDaniel W. Fink  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^`abcdefghijklmnopqrstuvwxyz{|}~Root EntrydO)Current UserSummaryInformation(_0UPowerPoint Document(DocumentSummaryInformation8