Oracle-L: Re: SQL statement with hints or without hints

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Fri, 12 Apr 2002 11:58:30 -0800
Message-ID: <F001.004438D0.20020412115830@fatcity.com>

Raj,
I shall comment on no 3.

Developers do not put hints in their SQL statements.
They implicitly reply on a set of indexes that have worked for them.
Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower.

I had gone through the same situation 1 year back after adding a new index on Oracle Financial Application to expedite or compliment a badly wirtten code and that code written fine but one of AR application using forms screen to add cash receipts become so slow (taking 10 minutes to move from a field instead of couple of seconds) and we have no option except to drop that index and things become normal but we have no option to change that form or put any hint. So under some situations we become helpless. Although I have been adding customized indexes to improve performance of certain Oracle Financials Reports like Accrual Rebuild REconcilation which was taking 30 hours and after adding 6 indexes the time went to 1.5 hours.

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 12 Apr 2002 06:03:28 -0800

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,

Developers do not put hints in their SQL statements.
They implicitly reply on a set of indexes that have worked for them.
Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower.
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!

<< ESPN_Disclaimer.txt >>

MOHAMMAD RAFIQ

Chat with friends online, try MSN Messenger: http://messenger.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.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).

Received on Fri Apr 12 2002 - 14:58:30 CDT