SQL Performance for DBAs

1. SQL Performance for DBAs Steve Catmull UTOUG DBA SIG May 2012

2. A bit about Intermountain’s Data Warehouse • We have an open environment. • Any of our 200+ analysts can submit any query into production. • We reserve the right to kill sessions. • We publish high-resource users of our system. • Just switched from single box to RAC last weekend.

3. A bit about me… • I have worked for Intermountain Healthcare for 15 years (12 on the Data Warehousing Team). • My fascination with databases started around 1982 when I started on Commodore 64 and helped write an invoicing application for my Mom’s business. • I love a good challenge. A bit of obsessing goes a long way in solving difficult problems.

4. My SQL Performance Principles • Know your data or know someone who does. • Correct cardinality in explain plans is the heart of enduring performance. • Right balance of simplicity and performance. • Techniques have tradeoffs • Don’t wait to look at waits.

5. Skills You Need • Be able to read an explain plan • Familiarity with the Oracle Concepts guide. • Learn a few simple rules of how the optimizer works.

6. Know Your Data • If you don’t know your data better than Oracle, this technique will not work. • It’s okay to know someone who does.

7. What You Need to Know About Your Data • Rough idea of how many rows are in tables. • Rough idea of join cardinalities. • For each parent, how many children rows? • 1:1, 1:2, 1:3 … 1:n • For popular columns that are filtered, know roughly how they are distributed. • How much of a table are you going to select if you filter the table on that value?

8. What an Explain Plan Teaches • Oracle thinks that about 4,000 male customers have spent more than 1775. • With this explain plan what questions could you ask a subject matter expert?

9. Why is Cardinality Important? • It is the key input to the optimizer. • It’s why Oracle gathers these stats: • Row counts for objects, segments, etc. • Number of distinct values (NDV) • Histograms

10. Beyond row cardinality • Cardinality impacts cost. • Cardinality is an input to cost calculations. • Some other factors that affect plan choice & cost is: • # of blocks (leaf/data) • Clustering factor • Density • …

11. Cardinality Challenges • Correlated columns • Situational guesstimating • Non-uniform distributions • Non-uniform join cardinalities

12. Techniques Have Tradeoffs • We are dealing with database SYSTEMS. • Systems have lot of dependencies. • If you add an index how might it effect… • Overall processing time • INSERT/UPDATE/DELETE performance • Space used • Contention • Accept that sometimes the best thing may be no action at all.

13. Looking at Wait Events • We call them wait events because something is always waiting. • Waiting on IO • Waiting on CPU • Waiting for another session • Waiting for the client to send a command. • They give insight on what is going on right now and historically. • “Database Performance Tuning Guide” for more details.

14. Looking at Waits (Instance)

15. Looking at Waits (session)

16. Applying These Principles “It Ran Fine Yesterday… and nothing changed”

17. “It Ran Fine Yesterday!” • Common for DBA or Developer to say “Nothing changed.” • Reality is that something changed. • It can be costly to find the change vector / root cause. • I often shift to making the most optimal query I can.

18. What Could Change? • The common answer is “nothing changed” but… • Everything could change. • In reality it’s probably one of these: • Underlying data • Stats (object or system) • Explain plan • System load • Cache hit on query • Storage performance • Query • Execution method (binds versus literals) • Oracle parameters

19. Applying the Principles Looking for Undesirable Patterns

20. Ascension of Doom • Typical pattern • Lots of work and the same cardinality for a long stretch. • Often involving nested loops. • Coupled with complains of poor execution time.

21. Are You Kidding Me? • Resultant cardinality looks more like a Cartesian join. • The cause is often Oracle gets confused on join cardinalities. • Usually when someone is joining on data that has a function wrapped around it and especially analytical functions.

22. System HOGS

23. System Hogs • Identify through “Top SQL” in OEM. • Read explain plan through the lens of someone who knows the data. • Do the cardinalities make sense? • Any aggressive transformations being made by the optimizer?

24. Summary

25. Summary: Your Success • It will depend on: • Knowing your data or people who know your data • Discovering how Oracle works. • Example: Can you explain what these plan operations are doing? • Determination

26. Summary – My Typical Checklist • Does resultant cardinality look what I expect? • Do I see odd patterns that I need to verify? • Do I see nested loops where hash joins may be more appropriate. • Do I see evidence of the optimizer doing some query transformations? • Concatentation… • Pushed predicates… • Merge Cartesian…. • Subqueryunnesting • Is the cardinality of the first few steps look right?

27. Let’s Explore