| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RULE hint
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.
what are your
optimizer_index_caching optimizer_index_cost_adj optimizer_max_permutations
>>> [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).Received on Wed Jul 09 2003 - 16:46:03 CDT
 |
 |