本文由恩墨大講堂150期線上分享整理而成。課程回看可點(diǎn)擊文末“閱讀原文”。 

優(yōu)化器是 Oracle 數(shù)據(jù)庫最引人入勝的部件之一，因為它對每一個 SQL 語句的處理都必不可少。優(yōu)化器為每個 SQL 語句確定最有效的執(zhí)行計劃，這是基于給定的查詢的結(jié)構(gòu)，可用的關(guān)于底層對象的統(tǒng)計信息，以及所有與優(yōu)化器和執(zhí)行相關(guān)的特性。因此 Oracle 在每一個版本中，優(yōu)化器都引入了新特性，本文將詳細(xì)講解 12C 中標(biāo)量子查詢自動轉(zhuǎn)換的新特性的原理，優(yōu)勢，適用場景和案例分享。

首先我們來看官方文檔的說明：

標(biāo)量子查詢是出現(xiàn)在 SQL 語句的 SELECT 子句的子查詢。因為標(biāo)量子查詢不能被展開，所以一個相關(guān)的標(biāo)量子查詢（它引用了子查詢之外的列）必須為外層查詢產(chǎn)生的每一行被取值?？紤]下面的查詢：

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 數(shù)據(jù)庫中，對于 CUSTOMERS 表中 CUST_CREDIT_LIMIT 大于 50000 的每一行，在 SALES 表上的標(biāo)量子查詢都必須被執(zhí)行。SALES 表是大表，把它掃描多次是非常耗費(fèi)資源的。

（圖: Oracle 11g 數(shù)據(jù)庫的計劃顯示，對于 customers 表返回的每一行，標(biāo)量子查詢都必須被取值）

將標(biāo)量子查詢展開并且將其轉(zhuǎn)換為一個連接，就免除了為外層查詢的每一行都進(jìn)行求值的必要性。在 Oracle 12c 數(shù)據(jù)庫中，標(biāo)量子查詢能夠被展開，在這個例子中，SALES 表上的標(biāo)量子查詢被轉(zhuǎn)換成一個 group-by 視圖。group-by 視圖確定會返回每組一行，正如標(biāo)量子查詢一樣。查詢中同樣加入了一個外連接，這是為了確保即使當(dāng)視圖的結(jié)果為空時，CUSTOMERS 的數(shù)據(jù)仍然會被返回。轉(zhuǎn)換后的查詢?nèi)缦?

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 數(shù)據(jù)庫的計劃顯示標(biāo)量子查詢已經(jīng)被展開成外連接和 GROUP BY 視圖）

首先我們建立測試環(huán)境：Tab0 是小表，tab1 是大表。

---收集統(tǒng)計信息

① 在 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;

執(zhí)行計劃信息：

從上面可知，標(biāo)量子查詢轉(zhuǎn)換成了 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'));

可以發(fā)現(xiàn) 11g 查詢標(biāo)量子查詢成本還是蠻高的，要全表掃描 TAB1 兩次，而 TAB1 剛好是大表，導(dǎo)致的邏輯讀也剛好是 12C 中的差不多兩倍，可見性能肯定相差很多。 

③ 我們用 10053 分析在 12C 版本中，Oracle 到底是怎么自動改寫轉(zhuǎn)換

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"

首先把標(biāo)量子查詢改寫成了一個視圖 VW_SSQ_1，然后再跟 TAB0 進(jìn)行外連接，我們知道在 11G 中 tab0 有多少記錄，子查詢（也就是視圖 VW_SSQ_1）就要執(zhí)行多少次，改成連接后，先過濾符合和 tab0 條件的，這樣就大大減小了對 tab0 的掃描次數(shù)。

從上述測試的結(jié)果來看，在 12C 版本中，標(biāo)量子查詢因為優(yōu)化器會自動改寫所以性能將大大提高，也省去了我們在 11G 對標(biāo)量子查詢的優(yōu)化中手工改成外連接。那是不是在 12C 中所有標(biāo)量子查詢都會自動改寫轉(zhuǎn)換呢？下面我們來測試幾個場景看看。

場景1

如上章節(jié)，聚合函數(shù) MAX，我們知道優(yōu)化器自動改寫了 SQL，性能也大大提高了。

場景2

聚合函數(shù) 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'));

執(zhí)行計劃信息：

由此可見 count（1）并沒有發(fā)生自動改寫轉(zhuǎn)換，我們用 10053 事件分析為什么沒有發(fā)生自動改寫：

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 無法轉(zhuǎn)換的原因是有空值，優(yōu)化器在此步不能轉(zhuǎn)換。

場景3

沒有聚合函數(shù)

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'));

執(zhí)行計劃信息：

由此可見，如果不是聚合函數(shù)在 12C 優(yōu)化器也不會自動改寫轉(zhuǎn)換。

下面是來自某銀行系統(tǒng)的真實案例模擬，數(shù)據(jù)庫 SQL 代碼是從 11g 中直接拿來在 12C 中使用，運(yùn)行出現(xiàn)報錯。運(yùn)行環(huán)境是在 IBM 小機(jī)上的，數(shù)據(jù)庫版本是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

當(dāng)我們做 insert 時，插入失敗，同時會話自動斷開，在 alert 日志信息出現(xiàn) ORA-07445 錯誤，嘗試在 session 級別關(guān)閉標(biāo)量子查詢的自動轉(zhuǎn)換功能，alter session set "_optimizer_unnest_scalar_sq"=false;

可以看到并沒有報錯，可以插入成功。

當(dāng)然我們也可以用 no_unnesthint 強(qiáng)制關(guān)閉標(biāo)量子查詢的展開。

由上案例我們可知道，在 12C 中，標(biāo)量子查詢自動改寫的功能是有隱含參數(shù)_optimizer_unnest_all_subqueries 控制，默認(rèn)是 true，意味著開啟，如果遇到 bug，出錯或者在自動改寫轉(zhuǎn)換后出現(xiàn)性能問題時，可以先嘗試更改為 false，或者用hint(no_unnest) 避免子查詢展開。

① 12C 標(biāo)量子查詢優(yōu)化器可以實現(xiàn)自動改成一個外連接，僅僅出現(xiàn)在一些聚集函數(shù)；

② 并不是所有的聚集函數(shù)都會出現(xiàn)，比如 count；

③ 如果連接列中中出現(xiàn)一些空值，優(yōu)化器是不會自動改寫轉(zhuǎn)換的。因為結(jié)果不等價；

④ 子查詢中沒有聚集函數(shù)（AVG, MAX, MIN, STDDEV, VARIANCE, SUM, COUNT, MEDIAN）優(yōu)化器是不會自動改寫轉(zhuǎn)換的；

⑤ 12C 標(biāo)量子查詢優(yōu)化器自動轉(zhuǎn)換受_optimizer_unnest_scalar_sq 參數(shù)的控制；

⑥ 如果在 12C 中，標(biāo)量子查詢優(yōu)化器自動轉(zhuǎn)換導(dǎo)致了 SQL 語句遇到 bug，出錯或者再出現(xiàn)性能問題，可以用 alter session set "_optimizer_unnest_scalar_sq"=false 將其關(guān)閉此功能，或者用 hint no_unnest 來避免子查詢展開。