실행 계획(PLAN) 분석: Nested Loop Join과 테이블 FULL Scan

Nested Loop Join이면서 Full Scan을 수행하는 쿼리의 실행 계획을 분석하여 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) FULL(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 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     6 |   636 |    14   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     6 |   396 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    40 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

실행 계획에서 "Rows"는 해당 단계에서 예상되는 출력 레코드 수를 나타냅니다. TABLE ACCESS FULL 단계에서 "Rows"는 예상 출력 레코드 수를 의미하며, 해당 테이블을 스캔하여 가져온 데이터의 예상적인 건수를 보여줍니다. 즉, "EMP" 테이블을 TABLE ACCESS FULL 단계에서 6건의 출력 레코드가 예상된다는 의미입니다.

실제로 테이블을 스캔하여 가져온 데이터의 건수는 실제 실행할 때마다 달라질 수 있습니다. 그리고 "Rows" 값은 최적화를 위해 옵티마이저가 쿼리 실행 계획을 작성할 때 사용되는 예상치입니다. 따라서 "Rows" 값은 정확한 결과를 나타내는 것이 아니라 예상치에 근사한 값입니다. 실제 실행 시에는 데이터의 분포나 인덱스 상태 등에 따라 실제로 가져오는 데이터 건수가 달라질 수 있습니다.

 

---

 

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

 

쿼리:

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

-- 쿼리 실행
SELECT /*+ USE_NL(e d) FULL(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 |        |       |    14 (100)|          |      6 |00:00:00.01 |      61 |
|   1 |  NESTED LOOPS      |      |      1 |      6 |   636 |    14   (0)| 00:00:01 |      6 |00:00:00.01 |      61 |
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |      6 |   396 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |      13 |
|*  3 |   TABLE ACCESS FULL| DEPT |      6 |      1 |    40 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      48 |
---------------------------------------------------------------------------------------------------------------------

 

실행 계획에서 Starts는 각 단계의 연산이 시작된 횟수를 나타냅니다. 각 단계에서 스캔이 발생하는 경우, 해당 스캔이 시작된 횟수가 Starts에 표시됩니다. 따라서 Starts 값을 통해 각 테이블에 대한 스캔이 몇 번 수행되었는지 확인할 수 있습니다.

실행 계획에서 특정 단계가 여러 번 시작된 경우, 예를 들어 Nested Loop Join에서 바깥쪽 루프가 여러 번 실행되면 각 루프의 시작 횟수가 Starts에 표시될 수 있습니다. 이를 통해 어떤 단계가 얼마나 많이 실행되었는지를 파악할 수 있습니다. Starts 값은 성능 튜닝과 디버깅에 유용한 정보 중 하나입니다.

E-Rows는 예상되는 결과 건수를 나타내며, A-Rows는 실제로 반환된 결과 건수를 나타냅니다.

Nested Loop Join에서 바깥쪽 테이블(EMP 테이블)의 각 행에 대해 안쪽 테이블(DEPT 테이블)을 조인하는 경우, E-Rows는 예상되는 조인 결과 건수를 나타냅니다. E-Rows 값이 6이라면, 바깥쪽 테이블의 각 행에 대해 6개의 매칭되는 행이 DEPT 테이블에서 예상되며, 따라서 6번의 스캔 시도가 이루어질 것으로 예상됩니다.

그러나 A-Rows는 실제로 실행된 스캔 시도 중에 조인으로 반환된 결과 건수를 나타냅니다. 예를 들어, 6번의 스캔 시도 중에 실제로 매칭되는 결과가 1건씩 총 6건이 반환되었다면, A-Rows 값은 6이 될 것입니다.

따라서 E-Rows는 예상되는 조인 결과 건수를 나타내며, A-Rows는 실제로 반환된 결과 건수를 나타냅니다. 이를 통해 실행 계획에서 예상과 실제의 차이를 확인하고 성능 이슈를 분석할 수 있습니다.

 

💡 Tip!

실행 계획은 데이터베이스 옵티마이저가 쿼리를 실행할 때 어떤 방법으로 데이터에 접근하고 조인을 수행할지를 결정하는데 도움을 주는 정보입니다. 실행 계획에서는 주로 예상된 결과 건수(E-Rows)와 실제 결과 건수(A-Rows)가 제공됩니다. 하지만 스캔된 건수를 직접 확인하는 것은 보통 불가능합니다.

실제로 스캔 건수를 확인하기 위해서는 실행 시점에서 쿼리를 실행하고 실제로 데이터베이스에서 쿼리가 수행되는 정보를 확인해야 합니다. 예를 들어, 조건을 제외한 전체 테이블 건수를 확인하거나, 스캔된 건수를 추정할 수 있는 다른 방법을 사용할 수 있습니다.

데이터베이스에서 테이블의 전체 건수를 확인하기 위해서는 다음과 같이 쿼리를 실행할 수 있습니다.
SELECT count(*) FROM emp; 또는 salary >= 1000000과 같은 조건을 제외하고 전체 테이블 건수를 확인해볼 수도 있습니다. 이를 통해 조건에 의해 얼마나 많은 레코드가 스캔되는지 대략적으로 알 수 있습니다.

예상되는 스캔 건수와 실행 계획에서 결과 건수를 비교하여 건수 차이를 파악하는 것이 중요합니다. 스캔 건수와 결과 건수의 차이가 큰 경우, 해당 조건에 대해 인덱스 생성을 고려해볼 필요성이 있습니다. 인덱스를 생성함으로써 스캔된 건수를 줄이고 쿼리 성능을 향상시킬 수 있습니다.

따라서 실행 계획에서 제공하는 정보를 통해 스캔 건수와 결과 건수의 차이를 이해하고 인덱스 생성에 대한 고려를 하는 것이 데이터베이스 튜닝에 도움이 될 것입니다.

 

 

목차