PL/SQL 성능 저하 부분 확인 하기 ( 11g, DBMS_PROFILER )
참조 : https://oracle-base.com/articles/9i/dbms_profiler
가. DBMS_PROFILE PACKAGE 설치
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | [TGDB]tggo:/home/oracle> sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 13 17:36:55 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@TGDB=> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production Elapsed: 00:00:00.00 SYS@TGDB=> !ls -al $ORACLE_HOME/rdbms/admin/profload.sql -rw-r--r--. 1 oracle oinstall 4085 Dec 18 2001 /u01/app/oracle/product/11.2.0.4/rdbms/admin/profload.sql SYS@TGDB=> @/u01/app/oracle/product/11.2.0.4/rdbms/admin/profload.sql Package created. Elapsed: 00:00:00.12 Grant succeeded. Elapsed: 00:00:00.01 Synonym created. Elapsed: 00:00:00.00 Library created. Elapsed: 00:00:00.01 Package body created. Elapsed: 00:00:00.01 Testing for correct installation SYS.DBMS_PROFILER successfully loaded. PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SYS@TGDB=> | cs |
나. PL/SQL 성능 누적 테이블 생성
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | SYS@TGDB=> !ls -al $ORACLE_HOME/rdbms/admin/proftab.sql -rw-r--r--. 1 oracle oinstall 4598 Oct 18 1999 /u01/app/oracle/product/11.2.0.4/rdbms/admin/proftab.sql SYS@TGDB=> @/u01/app/oracle/product/11.2.0.4/rdbms/admin/proftab.sql drop table plsql_profiler_data cascade constraints * ERROR at line 1: ORA-00942: table or view does not exist Elapsed: 00:00:00.09 drop table plsql_profiler_units cascade constraints * ERROR at line 1: ORA-00942: table or view does not exist Elapsed: 00:00:00.01 drop table plsql_profiler_runs cascade constraints * ERROR at line 1: ORA-00942: table or view does not exist Elapsed: 00:00:00.05 drop sequence plsql_profiler_runnumber * ERROR at line 1: ORA-02289: sequence does not exist Elapsed: 00:00:00.02 Table created. Elapsed: 00:00:00.69 Comment created. Elapsed: 00:00:00.15 Table created. Elapsed: 00:00:00.10 Comment created. Elapsed: 00:00:00.02 Table created. Elapsed: 00:00:00.19 Comment created. Elapsed: 00:00:00.03 Sequence created. Elapsed: 00:00:00.17 SYS@TGDB=> | cs |
다. 성능 수집 대상 PL/SQL 생성 및 수행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | SYS@TGDB=> create or replace function get_cnt_func 2 return number 3 is 4 i_cnt pls_integer ; 5 begin 6 select count(*) into i_cnt from scott.emp ; 7 return i_cnt ; 8 end; 9 / Function created. Elapsed: 00:00:00.11 SYS@TGDB=> SYS@TGDB=> create or replace function get_cnt_func2 2 return number 3 is 4 i_cnt number; 5 begin 6 select count(*) into i_cnt from scott.emp e1, scott.emp e2, scott.emp e3, scott.emp e4, scott.emp e5 ; 7 return i_cnt ; 8 end; 9 / Function created. Elapsed: 00:00:00.09 SYS@TGDB=> SYS@TGDB=> create or replace procedure test_dbms_profiler_proc 2 is 3 i number := 0 ; 4 i_cnt1 number ; 5 i_cnt2 number ; 6 begin 7 loop 8 i := i+1 ; 9 exit when i> 100 ; 10 i_cnt1 := get_cnt_func; 11 i_cnt2 := get_cnt_func2 ; 12 dbms_output.put_line(i_cnt1); 13 dbms_output.put_line(i_cnt2); 14 end loop; 15 end ; 16 / Procedure created. Elapsed: 00:00:00.06 SYS@TGDB=> SYS@TGDB=> SYS@TGDB=> declare 2 err number ; 3 begin 4 err := dbms_profiler.start_profiler('Profiler Test #1 '||to_char(sysdate,'dd-Mon-YYYYhh24:mi:ss')); 5 test_dbms_profiler_proc ; 6 err := dbms_profiler.stop_profiler; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:02.28 SYS@TGDB=> | cs |
라. 성능 확인 하기
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | SYS@TGDB=> col run_comment for a50 SYS@TGDB=> col runid new_value runid SYS@TGDB=> select runid, run_date, run_comment 2 from plsql_profiler_runs 3 order by runid ; RUNID RUN_DATE RUN_COMMENT ---------- --------- -------------------------------------------------- 1 13-FEB-18 Profiler Test #1 13-Feb-201817:48:32 SYS@TGDB=> set pagesize 2000 SYS@TGDB=> set lines 200 SYS@TGDB=> column unit_name format a25 SYS@TGDB=> column test format a25 SYS@TGDB=> column occured format 999999 SYS@TGDB=> column line# format 99999 SYS@TGDB=> column "TOTAL TIME(S)" format 999.99999 SYS@TGDB=> select p.unit_name, p.occured, p.tot_time "Total Time(s)", p.line# line, substr(s.text,1,20) text 2 from ( select u.unit_name, d.total_occur occured, (d.total_time/100000000) tot_time, d.line# 3 from plsql_profiler_units u, plsql_profiler_data d 4 where d.runid = u.runid 5 and d.unit_number = u.unit_number 6 and d.total_occur > 0 7 and u.runid = &runid) p, user_source s 8 where p.unit_name = s.name(+) 9 and p.line# = s.line(+) 10* order by p.unit_name, p.line# ; old 7: and u.runid = &runid) p, user_source s new 7: and u.runid = 1) p, user_source s UNIT_NAME OCCURED Total Time(s) LINE TEXT ------------------------- ------- ------------- ---------- -------------------------------------------------------------------------------- <anonymous> 2 .00031 5 <anonymous> 1 .00002 6 DBMS_OUTPUT 200 .00042 111 -- put_line or DBMS_PROFILER 1 .00014 67 DBMS_PROFILER 1 .00002 80 pragma exception_i DBMS_PROFILER 1 .00000 81 DBMS_PROFILER 1 .00000 96 -- DBMS_PROFILER 1 .00002 104 return binary_in DBMS_PROFILER 1 .00002 140 DBMS_PROFILER 1 .00000 143 -- GET_CNT_FUNC 100 .11921 6 select count(*) i GET_CNT_FUNC 100 .00043 7 return i_cnt ; GET_CNT_FUNC 100 .00059 8 end; GET_CNT_FUNC2 100 .00084 1 function get_cnt_fun GET_CNT_FUNC2 100 20.60200 6 select count(*) i <<<<<< 이 부분에서 가장 느림 확인 GET_CNT_FUNC2 100 .00046 7 return i_cnt ; GET_CNT_FUNC2 100 .00198 8 end; TEST_DBMS_PROFILER_PROC 1 .00000 3 i number := 0 ; TEST_DBMS_PROFILER_PROC 1 .00001 6 begin TEST_DBMS_PROFILER_PROC 101 .00000 7 loop TEST_DBMS_PROFILER_PROC 101 .00073 8 i := i+1 ; TEST_DBMS_PROFILER_PROC 101 .00048 9 exit when i> 100 ; TEST_DBMS_PROFILER_PROC 100 .00137 10 i_cnt1 := get_cnt TEST_DBMS_PROFILER_PROC 100 .00194 11 i_cnt2 := get_cnt TEST_DBMS_PROFILER_PROC 100 .00474 12 dbms_output.put_li TEST_DBMS_PROFILER_PROC 100 .00079 13 dbms_output.put_li TEST_DBMS_PROFILER_PROC 1 .00001 15 end ; 27 rows selected. Elapsed: 00:00:00.00 SYS@TGDB=> | cs |
마. 11g 미만에서는
성능 누적 테이블 및 프로시저 생성 후, PL/SQL 내 호출 하는 방식으로
PL/SQL 내 Bottleneck 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> CREATE TABLE LOG ( LOG_DATE DATE, MSG VARCHAR2(50)); Table created. SQL> CREATE TABLE TRANSACTION ( DATA_VALUE VARCHAR2(50)); Table created. SQL> CREATE OR REPLACE PROCEDURE LOGGER ( I_LOG IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION ; BEGIN INSERT INTO LOG ( LOG_DATE, MSG ) VALUES ( SYSDATE, I_LOG ) ; COMMIT ; END ; / Procedure created. --> 메인 트랜잭션 에서 ROLLBACK, COMMIT 에 관계없이, 로그 프로시져에서 COMMIT 에 따라, 배치 처리 실행을 확인 가능하다. | cs |