DBMS_HPROF

• 세션 레벨에서 계층형 프로파일러 제공
  • 호출이 실행된 총 횟수
  • 호출을 처리하는 데 소모한 시간
  • 서브 호출을 처리하는 데 소모한 시간
  • 호출 계층에 대한 정보

• 대상 : 사용자 실행 가능 모든 PL/SQL 코드 (wrapped PL/SQL 은 최상위 레벨 정보만 수집됨)
• 출력 : 트레이스 파일 → 테이블 적재 or PLSHPROF 유틸리티 적용

출력 테이블 설치하기

• DBMS_HPROF 는 사용자 권한 수행
  • dbmshptab.sql 수행 후 SYNONYM & GRANT 처리
  • 사용자 계정에 테이블 생성

프로파일링 데이터 수집

• dbms_hprof.start_profiling : 프로파일러 활성화

• dbms_hprof.stop_profiling : 프로파일링 비활성화
• dbms_hprof.analyze : 출력테이블 저장

BEGIN
  dbms_hprof.start_profiling(location => 'PLSHPROF_DIR',
                             filename => 'dbms_hprof.trc');
END;
/

SET TIMING ON
DECLARE
  l_count INTEGER;
BEGIN
  perfect_triangles(1000);
  SELECT count(*) INTO l_count
  FROM all_objects;
END;
/

BEGIN
  dbms_hprof.stop_profiling;
END;
/

SELECT dbms_hprof.analyze(location => 'PLSHPROF_DIR',
                          filename => 'dbms_hprof.trc') AS runid
FROM dual;

프로파일링 데이터를 수동으로 리포팅하기

• 네임스페이스 별로 나눔

SELECT sum(function_elapsed_time)/1000 AS total_ms,
       100*ratio_to_report(sum(function_elapsed_time)) over () AS total_percent,
       sum(calls) AS calls,
       100*ratio_to_report(sum(calls)) over () AS calls_percent,
       namespace AS namespace_name
FROM dbmshp_function_info
WHERE runid = &runid
GROUP BY namespace
ORDER BY total_ms DESC;

TOTAL [ms]   TOT%  CALLS   CAL% NAMESPACE_NAME
---------- ------ ------- ----- --------------
      565	54.9%	  89    5.6 SQL
      464	45.1%   1494   94.4 PLSQL

• 모듈 별로 나눔 (p.128)

SELECT sum(function_elapsed_time)/1000 AS total_ms,
       100*ratio_to_report(sum(function_elapsed_time)) over () AS total_percent,
       sum(calls) AS calls,
       100*ratio_to_report(sum(calls)) over () AS calls_percent,
       namespace,
       nvl(nullif(owner || '.' || module, '.'), function) AS module_name,
       type
FROM dbmshp_function_info
WHERE runid = &runid
GROUP BY namespace, nvl(nullif(owner || '.' || module, '.'), function), type
ORDER BY total_ms DESC;

• 계층 구조에 따라 표시 (p.129)

SELECT lpad(' ', (level-1) * 2) || nullif(c.owner || '.', '.') ||
       CASE WHEN c.module = c.function THEN c.function ELSE nullif(c.module || '.', '.') || c.function END AS function_name,
       pc.subtree_elapsed_time/1000 AS total_ms,
       pc.function_elapsed_time/1000 AS function_ms,
       pc.calls AS calls
FROM dbmshp_parent_child_info pc,
     dbmshp_function_info p,
     dbmshp_function_info c
START WITH pc.runid = &runid
AND p.runid = pc.runid
AND c.runid = pc.runid
AND pc.childsymid = c.symbolid
AND pc.parentsymid = p.symbolid
AND p.symbolid = 1
CONNECT BY pc.runid = prior pc.runid
AND p.runid = pc.runid
AND c.runid = pc.runid
AND pc.childsymid = c.symbolid
AND pc.parentsymid = p.symbolid
AND prior pc.childsymid = pc.parentsymid
ORDER SIBLINGS BY total_ms DESC;

PLSHPROF 사용하기

$ plshprof -output dbms_hprof dbms_hprof.trc
-- 두개 트레이스 파일 지정 시 둘 사이 값의 차이(Delta) 표시

• 결과물(dbms_hprof.html)
  • 8개의 서로 다른 기준에 따라 정렬된 함수별 경과시간 데이터
  • 3개의 서로 다룬 기준에 따라 정렬된 모듈별 경과시간 데이터
  • 3개의 서로 다른 기준에 따라 정렬된 네임스페이스별 경과시간 데이터
  • 부모와 자식의 경과시간 데이터

GUI 사용하기

• SQL Developer
• Dell Toad