[초보 DBA를 위한 이것저것]파싱(Parsing)과 실행계획(Excution Plan)

파싱(Parsing)

Oracle DB의 메모리 구조

SQL은 SGA(System Global Area) 내부 Shared Pool의 라이브러리 캐시(Library Cache)에는 SQL 실행 정보(실행 계획 등...)가 들어있는데, 이러한 실행 정보를 만드는 과정을 파싱(Parsing)이라고 합니다. 

파싱(Parsing)은 SQL이 들어오면 이를 분석하여 어떻게 실행할지 계획을 세우고 실행하여 결과를 만들어 냅니다. 이러한 파싱은 소프트 파싱(Soft Parsing)과 하드 파싱(Hard Parsing)으로 나뉘어집니다. 

소프트 파싱(Soft Parsing)

파싱(Parsing)을 할 때 SQL 자체가 잘못된 SQL. 즉, 문법이 맞지 않거나, 존재하지 않는 테이블에 CRUD 작업을 하는 SQL이 들어올 수 있는데, 이 때 잘못된 SQL에 대한 체크가 필요합니다. 이러한 체크를 할 때 우리는 "Syntax Check를 한다.", "Semantic Check를 한다"라고 하는데, 이렇게 SQL에 대한 체크를 하는 것을 소프트 파싱(Soft Parsing)이라고 합니다.

 

하드 파싱(Hard Parsing)

반면, SQL이 정상적이라면 어떤 식으로 SQL을 실행할지에 대한 실행 계획을 구성해야 하는데, 이러한 과정을 하드 파싱(Hard Parsing)이라고 합니다. 

 

하드 파싱(Hard Parsing)은 SQL이 복잡해질수록 실행 계획을 만드는 과정에 많은 시간이 걸립니다. 반면, 소프트 파싱(Soft Parsing)은 룰 체크를 하는 작업이라 금방 작업이 끝나겠지만, 하드 파싱은 SQL를 분석해야 하기 때문에 많은 비용이 들어갑니다.

 

그래서 웬만하면 하드 파싱은 새로운 SQL이 들어올 때 한번만 진행하고 이후, 동일한 작업의 SQL이 들어올 경우 소프트 파싱(Soft Parsing)만 진행하고, 라이브러리 캐시(Library Cache)에 저장해 두었다가 바로 실행할 수 있도록 합니다.

---

실행 계획(Excution Plan)

파싱(Parsing)을 통해 작성된 실행 계획(Excution Plan)은 EXPLAIN PLAN 구문을 통해 확인할 수 있고, Toad나 SQL Developer와 같은 Tool에서도 실행 계획을 볼 수 있도록 지원하고 있습니다.

-- statement_id = 'identifier' : 1 ~ 30자로 부여할 수 있는 해당 실행문의 제목
-- FOR statement: 실행 계획을 수립하고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)
EXPLAIN PLAN[set statement_id = 'identifier'] FOR sql statement

-- 아래와 같은 구문을 사용하면 실행 계획이 내부적으로 세워집니다.
EXPLAIN PLAN FOR
SELECT e.EMPNO, e.ENAME, d.DEPTNO, d.DNAME
FROM HSJTB_EMP e, HSJTB_DEPT d
WHERE e.DEPTNO = d.DEPTNO;

-- 아래와 같은 구문을 통해 세운 실행 계획을 조회합니다.
SELECT * FROM TABLE(dbms_xplan.display);

 

또한, 실행 계획(Excution Plan) 이외에도 트레이스(Trace)라는 것이 존재합니다. 실행 계획과 비슷한데, 실행 계획(Excution Plan)은 SQL이 실행되기 전에 "이렇게 SQL을 실행할것이다."라고 보여주는 것이고 트레이스(Trace)는 SQL을 실행한 다음에 실제로 어떻게 SQL이 실행되었는지 보여주는 것입니다. 여기서 한 가지 유의할 점은 실행 계획과 트레이스는 일치하지 않을 수도 있다는 점입니다. 그 이유는 몇 가지 옵션에 따라서 실행 계획과는 좀 다르게 동작되는 경우가 가끔씩 있기 때문입니다.

 

실습을 통해 살펴보기

이번엔 실습을 통해 실행 계획(Excution Plan)과 트레이스(Trace)에 대해 좀 더 살펴보겠습니다.

실행 계획(Excution Plan)

SELECT empno, ename, sal
FROM EMP a, DEPT b
WHERE a.deptno = b.deptno
AND b.name = 'RESEARCH'

위와 같은 쿼리를 실행한 후, SQL Developer과 같은 Tool을 통해 실행 계획(Excution Plan)을 살펴보면 다음과 같은 결과를 볼 수 있습니다.

 

SQL Developer로 살펴본 실행 계획

또한, 직접 쿼리를 실행하지 않아도 EXPLAIN PLAN FOR 구문을 통해서 해당 쿼리문에 대한 실행 계획(Excution Plan)을 확인할 수 있습니다.

EXPLAIN PLAN FOR
SELECT empno, ename, sal
FROM EMP a, DEPT b
WHERE a.deptno = b.deptno
AND b.name = 'RESEARCH'

-- EXPLAIN PLAN FOR 구문을 통해 올려놓은 쿼리문의 실행 계획을 확인할 수 있음.
SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR을 실행하여 살펴본 실행 계획

트레이스(Trace)

트레이스(Trace)를 확인하기 위해선 우선, SET AUTOTRACE ON 을 통해 트레이스 기능을 활성화 시켜야 합니다.

기능을 활성화 시킨 이후 쿼리문을 전송하면 다양한 출력 결과(실행 결과, 실행 계획(Excution Plan), Predicate Information/Statistics)를 확인할 수 있습니다.

-- AUTOTRACE 옵션 실행, 트레이스 활성화
SET AUTOTRACE ON

-- 실행 결과/실행 계획/Predicate Information/Statistics가 조회됨
SELECT empno, ename, sal
FROM EMP a, DEPT b
WHERE a.deptno = b.deptno
AND b.name = 'RESEARCH'

TRACE 조회 결과 - 1

TRACE 조회 결과 - 2

SET AUTOTRACE OFF 로 트레이스 기능을 비활성화 할 수도 있습니다. 

-- AUTOTRACE 옵션 종료
SET AUTOTRACE OFF

-- 실행 결과만 조회됨
SELECT empno, ename, sal
FROM EMP a, DEPT b
WHERE a.deptno = b.deptno
AND b.name = 'RESEARCH'

TRACE 기능 비활성화한 후 결과

또한, SET AUTOTRACE TRACEONLY를 통해 실행 결과를 빼고 트레이스(Trace)만 조회할 수 있습니다.

-- 실행 결과를 빼고 TRACE만 조회
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT empno, ename, sal
FROM EMP a, DEPT b
WHERE a.deptno = b.deptno
AND b.dname = 'RESEARCH'