I figured, being an Oracle guy at a MySQL conference, this might be my only chance to answer something, so I went for the mic. Here is my answer.
The most common performance problem I see is people who think there's a most-common performance problem that they should be looking for, instead of measuring to find out what their actual performance problem actually is.It's a meta answer, but it's a meta problem. The biggest performance problems I see, and the ones I see most often, are not problems with machines or software. They're problems with people who don't have a reliable process of identifying the right thing to work on in the first place.
That's why the definition of Method R doesn't mention Oracle, or databases, or even computers. It's why Optimizing Oracle Performance spends the first 69 pages talking about red rocks and informed consent and Eli Goldratt instead of Oracle, or databases, or even computers.
The most common performance problem I see is that people guess instead of knowing. The worst cases are when people think they know because they're looking at data, but they really don't know, because they're looking at the wrong data. Unfortunately, every case of guessing that I ever see is this worst case, because nobody in our business goes very far without consulting some kind of data to justify his opinions. Tim Cook from Sun Microsystems pointed me yesterday to a blog post that gives a great example of that illusion of knowing when you really don't.
17 comments:
Cary,
I agree 100% with your Method R and have used it at clients to solve performance related issues. Even many experienced Oracle DBAs fail to use a methodology and spin their wheels with guess work.
Cheers,
Ben
Treat me as newbie.......
What is method r?
can you redirect me to a url where i can understand it.
Thanks,
Shailesh
Shailesh,
Click the Method R hyperlink in the blog post, and you'll be on your way.
—Cary
Are the slides available to "Performance Instrumentation: Beyond What You Do Now"?
ReneL,
I left a copy with the Percona folks. I'm not sure how they intend to distribute them. If you don't find them that way (or if you get tired of waiting), then send a note to info at method-r dot com, and I'll mail 'em to you.
Cary
@ReneL The slides are at http://conferences.percona.com/percona-performance-conference-2009/schedule.html
@Cary Thanks again for such a great presentation!
-- Ryan Lowe
Thank you, Ryan. It was my pleasure to join you guys. —Cary
Some people get it, some don't, some don't want to.
I agree that people are looking for common performance problem and don't want to understand that every application or environment is unique.
Cary,
just for curiosity;
Did your answer satisfy your audience?
Martin
Martin: Yes, I think it did. —Cary
Hi Cary,
In your book "Optimizing Oracle Performance", you mentioned that high number of parses can cause hige waits on SQL*Net message from client, but did not explain the rationale behind this. Would appreciate if you help me understand the reason for this.
Regards
VLS
Sure, Vivek.
Every time a client application makes a parse call (e.g., prepareStatement() in Java), it inspires a network round-trip between the client process and the Oracle server process. No matter how clever the Oracle Database software is about how to process that parse call, that network I/O eats end-user response time, and it causes network unwanted competition that all the other programs on your system have to contend with.
A parse call is a pure waste of time (and expensive network and computer system capacity) if that call is not absolutely necessary. ...And most parse calls just aren't necessary. See this article for some more information.
Thanks for a very good article Cary. It cleared most of the concepts, especially the one that I was looking for.
Is it applicable for Soft Parses as well ? The reason I as asking this is that I have a TKPROF output, wherein I can see PARSE and EXECUTE to be same for most of the sql's, while the Misses in Library Cache is 0. At the end, I can see that most of the waits are on SQL*Net message from client. Therefore, since misses in library cache is 0, I assume that this infers Soft Parses.
Thanks for your patience and guidance.
Regards
VLS
Vivek,
Yes, you're right about them being "soft parses."
A lot of analysts miss excellent performance optimization opportunities by thinking that "soft parses are good; hard parses are bad." A better performance optimization policy is, "Any unnecessary call is bad."
Think about what a "soft parse" is for a second: it's a parse call that the Oracle Database kernel is able to figure out that it has already seen before. (So it's able, then, to shortcut some internal code path.) But think about that: It takes CPU cycles for the kernel to figure out that it has seen that identical SQL before, and some of that code path is serialized. Also, as I mentioned previously, the parse call itself from the app to the db has consumed irretrievable time and capacity on the network.
You need to ask the question, "Why is the application re-sending a parse call for SQL to the Oracle Database kernel that it has already parsed before?"
The answer is usually that it shouldn't be doing that.
I don't like the name "soft parse," because it makes it sound like excessive "soft parsing" is ok. Soft parses are NOT good. A "soft parse" call is really just a parse call that the application should never have made.
Especially since parsing is such a performance and scalability penalty, it is vital to eliminate as much unnecessary parsing as you can. You can do that only by manipulating your application code.
Thanks for your explanation Cary. I appreciate your patience and knowledge and have been one a regular visitor of your Blogs.
BTW..I am blogging at viveklsharma.wordpress.com
For unknown reasons, my previous URL is not accessible. Your comments and suggestions are a welcome.
Regards
Vivek
It's my pleasure, Vivek.
Post a Comment