1. EXTREME PERFORMANCE TUNING GAME PLAN FOR SUCCESS Chris Lawson

2. Agenda • What is Extreme Performance Tuning? • Seven attributes of Extreme Perf Tuning • Chris’ favorite tuning techniques • Some pitfalls to avoid When it doesn’t work— the missing piece of the puzzle

3. A Key Question After you attend a seminar on performance tuning, do you discover that you have become a performance expert?

4. Performance Tuning is Not: • Having really tricky scripts • Running colorful programs • Delivering long reports full of generalities • Being smarter, faster, better-looking than other DBAs

5. The Paradox of Performance Tuning • Being extremely good means being adept in multiple areas • A single strength will not work. • Example: New DBA uses tool (e.g., OEM) to analyze performance • Q: What will be the likely outcome?

6. The 7 Facets of Extreme Performance Logical Reasoning Good Judgment Techniques Hands-on Experience Scripts Focus Tools

7. Q: Which Ones are Taught in Seminars? Logical Reasoning Good Judgment Techniques Hands-on Experience Scripts Focus Tools

8. Only These are Taught in Class Logical Reasoning Good Judgment Techniques Hands-on Experience Scripts Focus Tools

9. Let’s Examine the 7 • Good Judgment • Logical Reasoning • Good Techniques • Scripts • Tools (maybe) • Focus • Hands-on Experience

10. Strategy for Success Logical Reasoning Good Judgment (1) Techniques Hands-on Experience Scripts Focus Tools

11. Good Judgment: Thinking Clearly Difficult to explain—it’s a sound approach to gray areas Q: How do you spot this in candidates? I don’t mean I.Q. “The man who was too smart to talk to” Good Judgment

12. Good Judgment: Thinking Clearly • Easier to spot bad judgment than good • Bad judgment stands out Good Judgment Tale of the “No-good Nested Loops”

13. Good Judgment May be Hidden;Bad Judgment Stands Out “The Thrill of Thrill Hill” The result . . . Good Judgment ?

15. Good Judgment • Firms want people with good judgment, but how do you measure? • Common sense reasoning maybe not so common? • Some examples of smart people using not-so-smart judgment

16. Common Sense Reasoning not so Common . . .

17. Common Sense Reasoning not so Common . . . Sybase DBA violated SOX

18. Common Sense Reasoning not so Common . . . Sqlserver DBA QA ?

19. A Class for Good Judgment? • Realistically, I probably can’t teach you good judgment. • Is there some class to attend, to teach you to not do certain things, like these:

20. Common Sense Reasoning not so Common . . .

21. Common Sense Reasoning not so Common . . . NOT PG&E employees Note parallelism Degree = 3

22. On-Call DBA Common Sense Reasoning not so Common . . . DBA of the day DBA in training

23. Strategy for Success Logical Reasoning Good Judgment Techniques Hands-on Experience Scripts Focus 2 Tools

24. Focus: Keep Your Eye on the Ball Focus

25. Focus Doggedly pursue the root cause

26. Losing Focus: Some Distractions • Pet ideas—maybe RAC or Streams? • To redesign the application—let’s make it more “elegant” • Trying to solve problem in general • My favorite: Avoid solving root problem by blaming others “The burnt-up power box”

27. An Unhelpful Tactic When my application runs slowly, likely root cause is found in: • My app, • Database issue, or • Somebody else's’ app “Those other guys are slowing the disk!”

28. Staying on Focus is Hard Work It’s tempting to switch to generalities. Benefit: • You don’t need to know much. • Avoids hard analysis. • Everybody knows what “Add CPUs” means. Cary Milsap calls this using the “aggregate”

29. Avoid the Dark Side“Aggregate” Method • Avoiding hard work of detailed analysis is immensely appealing. • It’s widespread because anyone can become an expert • You “fix” lots of problems at once! • Solution usually ”Add more hardware.” But it only works for a while . . .

30. Focus: Stay on Target! • Home-in on the exact problem & fix • “Micro” not “Macro.” • Solutions are directly applicable to the problem at hand, so… • You can estimate improvement • It will often be a huge pickup

31. Strategy for Success Logical Reasoning (3) Good Judgment Techniques Hands-on Experience Scripts Focus Tools

32. This is a Huge Deal … Similar to staying focused My definition: “An inductive process of working from facts to conclusion.” Logical Reasoning

33. Barriers to Logical Reasoning • Presupposing form of the answer • Pet solutions Logical Reasoning “Must be the db writer..”

34. A Logical Process:“Physician to Magician” • State problem • Gather facts • Identify bottleneck • Create solution • Document & Declare Victory Logical Reasoning

35. Strategy for Success Logical Reasoning Good Judgment Techniques Hands-on Experience (4) Scripts Focus Tools

36. There Really Isn’t a Substitute “We don’t want DBAs with OCP!” (esp. if in bold red on the resume) Hands-on Experience

37. An Important Question • Why is experience so important? • What does it achieve?

38. Why is Experience So Critical? • Save time by skirting crazy ideas. • Reduces scope of possible solutions • Know how Oracle works in practice • Pattern recognition: “Aha—I’ve seen that one before!” • Gain credibility with your customers. • Inspired by other ideas you’ve seen.

39. You Can’t Transfer Experience “Spend 5 minutes & tell me what you do and …” Hands-on Experience

40. A Big Turn-off: the Hard Work Expert: 10,000 hours of deliberative practice Hands-on Experience Talent Is Overrated By Geoff Colvin

41. Strategy for Success Logical Reasoning Good Judgment Techniques Hands-on Experience Scripts Focus Tools

42. Helpful in Some Cases Tools • A matter of personal preference--could be useful. • Caution: Tool may direct your effort: “OEM says …” • Pitfall: Never gain competence ? A useful analogy >

43. Doctor versus DBA:What do YOU want in a Doctor? • Pleasant? • Nice Office? • Cheap?

44. My Thoughts • Takes the job seriously • Listens to what you say • Understands the problem • Has expertise in the field • Knows practical solutions • How often is he sued?* * Courtesy Dan Grey, PG&E

45. Do You Ask the Doctor This? “What brand of stethoscope do you use?

46. Strategy for Success Logical Reasoning Good Judgment Techniques Hands-on Experience Scripts (6) Focus Tools

47. Scripts Scripts Are Important • Ten DBAs > 10 different scripts. • There usually isn’t just “1 way.” • Your process should drive your scripts, not the other way around. • “Roll your own” scripts or borrow ideas from others

48. Be Open to Different Methods • There’s more than 1 way to solve a problem. • There’s tons of smart people here. Learning from others is better than formal classes—it’s supremely practical. • It’s hard for anyone to be an expert at more than 1 thing. • Watch for people who have a better way at doing something and copy them!

49. Script ideas • Most of my scripts are from others • Other ideas came from user groups and forums, etc. • One idea (following) came from a beginner with almost no experience!

50. What I Learned from a NewbieObject Waited-on SeleCT DISTINCT v$session.module, Sid, username, Substr(program,1,19) PROG , sql_text, object_name From V$Session, V$Sql, dba_objects o Where v$session.status = 'ACTIVE' And username is not null and o.object_id = row_wait_obj# And v$session.sql_hash_value = hash_value and v$session.sql_address = v$sql.address and username <> 'SYS'