실행 계획(PLAN) 분석: Nested Loop Join과 INDEX Scan

Nested Loop Join이면서 INDEX 스캔을 수행하는 쿼리의 실행 계획을 분석하여 Nested Loop의 동작 방식에 대해 자세히 알아보고자 합니다.

 

emp와 dept 테이블을 생성하고 데이터를 삽입하는 쿼리입니다.

 

쿼리:

-- EMP 테이블 생성
CREATE TABLE emp (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER,
    dept_id NUMBER
);

-- DEPT 테이블 생성
CREATE TABLE dept (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50)
);

-- EMP 테이블에 데이터 삽입
INSERT INTO emp VALUES (1, 'John Doe', 1500000, 101);
INSERT INTO emp VALUES (2, 'Jane Smith', 1200000, 102);
INSERT INTO emp VALUES (3, 'Michael Johnson', 800000, 103);
INSERT INTO emp VALUES (4, 'Emily Brown', 1800000, 101);
INSERT INTO emp VALUES (5, 'William Lee', 900000, 103);
INSERT INTO emp VALUES (6, 'Olivia Kim', 1100000, 102);
INSERT INTO emp VALUES (7, 'James Wilson', 1300000, 101);
INSERT INTO emp VALUES (8, 'Sophia Davis', 950000, 103);
INSERT INTO emp VALUES (9, 'Alexander Martinez', 1400000, 102);
INSERT INTO emp VALUES (10, 'Emma Anderson', 750000, 101);

-- DEPT 테이블에 데이터 삽입
INSERT INTO dept VALUES (101, 'IT');
INSERT INTO dept VALUES (102, 'Sales');
INSERT INTO dept VALUES (103, 'HR');

 

위의 쿼리를 실행하면 emp 테이블과 dept 테이블이 생성되고, 각 테이블에 데이터가 삽입됩니다. 이제 해당 테이블들을 사용하여 조인을 수행할 수 있습니다. 각 테이블에서 salary >= 1000000인 데이터는 6건입니다.

 

쿼리:

EXPLAIN PLAN FOR
SELECT /*+ USE_NL(e d) LEADING(e d) */ e.emp_id, e.emp_name, e.salary, d.dept_name
FROM emp e, dept d
WHERE e.dept_id = d.dept_id
AND e.salary >= 1000000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행계획:

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     6 |   636 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                  |     6 |   636 |     9   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                  |     6 |   636 |     9   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP              |     6 |   396 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C00128859997 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT             |     1 |    40 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

 

이 실행 계획은 Nested Loop Join을 사용하여 EMP 테이블과 DEPT 테이블을 조인하는 쿼리입니다. 먼저 EMP 테이블을 FULL 스캔하여 6건의 레코드를 가져옵니다. 그리고 INNER 테이블로 DEPT 테이블을 선택하고, DEPT 테이블의 인덱스를 이용하여 조인 조건에 해당하는 데이터를 찾습니다. 이 때, EMP 테이블의 레코드 하나당 DEPT 테이블에서 1건의 데이터를 찾아 조인합니다. 최종적으로 6건의 결과가 출력됩니다. 전체 쿼리의 비용은 9이며, 예상 시간은 1초입니다.

 

---

 

다음은 "DBMS_XPLAN.DISPLAY_CURSOR" 함수를 사용하여 실행 계획을 조회하는 쿼리입니다:

 

쿼리:

-- 세션의 통계 수집 레벨을 설정합니다.
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 쿼리 실행
SELECT /*+ USE_NL(e d) LEADING(e d) */ e.emp_id, e.emp_name, e.salary, d.dept_name
FROM emp e, dept d
WHERE e.dept_id = d.dept_id
AND e.salary >= 1000000;

-- 실행한 SQL의 sql_id 확인
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text NOT LIKE '%v$sql%'
AND sql_text LIKE '%salary >= 1000000%'
ORDER BY last_active_time desc;

-- 실행 계획과 통계 정보를 조회합니다. 'sql_id'는 실행한 SQL의 식별자인 sql_id로 대체
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, NULL, 'ADVANCED ALLSTATS LAST'));
-- 현재 세션에서 실행된 최근 SQL 쿼리의 실행 계획과 통계 정보를 조회합니다.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));

 

실행계획:

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |       |     9 (100)|          |      6 |00:00:00.01 |      25 |
|   1 |  NESTED LOOPS                |                  |      1 |      6 |   636 |     9   (0)| 00:00:01 |      6 |00:00:00.01 |      25 |
|   2 |   NESTED LOOPS               |                  |      1 |      6 |   636 |     9   (0)| 00:00:01 |      6 |00:00:00.01 |      19 |
|*  3 |    TABLE ACCESS FULL         | EMP              |      1 |      6 |   396 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |      13 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C00128859997 |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       6 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT             |      6 |      1 |    40 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------------------------------------------

 

실행 계획을 통해 스캔 횟수를 직접 확인하는 것은 어렵지만, 실행 계획의 패턴과 스캔 횟수 간의 관계를 파악하여 쿼리의 성능 튜닝과 최적화에 도움이 될 수 있습니다. 위 실행 계획에서는 EMP 테이블을 FULL SCAN하여 10건을 스캔하고 6건의 결과를 얻었습니다. DEPT 테이블과 6번의 조인 시도마다 1건의 INDEX SCAN과 테이블 SCAN이 발생했습니다.

반면 DEPT 테이블을 FULL SCAN할 경우, EMP 테이블의 각 데이터 1건당 DEPT 테이블 전체 데이터인 3건을 스캔합니다. 이를 비교하면 위 실행 계획에 INDEX UNIQUE SCAN이 추가되었지만 테이블 스캔이 1/3로 줄어들었음을 확인할 수 있습니다. DEPT 테이블의 데이터가 많을 경우 이 격차가 더욱 커질 수 있습니다.

따라서 실행 계획을 통해 스캔 횟수를 직접 확인하는 것은 어렵지만, 실행 계획의 패턴과 스캔 횟수 간의 관계를 파악하여 쿼리의 성능 튜닝과 최적화에 도움이 될 수 있습니다. 이렇게 실행 계획을 분석하여 스캔 횟수를 추정하는 것은 쿼리의 성능 분석에 유용한 정보를 제공합니다.

 

💡 Tip!

스캔(Scan) 건수와 스캔(Scan) 횟수는 서로 다른 개념입니다. 스캔 건수는 실제로 스캔하여 가져온 데이터의 건수를 나타내는 반면, 스캔 횟수는 스캔을 시도한 횟수를 의미합니다. 한 번의 스캔으로 여러 건의 데이터를 가져올 수 있으므로 스캔 건수와 스캔 횟수가 다를 수 있습니다.

 

 

목차