[SQL튜닝]2023.11.23

망구씨·2023년 11월 23일

📖 소목차
1. nested loop 조인
2. hash 조인
3. sort merge 조인
4. 조인 순서의 중요성
5. outer 조인
6. 스칼라 서브쿼리를 이용한 조인 튜닝
7. 조인을 내포한 DML 문 튜닝
8. 고급 조인 테크닉

✏️ 고급 조인 문장 테크닉(SQLP준비를 위한 성능 고도화책p.301)

✅ 튜닝케이스 1. 누적 데이터 구하기

✔️ 튜닝 전

select /*+ gather_plan_statistics*/ ename, sal, 
                                   (select sum(sal)
                                      from emp e2
                                      where e2.empno <= e1.empno) sumsal
    from emp e1
    order by empno;

SELECT * 
FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |     105 |       |       |          |
|   1 |  SORT AGGREGATE    |      |     14 |      1 |     14 |00:00:00.01 |      98 |       |       |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     14 |      1 |    105 |00:00:00.01 |      98 |       |       |          |
|   3 |  SORT ORDER BY     |      |      1 |     14 |     14 |00:00:00.01 |     105 |  2048 |  2048 | 2048  (0)|
|   4 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

✔️ 튜닝 후

select /*+ gather_plan_statistics*/ 
        ename, sal, sum(sal) over(order by empno) sumsal
    from emp;

SELECT * 
FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |       7 |       |       |          |
|   1 |  WINDOW SORT       |      |      1 |     14 |     14 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

✅ 튜닝케이스 2. 부서번호별로 각각 누적 데이터 구하기

✔️ 튜닝 전 (버퍼 갯수 105개)

select /*+ gather_plan_statistics*/ deptno, ename, sal,
        (select sum(sal)
          from emp e2
          where e2.empno <= e1.empno
          and e2.deptno=e1.deptno)sumsal
   from emp e1
   order by deptno, emptno;

➡️ 같은 부서번호끼리 누적된 값이 나오고있다.
✔️ 튜닝 후 (버퍼 갯수 7개)

select /*+ gather_plan_statistics*/ 
        deptno, ename, sal, sum(sal) over(partition by deptno order by empno) sumsal
    from emp;

✅ 튜닝케이스 3. 총 합 구하기

✔️ 튜닝 전

select /*+ gather_plan_statistics*/  decode(no,1, deptno, 2, null)  as deptno, sum(sal)
               from  emp  e,  (select  rownum  no
                                  from  dual
                                  connect  by level <= 2 )  d
               group  by  decode(no, 1, deptno, 2, null )
               order by deptno;

✔️ 튜닝 후

select /*+ gather_plan_statistics*/  deptno, sum(sal)
  from emp
  group by rollup(deptno);

✅ 튜닝케이스 4. 총 합 구하기

✔️ 튜닝 전

 select decode(no,1, to_char(deptno), 2, '토탈')  as deptno, sum(sal)
               from  emp  e,  (select  rownum  no
                                  from  dual
                                  connect  by level <= 2 )  d
               group  by  decode(no, 1, to_char(deptno), 2, '토탈' )
               order by deptno;

✔️ 튜닝 후

select /*+ gather_plan_statistics*/ nvl(to_char(deptno),'total'), deptno, sum(sal)
  from emp
  group by rollup(deptno);

✏️ 상호 배타적 관계의 조인 (성능 고도화 2권 p.310)

✍🏻 환경구성

@demo

alter  table  emp
    add  emp_kind   varchar2(1)  default  1  not  null;

update  emp
   set  emp_kind =  case  when  mod(empno,2) = 1  then  1
                            else  2  end ;

select  ename, emp_kind  from  emp;     
                                     -- emp_kind 가 1이면 정규직 
                                     -- emp_kind 가 2이면 비정규직

✔️ 정규직 사원 테이블 생성

 create  table  emp_kind1
   as
  select  empno, ename, sal + 200 as  office_sal
       from  emp
       where emp_kind ='1';

✔️ 비정규직 사원 테이블 생성

 create  table  emp_kind2
   as
  select  empno, ename, sal + 200 as  sal
       from  emp
       where emp_kind ='2';

✔️ primary key 제약을 각각 건다

 alter  table  emp_kind1
   add  constraint   pk_emp_kind1   primary  key(empno); 

 alter  table  emp_kind2
   add  constraint   pk_emp_kind2  primary  key(empno);

-- 월급 컬럼 날리기
 alter  table  emp  
   drop  column  sal ;

 select  * from emp_kind1; 
 select  * from emp_kind2;

✅ 아래의 SQL을 튜닝하기

-- emp_kind1(정규직사원) , emp_kind2(비정규직사원), emp(그냥사원)
select /*+ gather_plan_statistics*/ e.empno, e.ename, k1.office_sal, k2.sal
  from emp e, emp_kind1 k1, emp_kind2 k2
  where e.empno = k1.empno(+)
  and e.empno = k2.empno(+)
  and e.empno = 7839;

SELECT * 
FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |      1 |00:00:00.01 |      10 |       |       |          |
|   1 |  MERGE JOIN OUTER              |              |      1 |      1 |      1 |00:00:00.01 |      10 |       |       |          |
|   2 |   MERGE JOIN OUTER             |              |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|*  3 |    TABLE ACCESS FULL           | EMP          |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   4 |    BUFFER SORT                 |              |      1 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID| EMP_KIND1    |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_EMP_KIND1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   7 |   BUFFER SORT                  |              |      1 |      1 |      0 |00:00:00.01 |       1 |  1024 |  1024 |          |
|   8 |    TABLE ACCESS BY INDEX ROWID | EMP_KIND2    |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|*  9 |     INDEX UNIQUE SCAN          | PK_EMP_KIND2 |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

➡️ 한명의 사원을 조회하려고 하는데 정규직인지 비정규직인지 모르기때문에 3개의 테이블을 조인할 수 밖에 없는 상황이다. outer join sign을 쓴 이유는 null값이 나오는 것을 보려고!
➡️ 정규직 사원 번호가 들어오면 비정규직 사원이랑 조인을 하면 안되고, 비정규직 사원의 사원번호가 들어오면 정규직 사원 테이블이랑 조인을 하면 안되는데 위 실행계획보면 다 하고있다. 버퍼가 0이 아님.

✔️ 튜닝 후

select /*+ gather_plan_statistics*/ e.empno, e.ename, k1.office_sal, k2.sal
  from emp e, emp_kind1 k1, emp_kind2 k2
  where decode(e.emp_kind,1, e.empno) = k1.empno(+)
  and (e.emp_kind,2, e.empno) = k2.empno(+)
  and e.empno = 7839;

(e.emp_kind,1, e.empno) 1이면 정규직일거고 2 이면 비정규직일것이다. 선택적 조인이 되게끔 한다.

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS OUTER           |              |      1 |      1 |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS OUTER          |              |      1 |      1 |      1 |00:00:00.01 |       9 |
|*  3 |    TABLE ACCESS FULL          | EMP          |      1 |      1 |      1 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP_KIND1    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |     INDEX UNIQUE SCAN         | PK_EMP_KIND1 |      1 |      1 |      1 |00:00:00.01 |       1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | EMP_KIND2    |      1 |      1 |      0 |00:00:00.01 |       0 |
|*  7 |    INDEX UNIQUE SCAN          | PK_EMP_KIND2 |      1 |      1 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------

➡️ 버퍼의 갯수가 0으로 바뀌었다.

✏️ 서브쿼리 튜닝 방법

1. 순수하게 서브쿼리가 실행되는 SQL (no_unnest)

1) 서브쿼리 부터 수행되도록 할 수 있음 (push_subq)
2) 메인쿼리 부터 수행되로록 할 수 있음 (no_push_subq)

2. 쿼리 변형기에 의해 조인으로 변경되는 SQL (unnest)

1) nested loop semi join (nl_sj)
2) hash semi join (hash_sj)
3) sort merge semi join (merge_sj)

4) nested loop anti join (nl_aj)
5) hash anti join (hash_aj)
6) sort merge anti join (merge_aj)

문제1. 아래 SQL의 실행계확은 순수하게 서브쿼리로 수행되는가 아니면 조인으로 변경이 되어 수행되는가??

select /*+ gather_plan_statistics*/ ename, sal 
 from emp
 where deptno in (select deptno 
                   from dept
                   where deptno=10);

SELECT * 
FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      10 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      3 |      3 |00:00:00.01 |      10 |  1557K|  1557K|  400K (0)|
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------

-- 튜닝 후

select /*+ gather_plan_statistics*/ ename, sal 
 from emp
 where deptno in (select /*+ no_unnest */ deptno 
                   from dept
                   where deptno=10);

no_unnest 부정이 두개라 강한 긍정. 심하게 감싸라 ! -> 즉 서브쿼리를 해체해서 조인을 풀지 말고 순수하게 서브쿼리로 풀어라!
➡️ 메인 쿼리부터 수행이 되었다.

문제2. 위 SQL은 메인 쿼리부터 수행이 되었는데, 서브쿼리부터 수행되게 해보기

select /*+ gather_plan_statistics*/ ename, sal 
 from emp
 where deptno in (select /*+ no_unnest push_subq */ deptno 
                   from dept
                   where deptno=10);

➡️ select /*+ no_unnest push_subq */ : 서브쿼리부터 수행해라
➡️ select /*+ no_unnest no_push_subq */ : 메인쿼리부터 수행해라

문제3. 아래의 SQL이 순수하게 서브쿼리로 실행되게 하는데 서브쿼리부터 수행되게 하기

-- 튜닝 전
select ename, sal
  from emp
  where sal = (select max(sal)
                from emp );

-- 튜닝 후
select /*+ gather_plan_statistics QB_NAME(main) */ ename, sal
from emp
where sal = (select /*+ no_unnest push_subq QB_NAME(sub) */ max(sal) from emp);
                
select * from table(dbms_xplan.display_cursor(format=>'advanced'));  

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     6 (100)|          |
|*  1 |  TABLE ACCESS FULL  | EMP  |     1 |    20 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |      |     1 |    13 |            |          |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - MAIN / EMP@MAIN
   2 - SUB 
   3 - SUB  / EMP@SUB

➡️ QB_NAME 힌트는 쿼리의 이름을 지정해주는 힌트이다. 괄호 안에는 마음대로 써도 됨!
순수하게 서브쿼리로 수행되는게 느리다면 조인으로 변경하는것을 권장한다!!!

문제4. 아래의 SQL을 조인으로 수행되게끔 힌트를 주기!

select ename, sal
  from emp
  where deptno in (select deptno
                    from emp
                    where loc = 'DALLAS');

  SELECT * 
FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |      14 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      5 |      5 |00:00:00.01 |      14 |  1557K|  1557K|  625K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

select /*+ gather_plan_statistics */ ename, sal
  from emp
  where deptno in (select/*+ unnest nl_sj */ deptno
                    from dept
                    where loc = 'DALLAS');

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |      24 |
|   1 |  NESTED LOOPS SEMI |      |      1 |      5 |      5 |00:00:00.01 |      24 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| DEPT |      3 |      1 |      1 |00:00:00.01 |      17 |
-------------------------------------------------------------------------------------

➡️ SEMI JOIN을했다. SEMI의 뜻이 절반 이라는 뜻이있다. 완전한 조인은 조인 순서와 조인 방법을 자유롭게 힌트를 통해 결정할 수 있는데 semi 조인은 조인 방법은 자유롭게 힌트를 통해 지정할 수 있는데 조인 순서를 못바꾼다. 무조건 메인쿼리부터 수행되기 때문에 절반의 조인이다. 해쉬 세미 조인만 조인 순서를 조정할 수 있다.

문제5. 아래의 SQL을 해쉬 세미 조인으로 수행되게 하기

select /*+ gather_plan_statistics */ ename, sal
  from emp
  where deptno in (select/*+ unnest nl_sj */ deptno
                    from dept
                    where loc = 'DALLAS');

select /*+ gather_plan_statistics */ ename, sal
  from emp
  where deptno in (select/*+ unnest hash_sj */ deptno
                    from dept
                    where loc = 'DALLAS');

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |      14 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      5 |      5 |00:00:00.01 |      14 |  1557K|  1557K|  647K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

➡️ 지금 hash table이 emp 이다. 근데 dept가 올라가야함

문제6. 위 SQL의 해쉬 테이블이 dept가 되게 하기

select /*+ gather_plan_statistics */ ename, sal
  from emp
  where deptno in (select/*+ unnest hash_sj swap_join_inputs(dept) */ deptno
                    from dept
                    where loc = 'DALLAS');

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      5 |00:00:00.01 |      14 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI|      |      1 |      5 |      5 |00:00:00.01 |      14 |  2440K|  2440K|  712K (0)|
|*  2 |   TABLE ACCESS FULL  | DEPT |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------

➡️ 힌트는 서브쿼리쪽에 주기

문제7. 아래 SQL을 튜닝하기

select /*+ gather_plan_statistics */ ename, sal
  from emp
  where deptno not in (select deptno
                        from dept
                        where loc='DALLAS');

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      9 |00:00:00.01 |      14 |       |       |          |
|*  1 |  HASH JOIN ANTI NA |      |      1 |     14 |      9 |00:00:00.01 |      14 |  1557K|  1557K|  649K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

➡️ 서브쿼리가 조인으로 풀릴 때 not in을 사용했으면 anti join이 된다. 서브쿼리가 조인으로 풀릴 때 in을 사용했으면 semi join이 나온다.
위 실행계획에서 해쉬 테이블이 dept가 아니라 emp여서 튜닝이 필요한 것이다.

✔️ dept테이블이 해쉬 테이블이 되도록 튜닝!!

select /*+ gather_plan_statistics */ ename, sal
  from emp
  where deptno not in (select /*+ unnest hash_aj swap_join_inputs(dept) */ deptno
                        from dept
                        where loc='DALLAS'
                        and deptno is not null)
   and deptno is not null;

➡️ not in으로 쓴 서브쿼리가 굉장히 느릴 때 and deptno is not null을 꼭 추가하기.

문제8. 아래의 SQL을 튜닝하기

-- customers100 만들기
create table customers100 as select * from sh.customers;
create table sales100 as select * from sh.sales;

-- 튜닝 전
select /*+ gather_plan_statistics */ count(*) 
  from customers100 c
  where c.cust_id not in (select /*+ no_unnest push_subq */ cust_id
                             from sales100
                             where amount_sold between 0 and 10000);

✔️ 튜닝 후

select /*+ gather_plan_statistics */ count(*) 
  from customers100 c
  where c.cust_id not in (select /*+ unnest hash_aj */ cust_id
                             from sales100 
                             where amount_sold between 0 and 10000);

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |      1 |00:00:00.40 |    5852 |   5845 |       |       |          |
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:00.40 |    5852 |   5845 |       |       |          |
|*  2 |   HASH JOIN ANTI NA |              |      1 |  48304 |  47799 |00:00:00.40 |    5852 |   5845 |  4010K|  3056K| 4596K (0)|
|   3 |    TABLE ACCESS FULL| CUSTOMERS100 |      1 |  48304 |  54288 |00:00:00.03 |    1416 |   1413 |       |       |          |
|*  4 |    TABLE ACCESS FULL| SALES100     |      1 |    712K|    918K|00:00:00.18 |    4436 |   4432 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

➡️ swap_join_inputs 안써서 where 밑에 조건 빼주었다.

뷰를 해체하거나 해체하지 않게 하는 힌트

1. merge : view, in line view를 풀어해쳐라
2. no_merge : view, in line view를 풀어해치지 말아라

select /*+ gather_plan_statistics */ v.ename, v.loc, s.grade
  from salgrade s, (select e.ename, d.loc, e.sal, e.deptno
                     from emp e, dept d
                     where e.deptno = d.deptno ) v
  where v.sal between s.losal and s.hisal;

➡️ 실행계획에서 뷰가 안나오는 이유는 쿼리 변형기가 in line view를 해체해서 이다. 쿼리 변형기가 아래와 같이 SQL을 변경했다. 뷰를 해체하지 말라고 해야한다 !!! no_merge

select e.ename, d.loc, s.grade
  from emp e, dept d, salgrade s
  where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

-- 힌트 사용하기
select /*+ gather_plan_statistics no_merge(v) */ v.ename, v.loc, s.grade
  from salgrade s, (select e.ename, d.loc, e.sal, e.deptno
                     from emp e, dept d
                     where e.deptno = d.deptno ) v
  where v.sal between s.losal and s.hisal;

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |     14 |00:00:00.01 |      20 |       |       |          |
|   1 |  MERGE JOIN            |          |      1 |      1 |     14 |00:00:00.01 |      20 |       |       |          |
|   2 |   SORT JOIN            |          |      1 |      5 |      5 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL   | SALGRADE |      1 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
|*  4 |   FILTER               |          |      5 |        |     14 |00:00:00.01 |      14 |       |       |          |
|*  5 |    SORT JOIN           |          |      5 |     14 |     40 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   6 |     VIEW               |          |      1 |     14 |     14 |00:00:00.01 |      14 |       |       |          |
|*  7 |      HASH JOIN         |          |      1 |     14 |     14 |00:00:00.01 |      14 |  1696K|  1696K| 1071K (0)|
|   8 |       TABLE ACCESS FULL| DEPT     |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|   9 |       TABLE ACCESS FULL| EMP      |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

✔️ 인라인뷰 말고 진짜 view만들기

create or replace view emp142
as
select e.ename, d.loc, e.sal, e.deptno
                     from emp e, dept d
                     where e.deptno = d.deptno;

✔️ view랑 조인하기

select /*+ gather_plan_statistics */ v.ename, v.loc, s.grade
  from emp142 v, salgrade s
  where v.sal between s.losal and s.hisal;
>
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     14 |00:00:00.01 |      20 |       |       |          |
|*  1 |  HASH JOIN            |          |      1 |      1 |     14 |00:00:00.01 |      20 |  1557K|  1557K|  663K (0)|
|   2 |   MERGE JOIN          |          |      1 |      1 |     14 |00:00:00.01 |      13 |       |       |          |
|   3 |    SORT JOIN          |          |      1 |      5 |      5 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL | SALGRADE |      1 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
|*  5 |    FILTER             |          |      5 |        |     14 |00:00:00.01 |       7 |       |       |          |
|*  6 |     SORT JOIN         |          |      5 |     14 |     40 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL| EMP      |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|   8 |   TABLE ACCESS FULL   | DEPT     |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

➡️ 뷰를 해체했다

✔️ 그렇다면 view를 해체하지 않게 하기

select /*+ gather_plan_statistics no_merge(v) */ v.ename, v.loc, s.grade
  from emp142 v, salgrade s
  where v.sal between s.losal and s.hisal;

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |     14 |00:00:00.01 |      20 |       |       |          |
|   1 |  MERGE JOIN            |          |      1 |      1 |     14 |00:00:00.01 |      20 |       |       |          |
|   2 |   SORT JOIN            |          |      1 |      5 |      5 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL   | SALGRADE |      1 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
|*  4 |   FILTER               |          |      5 |        |     14 |00:00:00.01 |      14 |       |       |          |
|*  5 |    SORT JOIN           |          |      5 |     14 |     40 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   6 |     VIEW               | EMP142   |      1 |     14 |     14 |00:00:00.01 |      14 |       |       |          |
|*  7 |      HASH JOIN         |          |      1 |     14 |     14 |00:00:00.01 |      14 |  1696K|  1696K| 1071K (0)|
|   8 |       TABLE ACCESS FULL| DEPT     |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|   9 |       TABLE ACCESS FULL| EMP      |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

문제2. 위 실행계획에 view 안의 테이블의 조인 순서를 emp -> dept순이 되게 하기

select /*+ gather_plan_statistics no_merge(v) leading(v.e v.d) */ v.ename, v.loc, s.grade
  from emp142 v, salgrade s
  where v.sal between s.losal and s.hisal;

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |     14 |00:00:00.01 |      20 |       |       |          |
|   1 |  MERGE JOIN            |          |      1 |      1 |     14 |00:00:00.01 |      20 |       |       |          |
|   2 |   SORT JOIN            |          |      1 |      5 |      5 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL   | SALGRADE |      1 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
|*  4 |   FILTER               |          |      5 |        |     14 |00:00:00.01 |      14 |       |       |          |
|*  5 |    SORT JOIN           |          |      5 |     14 |     40 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   6 |     VIEW               | EMP142   |      1 |     14 |     14 |00:00:00.01 |      14 |       |       |          |
|*  7 |      HASH JOIN         |          |      1 |     14 |     14 |00:00:00.01 |      14 |  1557K|  1557K|  662K (0)|
|   8 |       TABLE ACCESS FULL| EMP      |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|   9 |       TABLE ACCESS FULL| DEPT     |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

문제3. (전체 과정의 마지막 문제) 위 SQL의 실행 계획이 아래와 같이 출력되게 하기

select /*+ gather_plan_statistics no_merge(v) leading(v.e v.d) leading(v s) use_nl(s) */ v.ename, v.loc, s.grade
  from emp142 v, salgrade s
  where v.sal between s.losal and s.hisal;

망구씨

Slow and steady wins the race.

이전 포스트

[Oracle Exadata]2023.11.23

다음 포스트

[SQL튜닝]2023.11.23

✏️ 고급 조인 문장 테크닉(SQLP준비를 위한 성능 고도화책p.301)

✅ 튜닝케이스 1. 누적 데이터 구하기

✅ 튜닝케이스 2. 부서번호별로 각각 누적 데이터 구하기

✅ 튜닝케이스 3. 총 합 구하기

✅ 튜닝케이스 4. 총 합 구하기

✏️ 상호 배타적 관계의 조인 (성능 고도화 2권 p.310)

✏️ 서브쿼리 튜닝 방법

뷰를 해체하거나 해체하지 않게 하는 힌트

[Oracle Exadata]2023.11.23

[2023.12.12]교육 2일차_admin

0개의 댓글