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