Re: RULE hint

From: Bala Regupathy <oramail_at_sbcglobal.net>
Date: Wed, 09 Jul 2003 15:06:37 -0700
Message-ID: <F001.005C41ED.20030709145924@fatcity.com>

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

Received on Wed Jul 09 2003 - 17:06:37 CDT