Re: RULE hint

Tanel,

Thanks for your suggestions, I'll do some more research on these parameters.

Thanks,

Bala.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 5:39 PM

> Hi!
>
> First of course get the company, who sold you your development
environment,
> basic functionality like bind variables to work.
>
> You could reduce your optimizer_max_permutations (default 80000), but be
> aware, that CBO might not be able to calculate that good execution plans
for
> big-number-table-joins anymore. For later versions of Oracle Applications
> 11i, the max permutations is set to 2000 for example.
>
> If lower number of permutations isn't ok for you, then you could set max
> permutations to a huge value, *temporarily* set _optmizer_search_limit to
12
> (default is 5), then parse, record the best join order using event 10053,
> then rewrite the SQL with the best table order computed by CBO, and add
> ORDERED and relevant join hints to your SQL. Actually it might be hard
with
> so many tables (of which some are possibly views?).
>
> Then set your search limit and permutations back to normal.
>
> Also, removing histograms could help reducing parsing time, if you have
them
> calculated... (as the parsing time is the problem here, not actually
> fetching data).
>
> Tanel.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Thursday, July 10, 2003 12:14 AM
>
>
> 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: 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).

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