微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

12C 新特性 | 标量子查询自动转换

2018-03-11 23:59 钟浪峰

有超过6年超大型数据库专业服务经验,擅长数据库解决方案设计与项目管理;在多年的技术实践中,先后为运营商(移动、电信)、银行、保险、制造业等各行业客户的业务关键型系统提供了运维、升级、性能优化、项目实施与管理、容灾建设等咨询与技术实施服务。

钟浪峰

云和恩墨交付工程师


本文由恩墨大讲堂150期线上分享整理而成。课程回看可点击文末“阅读原文”。 


优化器是 Oracle 数据库最引人入胜的部件之一,因为它对每一个 SQL 语句的处理都必不可少。优化器为每个 SQL 语句确定最有效的执行计划,这是基于给定的查询的结构,可用的关于底层对象的统计信息,以及所有与优化器和执行相关的特性。因此 Oracle 在每一个版本中,优化器都引入了新特性,本文将详细讲解 12C 中标量子查询自动转换的新特性的原理,优势,适用场景和案例分享。


1

12C 标量子查询自动转换说明

 

首先我们来看官方文档的说明:

 

标量子查询是出现在 SQL 语句的 SELECT 子句的子查询。因为标量子查询不能被展开,所以一个相关的标量子查询(它引用了子查询之外的列)必须为外层查询产生的每一行被取值。考虑下面的查询:


SELECT c.cust_id, c.cust_last_name,c.cust_city,

     (SELECT avg(s.quantity_sold)

        FROM sales s

       WHERE s.cust_id = c.cust_id) avg_quan

FROM customers c

WHERE c.cust_credit_limit > 50000;

 

在 Oracle 11g 数据库中,对于 CUSTOMERS 表中 CUST_CREDIT_LIMIT 大于 50000 的每一行,在 SALES 表上的标量子查询都必须被执行。SALES 表是大表,把它扫描多次是非常耗费资源的。


(图: Oracle 11g 数据库的计划显示,对于 customers 表返回的每一行,标量子查询都必须被取值)

 

将标量子查询展开并且将其转换为一个连接,就免除了为外层查询的每一行都进行求值的必要性。在 Oracle 12c 数据库中,标量子查询能够被展开,在这个例子中,SALES 表上的标量子查询被转换成一个 group-by 视图。group-by 视图确定会返回每组一行,正如标量子查询一样。查询中同样加入了一个外连接,这是为了确保即使当视图的结果为空时,CUSTOMERS 的数据仍然会被返回。转换后的查询如下:

 

SELECT c.cust_id, c.cust_last_name,c.cust_city, v.avg_quan

FROM customers c,

     (SELECT avg(s.quantity_sold) avg_quan, s.cust_id

        FROM sales s

      GROUP BY s.cust_id) v

WHERE c.cust_credit_limit > 50000

     AND c.cust_id = v.cust_id(+);


(图: Oracle 12c 数据库的计划显示标量子查询已经被展开成外连接和 GROUP BY 视图)


2

标量子查询自动转换的优势


首先我们建立测试环境:Tab0 是小表,tab1 是大表。


---收集统计信息


① 在 12C 版本中

set linesize 150

alter session set statistics_level=all;

select distinct b.object_type, (select max(object_id) from tab1 a where a.object_type= b.object_type) max_id from tab0 b;


执行计划信息:

 

从上面可知,标量子查询转换成了 hash join outer,性能分析可知表 tab1 只扫描了一次,整个 SQL 消耗的逻辑读为 555。


在 11g 版本中

alter session set optimizer_features_enable='11.2.0.4';

select distinct b.object_type, (select max(object_id) from tab1 a where a.object_type= b.object_type) max_id from tab0 b;

select *from table(dbms_xplan.display_cursor(null,null,'allstats last'));



可以发现 11g 查询标量子查询成本还是蛮高的,要全表扫描 TAB1 两次,而 TAB1 刚好是大表,导致的逻辑读也刚好是 12C 中的差不多两倍,可见性能肯定相差很多。 


我们用 10053 分析在 12C 版本中,Oracle 到底是怎么自动改写转换

alter session set optimizer_features_enable='12.1.0.2.1';

oradebug setmypid

oradebug unlimit

oradebug event 10053 trace name context forever, level 12;

select distinct b.object_type, (select max(object_id) from tab1 a where a.object_type= b.object_type) max_id from tab0 b;

oradebug event 10053 trace name context off;

oradebug tracefile_name

 

CVM:   Merging complex viewSEL$683B0107 (#2) into SEL$C772B8D1 (#1).

qbcp:******* UNPARSED QUERY IS *******

SELECT DISTINCT"B"."OBJECT_TYPE""OBJECT_TYPE","VW_SSQ_1"."MAX(OBJECT_ID)" "MAX_ID"FROM  (SELECTMAX("A"."OBJECT_ID")"MAX(OBJECT_ID)","A"."OBJECT_TYPE""ITEM_1" FROM "SYS"."TAB1" "A" GROUP BY"A"."OBJECT_TYPE")"VW_SSQ_1","SYS"."TAB0" "B" WHERE"VW_SSQ_1"."ITEM_1"(+)="B"."OBJECT_TYPE"

vqbcp:******* UNPARSED QUERY IS *******

SELECT MAX("A"."OBJECT_ID")"MAX(OBJECT_ID)","A"."OBJECT_TYPE""ITEM_1" FROM "SYS"."TAB1" "A" GROUP BY"A"."OBJECT_TYPE"

CVM: result SEL$C772B8D1 (#1)

******* UNPARSED QUERY IS *******

SELECT DISTINCT "B"."OBJECT_TYPE""OBJECT_TYPE",MAX("A"."OBJECT_ID") "MAX_ID"FROM "SYS"."TAB1""A","SYS"."TAB0" "B" WHERE"A"."OBJECT_TYPE"(+)="B"."OBJECT_TYPE"GROUP BY "A"."OBJECT_TYPE","B".ROWID,"B"."OBJECT_TYPE"

OJE: Begin: find best directive for query block SEL$841DDE77 (#1)

OJE: Considering outer-join elimination on queryblock SEL$841DDE77 (#1)


从 10053 中我们也可以看出 SQL 被改写成了:


SELECT DISTINCT "B"."OBJECT_TYPE""OBJECT_TYPE","VW_SSQ_1"."MAX(OBJECT_ID)""MAX_ID" FROM  (SELECTMAX("A"."OBJECT_ID") "MAX(OBJECT_ID)","A"."OBJECT_TYPE""ITEM_1" FROM "SYS"."TAB1" "A" GROUP BY"A"."OBJECT_TYPE")"VW_SSQ_1","SYS"."TAB0" "B" WHERE"VW_SSQ_1"."ITEM_1"(+)="B"."OBJECT_TYPE"


首先把标量子查询改写成了一个视图 VW_SSQ_1,然后再跟 TAB0 进行外连接,我们知道在 11G 中 tab0 有多少记录,子查询(也就是视图 VW_SSQ_1)就要执行多少次,改成连接后,先过滤符合和 tab0 条件的,这样就大大减小了对 tab0 的扫描次数。


3

标量子查询自动转换适用场景


从上述测试的结果来看,在 12C 版本中,标量子查询因为优化器会自动改写所以性能将大大提高,也省去了我们在 11G 对标量子查询的优化中手工改成外连接。那是不是在 12C 中所有标量子查询都会自动改写转换呢?下面我们来测试几个场景看看。


场景1

如上章节,聚合函数 MAX,我们知道优化器自动改写了 SQL,性能也大大提高了。


场景2

聚合函数 count

set linesize 150

alter session set statistics_level=all;

select distinct b.object_type, (select count(1) from tab1 a where a.object_type =b.object_type) max_id from tab0 b;

select *from table(dbms_xplan.display_cursor(null,null,'allstats last'));


执行计划信息:



由此可见 count(1)并没有发生自动改写转换,我们用 10053 事件分析为什么没有发生自动改写:


kkqctdrvTD-start on query block SEL$1 (#0)

kkqctdrvTD-start: :

    call(in-use=2032, alloc=16344),compile(in-use=112416, alloc=115544), execution(in-use=3432, alloc=4032)

 

SU: bypassed: Scalar subquery has null-mutatingselect item.

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :

    call(in-use=2032, alloc=16344),compile(in-use=113088, alloc=115544), execution(in-use=3432, alloc=4032)

 

kkqctdrvTD-end:

    call(in-use=2032, alloc=16344),compile(in-use=113288, alloc=115544), execution(in-use=3432, alloc=4032)

 

SJC: Considering set-join conversion in query blockSEL$1 (#1)


从 trace 文件中,我们可以看出 Oracle 无法转换的原因是有空值,优化器在此步不能转换。


场景3

没有聚合函数

set linesize 150

alter session set statistics_level=all;

select b.object_id,(select a.object_name from tab1 a where a.object_id = b.object_id)from tab0 b;

select *from table(dbms_xplan.display_cursor(null,null,'allstats last'));


执行计划信息:


由此可见,如果不是聚合函数在 12C 优化器也不会自动改写转换。


4

12C 标量子查询案例


下面是来自某银行系统的真实案例模拟,数据库 SQL 代码是从 11g 中直接拿来在 12C 中使用,运行出现报错。运行环境是在 IBM 小机上的,数据库版本是12.1.0.2。


create table tab4 (ck date);

create table tab3(lob varchar2(10) not null, uk number not null);

create table tab2 (pk_claim number not null, crt_data date);



Fri Feb23 10:11:57 2018

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x3B682D6,qcsogolz()+70] [flags: 0x0, count: 1]

Errors infile /u01/app/oracle/diag/rdbms/zxdb/zxdb/trace/zxdb_ora_3964.trc  (incident=91434):

ORA-07445: exception encountered: core dump [qcsogolz()+70][SIGSEGV] [ADDR:0x0] [PC:0x3B682D6] [Address not mapped to object] []

Incident details in: /u01/app/oracle/diag/rdbms/zxdb/zxdb/incident/incdir_91434/zxdb_ora_3964_i91434.trc

Use ADRCIor Support Workbench to package the incident.

See Note411.1 at My Oracle Support for error and packaging details.

Fri Feb23 10:12:06 2018

Dumping diagnostic data in directory=[cdmp_20180223101206], requested by (instance=1,osid=3964), summary=[incident=91434].

Fri Feb23 10:12:11 2018

Sweep [inc][91434]: completed

Sweep [inc2][91434]: completed


当我们做 insert 时,插入失败,同时会话自动断开,在 alert 日志信息出现 ORA-07445 错误,尝试在 session 级别关闭标量子查询的自动转换功能,alter session set "_optimizer_unnest_scalar_sq"=false;



可以看到并没有报错,可以插入成功。

当然我们也可以用 no_unnesthint 强制关闭标量子查询的展开。



由上案例我们可知道,在 12C 中,标量子查询自动改写的功能是有隐含参数_optimizer_unnest_all_subqueries 控制,默认是 true,意味着开启,如果遇到 bug,出错或者在自动改写转换后出现性能问题时,可以先尝试更改为 false,或者用hint(no_unnest) 避免子查询展开。


5

总结


 12C 标量子查询优化器可以实现自动改成一个外连接,仅仅出现在一些聚集函数;

 并不是所有的聚集函数都会出现,比如 count;

 如果连接列中中出现一些空值,优化器是不会自动改写转换的。因为结果不等价;

 子查询中没有聚集函数(AVG, MAX, MIN, STDDEV, VARIANCE, SUM, COUNT, MEDIAN)优化器是不会自动改写转换的;

 12C 标量子查询优化器自动转换受_optimizer_unnest_scalar_sq 参数的控制;

 如果在 12C 中,标量子查询优化器自动转换导致了 SQL 语句遇到 bug,出错或者再出现性能问题,可以用 alter session set "_optimizer_unnest_scalar_sq"=false 将其关闭此功能,或者用 hint no_unnest 来避免子查询展开。


资源下载

关注公众号:数据和云(OraNews)回复关键字获取

‘2017DTC’,2017 DTC 大会 PPT

‘DBALIFE’,“DBA 的一天”海报

‘DBA04’,DBA 手记4经典篇章电子书

‘RACV1’, RAC 系列课程视频及ppt

‘122ARCH’,Oracle 12.2 体系结构图

‘2017OOW’,Oracle OpenWorld 资料

‘PRELECTION’,大讲堂讲师课程资料


戳原文,回看视频课程!
 
数据和云 更多文章 深入内核丨12C 新特性之 TOP - N 频率柱状图原理和算法 美团外卖自动化业务运维系统 - Alfred 关于 Oracle 存储双活配置和实战 18C 也不能避免 SQL 解析的 Bug 内核解密 | Oracle 18c 数据库安装ORA-12754的两种解决方案
猜您喜欢 一个五年 Android 开发者百度、阿里、聚美、映客的面试心经 在游戏中学习!LOL新英雄与新皮肤移动端页面二三事 【冬瓜哥演讲主题通知】IO协议栈前沿技术分析 在你里面,他干啥了?QuestMobile Inapp分析 基于Docker的微服务架构试水