| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: About an old hint question
I don't believe the case for hints is as clear-cut as "yes" or "no". Even
the article suggests that hints have some purpose...
FIRST_ROWS tells the optimisor about your preference for receiving results - this is a very valid reason to use a hint. There are a handful of other hints which give additional info to the database engine. Similarly we sometime abuse hints, such as forcing an Index traversal to avoid a sort operation with queries along the lines of "give me the 100 oldest products".
On the other hand many hints like "INDEX" or "USE_HASH" typically shouldn't be required if the database has the appropriate statistics collected and up to date. Bind variables (mentioned below) used to be a big problem but Oracle 9 is beginning to address this issue by peeking at the value whilst working out the explain plan.
I definitely agree with the concept of using hints as a last resort, as opposed to needlessly including them. One good reason is to make use of new database features, or to allow changes in data volume to be appropriately handled. I've seen code written for Oracle 7 and the hints cause Oracle 8 to ignore certain options which would be a faster way to complete the query. The work involved in raising defects to have the application codebase modified is barely justifyable but could be avoided totally by not putting the hint there in the first place. Likewise a hint which was great with 100,000 rows of test data might not be appropriate when scaled up to 100,000,000 rows in production.
Hints definitely have their place in society but I hate people that think they are (a) necessary always and (b) the best way to solve a performance problem.
Regards,
Mark.
Mladen Gogala
<mgogala_at_adelphia To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.net> cc:
Sent by: Subject: Re: About an old hint question
root_at_fatcity.com
15/05/2003 10:11
Please respond to
ORACLE-L
So, if hints are such a big no-no, what other methods are there at our
disposal
for tuning queries? Trust the optimizer? With all due respect, CBO
didn't
yet fully deserve my trust. I've even experimented with
optimizer_index_caching and optimizer_index_cost_adj
with good results. Those two parameters are crucial in making CBO behave more like RBO and use an index when it's there (the mantra of the RBO).
The contention issue are the bind variables from PRO*C programs where things "LIKE :S" are usually resolved by using a full table scan, even if :S is of the form 'ABC%' in which case the query should, as we all know, use an index. The only solution that I have for the problem is to put a dreaded hint in the select.
I believe that those "hints are bad" statements are a part of oracle
marketing
of "smart database which doesn't need a DBA". One thing that Tom doesn't
explain in his article is exactly what does he do to tune an
application?
The phrase was "we find an underlying problem and fix it". How exactly?
By developing a kernel patch? Are we about to see some form of "develop
your
own RDBMS kernel patch" utility? The first version of such utility was
called "external procedures".
In other words, Oracle is being squeezed by M$ and they're trying to
develop
an image of a database which doesn't need tuning and expensive
specialists to
operate. Tom, being a politically correct employee of the Oracle Corp.,
is
wholeheartedly promoting that image. Unless they give me an optimizer
which
will have 100% predicable outcome when I'm writing the query, I'll use
the
tuning tools. Oracle has done a lot of quick and dirty things to emulate
the behavior of SQL Server, one of which are global temporary tables. To
add insult to injury, the quality of their code is more and more like
M$.
On 2003.05.14 16:21 Stephane Paquette wrote:
> Hi,
>
> Some weeks ago, I post a question on the future of hints.
> Today, as I was browsing on asktom, I found this :
>
> In Oracle Applications development (11i apps - HR, CRM, etc) Hints are
> strictly
> forbidden. We find the underlying cause and fix it.
>
> The link is
> :
http://asktom.oracle.com/pls/ask/f?p=4950:8:169648911033790652::NO::F4950_P
> 8_DISPLAYID,F4950_P8_CRITERIA:7038986332061,
>
>
>
> Stephane Paquette
> Administrateur de bases de donnees
> Database Administrator
> Standard Life
> www.standardlife.ca
> Tel. (514) 925-7187
> stephane.paquette_at_standardlife.ca
> <mailto:stephane.paquette_at_standardlife.ca>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephane Paquette
> INET: stephane.paquette_at_standardlife.ca
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: mgogala_at_adelphia.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed May 14 2003 - 21:01:43 CDT
 |
 |