EXPLAIN PLAN 구문 이해

EXPLAIN PLAN 구문이란 무엇인가?

Oracle 데이터베이스에서 EXPLAIN PLAN 구문은 SQL 쿼리가 어떻게 실행될지 미리 보여주는 강력한 도구입니다. 쿼리 옵티마이저가 선택한 실행 계획을 분석하여 성능 병목 지점을 파악하고, 인덱스 추가, 쿼리 재작성 등의 최적화 방법을 결정하는 데 도움을 줍니다.

EXPLAIN PLAN 사용 방법

EXPLAIN PLAN은 쿼리를 실제로 실행하지 않고 실행 계획만 생성합니다. 다음은 기본적인 사용법입니다.

  1. PLAN_TABLE 생성: 실행 계획을 저장할 테이블을 생성합니다. Oracle에서 제공하는 UTLXP12.SQL 스크립트를 사용하여 PLAN_TABLE을 생성할 수 있습니다.
  2. EXPLAIN PLAN 실행: EXPLAIN PLAN FOR 구문을 사용하여 분석할 SQL 쿼리를 지정합니다.
  3. 실행 계획 조회: PLAN_TABLE 테이블을 쿼리하여 생성된 실행 계획을 확인합니다.

PLAN_TABLE 생성 예시

SQL*Plus 또는 SQL Developer에서 다음 명령을 실행하여 PLAN_TABLE을 생성합니다.

  
  @?/rdbms/admin/utlxplan.sql

위 명령을 실행하면 현재 스키마에 PLAN_TABLE이라는 테이블이 생성됩니다. 만약 다른 스키마에 테이블을 생성하고 싶다면, 스크립트 실행 전에 해당 스키마로 접속해야 합니다.

EXPLAIN PLAN 실행 예시

다음은 employees 테이블에서 특정 부서의 직원 정보를 조회하는 쿼리의 실행 계획을 생성하는 예시입니다.

  
  EXPLAIN PLAN FOR
  SELECT employee_id, first_name, last_name
  FROM employees
  WHERE department_id = 50;

실행 계획 조회 예시

다음 SQL 쿼리를 사용하여 PLAN_TABLE에 저장된 실행 계획을 조회할 수 있습니다. DBMS_XPLAN.DISPLAY 함수를 사용하여 더 보기 좋게 포맷된 결과를 얻을 수 있습니다.

  
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

만약 특정 STATEMENT_ID를 사용하여 PLAN_TABLE에 저장했다면 다음과 같이 조회합니다.

  
  EXPLAIN PLAN SET STATEMENT_ID = 'emp_dept_50' FOR
  SELECT employee_id, first_name, last_name
  FROM employees
  WHERE department_id = 50;

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(STATEMENT_ID => 'emp_dept_50'));

실행 계획 결과 분석

DBMS_XPLAN.DISPLAY 함수의 출력은 여러 행으로 구성되며, 각 행은 실행 계획의 단계를 나타냅니다. 중요한 컬럼은 다음과 같습니다.

  • ID: 실행 단계의 고유 식별자
  • OPERATION: 수행되는 작업 (예: TABLE ACCESS FULL, INDEX RANGE SCAN)
  • NAME: 작업 대상 객체 (예: 테이블 또는 인덱스 이름)
  • ROWS: 예상 처리 행 수
  • BYTES: 예상 처리 바이트 수
  • COST: 단계별 예상 비용
  • TIME: 단계별 예상 시간

전체 테이블 스캔(TABLE ACCESS FULL)은 일반적으로 비효율적인 작업이며, 인덱스 사용을 고려해야 합니다. 예상 처리 행 수가 실제 행 수와 크게 다르다면, 통계 정보 업데이트가 필요할 수 있습니다.

실전 튜닝 예시

다음은 orders 테이블과 customers 테이블을 조인하는 쿼리의 튜닝 예시입니다.

  
  EXPLAIN PLAN FOR
  SELECT o.order_id, c.customer_name, o.order_date
  FROM orders o, customers c
  WHERE o.customer_id = c.customer_id
  AND o.order_date BETWEEN '20230101' AND '20230131';

실행 계획을 확인한 결과, orders 테이블과 customers 테이블 모두 전체 테이블 스캔을 수행하고 있습니다. 이는 성능 저하의 원인이 될 수 있습니다.

튜닝 방법

  1. customer_id 컬럼에 인덱스 생성:
    2.   
  CREATE INDEX idx_orders_customer_id ON orders (customer_id);
  CREATE INDEX idx_customers_customer_id ON customers (customer_id);
  2. order_date 컬럼에 인덱스 생성:
    3.   
  CREATE INDEX idx_orders_order_date ON orders (order_date);

인덱스 생성 후, 다시 EXPLAIN PLAN을 실행하여 변경된 실행 계획을 확인합니다. 인덱스 범위 스캔(INDEX RANGE SCAN)으로 변경되었다면 성능 개선 효과를 기대할 수 있습니다.

튜닝 후 실행 계획 확인

인덱스 생성 후 실행 계획은 다음과 같이 변경될 수 있습니다.

  
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

변경된 실행 계획에서 TABLE ACCESS FULL이 INDEX RANGE SCAN으로 변경되었는지 확인합니다. 또한, 각 단계별 COST 및 BYTES가 감소했는지 확인하여 성능 개선 효과를 예측할 수 있습니다.

팁 및 주의사항

  • EXPLAIN PLAN은 예상 실행 계획을 보여주므로, 실제 실행 결과와 다를 수 있습니다.
  • 통계 정보가 오래되었거나 부정확하면 옵티마이저가 잘못된 실행 계획을 선택할 수 있습니다. 주기적으로 통계 정보를 업데이트해야 합니다.
  • DBMS_XPLAN.DISPLAY 함수의 다양한 옵션을 활용하여 실행 계획을 상세하게 분석할 수 있습니다.
  • 힌트(hint)를 사용하여 옵티마이저가 특정 실행 계획을 선택하도록 강제할 수 있지만, 신중하게 사용해야 합니다.

결론

EXPLAIN PLAN 구문은 Oracle 데이터베이스 튜닝의 필수적인 도구입니다. 실행 계획 분석을 통해 쿼리 성능을 개선하고, 애플리케이션의 응답 시간을 단축할 수 있습니다. 꾸준한 분석과 튜닝을 통해 데이터베이스 성능을 최적화하십시오.

위로 스크롤