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 [EMAIL PROTECTED] 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" <[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: Tim Gorman 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).
