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
Received on Wed Jul 09 2003 - 15:20:46 CDT
