RE: SQL statement with hints or without hints

Hi Raj, my two cents, fwiw:

Interesting. I have worked places where hints were frowned upon ("why is Oracle not doing it's job? Hey, you, DBA, you fix it. No, I don't like your hint. Fix it")

However I feel the opposite way. I have also worked in very liberal environments ("make it work! now!"). I truly believe that we, as developers, know the data better than the optimizer. Don't get me wrong, not all sql statements require hints, but there are statements that will benefit from having them. Determining which is which is the trick, based upon application behavior.

I also think that a hybrid environment requires more "assistance" via hints than pure oltp or dss. Hybrids provide their own unique type of "challenge".

omg did I just refer to myself as a developer...

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117

> -----Original Message-----
> From: Jamadagni, Rajendra [SMTP:Rajendra.Jamadagni_at_espn.com]
> Sent: Friday, April 12, 2002 10:03 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL statement with hints or without hints
>
> Hi all,
>
> We are running a kind of hybrid mode, mainly RBO with some tables analyzed
> (mainly for intermedia). Last week we had an interesting situation, when
> tuning a huge SQL, we created an index and the query worked fine, but
> later
> in the day developers complained that their queries are running slower.
>
> We finally took out the new index and things were fine after that, but
> this
> brought out few important issues,
>
> 1. Developers do not put hints in their SQL statements.
> 2. They implicitly reply on a set of indexes that have worked for them.
> 3. Due to reasons of 1 and 2, no new indexes can be created because it
> will
> make their queries run slower.
> 4. As an effect of 3, new queries that can't make use of these set indexes
> will always be slower. Some of these queries can't be rewritten to make
> use
> of the existing indexes due to business logic involved.
>
> My question is, do you, in your organization recommend putting hints in
> the
> SQLs all the time, some times or not at all ?? It doesn't really matter
> if
> you use RBO or CBO.
>
> When the developers questioned me, I told them 'Oracle optimizer is not an
> exact science, especially in a mixed RBO/CBO mode, so it is bound to make
> some wrong choices and that is precisely why Oracle calls these things as
> "hints", so we tell the optimizer to do the right thing."
>
> I am in a good mood to write a short note explaining developers why they
> (must) use hints in their SQL statements.
>
> Your input is greatly appreciated
> TIA
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of ESPN
> Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
> << File: ESPN_Disclaimer.txt >>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).