지난 글에서 스칼라 서브 쿼리의 실행 계획 표시 순서에 대한 내용을 살펴봤다. 이번 글에서는 스칼라 서브 쿼리의 소요 시간과 블록 I/O에 대한 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1-1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; DROP TABLE t3 PURGE; DROP TABLE t4 PURGE; CREATE TABLE t1 AS SELECT 1 AS c1 FROM DUAL; CREATE TABLE t2 AS SELECT * FROM t1; CREATE TABLE t3 AS SELECT * FROM t2; CREATE TABLE t4 AS SELECT * FROM t3;
수행 시간 측정을 위해 아래와 같이 함수를 생성하자. 입력한 초(i_seconds)만큼 대기하는 함수다.
-- 2-1 : SYS
GRANT EXECUTE ON DBMS_LOCK TO tuna;
-- 2-2
CREATE OR REPLACE FUNCTION SCOTT.fnc_sleep (i_seconds IN NUMBER) RETURN NUMBER
IS
BEGIN
DBMS_LOCK.SLEEP (i_seconds);
RETURN 1;
END fnc_sleep;
/
아래 쿼리는 스칼라 서브 쿼리 3개를 사용했다. 메인 쿼리인 t1은 1초, 스칼라 서브 쿼리인 t2, t3, t4는 각각 2초, 3초, 4초가 소요된다. 일반적으로 스칼라 서브 쿼리의 수행 시간과 블록 I/O는 메인 쿼리에 포함되지 않는다. 실행 계획 7번에서 사실을 확인할 수 있다.
-- 3
SELECT a.*
, (SELECT 1 FROM t4 WHERE fnc_sleep (4) = 1) AS t4 -- T4
FROM (SELECT a.*
, (SELECT 1 FROM t3 WHERE fnc_sleep (3) = 1) AS t3 -- T3
FROM (SELECT 1 AS t1
, (SELECT 1 FROM t2 WHERE fnc_sleep (2) = 1) AS t2 -- T2
FROM t1
WHERE fnc_sleep (1) = 1) a) a;
----------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:01.00 | 3 |
|* 1 | FILTER | |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL| T2 |00:00:00.01 | 3 |
|* 3 | FILTER | |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL| T3 |00:00:00.01 | 3 |
|* 5 | FILTER | |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL| T4 |00:00:00.01 | 3 |
|* 7 | FILTER | |00:00:01.00 | 3 | -- !
| 8 | TABLE ACCESS FULL| T1 |00:00:00.01 | 3 |
----------------------------------------------------------
아래 쿼리는 t2 스칼라 서브 쿼리가 포함된 인라인 뷰를 병합(merge)하지 않았다. 실행 계획 7번에서 t2 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 메인 쿼리에 포함된 것을 확인할 수 있다.
-- 4
SELECT a.*
, (SELECT 1 FROM t4 WHERE fnc_sleep (4) = 1) AS t4
FROM (SELECT a.*
, (SELECT 1 FROM t3 WHERE fnc_sleep (3) = 1) AS t3
FROM (SELECT /*+ NO_MERGE */
1 AS t1
, (SELECT 1 FROM t2 WHERE fnc_sleep (2) = 1) AS t2
FROM t1
WHERE fnc_sleep (1) = 1) a) a;
-----------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:03.00 | 6 |
|* 1 | FILTER | |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 |00:00:00.01 | 3 |
|* 3 | FILTER | |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 |00:00:00.01 | 3 |
|* 5 | FILTER | |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 |00:00:00.01 | 3 |
| 7 | VIEW | |00:00:03.00 | 6 | -- T1 + T2
|* 8 | FILTER | |00:00:01.00 | 3 |
| 9 | TABLE ACCESS FULL| T1 |00:00:00.01 | 3 |
-----------------------------------------------------------
아래 쿼리는 t3 스칼라 서브 쿼리가 포함된 인라인 뷰를 병합(merge)하지 않았다. 병합되지 않은 인라인 뷰에 포함된 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 메인 쿼리에 포함된다. 실행 계획 7번에서 t2, t3 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 메인 쿼리에 포함된 것을 확인할 수 있다.
-- 5
SELECT a.*
, (SELECT 1 FROM t4 WHERE fnc_sleep (4) = 1) AS t4
FROM (SELECT /*+ NO_MERGE */
a.*
, (SELECT 1 FROM t3 WHERE fnc_sleep (3) = 1) AS t3
FROM (SELECT 1 AS t1
, (SELECT 1 FROM t2 WHERE fnc_sleep (2) = 1) AS t2
FROM t1
WHERE fnc_sleep (1) = 1) a) a;
-----------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:06.00 | 9 |
|* 1 | FILTER | |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 |00:00:00.01 | 3 |
|* 3 | FILTER | |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 |00:00:00.01 | 3 |
|* 5 | FILTER | |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 |00:00:00.01 | 3 |
| 7 | VIEW | |00:00:06.00 | 9 | -- T1 + T2 + T3
|* 8 | FILTER | |00:00:01.00 | 3 |
| 9 | TABLE ACCESS FULL| T1 |00:00:00.01 | 3 |
-----------------------------------------------------------
아래 쿼리는 t2, t3 스칼라 서브 쿼리가 포함된 인라인 뷰를 병합(merge)하지 않았다. 실행 계획 7, 8번에서 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 인라인 뷰에 각각 포함된 것을 확인할 수 있다.
-- 6
SELECT a.*
, (SELECT 1 FROM t4 WHERE fnc_sleep (4) = 1) AS t4
FROM (SELECT /*+ NO_MERGE */
a.*
, (SELECT 1 FROM t3 WHERE fnc_sleep (3) = 1) AS t3
FROM (SELECT /*+ NO_MERGE */
1 AS t1
, (SELECT 1 FROM t2 WHERE fnc_sleep (2) = 1) AS t2
FROM t1
WHERE fnc_sleep (1) = 1) a) a;
------------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:06.00 | 9 |
|* 1 | FILTER | |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 |00:00:00.01 | 3 |
|* 3 | FILTER | |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 |00:00:00.01 | 3 |
|* 5 | FILTER | |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 |00:00:00.01 | 3 |
| 7 | VIEW | |00:00:06.00 | 9 | -- T1 + T2 + T3
| 8 | VIEW | |00:00:03.00 | 6 | -- T1 + T2
|* 9 | FILTER | |00:00:01.00 | 3 |
| 10 | TABLE ACCESS FULL| T1 |00:00:00.01 | 3 |
------------------------------------------------------------
아래 쿼리는 쿼리 블록을 추가해 t4 스칼라 서브 쿼리가 포함된 인라인 뷰까지 병합(merge)하지 않았다. 실행 계획 7번에서 전체 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 포함된 값을 확인할 수 있다.
-- 7
SELECT *
FROM (SELECT /*+ NO_MERGE */
a.*
, (SELECT 1 FROM t4 WHERE fnc_sleep (4) = 1) AS t4
FROM (SELECT /*+ NO_MERGE */
a.*
, (SELECT 1 FROM t3 WHERE fnc_sleep (3) = 1) AS t3
FROM (SELECT /*+ NO_MERGE */
1 AS t1
, (SELECT 1 FROM t2 WHERE fnc_sleep (2) = 1) AS t2
FROM t1
WHERE fnc_sleep (1) = 1) a) a);
-------------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:10.00 | 12 |
|* 1 | FILTER | |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 |00:00:00.01 | 3 |
|* 3 | FILTER | |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 |00:00:00.01 | 3 |
|* 5 | FILTER | |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 |00:00:00.01 | 3 |
| 7 | VIEW | |00:00:10.00 | 12 | -- T1 + T2 + T3 + T4
| 8 | VIEW | |00:00:06.00 | 9 | -- T1 + T2 + T3
| 9 | VIEW | |00:00:03.00 | 6 | -- T1 + T2
|* 10 | FILTER | |00:00:01.00 | 3 |
| 11 | TABLE ACCESS FULL| T1 |00:00:00.01 | 3 |
-------------------------------------------------------------'Oracle > Tuning' 카테고리의 다른 글
| 바인드 변수 값 조회 (1) | 2018.11.09 |
|---|---|
| 'PLAN_TABLE' is old version (0) | 2018.11.08 |
| 스칼라 서브 쿼리의 실행 계획 #1 - 표시 순서 (0) | 2018.11.06 |
| 인덱스와 부정형 조건 (0) | 2018.11.04 |
| 제약 조건과 인덱스 (0) | 2018.11.04 |
