Last night the Suncoast Oracle Users' Group hosted Cary Millsap of Method-R Corporation. You may have heard about Mr. Millsap, he's written a few books, a few papers, done a couple of presentations...
Thinking Clearly About Performance
Find his paper here.
While the paper is good, I don't think it compares to the presentation.
This was my
We were treated to 2+ hours ending a little after 9. There wasn't a dry eye in the house. Oh wait, that was something else. It was a packed house, approximately 50 attendees including Enrique Aviles (aka "evil Les" or @eaviles94) and his friend Eric, who drove down from Orlando.
One of my (many) takeaways was finally understanding what the prepare, execute and fetch were.
Here's the sequence diagram...
Here's the profile...
I had never thought of it in the context of what something like java does. Once I did, easy. Here's some pseudo-code:
ps = prepareStatement( "SELECT table_name FROM user_tables" );OK, not the best example, but it helped me finally make the connection.
rs = executeStatement( ps );
It's All About the Skew
So you have CPU, Network, IO and Something Else. Is it divided evenly among the 4 components (talking about performance here)? 25% CPU. 25% Network. 25% IO. 25% Something Else. No, typically it's not. When you see the spike, you know that's where you start to look (is that Axiomatic?). I can't do it justice (pun intended)...so read the paper.
The beauty of this approach though is that it's not just confined to database performance, it's software performance in general. I think the "software" people get it more than we database people do too. You have to instrument your code if you are ever going to be good at Performance Tuning.
If you ever have the opportunity to see Cary speak on this topic (really, on any topic, he's incredibly good a breaking down a complex topic and making it digestable by mortals), don't hesitate, go see it.
Cary's got some plans for the near future too, so you may just get that opportunity sooner than you think. I won't spoil it, but I will announce it after he goes public.