이펙티브 오라클 (2008년)
프로파일러를 사용하고자 하는 이유
- 테스트 케이스가 코드 전체를 포괄하는지을 보장하기 위한 코드 테스트
- "낮게 걸린 과일", 즉 죽음으로써 이득을 줄 코드를 찾음으로서 수행한 알고리즘 튜닝
테스트 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
- 프로파일러가 갖고 있는 장점 중 하나는 데이터가 데이터베이스 테이블의에 저장된다.
참조페이지
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3532
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.