从Oracle 11.2.0.4 BUG到Oracle子查询展开分析

本文详细分析了Oracle 11.2.0.4版本中由于BUG导致子查询无法展开的问题,探讨了子查询展开的两种情形和判断标准。通过对SQL执行计划的分析,揭示了统计信息不准确和特定BUG对优化器决策的影响。通过打补丁和SQL改写,成功解决了子查询不展开的问题,降低了执行成本。
摘要由CSDN通过智能技术生成

本文测试用语句来自下面大佬文章《ORACLE CBO 的 SQL 自动转换(Cost Based Transformations)之五》
本文主要介绍由于11.2.0.4.190115中由于bug导致子查询不展开的情况。

https://blog.csdn.net/weixin_50513167/article/details/115694814

1. 基础知识之子查询展开

​ 子查询展开(Subquery Unnesting)是优化器处理带子查询的目标SQL的一种优化手段,它是指优化器不再将目标SQL中的子查询当作一个独立的处理单元来单独执行,而是将该子查询转换为它自身和外部查询之间等价的表连接。

1.1 子查询展开两种情形

  • 子查询拆开(即将该子查询中的表、视图从子查询中拿出来,然后和外部查询中的表、视图做表连接)
  • 不拆开但是会把该子查询转换为一个内嵌视图(Inline View),然后再和外部查询中的表、视图做表连接,这种情况下,相当于优化器多了一条路径,CBO会考虑cost高低来决定是否将内嵌视图用来与外部查询表做关联,还是用外部表的数据来驱动内嵌视图走filter类型执行计划。

1.2 子查询是否展开判断

  • 子查询如果展开,则会看到执行计划中会将子查询中基表拿出来与主查询中基表做关联或将子查询转换为内部视图与主查询中基表做关联。
  • 子查询如果未展开,一般会在执行计划中最晚执行filter类型的子查询过滤。

1.3 _optimizer_unnest_corr_set_subq

该参数控制优化器在SQL语句包含子查询时,是否在满足子查询展开条件时,是否可以将子查询作为一个视图与主查询关联查询。

2. Oracle 11.2.0.4.190115 PSU下测试

Oracle版本:Oracle 11.2.0.4.190115 for Linux x86_64

Oracle参数: 优化器相关参数均为默认值,即相关_optimizer_unnest_corr_set_subq等参数默认均为true。

2.1 构建测试环境

测试用例均来自于https://blog.csdn.net/weixin_50513167/article/details/115694814

drop table t1 purge;
drop table t2 purge;
drop table drv purge;

create table t1(key, pad) as
select to_char(rownum*2-1), lpad(' ',50) from dual connect by level <= 100;

create table t2(key, pad) as
select to_char(rownum*2), lpad(' ',50) from dual connect by level <= 100;

create table drv(key, pad) as
select rownum, lpad(' ',500) from dual connect by level <= 5
union all
select -rownum, lpad(' ',500) from dual connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');
exec dbms_stats.gather_table_stats(user, 'DRV');

2.2 执行测试SQL

# 下面参数默认即为true,此处设置为true是为了体现该参数确实为true
SQL > alter session set "_optimizer_unnest_corr_set_subq" = TRUE;

Session altered.

SQL > set autot on
SQL > select key from drv
  2  where exists (
  3      select key
  4      from t1
  5      where drv.key = to_number(t1.key)
  6      union all
  7      select key
  8      from t2
  9      where drv.key = to_number(t2.key)
 10    );

                                     KEY
----------------------------------------
                                       1
                                       2
                                       3
                                       4
                                       5


Execution Plan
----------------------------------------------------------
Plan hash value: 1881039188

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     5 |  3040   (1)| 00:00:37 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | DRV  |  1005 |  5025 |    23   (0)| 00:00:01 |
|   3 |   UNION-ALL         |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| T1   |     1 |     4 |     3   (0)| 00:00:01 |
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值