Cary Millsap’s recent post prompted me to write down some of the related thoughts in my head.
Here are few of my mantras for systematic troubleshooting and performance tuning, which have materialized in my head over the years of work:
- Picking the right starting point to troubleshooting and performance tuning is the most important decision in that process.
- Pick the wrong starting point and you end up going in circles.
- The scope of your performance data needs to match the scope of your problem, otherwise you end up going in circles.
- If you don’t measure what matters, you may end up fixing what doesn’t matter.
- If you’re not systematic in your troubleshooting, you may get lucky, but you don’t want to be dependent on luck! Moreover, you wont’t need to be lucky if you are systematic in your work!
- Performance tuning is overrated. Fixing fundamental design and coding flaws via changing a magic configuration parameter is a dream just like is getting slim and healthy via eating magic diet pills bought from TV shop.
- Your response times are too long for only two reasons:
- You are doing too much work
- You are waiting for too much
…both of the above things can be measured in Oracle…
- There’s no such thing as slow database or slow system. How can it be slow independently, without anyone experiencing this slowness?
- If users say that a database is slow, they must be experiencing that somehow! The only way to experience database slowness is via a connection to it, in which case you’ll have a session (to measure).
- If a monitoring system says that a database is slow, then it must be running and measuring response time of some task just like users do, otherwise it can not reliably say something is slow.
- Performance is about one thing and one thing only – time. And time is measured in seconds, not in CPU utilization, number of physical IOs or looks of an execution plan.
Here’s a link to a Cary Millsap’s awesome post, read it!