Re: SQL Tuning Case .... When is Select /*+ FIRST_ROWS(200) */ Hint Useful ?

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Sun, 09 Nov 2008 12:38:44 +0100
Message-ID: <4916CBC4.3010606@roughsea.com>

Vivek,

  I have elaborated on my comments. I can be useful to others :-).

 

Qs Should the OR Conditions be replaced by UNION ALL for Reducing CPU Usage of DB Server?

The first thing is that in a relatively simple case (I mean not a query built on views built on views built on views ...) the optimizer should be able to decide whether it should run the query as an OR or an UNION ALL. In your case, CONCATENATION in the execution plan shows that Oracle actually runs your query as an UNION ALL (UNION ALL means several queries, while OR is only one). The question is to know whether a query in which you have to test a row against several conditions (OR) will be faster or not that several queries where there is only one condition. You can have all cases, it depends on the efficiency of the OTHER search criteria. I think that your query would be less legible with UNION ALL, but it's a matter of taste.

1) Don't join things just in case. It's easy to add a subquery if there is a condition on a related table. The optimizer will know how to handle it. Be minimalist.

Qs An Example pls?

Well, suppose that you want to return data from a table called CUSTOMERS, and that the interface allows users to return a list of customers based on several possible criteria,  say whether they live in a given town, or whether they have ordered something  recently, or whether they have ordered a particular item.

If your only criterion is the town, you need nothing but CUSTOMERS. If you want to know whether customers are active, you need CUSTOMERS and (say) ORDERS. If your criterion is based on what customers purchased, you need CUSTOMERS, ORDERS and ARTICLES.

So you *may* have some conditions. Some people will  write a big query that joins everything, complete with join conditions and conditions  such as:
                     and a.article_name  = nvl(:article, a.article_name)
                     and o.order_date >= nvl(:date, o.order_date)
                     and c.town = nvl(:town, c.town)
 with usually a DISTINCT at the  top of the query ... For all cases where there is no condition on anything that isn't in CUSTOMERS, it will be an overkill, because you'll execute joins for nothing. You'll end up with queries that will be much slower than they should.
Some other people will shun nvl() and dynamically add the conditions, but it's not better. And if they hard-code conditions, concatenating values to the statement, it will be worse because each execution will mean parsing. What you should really start with is the simplest query on CUSTOMERS, then, if you have (say) a condition on ARTICLE add (dynamically)
                        and c.customer_id in (select o.customer_id
                                                     from order o, articles a
                                                     where o.article_id = a.article_id
                                                       and a.article_name = :article)

(and if you also have a condition on the order date, throw it in). That's what I call being minimalist: doing what is required when it is required, but not more often. Put intelligence in the building of the query.

2) Don't hard-code.

Qs if you mean remove the hard-coded 200?  … This is linked to Value of “ROWNUM” which is 201

A limit on the number of rows that is returned is typically something that SHOULD be hard-coded, because it shall not change from execution to execution (and it matters to the optimizer). But values that change between successive executions must be passed as parameters. And when all your code is clean about what should be a parameter and what can be a constant, you'll be able to remove this ugly CURSOR_SHARING = FORCE which is just a fix when developers don't know how to code as they should.

3) At this stage the question about the hint should be moot.

Qs If you mean Hint /*+ FIRST_ROWS(200) */ should be avoided?

I think that hints should be avoided. Bar the odd bug, if you need to tell the optimizer how it should perform its internal cuisine that it knows better than most of us do (I'll make an exception for Jonathan Lewis, Wolfgang Breitling and a few others :-)), there's something wrong in the logic of your query. Hints are like telling a (honest) taxi-cab the best way to take in a town you only vaguely know.

-- http://www.freelists.org/webpage/oracle-l Received on Sun Nov 09 2008 - 05:38:44 CST