1. 스칼라 서브쿼리는 최종 결과 만큼 수행하자.
– TEST3. 스칼라 서브쿼리의 수행 위치에 따른 성능 문제
-- 스칼라 서브쿼리 작성 1.
SELECT ROWNUM rnum, x.*
FROM (
SELECT c1,c2,c3,
(SELECT t2.C3
FROM SCALAR_T2 T2
WHERE t2.c1 = t1.c1) AS t2_c3
FROM SCALAR_T1 T1
ORDER BY c1 , c2
) x
WHERE ROWNUM <= 10 ;
*************************[Explain Plan Time: 2013/09/05 15:21:07]*************************
Execution Plan
Predicate information (identified by operation id):
-- 스칼라서브쿼리 작성 2.
SELECT ROWNUM rnum, x.* ,
(SELECT t2.c3
FROM SCALAR_T2 T2
WHERE t2.c1 = x.c1) AS t2_c3
FROM (
SELECT c1, c2, c3
FROM SCALAR_T1 T1
ORDER BY c1, c2
) x
WHERE ROWNUM <= 10 ;
*************************[Explain Plan Time: 2013/09/05 15:21:36]*************************
Execution Plan
Predicate information (identified by operation id):
2. 스칼라 서브쿼리와 조인의 관계로 보는 SQL
SELECT ROWNUM rnum, x.*
FROM (
SELECT c1,c2,c3,
(SELECT t2.C3
FROM SCALAR_T2 T2
WHERE t2.c1 = t1.c1) AS t2_c3
FROM SCALAR_T1 T1
ORDER BY c1 , c2
) x
WHERE ROWNUM <= 10
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
Rows Execution Plan
SELECT ROWNUM rnum, x.* ,
(SELECT t2.c3
FROM SCALAR_T2 T2
WHERE t2.c1 = x.c1) AS t2_c3
FROM (
SELECT c1, c2, c3
FROM SCALAR_T1 T1
ORDER BY c1, c2
) x
WHERE ROWNUM <= 10
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
Rows Execution Plan
두 SQL 의 IO 처리량의 차이
SCALAR_T2에서 P2_C3 컬럼 값을 추출하기 위해 스칼라 서브쿼리를 사용하였다.
SQL[l] 은 SCALAR_T1 테이블의 모든 데이터 건수인 500000 건 만큼 스칼라 서브쿼리(SCALAR_T2)가
반복 수행되어 총 1501114 블록을 읽었다.
1501114 = 1502292 (전체 처리 Block) - 1178 (스칼라 서브쿼리 수행이전 처리 Block)
SQL[2]는 SCALAR_T1 테이블을 먼저 C1,C2 컬럼으로 정렬한후，ROWNUM 조건으로 10 건만 추출하여
스칼라 서브쿼리 (SCALAR_T2)를 수행했다.
그러므로 SQL[2]는 SQL[1] 에 비해 스칼라 서브쿼리의 수행횟수가 줄어， SQL[1] 에 비해
매우 적은 1/0 처리량으로 SQL 을 수행하였다.
튜닝전]
SELECT T1.C1 ,T1.C2, T1.C3,
(SELECT T2.C3
FROM SCALAR_T2 T2
WHERE T2.C1 = T1.C1) AS T2_C3,
(SELECT T3.C3
FROM SCALAR_T3 T3
WHERE T3.C1 = T1.C1) AS T3_C3
FROM SCALAR_T1 T1
ORDER BY T1.C1, T1.C2 ;
call count cpu elapsed disk query current rows
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
튜닝후]
SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 AS t2_c3, t3.c3 AS t3_c3
FROM SCALAR_T1 T1, SCALAR_T2 T2, SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.c2;
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
튜닝전]
SELECT ROWNUM rnum, X.*
FROM (
SELECT /*+ USE_NL(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 AS t2_c3, t3.c3 AS t3_c3
FROM SCALAR_T1 T1, SCALAR_T2 T2, SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.c2
) X
WHERE ROWNUM <= 10 ;
call count cpu elapsed disk query current rows
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
튜닝1. 해쉬변환]
SELECT ROWNUM rnum, X.*
FROM (
SELECT /*+ LEADING(T1) USE HASH(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 AS t2_c3, t3.c3 AS t3_c3
FROM SCALAR_T1 T1, SCALAR_T2 T2, SCALAR_T3 T3
WHERE t1.c1 = t2.c1( +)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1 , t1.c2
) X
WHERE ROWNUM <= 10
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
튜닝2. 최종]
SELECT ROWNUM rnum, X.*,
(SELECT t2.C3
FROM scalar_t2 t2
WHERE t2.c1 = x.c1) AS t2_c3 ,
(SELECT t3.C3
FROM scalar_t3 t3
WHERE t3.c1 = x.c1) AS t3_c3
FROM (SELECT t1.C1, t1.c2, t1.c3
FROM scalar_t1 t1
ORDER BY t1.c1, t1.c2 ) X
WHERE ROWNUM <= 10
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
######################################################################
(3) 두개이상의 값을 리턴하고 싶을때
select d.deptno, d.dname, avg_sal, min_sal, max_sal
from dept d
,(select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
from emp group by deptno) e
where e.deptno(+) = d.deptno
and d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
30 SALES 1566.666666666666666666666666666666666667 950 2850
Execution Plan
==> 시카고 부서만 대상으로 급여 집계해야하는데 EMP 테이블을 전체 스캔 하는 비효율 존재
select d.deptno, d.dname
,(select avg(sal), min(sal), max(sal) from emp where deptno = d.deptno)
from dept d
where d.loc = 'CHICAGO';
Error: # 913, ORA-00913: 값의 수가 너무 많습니다
SQL Execution Time > 00:00:00.000
==> 문맥상 안맞는 의미 : 스칼라서브 쿼리는 한 레코드당 하나의 값만 리턴
select d.deptno, d.dname
,(select avg(sal) from emp where deptno = d.deptno) avg_sal
,(select min(sal) from emp where deptno = d.deptno) min_sal
,(select max(sal) from emp where deptno = d.deptno) max_sal
from dept d
where d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
Execution Plan
Predicate information (identified by operation id):
==> EMP에서 같은 범위를 반복 억세스 하는 비효율이 발생.
select deptno, dname
, to_number(substr(sal, 1, 7)) avg_sal
, to_number(substr(sal, 8, 7)) min_sal
, to_number(substr(sal, 15)) max_sal
from (
select d.deptno, d.dname
,(select lpad(avg(sal), 7) || lpad(min(sal), 7) || max(sal)
from emp where deptno = d.deptno) sal
from dept d
where d.loc = 'CHICAGO'
);
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
30 SALES 1566.66 950 2850
*************************[Explain Plan Time: 2013/09/04 15:00:10]*************************
Execution Plan
Predicate information (identified by operation id):
==> 컨캣을 활용하여 하나의 컬럼에 넣은후 substr을 이용하여 원하는 값을 잘라내어 보여줌.
SQL> create or replace type sal_type as object
( avg_sal number, min_sal number, max_sal number ) ;
/
Statement Processed.
SQL> select deptno, dname
, a.sal.avg_sal, a.sal.min_sal, a.sal.max_sal
from (
select d.deptno, d.dname
,(select sal_type( avg(sal), min(sal), max(sal) )
from emp where deptno = d.deptno) sal
from dept d
where d.loc = 'CHICAGO'
) a;
DEPTNO DNAME SAL.AVG_SAL SAL.MIN_SAL SAL.MAX_SAL
1 rows selected.
*************************[Explain Plan Time: 2013/09/04 15:51:21]*************************
Execution Plan
Predicate information (identified by operation id):
==> SQL이 지저분해지는 것을 방지 하기 위하여 TYPE 오브젝트 활용.
##############################################################
##################################################
SELECT A.EMPLOYEE_ID, A.FIRST_NAME , A.LAST_NAME , A.EMAIL
FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG') A
WHERE A.LOCATION_ID> 0;
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG';
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME ,EMAIL
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG'
AND (SELECT LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) > 0 ;
Predicate Information이완전히 같음을 알 수 있다.
10053 Trace를 분석해보자
검증1.
SELECT /*+ PUSH_SUBQ(@SUB) */
A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.EMAIL
FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT /*+ QB_NAME(SUB) */ LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG' ) A
WHERE A.LOCATION_ID > 0;
검증2.
CREATE INDEX EMP_JOB_DEPT_IX ON EMPLOYEES(JOB_ID, DEPARTMENT_ID);