Re: RULE hint

Hi!

It's generally good idea to review optimizer_index_caching and optimizer_index_cost_adj in CBO environments, but since you got parsing performance problem currently, I'd not look at them first. You can get elaborate information about those if you search for Tim Gorman's "The Search for Intelligent Life in the Cost-Based Optimizer" document (his site www.evdbt.com was down for some reason).

optimizer_index_* parameters affect costs for index-accesses, but changing them shouldn't make much difference in parsing time (only case I can think of is that when making indexes very sexy using above parameters, then it might allow execution plan early elimination to cut off some plans earlier).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, July 10, 2003 1:59 AM

> John,
>
> I did not change these parameters from its defaults :
>
> They are currently set to :
>
> optimizer_index_caching = 0
> optimizer_index_cost_adj = 100
> optimizer_max_permutations = 80000
>
> Thanks,
>
> Bala.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, July 09, 2003 4:14 PM
>
>
> what are your
> optimizer_index_caching
> optimizer_index_cost_adj
> optimizer_max_permutations
> set to?
>
>
> >>> [EMAIL PROTECTED] 7/9/2003 3:39:31 PM >>>
>
> Few sql queries run fast (less than a second) when I use RULE hint and
> without RULE hint it takes 30 seconds. The queries are not that
> complicated, but joins 10 - 12 tables. I compared the plans between RULE
> hint and witout RULE hint, they are almost the same except join methods.
> Also, I checked the trace with 10046 event, and found out that without
RULE
> hint, the parsing takes almost 30 seconds (when I run the same query for
the
> second/third time it takes less than a second). Our developers ruled out
of
> using bind variables as the roguewave's SourcePro C++ (Edition 5)
libraries
> have serious bugs when they use bind variables (Please let me know if some
> of your developers have any fixes for this). I'm able to tune most of the
> queries by re-writing using UNION/EXISTS/in-line-view etc and get them to
> less than a second, for the rest I'm pushing RULE hint to the developers.
I
> hate to use any hints as they are not scalable between versions/OS/patches
> etc. Do you guys have any idea of reducing parsing time or any
suggestions=
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Bala Regupathy
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Tanel Poder
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).