RSS Feed

Method R Discussion Group

Hejsa!

I have started reading "Optimizing Oracle performance" (Cary Millsap, Jeff Holt). So far I find it quite good.

However, I haven't been able to see so far, whether Method R is compatible with this specific product information. Can you help?

"Performing ANALYZE or dbms_stat on our product tables will cause Oracle to choose the Cost Based Optimizer and not only that it might slow the application but can cause a malfunctioning of our product functions.

Our product was written and is tuned to work with the Rule Based optimizer.  The queries we issue on our product tables are very simple.  It is usually a select by index key prefix.  We generally do not do joins or anything fancy on them.  There is no benefit in having cost-based-optimization of our product queries because the rule-based query plan is ALWAYS optimal.  The Cost Based Optimizer can only slow them down."

After all, I don't want to wreck this product...

Med venlig hilsen
Lise Andreasen

FUJITSU

Lise, thank you for your interest and your kind words.

The answer to your core question, "Is Method R compatible with this specific product information?" is yes. Method R is a means for determining whether something is performing optimally, and, if it's not, for determining exactly what you should do about it. Method R works equally well whether you're using Oracle CBO, Oracle RBO, or in fact not even Oracle at all. Notice in the Method R steps (I'll restate them here), there's no mention of Oracle, or databases, or even computers for that matter:

Method R
1. Identify the important task.
2. Measure its response time (R). In detail.
3. Optimize R in the most economically efficient way.
4. Repeat until system is economically optimal.

We could just as well be talking about how you optimize your drive to the office as about Oracle CBO or RBO.

So, please do pay attention to the advice in the book, because it is relevant to you regardless of how your vendor writes its code.

Now, to the product information itself. The information you quote here is sensible from one perspective, and I think I do understand that perspective very well. I'd like to comment on it, however.

Realize that Oracle, as of release 10.1 of the Database, no longer supports the rule-based optimization (RBO) code path in the Oracle Database kernel. At one time, I thought this was no big deal. The code isn't going away, after all. In fact, parts of the Oracle Database 10g kernel actually still use parts of the RBO. The most common argument for moving from RBO to CBO was "with RBO, you don't get any of the new join methods, like hash joins for example." Of course your vendor is immune to such an argument, because, to paraphrase their own words, "vi bruger ikke joins" (www.vibrugerikke.dk).

But soon after the release of 10g, a student described a problem he was having. He had used the "RULE" hint in one of his queries as he was experimenting with ways to make his SQL run faster. With the "RULE" hint, the query returned the wrong result set--THE WRONG ANSWER.

Of course, this is a bug, right? Well, not anymore, because the rule-based optimization code path in 10g is no longer supported. So, not only will using RBO prevent you from getting the benefits of any of the new optimization technology available in 9i, 10g, and 11g, ..., as of 10g, using RBO puts your application at risk of returning the wrong answer. Forever.

Which, yes, is scary.

Now, the core of the problem is this: CBO is complex, it has a mixed history of success, and a lot of people don't trust it. It is in fact monstrously complex down inside the Oracle black box, but distrust of the CBO is, I believe, mostly unfair. It is very intelligent software. It does do the wrong thing sometimes, but most of those times it's an operational error that causes it (statistics collected poorly or not at all, etc.), not an Oracle software error.

Either way you feel about the RBO/CBO argument, CBO is here to stay, and RBO--as of 10g--is gone to stay. Vendors who express an unwillingness to make friends with CBO eventually will either change their mind or cease to be relevant in the Oracle marketplace.

Hejsa!

And thank you for your reply. Wow, surprising (to me) news about oracle 10. We'll see how that affects this particular product...

Meanwhile, having read some of the book, I guess I can now ask my original question in a slightly more sophisticated fashion.

Does "don't use analyze or dbms_stat" conflict with "use extended sql trace"? After all, I guess there's a reason analyze mess things up, and as long as I don't know that reason 100%, the reason might actually be the way it traces things...

I think a few of my hairs actually turned gray while reading chapter 5. But I still have high hopes for becoming a master trace file reader. And I am definitely still reading.

Lise

Your question: Does "don't use analyze or dbms_stat" conflict with "use extended sql trace"?

The answer is still "no." Using analyze or dbms_stats puts statistics into your database that will influence how the Oracle query optimizer works. Using extended SQL trace doesn't influence how the optimizer works; it reports after the fact what the optimizer DID do. No conflict there.

Regarding your hopes for becoming a master trace file reader... I wouldn't do it if I were you. That's why we built Hotsos Profiler. We've distilled over 10 man-years of hard-earned knowledge about trace files into a software product that can do 99%+ of the the worrying for you. It takes care of the "grunt work" of processing the raw trace data so you can solve your performance problems more quickly (and accurately) and then have some time left over to live your life and keep your hair dark. :)

Take a look at http://www.hotsos.com/results.html, if you have time, to see what I mean.

Thank you! Now I see the difference.

Just recently discover some peculiar behavior. I had a complex query which took over 3 seconds to run. However, when I ask my dba to run it in sqlplus at his desktop, it ran in sub-seconds. It looks to be consistent between running on sqlplus in my desktop and his desktop. So, we started to use sql*trace to see what happen. Once sql_trace turn on, the query ran in sub-second on my desktop, when turn off, it ran over 3 seconds. Does anyone knows why this is the behavior?

Sorry to intrude on this thread.

See if http://tkyte.blogspot.com/2007/09/tuning-with-sqltracetrue.html helps you.