RE: About an old hint question

Mladen,

Have you ever tried
_like_with_bind_as_equavlity=TRUE,

This parameter is introduced only to fix like your kind of problems.

KG

>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.

Best Regards,
K Gopalakrishnan

-----Original Message-----
Gogala
Sent: Wednesday, May 14, 2003 5:12 PM
To: Multiple recipients of list 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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.com

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).