4.14 실무에 적용하기

• 쿼리블럭이 특정 개수보다 많아지면 Best Plan을 구하지 못할 확률이 높아진다
  • 4.4, 4.5장 - 서브쿼리가 2개인 경우 Search Type은 Exhustive 로 항상 최저 Cost를 보장하였다
  • 서브쿼리 개수가 많아지면서 Search Type은 Linear 로 바뀌어 최저 Cost를 보장하지 못한다.
  • 서브쿼리가 7개가 되자 상황이 더욱 나빠졌다. Search Type이 Two_pass 가 되어 최악의 Plan을 만들 수 있다.

• Simple is Best
  • 서브쿼리 개수 2개 이하 : 최고의 Plan 을, 7개이면 최악의 Plan이 만들어질 가능성이 높다.
  • Complex 서브쿼리는 2개 이하로 SQL을 작성하는 것이 좋다. 안될 경우 6개 이하로 제한하는 것을 고려해야 한다.
    ( 서브쿼리 7개 이상일 때는 조인으로 변경하는 것이 좋다 )

• Cut-off의 법칙을 이해하라
  • 11.2.0.1 까지는 서브쿼리 개수가 3과 7일때 고려하는 Iteration 개수가 Down-Grade 된다.
  • 버전에 따라 달라질 수 있으므로 4.2장에 소개된 SQL을 새로운 버전에서 서브쿼리 숫자를 바꿔가며 테스트 해야한다.

• 하위 Loop 를 경계하라
  • 만일 서브쿼리가 6개, 각각 서브쿼리가 Unnesting 되어 CVM이나 JPPD가 모두 실행될 수 있는 상황이라면 ?
  • Hard Parsing에 걸리는 시간이 Table이나 Index scan에 걸리는 시간보다 더 클 수 있다.
  • 아래 psedo code는 Interleaving의 로직을 단순화 시킨 것이다 ( 4.12장 )

IF CSU is Enable THEN
  FOR i in 1 .. 12 LOOP        --> CSU를 위한 LOOP
       CSU 적용;
       CSU Costing;

       IF CVM IS Enable THEN
          FOR j in 1 .. 12 LOOP  --> CVM을 위한 하위 LOOP
            CVM 적용 ;
            CVM Costing;
       END LOOP;
END IF;

IF JPPD is Enable THEN
   FOR k in 1..12 LOOP        --> JPPD를 위한 하위 LOOP
       JPPD 적용;
       JPPD Costing;
    END LOOP;

END IF;

END LOOP;

최저 Cost 적용;
END IF;

• • CSU를 고려하기 위해 바깥쪽 LOOP가 12번 실행될 때마다 새로운 SQL을 CVM 모듈에 적용하고 있다.
  • CVM을 적용할 수 있는지 체크하고, 가능하다면 CVM LOOP (Iteration) 가 최대 12번 실행될 수 있다.
    (JPPD 경우도 마찬가지)
  • 이론적으로 가능한 Loop 횟수 : CSU 12회 X (CVM 12회 + JPPD 12회) = 288회

• 하위 Loop가 부하가 되는 경우 서브쿼리를 인라인뷰로 대체하라
  • 288회 loop가 반복된다. 1단계 LOOP는 개수가 늘어나도 성능에 미치는 영향은 일정하게 증가.
  • 하위 LOOP 가 있을 경우 성능이 급격히 저하될 수 있다.
  • 자주 수행되는 SQL이 Complex 서브쿼리 개수가 많으며, 매번 Hard parsing 이 발생하는 Dynamic SQL이라면 서브쿼리를 없애고 인라인뷰로 대신하는 것이 가능한지 생각해야 한다.

• LOOP 288회를 24회로 줄일 수 있어
  • shared pool 은 한정적이나 Hard Parsing 되어야 할 SQL은 넘쳐난다. -> SQL Aging Out 현상이 발생한다.
  • 그러므로 꼭 Dynamic SQL 이 아니더라도 이 방법을 적용해야 Hard Parsing 부하를 최소화 한다.
    (Loop 횟수가 단 24호로 줄어들 것이다.)
  • Interleaving 을 Juxtaposition 으로 바꾸어 적용하는 것으로 Hard Parsing 시 부하를 없앤다.