I was re-hosting the website, so that's why it was down for 36 hours last
week. These things are never smooth... :-(
on 7/9/03 4:24 PM, Tanel Poder at tanel.poder.003_at_mail.ee wrote:
> 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" <ORACLE-L_at_fatcity.com>
> 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" <ORACLE-L_at_fatcity.com>
>> Sent: Wednesday, July 09, 2003 4:14 PM
>>
>>
>> what are your
>> optimizer_index_caching
>> optimizer_index_cost_adj
>> optimizer_max_permutations
>> set to?
>>
>>
>>>>> oramail_at_sbcglobal.net 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: oramail_at_sbcglobal.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
Received on Thu Jul 17 2003 - 20:57:20 CDT
