by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

DBMS_PROFILER




프로파일러를 사용하고자 하는 이유

  • 테스트 케이스가 코드 전체를 포괄하는지을 보장하기 위한 코드 테스트
  • "낮게 걸린 과일", 즉 죽음으로써 이득을 줄 코드를 찾음으로서 수행한 알고리즘 튜닝


테스트 CASE

  • "큰 루프내에서 1,000레코드 (백만 또는 십억)마다 커밋을 수행하고 싶은 경우 mod()를 사용한 후에 commit을 수행하는 방법과 counter := counter+1, if count... then commit;
  • counter := 0와 같은 카운터를 설정하는 방법 중 어는 것이 효율적일까? 예를 들면,
1)......
 START LOOP
  ....
  cnt := cnt +1
  IF ( cnt%1000 ) = 0 THEN <= using mode() function
    commit;
  END IF;
  ...
 END LOOP;
 ....

2)......
 START LOOP
  ....
  cnt := cnt +1
  IF cnt = 1000 THEN <=  no mode() function
    commit;
    cnt := 0;
  END IF;
  ...
 END LOOP;
 ....

1) 과 2)중 어느 것이 효율적일까?


DBMS_PROFILER 설지작업

sys@PROD9I> @?\rdbms\admin\profload.sql
패키지가 생성되었습니다.

권한이 부여되었습니다.

동의어가 생성되었습니다.

라이브러리가 생성되었습니다.

패키지 본문이 생성되었습니다.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL 처리가 정상적으로 완료되었습니다.

sys@PROD9I> CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
사용자가 생성되었습니다.

sys@PROD9I> GRANT connect TO profiler;
권한이 부여되었습니다.

sys@PROD9I> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
동의어가 생성되었습니다.

sys@PROD9I> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
동의어가 생성되었습니다.

sys@PROD9I> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
동의어가 생성되었습니다.

sys@PROD9I> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;
동의어가 생성되었습니다.

sys@PROD9I> CONNECT profiler/profiler
연결되었습니다.

sys@PROD9I> @?\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
           *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다


drop table plsql_profiler_units cascade constraints
           *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다


drop table plsql_profiler_runs cascade constraints
           *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다


drop sequence plsql_profiler_runnumber
              *
1행에 오류:
ORA-02289: 시퀀스가 존재하지 않습니다


테이블이 생성되었습니다.


주석이 생성되었습니다.


테이블이 생성되었습니다.


주석이 생성되었습니다.


테이블이 생성되었습니다.


주석이 생성되었습니다.


주문번호가 생성되었습니다.


sys@PROD9I> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
권한이 부여되었습니다.

sys@PROD9I> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
권한이 부여되었습니다.

sys@PROD9I> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
권한이 부여되었습니다.

sys@PROD9I> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;
권한이 부여되었습니다.


DBMS_PROFILER를 통한 테스트 수행

sys@PROD9I> @connect tkyte/tkyte
tkyte@PROD9I> create or replace procedure do_mod
  2  as
  3      cnt number := 0;
  4  begin
  5      dbms_profiler.start_profiler( 'mod' );
  6      for i in 1 .. 500000
  7      loop
  8          cnt := cnt + 1;
  9          if ( mod(cnt,1000) = 0 )
 10          then
 11              commit;
 12          end if;
 13      end loop;
 14      dbms_profiler.stop_profiler;
 15  end;
 16  /
프로시저가 생성되었습니다.

tkyte@PROD9I> create or replace procedure no_mod
  2  as
  3      cnt number := 0;
  4  begin
  5      dbms_profiler.start_profiler( 'no mod' );
  6      for i in 1 .. 500000
  7      loop
  8          cnt := cnt + 1;
  9          if ( cnt = 1000 )
 10          then
 11              commit;
 12              cnt := 0;
 13          end if;
 14      end loop;
 15      dbms_profiler.stop_profiler;
 16  end;
 17  /
프로시저가 생성되었습니다.

tkyte@PROD9I> exec do_mod
PL/SQL 처리가 정상적으로 완료되었습니다.

tkyte@PROD9I> exec no_mod
PL/SQL 처리가 정상적으로 완료되었습니다.


DBMS_PROFILER 수행보고서

tkyte@PROD9I> COLUMN runid FORMAT 99999
tkyte@PROD9I> COLUMN run_comment FORMAT A50
tkyte@PROD9I> SELECT runid,
  2         run_date,
  3         run_comment,
  4         run_total_time
  5  FROM   plsql_profiler_runs
  6  ORDER BY runid;

 RUNID RUN_DATE RUN_COMMENT                                               RUN_TOTAL_TIME
------ -------- -------------------------------------------------- ---------------------
     1 08/08/23 mod                                                         626331465311
     2 08/08/23 no mod                                                      314398191948
  • 결과를 보면 MOD 함수가 오래 걸린것을 알수 있다.


tkyte@PROD9I> SELECT u.runid,
  2         u.unit_number,
  3         u.unit_type,
  4         u.unit_owner,
  5         u.unit_name,
  6         d.line#,
  7         d.total_occur,
  8         d.total_time,
  9         d.min_time,
 10         d.max_time
 11  FROM   plsql_profiler_units u
 12         JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
 13  ORDER BY u.unit_number, d.line#;

 RUNID UNIT_NUMBER UNIT_TYPE            UNIT_OWNER           UNIT_NAME                             LINE# TOTAL_OCCUR         TOTAL_TIME   MIN_TIME   MAX_TIME
------ ----------- -------------------- -------------------- -------------------------------- ---------- ----------- ------------------ ---------- ----------
     1           1 PROCEDURE            TKYTE                DO_MOD                                    3           0                  0          0          0
     2           1 PROCEDURE            TKYTE                NO_MOD                                    3           0                  0          0          0
     1           1 PROCEDURE            TKYTE                DO_MOD                                    5           0                  0          0          0
     2           1 PROCEDURE            TKYTE                NO_MOD                                    5           0                  0          0          0
     1           1 PROCEDURE            TKYTE                DO_MOD                                    6      500001        68035243306     110628  217855341
     2           1 PROCEDURE            TKYTE                NO_MOD                                    6      500001        71007624432     119847   85045725
     1           1 PROCEDURE            TKYTE                DO_MOD                                    8      500000        73947032094     116774  134149451
     2           1 PROCEDURE            TKYTE                NO_MOD                                    8      500000        70871274701     110628   12353525
     1           1 PROCEDURE            TKYTE                DO_MOD                                    9      500000       322508398134     540850  144539319
     2           1 PROCEDURE            TKYTE                NO_MOD                                    9      500000        70848137961     110628   53107867
     1           1 PROCEDURE            TKYTE                DO_MOD                                   11         500         1248603383    1330616   39518989
     2           1 PROCEDURE            TKYTE                NO_MOD                                   11         500          936535509    1192330   22067329
     2           1 PROCEDURE            TKYTE                NO_MOD                                   12         500           41731560      55314     565434
     1           1 PROCEDURE            TKYTE                DO_MOD                                   14           1            1327543    1327543    1327543
     2           1 PROCEDURE            TKYTE                NO_MOD                                   15           1            1345981    1345981    1345981


tkyte@PROD9I> SELECT line || ' : ' || text
  2  FROM   all_source
  3  WHERE  owner = 'TKYTE'
  4  AND    type  = 'PROCEDURE'
  5  AND    name  = 'DO_MOD';

LINE||':'||TEXT
-------------------------------------------------------------------------

1 : procedure do_mod
2 : as
3 :     cnt number := 0;
4 : begin
5 :     dbms_profiler.start_profiler( 'mod' );
6 :     for i in 1 .. 500000
7 :     loop
8 :         cnt := cnt + 1;
9 :         if ( mod(cnt,1000) = 0 )
10 :         then
11 :             commit;
12 :         end if;
13 :     end loop;
14 :     dbms_profiler.stop_profiler;
15 : end;

15 개의 행이 선택되었습니다.


tkyte@PROD9I> SELECT line || ' : ' || text
  2  FROM   all_source
  3  WHERE  owner = 'TKYTE'
  4  AND    type  = 'PROCEDURE'
  5  AND    name  = 'NO_MOD';

LINE||':'||TEXT
---------------------------------------------------------------------------

1 : procedure no_mod
2 : as
3 :     cnt number := 0;
4 : begin
5 :     dbms_profiler.start_profiler( 'no mod' );
6 :     for i in 1 .. 500000
7 :     loop
8 :         cnt := cnt + 1;
9 :         if ( cnt = 1000 )
10 :         then
11 :             commit;
12 :             cnt := 0;
13 :         end if;
14 :     end loop;
15 :     dbms_profiler.stop_profiler;
16 : end;

16 개의 행이 선택되었습니다.
  • 더 자세한 내용은 위와 같다. 9번 라인에서 TOTAL_TIME에 대한 비용차이가 크다.


프로파일러 리소스

  • 다음음 DBMS_PROFILER의 설치 및 이용과 DBMS_PROFILER 출력의 해석에 관한 정보를 얻는데 이용할 수 있는 리소스 중 일부이다.
  • "Expert One on One Oracle"부록
  • "Oracle Suppplied Packages and Types Guide"
  • www.google.com
  • 프로파일러가 갖고 있는 장점 중 하나는 데이터가 데이터베이스 테이블의에 저장된다.


참조페이지

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.