PL/SQL 계층적 프로파일러 (Hierarchical Profiler) 사용

PL/SQL 계층적 프로파일러

PL/SQL 계층적 프로파일러란 무엇인가?

PL/SQL 계층적 프로파일러는 PL/SQL 코드의 성능을 분석하고 튜닝하는 데 사용하는 강력한 도구입니다. 프로파일러는 PL/SQL 프로그램의 각 서브프로그램이 실행되는 동안 소비하는 시간과 호출 횟수와 같은 중요한 정보를 수집합니다. 이러한 정보를 통해 개발자는 성능 병목 현상을 식별하고 코드의 효율성을 개선할 수 있습니다.

Oracle Database 11g Release 2 (11.2)부터 도입된 DBMS_HPROF 패키지를 대체하며, 더욱 상세하고 사용하기 쉬운 인터페이스를 제공합니다. 특히 SQL 문장 및 Java 코드에 대한 정보를 제공하지 않았던 이전 버전과 달리, 계층적 프로파일러는 PL/SQL 유닛 내에서 실행되는 모든 SQL 문장과 Java 코드까지 추적합니다.

왜 계층적 프로파일러를 사용해야 하는가?

계층적 프로파일러를 사용하면 다음과 같은 이점을 얻을 수 있습니다.

  • 성능 병목 현상 식별: 어떤 서브프로그램이 가장 많은 시간을 소비하는지 정확히 파악합니다.
  • 코드 최적화: 불필요한 연산이나 비효율적인 알고리즘을 개선합니다.
  • SQL 튜닝: PL/SQL 코드 내의 느린 SQL 문장을 찾아내고 쿼리 성능을 향상시킵니다.
  • 전반적인 애플리케이션 성능 개선: 전체 시스템 성능을 향상시키기 위해 가장 중요한 부분을 먼저 튜닝합니다.

계층적 프로파일러 사용 방법

계층적 프로파일러를 사용하려면 다음 단계를 따릅니다.

  1. 프로파일링 테이블 생성: 프로파일러가 데이터를 저장할 테이블을 만듭니다.
  2. 프로파일링 시작: 프로파일링을 시작하여 데이터 수집을 활성화합니다.
  3. PL/SQL 코드 실행: 성능을 분석할 PL/SQL 코드를 실행합니다.
  4. 프로파일링 중지: 데이터 수집을 중단합니다.
  5. 프로파일링 데이터 분석: 수집된 데이터를 분석하여 성능 문제를 식별합니다.

단계별 가이드 및 예제

1단계: 프로파일링 테이블 생성

프로파일링 데이터를 저장할 테이블을 생성합니다. 다음 SQL 스크립트를 실행하여 필요한 테이블을 만듭니다.

<code>-- Create profiling tables
BEGIN
  DBMS_HPROF.CREATE_PROFILER_TABLES(
    TABLE_OWNER         => USER,
    TABLE_PREFIX        => 'HPROF',
    PROFILE_TABLE       => 'HPROF_DATA',
    FUNCTION_TABLE      => 'HPROF_FUNCTION',
    PARENT_CHILD_TABLE  => 'HPROF_PARENT_CHILD',
    SQL_TABLE             => 'HPROF_SQL',
    TRACE_TABLE           => 'HPROF_TRACE',
    EXTERNAL_TABLE      => 'HPROF_EXTENDED'
  );
END;
/</code>

이 코드는 현재 사용자의 스키마에 HPROF_DATA, HPROF_FUNCTION, HPROF_PARENT_CHILD, HPROF_SQL, HPROF_TRACE, HPROF_EXTENDED 테이블을 생성합니다. 이 테이블들은 프로파일링 과정에서 생성되는 데이터를 저장하는 데 사용됩니다.

2단계: 프로파일링 시작

프로파일링을 시작하려면 DBMS_HPROF.START_PROFILING 프로시저를 호출합니다.

<code>EXEC DBMS_HPROF.START_PROFILING(filename => 'my_profile');</code>

filename 매개변수는 프로파일링 데이터가 저장될 파일 이름을 지정합니다. 파일은 데이터베이스 서버의 UTL_FILE_DIR 디렉토리에 생성됩니다. 적절한 권한이 설정되어 있는지 확인하세요.

3단계: PL/SQL 코드 실행

이제 성능을 분석할 PL/SQL 코드를 실행합니다. 예를 들어, 다음과 같은 간단한 프로시저를 실행할 수 있습니다.

<code>CREATE OR REPLACE PROCEDURE my_procedure IS
  v_num NUMBER;
BEGIN
  FOR i IN 1..10000 LOOP
    v_num := i * i;
  END LOOP;
END;
/</code>
<code>EXEC my_procedure;</code>

4단계: 프로파일링 중지

프로파일링이 완료되면 DBMS_HPROF.STOP_PROFILING 프로시저를 호출하여 데이터 수집을 중단합니다.

<code>EXEC DBMS_HPROF.STOP_PROFILING;</code>

5단계: 프로파일링 데이터 분석

프로파일링 데이터는 생성된 테이블에 저장됩니다. 데이터를 분석하려면 SQL 쿼리를 사용하여 테이블에서 원하는 정보를 추출합니다.

가장 많이 사용되는 쿼리 중 일부는 다음과 같습니다.

  • 함수별 시간: 각 함수에서 소비한 시간 보기
<code>SELECT function_name, cumulative_time
FROM HPROF_FUNCTION
ORDER BY cumulative_time DESC;
</code>
  • SQL 문장별 시간: SQL 문장에서 소비한 시간 보기
<code>SELECT sql_text, cumulative_time
FROM HPROF_SQL
ORDER BY cumulative_time DESC;
</code>
  • 함수 호출 계층 구조: 함수 호출 관계와 시간 분포 보기
<code>SELECT child_function, parent_function, calls
FROM HPROF_PARENT_CHILD
ORDER BY calls DESC;
</code>

예제: 실제 PL/SQL 코드 프로파일링

다음은 보다 복잡한 PL/SQL 블록을 프로파일링하고 결과를 분석하는 예제입니다.

<code>CREATE OR REPLACE PACKAGE emp_mgmt AS
  PROCEDURE add_employee(p_empno NUMBER, p_ename VARCHAR2, p_deptno NUMBER);
  FUNCTION get_employee_count(p_deptno NUMBER) RETURN NUMBER;
END emp_mgmt;
/

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
  PROCEDURE add_employee(p_empno NUMBER, p_ename VARCHAR2, p_deptno NUMBER) IS
  BEGIN
    INSERT INTO employees (empno, ename, deptno) VALUES (p_empno, p_ename, p_deptno);
    COMMIT;
  END add_employee;

  FUNCTION get_employee_count(p_deptno NUMBER) RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees WHERE deptno = p_deptno;
    RETURN v_count;
  END get_employee_count;
END emp_mgmt;
/</code>
<code>-- 데이터 테이블 생성 (이미 존재하지 않는 경우)
CREATE TABLE employees (
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(50),
    deptno NUMBER
);
</code>

프로파일링 시작:

<code>EXEC DBMS_HPROF.START_PROFILING(filename => 'emp_mgmt_profile');</code>

PL/SQL 코드 실행:

<code>BEGIN
  emp_mgmt.add_employee(1001, 'John Smith', 10);
  emp_mgmt.add_employee(1002, 'Alice Johnson', 20);
  DBMS_OUTPUT.PUT_LINE('Department 10 count: ' || emp_mgmt.get_employee_count(10));
  DBMS_OUTPUT.PUT_LINE('Department 20 count: ' || emp_mgmt.get_employee_count(20));
END;
/</code>

프로파일링 중지:

<code>EXEC DBMS_HPROF.STOP_PROFILING;</code>

데이터 분석:

<code>SELECT function_name, cumulative_time
FROM HPROF_FUNCTION
ORDER BY cumulative_time DESC;
</code>
<code>SELECT sql_text, cumulative_time
FROM HPROF_SQL
ORDER BY cumulative_time DESC;
</code>

고급 팁 및 고려 사항

  • 프로파일링 범위 지정: 전체 애플리케이션보다는 특정 PL/SQL 유닛에 프로파일링을 집중하여 결과를 더 쉽게 관리할 수 있습니다.
  • 통계 유지 관리: 테이블에 대한 통계가 최신 상태인지 확인하여 최적화 프로그램이 가장 효율적인 실행 계획을 선택하도록 돕습니다.
  • DBMS_OUTPUT 사용 줄이기: DBMS_OUTPUT에 대한 과도한 호출은 프로파일링 데이터에 영향을 미칠 수 있습니다. 필요한 경우에만 사용하세요.
  • 디버깅: 문제 해결에 도움이 되지만 프로파일링 데이터가 크게 왜곡될 수 있으므로 프로파일링하는 동안 디버깅을 사용하지 마세요.

결론

PL/SQL 계층적 프로파일러는 PL/SQL 코드의 성능을 분석하고 개선하는 데 필수적인 도구입니다. 이 가이드에서 제공된 단계와 예제를 통해 프로파일러를 효과적으로 사용하여 애플리케이션의 성능을 극대화할 수 있습니다.

위로 스크롤