暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

_optimizer_cost_based_transformation

原创 刘朝阳 2021-03-29
2936

Oracle has many hidden parameters which should only be used at the direction of Oracle Technical Support, but under the right circumstances there can be a “silver bullet”, profoundly effecting system-wide performance.

http://www.dba-oracle.com/t_optimizer_cost_based_transformation_parameter.htm

====================================================================================
One of these undocumented parameters is _optimizer_cost_based_transformation, an important parameter for influencing the cost-based SQL optimizer, a parameter that enables cost-based transformations.

Many shops are experiencing issues after a 10g upgrade and for full details, see my notes on 10g upgrade performance issues.

What is a cost-based transformation?

Oracle author and SQL tuning expert Kim Floss notes that some common Oracle cost-based transformations include the all-important materialized views as well as select-join, group-by, and distinct-view merging.

OR expansion?converts a query with ORs in the WHERE-clause into a UNION ALL of several queries without ORs

Materialized view rewrite

Star transformation

Conversion of set operator intersect into join and minus into anti-join

Issues in 10g cost based transformations

The web is littered with end-user reports of sporadic performance issues after a 10g upgrade, some relating to sub-optimal SQL execution plans. For full details, see my notes on 10g upgrade performance issues.

Oracle bug 4648181, notes two possible workarounds as per MOSC note 4380992.8 to set _optimizer_cost_based_transformation = off or set _optimizer_push_pred_cost_based = false.

Oracle Freelists has this great note where we see a bug:

“Even since our upgrade from 10.2.0.2, we have started to hit bug#4686006.” . . . An alternate workaround mentioned in the MOSC doc#392551.1 is to unset the _optimizer_cost_based_transformation parameter.

I can understand that by turning it off I would be relying on heuristics for SQL transformations i.e. view merging, predicate push or un-nesting etc. But, by setting it at the
system level, wouldn’t that leave out a lot of good stuff that would have come to use because of the cost based SQL transformations."

This blog notes an action on Oracle Bug 3723007 - Predicate push down may not occur in 10g.

"There is a patch for this bug, but we wanted a quick workaround that we could apply immediately. There is a workaround that involves changing the parameter _optimizer_cost_based_transformation to off.

Now traditionally you are not supposed to go mucking around with underscore parameter but as oracle support were giving the go ahead we were not too worried. So we actually wanted this to just come into effect for the user that performs the whois queries so we decided to put a logon trigger for that user:

SQL> CREATE OR REPLACE TRIGGER logon_trigger
after logon on USER.schema
begin
execute immediate
‘alter session set “_optimizer_cost_based_transformation”=off’;
end;
/

We now have this running and the whois queries are taking around two orders of magnitude less database resources."

This document notes other 10g upgrade issues with advanced SQL optimization in subquery un-nesting and outer hash joins:

"I have noticed after upgrading from 9i to 10g, the biggest impacts for query performance were -

(1) subquery unnesting (mostly, unnesting EXISTS to HASH JOIN SEMI), and

(2)right outer hash join (smaller table in an outer join is used as the hash table, unlike what was happening in pre-10g versions).

In some cases (till 10.1.0.4) we would have to hint the sub-query with NO_UNNEST to avoid the hash join semi. . .

The only ways to get rid of this problem (ora-979 is omitted if it is raised in inline view) is to use the hint NO_MERGE in 9i, and depending on 10g version and testmerge table columns - setting the ‘_OPTIMIZER_COST_BASED_TRANSFORMATION’ to off on 10g."

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论