Join Factorization

Oracle에서 or 조건을 처리하는 연산으로 OR-EXPANSION이라고 불리우는 동작이 있습니다.

UNION ALL 로 풀어서 작업을 수행하는 CONCATENATION.or-expansion이라고 합니다. 힌트는 /*+ USE_CONCAT */를 사용합니다.

UNION ALL로 풀지말고 수행하는 CONCAT을 방지하는 힌트는 /*+ NO_EXPAND */ 가 있습니다.

 

두 힌트에 대한 내용은 아래 사이트에서 설명한 내용을 해석하였습니다.

USE_CONCAT 

USE_CONCAT 힌트는 UNION ALL 집합 연산자를 사용하여 쿼리의 WHERE 절에 있는 결합된 OR 조건을 복합 쿼리로 변환하도록 강제합니다. 일반적으로 이러한 변환은 연결을 사용하는 쿼리의 비용이 연결을 사용하지 않는 비용보다 저렴한 경우에만 발생합니다.

NO_EXPAND 

NO_EXPAND 힌트를 사용하면 비용 기반 최적화 프로그램에서 WHERE 절에 OR 조건 또는 IN-lists가 있는 쿼리에 대해 OR-확장을 고려할 수 없습니다. 일반적으로 최적화 도구는 OR 확장을 사용하는 것을 고려하고 사용하지 않는 것보다 비용이 낮다고 판단되면 이 방법을 사용합니다.

 

출처 : https://dbaora.com/no_expand-use_concat-hints/

NO_EXPAND USE_CONCAT hints | DBAORA

위의 두개뿐 아니라 이제 고려할 수 있는 힌트와 개념을 하나 더 알게 되어서 정리하려고 합니다.

Join Factorization (JF)라고 불리는 개념인데요.

오라클 옵티마이저가 판단하여 해당이 되면 SQL을 재작성해서 수행합니다. 실행계획상에는 VW_JF_SET$XXXXXX 라고 표시됩니다.

 

사용법은 아래와 같습니다.

강제 사용

FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2))

괄호 안의 내용의 의미입니다.

SET$1 ==>전체에 해당하는 쿼리 블록, SEL$1 => Union All 중 윗부분, SEL$2 => Union All 중 아랫 부분

 

강제 사용 방지

NO_FACTORIZE_JOIN(@SET$1)

 

테스트를 통해 발생시켜보도록 하겠습니다.

EMP와 DEPT를 이용하여 DEPTNO가 10인 EMP의 정보와 DEPTNO가 20인 EMP의 정보를 조회하겠습니다.

 

select /* FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2)) */
e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 10 
union all
select e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 20 ;

 --------------------------------------------------------------------------------------
| Id  | Operation            | Name               | E-Rows |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |        |       |       |          |
|*  1 |  HASH JOIN           |                    |      8 |  1398K|  1398K|  932K (0)|
|   2 |   TABLE ACCESS FULL  | DEPT               |      4 |       |       |          |
|   3 |   VIEW               | VW_JF_SET$35C2C1E7 |      8 |       |       |          |
|   4 |    UNION-ALL         |                    |        |       |       |          |
|*  5 |     TABLE ACCESS FULL| EMP                |      3 |       |       |          |
|*  6 |     TABLE ACCESS FULL| EMP                |      5 |       |       |          |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ITEM_1"="D"."DEPTNO")
   5 - filter("E"."DEPTNO"=10)
   6 - filter("E"."DEPTNO"=20)

쿼리가 변환되어서 VW_JF_SET$35C2C1E7가 생성되었고, DEPT는 한번만 읽고 처리되었습니다.

반대로 NO 힌트를 적용하여 결과값을 확인하겠습니다.

 

select /*+ NO_FACTORIZE_JOIN(@SET$1) */
e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 10 
union all
select e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 20 ;

 ------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |       |          |
|   1 |  UNION-ALL          |      |        |       |       |          |
|*  2 |   HASH JOIN         |      |      3 |  1335K|  1335K|  743K (0)|
|*  3 |    TABLE ACCESS FULL| DEPT |      1 |       |       |          |
|*  4 |    TABLE ACCESS FULL| EMP  |      3 |       |       |          |
|*  5 |   HASH JOIN         |      |      5 |  1423K|  1423K|  743K (0)|
|*  6 |    TABLE ACCESS FULL| DEPT |      1 |       |       |          |
|*  7 |    TABLE ACCESS FULL| EMP  |      5 |       |       |          |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("D"."DEPTNO"=10)
   4 - filter("E"."DEPTNO"=10)
   5 - access("E"."DEPTNO"="D"."DEPTNO")
   6 - filter("D"."DEPTNO"=20)
   7 - filter("E"."DEPTNO"=20)

Join Factorization 이 적용이 안된 쿼리의 경우, 각각 DEPT를 읽어(위에서는 2번 읽음) UNION ALL로 결과값을 조회합니다.

내부적으로 아래쿼리 처럼 변경하여 처리한 것입니다. 

VW_JF_SET$35C2C1E7 을 생성하여, EMP 만을 조회한 블록을 생성합니다. (deptno=10 인것과 deptno=20 인것)

그 블록과 dept가 조회가 되어서 최종적으로 결과값을 반환하였습니다. (JF를 수행하여 변경된 쿼리는 10053 trace를 떠서 확인했습니다. 

10053 trace 뜨는법

alter session set tracefile_identifier = 'ysbae';

ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';

##이곳에는 분석하고 싶은 쿼리를 넣으시면 됩니다.
select /* FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2)) */
e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 10
union all
select e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 20 ;


ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

 

##후에 log dest에 가서 ysbae로 된 .trc 파일을 열어서 확인하면 됩니다.

사용자가 수행한 쿼리
select 
e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 10 
union all
select e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 20 ;


JF를 수행하여 변경된 쿼리
SELECT 
"VW_JF_SEL$879ECCAB"."EMPNO" "EMPNO"
,"VW_JF_SEL$879ECCAB"."ENAME" "ENAME"
,"VW_JF_SEL$879ECCAB"."JOB" "JOB"
,"VW_JF_SEL$879ECCAB"."SAL" "SAL"
,"VW_JF_SEL$879ECCAB"."COMM" "COMM"
,"VW_JF_SEL$879ECCAB"."DEPTNO" "DEPTNO"
,"VW_JF_SEL$879ECCAB"."DNAME" "DNAME"
,"VW_JF_SEL$879ECCAB"."LOC" "LOC" 
FROM  (SELECT "VW_JF_SET$35C2C1E7"."ITEM_2" "EMPNO"
             ,"VW_JF_SET$35C2C1E7"."ITEM_3" "ENAME"
             ,"VW_JF_SET$35C2C1E7"."ITEM_4" "JOB"
             ,"VW_JF_SET$35C2C1E7"."ITEM_5" "SAL"
             ,"VW_JF_SET$35C2C1E7"."ITEM_6" "COMM"
             ,"D"."DEPTNO" "DEPTNO"
             ,"D"."DNAME" "DNAME"
             ,"D"."LOC" "LOC" 
             FROM  ( 
                (SELECT "E"."DEPTNO" "ITEM_1"
                       ,"E"."EMPNO" "ITEM_2"
                       ,"E"."ENAME" "ITEM_3"
                       ,"E"."JOB" "ITEM_4"
                       ,"E"."SAL" "ITEM_5"
                       ,"E"."COMM" "ITEM_6" 
                       FROM "DEV"."EMP""E" 
                       WHERE "E"."DEPTNO"=10
                       ) 
            UNION ALL  
                (SELECT "E"."DEPTNO" "ITEM_1"
                       ,"E"."EMPNO" "ITEM_2"
                       ,"E"."ENAME" "ITEM_3"
                       ,"E"."JOB" "ITEM_4"
                       ,"E"."SAL" "ITEM_5"
                       ,"E"."COMM" "ITEM_6" 
                       FROM "DEV"."EMP" "E" 
                       WHERE "E"."DEPTNO"=20)
                       ) "VW_JF_SET$35C2C1E7"
    ,"DEV"."DEPT" "D" 
WHERE "VW_JF_SET$35C2C1E7"."ITEM_1"="D"."DEPTNO") "VW_JF_SEL$879ECCAB"

 

+) 추가적으로 히든파라미터를 통해서도 join Factorization의 사용을 제어할 수 있습니다. True/False를 통해서 사용/비사용을 적용할 수 있습니다. 이것은 히든파라미터를 수정하는 것이기 때문에 되도록이면 위의 힌트를 쿼리내에 삽입하여 사용합시다

 

  

 

 

11gR2에서 부터 추가된 개념이라고 합니다. 튜너의 판단보다 옵티마이저의 자동적인 판단으로 튜닝을 진행하는 것이지요. 현재 19c를 주로 사용하고 21c까지 나온 지금을 보면.. 옵티마이저는 더 발전했을 것으로 생각됩니다.

새로 알게된 개념이 있다면 정리하면서 같이 배워나가면 좋겠습니다.

이름은 _optimizer_join_factorization 입니다. 기본적으로는 True입니다.

Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ---------- --------------------------------------------------
_optimizer_join_factorization            TRUE            true       use join factorization transformation

 두가지 방법으로 적용할 수 있습니다. 하나는 세션레벨, 하나는 쿼리에 힌트를 넣어서 적용하는 것입니다.

 

1.세션레벨

alter session set "_optimizer_join_factorization"=false;

2.쿼리 힌트

/*+ opt_param('_optimizer_join_factorization' 'false') */

 

 

 

 

출처 : https://scidb.tistory.com/entry/Transformer-SQL-%ED%8A%9C%EB%8B%9D%EC%9D%98-%EC%83%88%EB%A1%9C%EC%9A%B4-%ED%8C%A8%EB%9F%AC%EB%8B%A4%EC%9E%84

Transformer - SQL 튜닝의 새로운 패러다임