Scalar Subquery Unnesting Transformation (Oracle Database 12c R1 New Feature) Tweet

新機能の自分メモ　:)

スカラー副問合せを使って性能改善する場合も多いのですが、逆にそれが理由で性能劣化しているケースも意外と多いんですよね。

昨年開催したJPOUGのセッションでもネタとして取り上げたのでご存知の方も多いと思いますし、実際にチューニング手法の一つとしてうまく活用している方も多いと思います。
（やっちまった〜〜〜〜、orz。　という方もいると思いますがw）

ご利用は計画的に！w

Oracle Database 11gまでは、以下のようなスカラー副問合せがネックとなって処理時間が予想以上に延びてしまう場合の回避方法は、たった一つ！

スカラー副問合せを止めて外部結合に書き換える！

遅延するケースとして、

1.dept表の行数が多い
2.スカラー副問合せでemp表を全表走査していたり、範囲の広いレンジスキャンを繰り返している

（2.で索引がなくて　もしくは、索引が利用できずに全表走査してたら最悪ですよね！）

スカラー副問合せを利用したクエリーはNested Loopという操作は現れていませんが、外部結合でNested Loop結合しているのと同じ動きなんだぉ〜　＞＜
件数多いと辛いんだよ〜〜〜。　ってことは忘れないようにしないと...

dept表の件数が少なければ問題にはならないけど、(例が例なので部門が１０万行になることはないでしょうけどw）dept表が10万行以上だとすれば、Nested Loop結合で処理させるのはリスクが大きいですよね。
ところが、以下のようなスカラー副問合せで書かれてしまうとHASH結合やMERGE結合になって欲しくても絶対になりません... (11gまではね）

select
	deptno
	,dname
	,(
		select
			max(sal)
		from
			emp 	
		where
			emp.deptno=dept.deptno
	) as max_sal
from
	dept
order by
deptno;


実行計画
----------------------------------------------------------
Plan hash value: 3189108593

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     4 |    52 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |           |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     5 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_EMP_01 |     5 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_DEPT   |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

スカラー副問合せを外部結合に書き換え、オプティマイザおまかせでHASH結合やMERGE結合になるようにした方が、チューニング方法が増えることもあり後々楽になるケースが多いんですよね。
(スカラー副問合せにして、結合のコストを低く抑えたい場面もあるのは事実ですが）

余談：
データ量が見切れない、大きく変動するような表の場合には、素直に結合してもらっていたほうが楽なんですよ。
チューニング案を考える/する人目線ですなのですが、どの程度の件数になるか見切れている場合以外は結合で書いてもらって、どうしても結合のコストを下げたいという状況になったらスカラー副問合せにしてもらうというケースが一番多い、かな）

select
	dept.deptno
	,dept.dname
	,max(emp.sal) as max_sal
from
	dept
	left outer join emp
	on
		dept.deptno=emp.deptno
group by 
	dept.deptno
	,dept.dname
order by
	deptno;


実行計画
----------------------------------------------------------
Plan hash value: 1182128691

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     9 |   180 |     5   (0)| 00:00:01 |
|   1 |  SORT GROUP BY                |         |     9 |   180 |     5   (0)| 00:00:01 |
|   2 |   MERGE JOIN OUTER            |         |    15 |   300 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |    14 |    98 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

前述のような単純なクエリーなら書換も苦にならない(苦になる人もいるさ、きっと）と思いますが、見るのも嫌になるほど長文のクエリーだったらどうしますw
私ならストレス発散に文句いいならが修正案だしますよ。...たぶんw　

そんなあなたに朗報！？　か.....。　

Oracle Database 12c なら、そんな書換も不要に！　

オプティマイザが肩代わりしてくれます!
（駄目クエリー書いちゃった方は、オプティマイザが良きに計らってくれるので何が起きているかも知らずに成長が止まっちゃうかもね。という状況ではないと思うけど、楽になるのはいい事なのかもね。）

参考：Optimizer with Oracle Database 12c

explain plan for
select
	deptno
	,dname
	,(
		select
			max(sal)
		from
			emp 
		where
			emp.deptno=dept.deptno
	) as max_sal
from
	dept
order by
deptno;


実行計画
----------------------------------------------------------
Plan hash value: 2834279049

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     4 |   116 |     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |          |     4 |   116 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT  |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     3 |    48 |     3   (0)| 00:00:01 |
|   5 |    VIEW                      | VW_SSQ_1 |     3 |    48 |     3   (0)| 00:00:01 |
|   6 |     HASH GROUP BY            |          |     3 |    21 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$C772B8D1
   2 - SEL$C772B8D1 / DEPT@SEL$1
   3 - SEL$C772B8D1 / DEPT@SEL$1
   5 - SEL$683B0107 / VW_SSQ_1@SEL$7511BFD2
   6 - SEL$683B0107
   7 - SEL$683B0107 / EMP@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$683B0107")
      FULL(@"SEL$683B0107" "EMP"@"SEL$2")
      USE_MERGE(@"SEL$C772B8D1" "VW_SSQ_1"@"SEL$7511BFD2")
      LEADING(@"SEL$C772B8D1" "DEPT"@"SEL$1" "VW_SSQ_1"@"SEL$7511BFD2")
      NO_ACCESS(@"SEL$C772B8D1" "VW_SSQ_1"@"SEL$7511BFD2")
      INDEX(@"SEL$C772B8D1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$7511BFD2")
      OUTLINE(@"SEL$2")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$C772B8D1")
      OUTLINE_LEAF(@"SEL$683B0107")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
       filter("ITEM_1"(+)="DEPT"."DEPTNO")

ほら、ほら、ほらほらほらほら。。！！！！！

あら不思議、スカラー副問合せがUnnestされてMERGE結合に！！！！　outer joinに書き換えてくれています！！！

すげ〜〜〜〜〜〜〜〜〜〜〜〜。
ほかのケースでも試してみないと。..

OUTLINEを見るとUNNEST(@"SEL$2")なんてヒントがついてますね! ニッコリ。

NO_UNNESTにしてみる!

explain plan for
select
	/*+
		NO_UNNEST(@"SEL$2")
	*/
	deptno
	,dname
	,(
		select
			max(sal)
		from
			emp 	
		where
			emp.deptno=dept.deptno
	) as max_sal
from
	dept
order by
deptno;

Plan hash value: 3189108593

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     4 |    52 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |           |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     5 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_EMP_01 |     5 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_DEPT   |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

11gまでのスカラー副問合せの実行計画だ！　

スカラー副問合せ内にNO_UNNESTヒントを書いてみます（同じ結果になるはず）

explain plan for
select
	deptno
	,dname
	,(
		select
			/*+ NO_UNNEST */
			max(sal)
		from
			emp 
		where
			emp.deptno=dept.deptno
	) as max_sal
from
	dept
order by
deptno;

Plan hash value: 3189108593

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     4 |    52 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |           |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     5 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_EMP_01 |     5 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_DEPT   |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

よし、わかった。

関連している初期化パラメータはどれだろう...

参考: Mac De Oracle / Difference of Initialization Parameters between 11g r2 (11.2.0.1.0) and 12c r1 (12.1.0.1.0) - including hidden params

以下の赤字部分が12cで追加されたunnesting関連の隠しパラメータ、なかでも KSPPDESCを見ると、Scalar Subqueryのunnestingに関わりそうなのは、_optimizer_unnest_scalar_sq ですね。
備考を読んでると _optimizer_unnest_all_subqueries パラメータも気になる！w

SYS@orcl12c> r
  1  select
  2     a.ksppinm
  3    ,b.ksppstvl
  4    ,b.ksppstdf
  5    ,a.ksppdesc
  6  from
  7    x$ksppi a join x$ksppcv b
  8    on a.indx = b.indx
  9  where
 10    a.ksppinm like '%unnest%'
 11  order by
 12*   a.ksppinm

KSPPINM                                  KSPPSTVL             KSPPSTDF   KSPPDESC
---------------------------------------- -------------------- ---------- ----------------------------------------------------------------------
_distinct_view_unnesting                 FALSE                TRUE       enables unnesting of in subquery into distinct view
_optimizer_unnest_all_subqueries         TRUE                 TRUE       enables unnesting of every type of subquery
_optimizer_unnest_corr_set_subq          TRUE                 TRUE       Unnesting of correlated set subqueries (TRUE/FALSE)
_optimizer_unnest_disjunctive_subq       TRUE                 TRUE       Unnesting of disjunctive subqueries (TRUE/FALSE)
_optimizer_unnest_scalar_sq              TRUE                 TRUE       enables unnesting of of scalar subquery
_unnest_subquery                         TRUE                 TRUE       enables unnesting of complex subqueries

_optimizer_unnest_scalar_sqを無効にしてみると...

explain plan for
select
	/*+
		OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
	*/
	deptno
	,dname
	,(
		select
			max(sal)
		from
			emp 
		where
			emp.deptno=dept.deptno
	) as max_sal
from
	dept
order by
deptno;


Plan hash value: 3189108593

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     4 |    52 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |           |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     5 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_EMP_01 |     5 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_DEPT   |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

できた！

気になる_optimizer_unnest_all_subqueriesを無効にすると...

explain plan for
select
	/*+
		OPT_PARAM('_optimizer_unnest_all_subqueries' 'false')
	*/
	deptno
	,dname
	,(
		select
			max(sal)
		from
			emp 
		where
			emp.deptno=dept.deptno
	) as max_sal
from
	dept
order by
deptno;


Plan hash value: 3189108593

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     4 |    52 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |           |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     5 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_EMP_01 |     5 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_DEPT   |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

これでもできた！　　
このunder score parameterは12cで追加された一連のUnnesting transformationの元栓みたいな感じですかね :)
12cのUnnesting系で問題がでたらこのパラメータで全部止めるというケースがあるのかないのか...使うとすればこのパラメータだろうね。

将来は外部結合をスカラー副問合せに書き換えるなんてぇ、トランスフォームが登場したりするんだろうか..........

---

参考
TANEL PODER'S BLOG / Oracle 12c: Scalar Subquery Unnesting transformation