Re: CBO irregularity

Well it may be restricting your database from using better execution plans - in fact given proper care and attention for a few key cases it almost certainly is.

It, if implemented properly and with the full set of required hints
(i'd expect to see ordered in there as well for example), can help
with plan stability if you don't want execution plans to be changed, some (not me) see this as an advantage. I don't. i'm not smarter than the CBO and I don't know what every table and its data distribution looks like currently let alone what it will look like in 6 months time.

On the other hand it is probably a sign that your developers have thought about good execution plans - and that would be a first for me.

Finally, do you have reason to think its bad? do your end-users curse the performance of the ERP system? in particular areas? then it might
(read probably) be and is certainly worth looking at in conjunction
with the vendor. If the performance is fine and the business is running OK find something else to worry about dev/QA/live for example :)

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

----- Original Message -----
From: Raghu Kota (WBTQ) <rkota_at_westonbakeries.com>
Date: Tue, 8 Jun 2004 16:16:26 -0400
Subject: RE: CBO irregularity
To: oracle-l_at_freelists.org


In my ERP Oracle database I see most of the sql with USE_NL, is that bad?? 
 


-          Don't use plan-restricting hints, except in test situations
where you're trying to cause bad performance. For example, don't use
hints like RULE, [ USE_NL, INDEX, USE_HASH, FULL, AND_EQUAL, etc. in
production.

 

If you really want plan stability, stored outlines can give you that.
But the adaptive intelligence of a well-informed and properly
functioning CBO is a smarter long-term decision except in rare cases.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------