270 likes | 303 Views
Learn SQL performance principles from expert Steve Catmull and understand cardinality, waits, and system optimization techniques. Gain essential skills to enhance database performance.
E N D
SQL Performance for DBAs Steve Catmull UTOUG DBA SIG May 2012
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.
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.
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.
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.
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.
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?
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?
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
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 • …
Cardinality Challenges • Correlated columns • Situational guesstimating • Non-uniform distributions • Non-uniform join cardinalities
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.
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.
Applying These Principles “It Ran Fine Yesterday… and nothing changed”
“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.
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
Applying the Principles Looking for Undesirable Patterns
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.
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.
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?
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
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?