ANBOB™

最近看到一个oracle 19c bug子查询无法展开导致的性能下降，而且影响19c的大部分版本，可以查看Bug 34044661 – Poor Performance Due to SQL Not Unnesting in Oracle 19C (Doc ID 34044661.8). 最近刚好在Oracle to Highgo（postgresql）后相同的SQL在highgo中性能变差，并且当前的Hightgo还不支持sql hint， 在研究SQL重写刚好把这个问题题简单记录。

19c bug影响后执行计划

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------------------
| 143 |   NESTED LOOPS                       |             |    307K|   135M|       |  1808M  (2)| 19:37:40 |
|*144 |    TABLE ACCESS FULL                 | XXXX        |   2571K|  1054M|       | 49880   (2)| 00:00:02 |
|*145 |    TABLE ACCESS FULL                 | BBBB        |      1 |    32 |       |   703   (2)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

修复后的执行计划

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------------------
|*146 |    HASH JOIN                         |             |    273K|   120M|    11M|   104K  (1)| 00:00:05 |
|*147 |     TABLE ACCESS FULL                | BBBB        |    273K|  8547K|       |   703   (2)| 00:00:01 |
|*148 |     TABLE ACCESS FULL                | XXXX        |   2571K|  1054M|       | 49880   (2)| 00:00:02 |
------------------------------------------------------------------------------------------------------------

该bug 解决方法是

use UNNEST hint
or
alter session set "_optimizer_squ_bottomup" = false;
or
alter session set "_optimizer_cost_based_transformation" = OFF;

建议使用UNNEST或SQL级参数，不建议该_optimizer_cost_based_transformation参数，会级联影响其它功能，在好几年前从12c升级19c时，就因为这个参数，导致上线次日大量执行计划衰减影响了性能。而UNEST并不是总能有效展开，和它相关的hint还经常有[no_]unnest,[no_]push_subq,[no_]push_pred.

no_unnest/unnest
no_unnest/unnest是针对子查询是否展开的. nest嵌套 否认是unnest展开， no_unnest双重否定是不展开
格式

select .. from t1
 where exists(select /*+no_unnest*/  from t2 where t1.xx=tx.xx ... )

push_subq
push_subq是针对子查询的连接顺序.当子查询无法展开时，通常将以filter的方式执行子查询，并且子查询将在执行计划最后一步执行，子查询推入的出现将可能让子查询在无法展开的情况下优先被执行。
格式

select *+push_subq(@sql1_3)*/ * from t1
join t2 on t1.xx=tx.xx
where exists (select /*+qb_name(sql1_3)*/  from t3 where t1.xx=t3.xx)

       By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the end of the execution plan.
Historically the push_subq hint told Oracle to run any outstanding subqueries at the earliest possible moment. But in 10g this changes (for the better). Imagine I have two subqueries in the query; it is quite possible that the optimum execution plan is to run one early and the other late, but the original implementation of push_subq is an ‘all or nothing’ implementation. 10g allows you to be selective about which subqueries should be pushed.

push_pred
push_pred则是针对unmergeable view使用外部查询谓词，弥补merge hint的一些场景. 如与view关连，view中有connect by ,union all, distinct, group by ，或right out join view。
格式

create view v1 as select .. from t1, t2 where xxxx;

select /*+push_pred(v1) from t3,v1 where t3.xx=v1.xx(+) and t3.xxx;

子查询的展开有以下三个方式

• subquery unnesting
• Push Subquery
• Filter

subquery unnesting：

The purpose of subquery unnesting is to inject semi- (IN, EXISTS), anti-join (NOT IN, NOT EXISTS), and scalar subqueries into the FROM clause of the containing query block, and to transform them into inline views. Some unnestings are performed as heuristic-based query transformations, and others are carried out as cost-based query transformations. The main reason for applying this query transformation is to enable all available join methods. In fact, without subquery unnesting, a subquery might have to be executed once for every row returned by the containing query block . Subquery unnesting can’t always be applied, though. For example, unnesting isn’t possible if a subquery contains some types of aggregation, or if it contains the rownum pseudocolumn. Semi- and anti-join subqueries containing set operators can only be unnested as of version 11.2. In addition, from version 12.1 onward, scalar subquery unnesting has been improved to process scalar subqueries in SELECT clauses

Unnesting a subquery can be summarized in two steps.

• The first step, as shown in the following query, is to rewrite the subquery as an inline view. Note that what follows isn’t a valid SQL statement, because the operator implementing the semi-join (s=) isn’t available in the SQL syntax (it’s used internally by the SQL engine only):

• The second step, as shown here, is to rewrite the inline view as a regular join

如果子查询不能展开，那么将以Filter的方式在最后一步才会被执行。对于简单子查询(比如单表的select-project-join结构)的展开属于启发式查询转换，复杂子查询(比如子查询多表关联)的展开属于非启发式查询转换。相关参数

NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_unnest_subquery                         TRUE       enables unnesting of complex subqueries
_optimizer_unnest_all_subqueries         TRUE       enables unnesting of every type of subquery
_optimizer_unnest_scalar_sq              TRUE       enables unnesting of of scalar subquery
_distinct_view_unnesting                 FALSE      enables unnesting of in subquery into distinct view
_optimizer_unnest_disjunctive_subq       TRUE       Unnesting of disjunctive subqueries (TRUE/FALSE)
_optimizer_unnest_corr_set_subq          TRUE       Unnesting of correlated set subqueries (TRUE/FALSE)
_hcs_disable_unnest                      FALSE      disable unnest hint

子查询展开转换的结果：semi-join/anti-join/inter-join

• exists/in/any => SU: Transform an ANY subquery to semi-join or distinct
• not exists/not in/all => SU: Transform ALL/NOTEXISTS subquery into a regular anti-join

当然会有一些特殊，如semi-join被转换为了inter-join，在显示hash join semi时 SEMI_TO_INNER 不显示semi。 复杂子查询时还能看到VW_SQ_N的临时View. 更多见好友的笔记Query Transformation-子查询篇

打赏

微信扫一扫，打赏作者吧～