Finding the expensive SQL

NOT THE SLOWEST STATEMENT — the one whose TOTAL COST DOMINATES. THEY ARE USUALLY NOT THE SAME STATEMENT. This distinction decides where tuning effort goes, and getting it wrong is why so much tuning work produces no measurable improvement. THE SLOWEST STATEMENT IS THE ONE SOMEBODY COMPLAINS ABOUT. A report taking four minutes, run twice a day. Total cost: eight minutes of database time a day. THE EXPENSIVE STATEMENT IS OFTEN ONE NOBODY MENTIONS. A query taking two hundred milliseconds, run forty thousand times a day. Total cost: over two hours of database time, and nobody has ever noticed it because individually it is fast. SO FIXING THE FOUR-MINUTE REPORT MAKES ONE PERSON HAPPY AND CHANGES NOTHING ABOUT THE SYSTEM. Halving the two-hundred-millisecond query returns an hour of database capacity a day to everybody — which shows up as the whole system being more responsive, with no single user able…

In this module: Module 11 · Performance: finding the actual problem

1. 1Measure before you tuneFree preview
2. 2"The system is slow": turning that into a question
3. 3Where time actually goes
4. 4AWR and ASH, used properly
5. 5A baseline is what makes a report meaningful
6. 6Finding the expensive SQL
7. 7Execution plans, enough to act
8. 8Statistics: the commonest real cause
9. 9Locking and blocking
10. 10When it is the application, not the database
11. 11What breaks: four performance investigations that went wrong
12. 12Lab briefing · Diagnose three slow systems