| 스칼라 서브쿼리 실행계획 - 스칼라 서브 쿼리는 실행 계획 상단에 별도로 표시된다. - 스칼라 서브 쿼리는 최종 SELECT 목록 순서로 표시되며, 중첩 스칼라 서브 쿼리 안쪽부터 표시된다. 스칼라 서브쿼리의 buffers 와 수행시간 메인쿼리 실행통계에 포함 여부 - 일반적으로 스칼라 서브 쿼리의 수행 시간과 블록 I/O는 메인쿼리 실행 통계에 포함되지 않는다. - 인라인뷰와 같은 뷰머징이 발생하지 않을 경우 전체 실행통계에 포함되어 보인다. 메인쿼리 row 여러 개일 때 스칼라 서브쿼리 실행계획 - 일반상황 시 스칼라서브쿼리 실행통계는 메인쿼리와 별도로 보여짐 - 인라인뷰로 변경 시 메인쿼리 실행계획에 스칼라서브쿼리 시간 및 buffer 포함됨 |
아래 쿼리는 인라인 뷰(t2)와 메인 쿼리(t3)에 스칼라 서브 쿼리를 사용했다.
최종 SELECT 목록 순서(t3 -> t2)대로 스칼라 서브 쿼리가 표시되는 것을 확인할 수 있다.
SELECT (SELECT y.c2
FROM t3 y
WHERE y.c1 = a.c1) AS t3
, a.*
FROM (SELECT a.*
, (SELECT x.c2
FROM t2 x
WHERE x.c1 = a.c1) AS t2
FROM t1 a) a;
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 |
|* 2 | INDEX RANGE SCAN | T3_X1 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 4 | INDEX RANGE SCAN | T2_X1 |
| 5 | TABLE ACCESS FULL | T1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"."C1"=:B1)
4 - access("X"."C1"=:B1)
아래 쿼리는 스칼라 서브 쿼리를 중첩했다. 중첩 스칼라 서브 쿼리의 안쪽(t3)부터 표시되는 것을 확인할 수 있다.
SELECT a.*
, (SELECT (SELECT y.c2
FROM t3 y
WHERE y.c1 = x.c1) AS t4
FROM t2 x
WHERE x.c1 = a.c1
AND x.c2 > 0) AS t3
FROM t1 a;
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 |
|* 2 | INDEX RANGE SCAN | T3_X1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 4 | INDEX RANGE SCAN | T2_X1 |
| 5 | TABLE ACCESS FULL | T1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"."C1"=:B1)
3 - filter("X"."C2">0)
4 - access("X"."C1"=:B1)
스칼라 서브 쿼리의 소요 시간과 블록 I/O에 대한 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
CREATE TABLE sk.t1 AS SELECT 1 AS c1 FROM DUAL;
CREATE TABLE sk.t2 AS SELECT 1 AS c1 FROM DUAL;
CREATE TABLE sk.t3 AS SELECT 1 AS c1 FROM DUAL;
CREATE TABLE sk.t4 AS SELECT 1 AS c1 FROM DUAL;
수행 시간 측정을 위해 아래와 같이 함수를 생성하자. 입력한 초(i_seconds)만큼 대기하는 함수다.
GRANT EXECUTE ON DBMS_LOCK TO SK;
CREATE OR REPLACE FUNCTION SK.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번에서 사실을 확인할 수 있다.
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 | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| | 1 |00:00:01.00 | 134 | 3 |
|* 1 | FILTER | | 1 | | | | 1 |00:00:02.00 | 3 | 1 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1 |
|* 3 | FILTER | | 1 | | | | 1 |00:00:03.00 | 3 | 1 |
| 4 | TABLE ACCESS FULL| T3 | 1 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1 |
|* 5 | FILTER | | 1 | | | | 1 |00:00:04.00 | 3 | 1 |
| 6 | TABLE ACCESS FULL| T4 | 1 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1 |
|* 7 | FILTER | | 1 | | | | 1 |00:00:01.00 | 134 | 3 |
| 8 | TABLE ACCESS FULL| T1 | 1 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FNC_SLEEP"(2)=1)
3 - filter("FNC_SLEEP"(3)=1)
5 - filter("FNC_SLEEP"(4)=1)
7 - filter("FNC_SLEEP"(1)=1)
아래 쿼리는 t2 스칼라 서브 쿼리가 포함된 인라인 뷰를 병합(merge)하지 않았다.
실행 계획 7번에서 t2 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 메인 쿼리에 포함된 것을 확인할 수 있다.
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 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 12 (100)| | 1 |00:00:03.00 | 6 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | FILTER | | 1 | | | | | 1 |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | FILTER | | 1 | | | | | 1 |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 7 | VIEW | | 1 | 1 | 6 | 6 (0)| 00:00:01 | 1 |00:00:03.00 | 6 | -- T1 + T2
|* 8 | FILTER | | 1 | | | | | 1 |00:00:01.00 | 3 |
| 9 | TABLE ACCESS FULL| T1 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------
아래 쿼리는 t3 스칼라 서브 쿼리가 포함된 인라인 뷰를 병합(merge)하지 않았다.
병합되지 않은 인라인 뷰에 포함된 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 메인 쿼리에 포함된다.
실행 계획 7번에서 t2, t3 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 메인 쿼리에 포함된 것을 확인할 수 있다.
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 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 12 (100)| | 1 |00:00:06.00 | 9 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | FILTER | | 1 | | | | | 1 |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | FILTER | | 1 | | | | | 1 |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 7 | VIEW | | 1 | 1 | 9 | 9 (0)| 00:00:01 | 1 |00:00:06.00 | 9 | -- T1 + T2 + T3
|* 8 | FILTER | | 1 | | | | | 1 |00:00:01.00 | 3 |
| 9 | TABLE ACCESS FULL| T1 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------
아래 쿼리는 t2, t3 스칼라 서브 쿼리가 포함된 인라인 뷰를 병합(merge)하지 않았다.
실행 계획 7, 8번에서 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 인라인 뷰에 각각 포함된 것을 확인할 수 있다.
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 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 12 (100)| | 1 |00:00:06.00 | 9 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | FILTER | | 1 | | | | | 1 |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | FILTER | | 1 | | | | | 1 |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 7 | VIEW | | 1 | 1 | 9 | 9 (0)| 00:00:01 | 1 |00:00:06.00 | 9 | -- T1 + T2 + T3
| 8 | VIEW | | 1 | 1 | 6 | 6 (0)| 00:00:01 | 1 |00:00:03.00 | 6 | -- T1 + T2
|* 9 | FILTER | | 1 | | | | | 1 |00:00:01.00 | 3 |
| 10 | TABLE ACCESS FULL| T1 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------
아래 쿼리는 쿼리 블록을 추가해 t4 스칼라 서브 쿼리가 포함된 인라인 뷰까지 병합(merge)하지 않았다.
실행 계획 7번에서 전체 스칼라 서브 쿼리의 수행 시간과 블록 I/O가 포함된 값을 확인할 수 있다.
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 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 12 (100)| | 1 |00:00:10.00 | 12 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:02.00 | 3 |
| 2 | TABLE ACCESS FULL | T2 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | FILTER | | 1 | | | | | 1 |00:00:03.00 | 3 |
| 4 | TABLE ACCESS FULL | T3 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | FILTER | | 1 | | | | | 1 |00:00:04.00 | 3 |
| 6 | TABLE ACCESS FULL | T4 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 7 | VIEW | | 1 | 1 | 12 | 12 (0)| 00:00:01 | 1 |00:00:10.00 | 12 | -- T1 + T2 + T3 + T4
| 8 | VIEW | | 1 | 1 | 9 | 9 (0)| 00:00:01 | 1 |00:00:06.00 | 9 | -- T1 + T2 + T3
| 9 | VIEW | | 1 | 1 | 6 | 6 (0)| 00:00:01 | 1 |00:00:03.00 | 6 | -- T1 + T2
|* 10 | FILTER | | 1 | | | | | 1 |00:00:01.00 | 3 |
| 11 | TABLE ACCESS FULL| T1 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------
메인쿼리 row 여러 개일 때 스칼라 서브쿼리 실행계획
-- 테스트 테이블 생성 5개 row
CREATE TABLE SK.TAB100 AS
SELECT LEVEL LVL
FROM DUAL CONNECT BY LEVEL <= 5
;
일반상황 시 스칼라서브쿼리 실행통계는 메인쿼리와 별도로 보여짐
1+2+3+4+5 = 15 초 및 5번 수행 되는 것 확인
SELECT A.*, (SELECT 1 FROM SK.T2 WHERE SK.FNC_SLEEP(A.LVL) = 1) AS C1
FROM SK.TAB100 A
WHERE ROWNUM <= 5
;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 5 |00:00:00.01 | 3 |
|* 1 | FILTER | | 5 | | | | | 5 |00:00:15.00 | 10 |
| 2 | TABLE ACCESS FULL| T2 | 5 | 1 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 10 |
|* 3 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS FULL| TAB100 | 1 | 5 | 15 | 2 (0)| 00:00:01 | 5 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------
인라인뷰로 변경 시 메인쿼리 실행계획에 스칼라서브쿼리 시간 및 buffer 포함됨
SELECT * FROM (
SELECT A.*, (SELECT 1 FROM SK.T2 WHERE SK.FNC_SLEEP(A.LVL) = 1) AS C1
FROM SK.TAB100 A
WHERE ROWNUM <= 5
)
;
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 5 |00:00:15.00 | 13 |
|* 1 | FILTER | | 5 | | | | | 5 |00:00:15.00 | 10 |
| 2 | TABLE ACCESS FULL | T2 | 5 | 1 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 10 |
| 3 | VIEW | | 1 | 5 | 80 | 10 (0)| 00:00:01 | 5 |00:00:15.00 | 13 |
|* 4 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS FULL| TAB100 | 1 | 5 | 15 | 2 (0)| 00:00:01 | 5 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------'DBMS > SQL튜닝' 카테고리의 다른 글
| ORDER BY 컬럼명이 가리키는 컬럼은? (0) | 2023.07.03 |
|---|---|
| 펑션 위치별 실행 횟 수 (0) | 2023.07.02 |
| INLIST ITERATOR 성능개선(where 절 in 조건) (0) | 2023.06.29 |
| SELECT 절 펑션 수행 횟수 확인 (0) | 2023.06.26 |
| 스칼라 서브쿼리 캐싱 및 버퍼 pinning (0) | 2022.05.24 |
댓글