历史记录
Oracle 技术支持 - 2010年3月24日 上午07时15分57秒 Generic Note
------------
UPDATE:
=======

Hello Lianjia,

If you set the parameter using alter system set "_optimizer_connect_by_cost_based"=false; and you want to revert to the default,
issue alter system set "_optimizer_connect_by_cost_based"=true;

I see in the original SR the parameter is being set using alter system. It is generally recommended to only set the parameter for the sql encountering the problem and not database wide if at all possible.

Rather than using "alter system...", another option if the problem is only occurring on one sql (or only a few sql) and if you can add a hint to the sql, is to only set the parameter for the sql encountering the problem by setting the parameter in a hint:

SELECT /*+ OPT_PARAM('_optimizer_connect_by_cost_based' 'false') */ *
FROM (SELECT t.*, ROWNUM AS linenum
... ... ...

If you test this method and it helps avoid the error reported in the original SR, you do not need to set parameter to false (or back to true) database wide.

Let us know if this information helps.

Best Regards,

Kristin

STATUS:
=======

@CUS - Pending customer udpate.
LILIANJIA@ZKING.COM - 2010年3月23日 下午01时11分44秒 When I modify the parameter "_optimizer_connect_by_cost_based" to false. If there is any problem, how can I recover the parameter?
May I just modify the parameter "_optimizer_connect_by_cost_based" to true?

Oracle 技术支持 - 2010年3月23日 上午12时01分03秒 Comments
--------
=== ODM Answer ===

Hello Lianjia,

The parameter "_optimizer_connect_by_cost_based" is a hidden parameter and therefore there
is not much information available other than setting this to false would turn
off cost based transformation for the connect by statement. The "COST BASED
QUERY TRANSFORMATION" feature is new in 10G and performs additional costing for
features like unnesting, complex view merging, which were not done in previous 9i version.

Since the feature is new in 10G, setting it to false should not impact queries
that used to work well in previous versions as it is re-activating the behavior.
under the previous version. The parameter setting is referenced mainly in
terms of using it as a workaround in cases where you are experiencing a bug.

However, please keep in mind it is always recommended to test all queries to verify the
impact when implementing a parameter change.

Thank you,

Kristin

STATUS:
=======

@CUS - Pending customer update.
ISAK.IBRAHIM@ORACLE.COM - 2010年3月19日 下午07时54分41秒 Comments
--------
=== ODM Question ===
This is spin of 3-1507820061


What is the impact of setting _optimizer_connect_by_cost_based to false
ISAK.IBRAHIM@ORACLE.COM - 2010年3月19日 下午07时53分09秒 问题说明: Impact of setting _optimizer_connect_by_cost_based to false