스칼라 서브쿼리 캐싱효과 (장/단점)

쿼리 두개가 있다고 가정한다.

 

 

1. 함수 사용 쿼리

SELECT empno, ename, sal, hitedate, get_dname(e.deptno) AS dname

FROM emp e

WHERE sal >= 2000;

 

 

2. 함수 미사용 쿼리

SELECT empno, ename, sal, hiredate

       (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dname

FROM emp e

WHERE sal >= 2000;

 

 

1번 쿼리는 PL/SQL 컨텍스트 전환 + 함수가 row단위로 반복 실행된다.

 

2번 쿼리 : 스칼라 서브쿼리

- Outer 조인문처럼 NL 조인 방식으로 수행된다.
- 메인 쿼리 레코드마다 정확히 하나의 값만 반환한다.

 

 

더 쉽게 설명하면 Outer 조인문처럼 하나의 문장으로 이해하라는 뜻이다.

 

SELECT /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname

from emp e, dept d

where d.deptno(+) = e.deptno

and e.sal >= 2000;

    

 

 

 

 

 

스칼라 서브쿼리 캐싱 효과

 

스칼라 서브쿼리로 조인을 하게 되면 조인 횟수를 최소화하기 위하여

입력값과 출력 값을 내부 캐시 (Query Execution Cache)에 저장해둔다.

 

조인 할 때마다 캐시에서 '입력 값'을 찾아보고 찾으면 저장된 '출력 값'을 반환한다. 캐시에서 찾지못할때만 조인을 수행하고 결과는 캐시에 저장해둔다.

 

select empno, ename, sal, hiredate,  (    select d.dname                            → 출력 값 d.dname    from dept d    where d.deptno = e.deptno            → 입력 값 e.deptno  ) from e where sal >= 2000;

입력값 : 그 안에서 참조하는 메인 쿼리의 컬럼 값

 

많이 활용되는 튜닝 기법

 

SELECT - LIST에 사용한 함수는 메인쿼리 결과 건수만큼 반복 수행되는데

아래와 같이 스칼라 서브쿼리를 덧씌우면 호출횟수를 최소화할 수 있다. 이유는 캐싱효과 때문이다

 

 

SELECT empno, ename, sal, hiredate,

 (select GET_DNAME(e.deptno) from dual) dname

from  emp e

where sal > 2000;

 

 

 

 

 

아래는 테스트 케이스이다.

 

-------------------------------------------------------------------------------- -- 0. 환경 초기화 -------------------------------------------------------------------------------- DROP TABLE emp PURGE; DROP TABLE dept PURGE; DROP TABLE sales PURGE; CREATE TABLE dept (     dept_id NUMBER PRIMARY KEY,     dept_name VARCHAR2(100),     region_cd VARCHAR2(10) ); CREATE TABLE emp (     emp_id NUMBER PRIMARY KEY,     emp_name VARCHAR2(100),     dept_id NUMBER,     salary NUMBER,     hire_date DATE ); CREATE TABLE sales (     emp_id NUMBER,     sale_amt NUMBER,     sale_dt DATE ); -------------------------------------------------------------------------------- -- 1. 데이터 삽입 -------------------------------------------------------------------------------- BEGIN     FOR i IN 1..10 LOOP         INSERT INTO dept VALUES (i, 'DEPT_'||i, CASE WHEN i<=5 THEN 'A' ELSE 'B' END);     END LOOP;     FOR i IN 1..10000 LOOP         INSERT INTO emp VALUES (             i,             'EMP_'||i,             MOD(i,10)+1,             TRUNC(DBMS_RANDOM.VALUE(3000,8000)),             TRUNC(SYSDATE-DBMS_RANDOM.VALUE(0,1000))         );     END LOOP;     FOR i IN 1..50000 LOOP         INSERT INTO sales VALUES (             MOD(i,10000)+1,             TRUNC(DBMS_RANDOM.VALUE(100,1000)),             TRUNC(SYSDATE-DBMS_RANDOM.VALUE(0,365))         );     END LOOP;     COMMIT; END; / BEGIN     DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT');     DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP');     DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES'); END; / -------------------------------------------------------------------------------- -- 2. 기본 스칼라 서브쿼리 (NL 스타일) -------------------------------------------------------------------------------- -- 각 사원의 부서 이름을 스칼라 서브쿼리로 조회 SELECT e.emp_id, e.emp_name,        (SELECT d.dept_name FROM dept d WHERE d.dept_id = e.dept_id) AS dept_name FROM emp e WHERE ROWNUM <= 10000;   Execution Plan --------------------------------------------------------------------------------------------------    1  COUNT (STOP NODE) (STOP LIMIT 20001) (Cost:64, %%CPU:1, Rows:10000)    2    TABLE ACCESS (FULL): EMP (Cost:33, %%CPU:0, Rows:10000)    3    CACHE (Cost:3, %%CPU:0, Rows:0)    4      TABLE ACCESS (ROWID): DEPT (Cost:3, %%CPU:0, Rows:1)    5        INDEX (UNIQUE SCAN): _TIBERO_CON266000671 (Cost:2, %%CPU:0, Rows:1) Execution Stat --------------------------------------------------------------------------------------------------    1  COUNT (STOP NODE) (STOP LIMIT 20001) (Time:1.68 ms, Rows:10000, Starts:1)    2    TABLE ACCESS (FULL): EMP (Time:.3 ms, Rows:10000, Starts:1)    3    CACHE (Time:0. ms, Rows:10, Starts:10)    4      TABLE ACCESS (ROWID): DEPT (Time:.02 ms, Rows:10, Starts:10)    5        INDEX (UNIQUE SCAN): _TIBERO_CON266000671 (Time:.02 ms, Rows:10, Starts:10) Predicate Information --------------------------------------------------------------------------------------------------    5 - access: ("D"."DEPT_ID" = :0) (0.100)

 

DEPT_ID 컬럼의 값의 분포는 1~10이다.

 

동일한 DEPT_ID 값이 많으면 (즉 입력값의 종류가 소수라면)  Scalar Subquery Caching이 작동한다.

해시 충돌 가능성이 작을 때 효과가 발생한다. 만약 반대의 경우라면 매번 캐시를 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 늘게 된다.

 

 

Starts는 해당 플랜 노드가 실제 재시작된 횟수를 의미한다.

위에서 조인 할 때마다 캐시에서 '입력 값'을 찾아보고 찾으면 저장된 '출력 값'을 반환한다고 했고 

캐시에서 찾지못할때만 조인을 수행하고 결과는 캐시에 저장해둔다.

 

DEPT_ID 컬럼의 값의 분포가 10개이기 때문에 찾지 못한 경우인 10번을 수행하게 된 것이다.

 

 

UNNEST 힌트를 썼을 때는 다음과 같다

SELECT e.emp_id, e.emp_name,        (SELECT /*+ UNNEST */ d.dept_name FROM dept d WHERE d.dept_id = e.dept_id) AS dept_name FROM emp e WHERE ROWNUM <= 10000 / Total elapsed time 00:00:00.632990 Execution Plan --------------------------------------------------------------------------------------------------    1  HASH JOIN (REVERSE LEFT OUTER) (Cost:37, %%CPU:2, Rows:10000)    2    TABLE ACCESS (ROWID): DEPT (Cost:3, %%CPU:0, Rows:10)    3      INDEX (FULL): _TIBERO_CON266000671 (Cost:2, %%CPU:0, Rows:10)    4    COUNT (STOP NODE) (STOP LIMIT 10001) (Cost:34, %%CPU:2, Rows:10000)    5      TABLE ACCESS (FULL): EMP (Cost:33, %%CPU:0, Rows:10000) Execution Stat --------------------------------------------------------------------------------------------------    1  HASH JOIN (REVERSE LEFT OUTER) (Time:1.35 ms, Rows:10000, Starts:1)    2    TABLE ACCESS (ROWID): DEPT (Time:.01 ms, Rows:10, Starts:1)    3      INDEX (FULL): _TIBERO_CON266000671 (Time:.01 ms, Rows:10, Starts:1)    4    COUNT (STOP NODE) (STOP LIMIT 10001) (Time:.76 ms, Rows:10000, Starts:1)    5      TABLE ACCESS (FULL): EMP (Time:.31 ms, Rows:10000, Starts:1) Predicate Information --------------------------------------------------------------------------------------------------    1 - access: ("D"."DEPT_ID" = "E"."DEPT_ID") (0.100) NAME                                VALUE ------------------------------ ---------- db block gets                          13 consistent gets                        83 physical reads                          0 redo size                               0 sorts (disk)                            0 sorts (memory)                          3 rows processed                      10000

 

 

 

 

 

 

스칼라 서브쿼리 캐싱 부작용

 

1. 입력값이 많을 때

만일 다음과 같은 쿼리가 있다면 스칼라 서브쿼리 캐싱에서의 성능은 어떨까?

select 거래번호, 고객번호, 영업조직ID, 거래구분코드         , (select 고객명 from 고객 where 고객번호 = t.고객번호) 고객명 from 거래 t where 거래일자 >= to_char(add_months(sysdate, -3), 'yyyymmdd') -- 50,000건

 

고객은 100만 명이라고 할때 캐시에 담을 수는 없을만큼의 많은 고객번호가 존재한다. 불필요한 낭비일 뿐이다.

 

2. 위에서 많이 활용되는 튜닝 기법으로 캐싱효과를 보기 위해 함수를 감싼다고 했다.

select 매도회원번호,         매수회원번호, ...         매도 계좌번호, (select acnt_nm(매도계좌번호) from dual) 매도계좌명         매수 계좌번호, (select acnt_nm(매수계좌번호) from dual) 매수계좌명 ..

이렇게 위와같이 되어 있을 때 매도,매수 계좌번호가 무수히 많다면 서브쿼리 캐싱효과를 전혀 기대할 수 없다.