Oracle 실행 계획 확인 / 쿼리 튜닝과 최적화

▷ 실행계획이란

실행계획이란 SQL 쿼리가 데이터베이스에서 어떻게 처리되는지에 대한 실행 방법을 나타내는 것입니다.

실행계획은 SQL 옵티마이저(Optimizer)에 의해 생성되며, 옵티마이저가 판단하기에 효율적으로 실행하기 최적의 경로로 쿼리를 수행하도록 처리하는 역할을 합니다.

실행계획을 통해 쿼리의 성능을 최적화하고 튜닝할 수 있습니다.

실행계획을 확인하는 방식에는 EXPLAIN PLAN, AUTORACE, DBMS_XPLAN 등이 있습니다.

 

 

▷ EXPLAIN PLAN

-       PARSE 단계 까지만 보내서 받아진 PLAN으로 PLAN_TABLE에 저장하는 방식입니다.
(PARSE-EXECUTE-FETCH)

-       옵티마이저가 실행계획 생성합니다.

-       명령문을 실행하지는 않는 예상 실행계획을 보여줍니다.

-       예상 실행계획을 PLAN_TABLE에 저장합니다.

*PLAN_TABLE

- 옵티마이저가 SQL이 실행될 때마다 실행계획을 저장해두는 테이블입니다.

- 임시테이블로, 로그아웃 하면 SYS.PLAN_TABLE$은 사라집니다. (10g부터 자동으로 설치)

 

SQL> EXPLAIN PLAN FOR [SQL 쿼리]; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);   Ex) EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=100; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

 

 

▷ AUTOTRACE

-       SQL*PLUS 및 SQL Developer tool에서만 제공하는 기능입니다.

-       SQL 명령의 결과, 예상 실행계획 및 실행 통계 제공합니다.

-       PLAN_TABLE 테이블 및 PLUSTRACE ROLE 필요합니다.

-       autotrace on 하면 결과, 예상 Plan, 통계를 자동으로 확인할 수 있습니다.

-- Windows SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin\plustrce.sql   -- LINUX SQL> $ORALCE_HOME/sqlplus/admin/plustrce.sql   -- autotrace 하고 싶은 계정에 grant plustrace to user 권한 부여 SQL> Grant plustrce to [사용자];

 

-       AUTOTRACE 활성화

- set autotrace on : 결과, 예상 plan, 통계 확인 가능 - set autotrace traceonly : 예상 plan, 통계만 확인 가능 - set autotrace on explain : 결과, 예상 plan 확인 가능 - set autotrace on statistics : 결과, 통계 확인 가능 - set autotrace traceonly explain : 예상 plan만 확인 가능 (= explain plan for) - set autotrace traceonly statistics : 통계만 확인 가능

 

-       AUTOTRACE 예시

AUTOTRACE 활성화 후 쿼리 실행으로 실행계획을 확인할 수 있습니다.

 

 

▷ DBMS_XPLAN

-       실제 명령어 수행한 통계를 받기 위한 뷰입니다.

-       오라클에서 실행계획을 수집할 경우 가장 많이 사용하는 방식입니다.

-       단계별 실행 통계를 제공합니다.

-       SQL 명령 수행시 통계정보를 수집하기 위해 아래 방법으로 수집 지정 필요합니다.

①   ALTER SESSION SET STATISTICS_LEVEL = ALL;

오라클 데이터베이스 세션의 실행 계획 통계 수준을 변경하는 명령입니다.

현재 세션에서 실행되는 모든 쿼리의 실행 계획 및 관련 통계 정보를 자세히 수집하도록 설정합니다.

l  STATISTICS_LEVEL로 데이터베이스 통계정보에 대한 수집 수준을 지정합니다. (기본 값 : TYPICAL)

통계 수준	설명
BASIC	실행 계획 기본 통계 정보만 수집
TYPICAL	실행 계획의 더 많은 통계 정보를 수집 데이터베이스 자체 관리 기능에 필요한 모든 주요 통계 수집
ALL	가장 상세한 실행 계획 통계 정보를 수집 TYPICAL 설정으로 수집된 통계정보에 부가적인 통계를 수집

 

②   GATHER_PLAN_STATISTICS 힌트

이 힌트를 SQL 문에 추가하면 옵티마이저가 해당 쿼리의 실행 계획을 분석하고 실행하는 동안 실행 계획의 통계 정보를 수집합니다.

SQL문에 /*+ GATHER_PLAN_STATISTICS */ 힌트를 추가해서 통계를 수집합니다.

-       DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID', CHILD NUMBER, 'FORMAT’) 이용하여 실행 계획 및 단계별 실행 통계 출력할 수 있습니다.

*FORMAT : [LOSTATS | MEMSTATS | ALLSTATS] LAST

 

1)    ALTER SESSION SET STATISTICS_LEVEL = ALL; 방식으로 통계 수집 및 실행계획 출력 예시

ALTER SESSION SET STATISTICS_LEVEL = ALL; SELECT * FROM departments WHERE department_id=10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', '', 'ALLSTATS LAST'));

 

위와 같이 현재 세션동안 통계를 수집하고 쿼리를 수행하면 아래와 같이 실행계획이 출력됩니다.

 

2)    /*+ GATHER_PLAN_STATISTICS */ 힌트를 통한 통계 수집 및 실행계획 출력 예시

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM emp WHERE deptno=10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', '', 'ALLSTATS LAST'));

 

SQL 쿼리문에 힌트를 위와 같이 추가함으로써 실행계획을 아래처럼 출력할 수 있습니다.

 

 

▷ 통계 수집 중지

통계 수집을 계속 하게 되면 명령어 수행시 느려질 수 있어 통계 수집을 그만 두고 싶으면 아래 명령어 수행 필요합니다.

통계 수집 수준을 기본값으로 변경하는 것입니다.

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

 

 

▷ DBMS_XPLAN 실행계획 컬럼

Column	Description
Id	각 단계 ID (Id 앞 * : Predicate Information 정보)
Operation	실행되는 job
Name	단계에서 액세스하는 테이블, 인덱스
Starts	각 단계를 반복 실행한 횟수 (=호출 수)
E-Rows	각 단계에서 리턴하는 예상 건수
A-Rows	각 단계에서 리턴하는 실제 건수
Buffers	각 단계별 읽은 총 블록 수 (읽어낸 logical I/O)
Reads	각 단계별 디스크에서 읽은 블록 수
Write	각 단계별 디스크에 기록한 블록 수
OMem	Optimal 일 경우 예상되는 PGA 사용량 (optimal : 메모리에서 모두 처리)
1Mem	One-pass시 예상되는 PGA 사용량 (one-pass : operation 처리시 PGA가 부족해서 디스크 한번 경유)
Used-mem	실제 사용된 PGA 메모리 양

 

 

▷ 실행계획 읽는 방법

위에서 아래로 실행계획을 확인하며 제일 먼저 읽을 단계를 확인합니다.

단계가 같은 동일 level(들여쓰기 위치가 동일)끼리는 먼저 나오는 순서대로 수행합니다.

읽으려는 단계보다 들여쓰기가 된 하위 단계가 존재한다면, 가장 안쪽으로 들여쓰기 된 단계를 시작으로 하여 한 단계씩 상위 단계로 읽어 나갑니다.

 

예시1)

아래 예시로 실행계획 읽는 순서를 확인해 보면 제일 먼저 읽어야할 단계는 2단계로 그 후 상위 단계인 1단계를 읽고 0단계를 읽는 2 → 1 → 0 단계 순서로 진행되게 됩니다.

 

예시2)

아래 예시는 제일 먼저 읽어야 할 단계는 3단계로 먼저 읽게 됩니다.

그 후 같은 level에 있는 4단계, 상위 레벨 순서로 읽게 되는 3 → 4 → 2 → 1 → 0 순서로 읽게 됩니다.