DBMS_PROFILER package provides developer a way to profile PL/SQL program unit and determine the performance bottlenecks. DBMS_PROFILER allows database developers to analyze the run time behavior of PL/SQL code and helps you in identifying performance issues by providing you the "number of execution" and "time taken" by each line in the PL/SQL block. DBMS_PROFILER generates following useful profiler statistics: - Total elapsed time in execution of whole code. - Total number of times each line of code was executed. - Total time spent on execution of each line of code. - Minimum/Maximum time spent on each line of code in single execution. - The Code executed for a given scenario and conditions. DBMS_PROFILER package provides us following 3 important procedures: - DBMS_PROFILER.START_PROFILER: start the monitoring process - DBMS_PROFILER.STOP_PROFILER: stop the monitoring process - DBMS_PROFILER.FLUSH_DATA: save profiler stats in tables and flush the memory. DBMS_PROFILE -->-- Supplied Packages In Oracle -->-- Sub Programs are below START_PROFILE STOP_PROFILE FLUS_DATA Note: if you are using DBMS_PROFILER for the very first time, you may need to install it. The installation scripts are located at "$ORACLE_HOME/rdbms/admin" How to install DBMS_PROFILER package: Installation of DBMS_PROFILER package is just a 2 step process. 1. execute "@$ORACLE_HOME/rdbms/admin/profload.sql" as sys user 2. execute "@$ORACLE_HOME/rdbms/admin/proftab.sql" by the user on which you want to use DBMS_PROFILER. Step 2 will create following tables where profiling data will get stored, from which we can easy extract the data to determine the performance bottlenecks. Important Tables - PLSQL_PROFILER_RUNS RUNID - PLSQL_PROFILER_UNITS - PLSQL_PROFILER_DATA User_source line | Text Step: 1 - profload.sql C:\>sqlplus sys/sys as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 13:29:55 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - Production SQL> @E:\oracle\app\nimish.garg\product\11.2.0\dbhome_1\RDBMS\ADMIN\profload.sql Package created. Grant succeeded. Synonym created. Library created. Package body created. Testing for correct installation SYS.DBMS_PROFILER successfully loaded. PL/SQL procedure successfully completed. Step: 2 - proftab.sql C:\>sqlplus scott/tiger SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 13:33:18 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - Production SQL> @E:\oracle\app\nimish.garg\product\11.2.0\dbhome_1\RDBMS\ADMIN\proftab.sql drop table plsql_profiler_data cascade constraints * ERROR at line 1: ORA-00942: table or view does not exist drop table plsql_profiler_units cascade constraints * ERROR at line 1: ORA-00942: table or view does not exist drop table plsql_profiler_runs cascade constraints * ERROR at line 1: ORA-00942: table or view does not exist drop sequence plsql_profiler_runnumber * ERROR at line 1: ORA-02289: sequence does not exist Table created. Comment created. Table created. Comment created. Table created. Comment created. Sequence created. ------------------------------------------------------------------------------------------------- To analyze the PL/SQL code and identifying performance issues using DBMS_PROFILER, we need to first start the profiler using DBMS_PROFILER.START_PROFILER, then we can execute the our pl/sql procedure we want monitored and at last we need to simply call DBMS_PROFILER.STOP_PROFILER to stop the profiler. We do not need to call DBMS_PROFILER.FLUSH_DATA explicitly as DBMS_PROFILER.STOP_PROFILE flush profiler data automatically. To analyze PL/SQL and identify bottlenecks, we can break the use of DBMS_PROFILER in following steps: 1. Collect Profiler data for PL/SQL Block 2. Identify RUNID using PLSQL_PROFILER_RUNS 3. Identify UNIT_NUMBER using PLSQL_PROFILER_UNITS 4. Identify PL/SQL Line Number which may have performace issue by PLSQL_PROFILER_DATA 5. Get the Line of Code by USER_SOURCE We have create a procedure "SP_CREATE_CSV" hich creates CSV file, using following example: -- infycle_adv_create_csv_sp.sql CREATE OR REPLACE DIRECTORY INFY_DIR AS 'D:\Oracle\Reports'; GRANT READ, WRITE ON INFY_DIR TO ENH_DB; CREATE OR REPLACE PROCEDURE infycle_adv_create_csv_sp AS f utl_file.file_type; CURSOR data_c IS SELECT e.employee_id, e.first_name, e.salary, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id ORDER BY employee_id; BEGIN f := utl_file.fopen('INFY_DIR', 'EMP_DEPT.CSV', 'w', 32767); FOR i IN data_c LOOP utl_file.put(f, i.employee_id); utl_file.put(f, ',' || i.first_name); utl_file.put(f, ',' ||i.salary); utl_file.put(f, ',' || i.department_id); utl_file.put(f, ',' || i.dname); utl_file.new_line(f); END LOOP; utl_file.fclose(f); EXCEPTION WHEN OTHERS THEN NULL; utl_file.fclose(f); END infycle_adv_create_csv_sp; / Identify the line which is having long running time C:\>sqlplus ENH_DB/ENH_DB SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 14:21:35 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - Production SQL> exec dbms_profiler.start_profiler('infycle_adv_create_csv_sp'); PL/SQL procedure successfully completed. SQL> exec infycle_adv_create_csv_sp; PL/SQL procedure successfully completed. SQL> exec dbms_profiler.stop_profiler; PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Step 2. Identify RUNID using PLSQL_PROFILER_RUNS SQL> select runid, run_owner, run_date, run_total_time 2 from plsql_profiler_runs 3 where run_comment='Test SP_CREATE_CSV'; RUNID RUN_OWNER RUN_DATE RUN_TOTAL_TIME ---------- -------------------------------- --------- -------------- 1 ENH_DB 29-APR-21 2.3313E+10 Step 3. Identify UNIT_NUMBER using PLSQL_PROFILER_UNITS SQL> select unit_number, unit_timestamp, total_time 2 from plsql_profiler_units 3 where runid=1 and unit_name='INFYCLE_ADV_CREATE_CSV_SP'; UNIT_NUMBER UNIT_TIME TOTAL_TIME ----------- --------- ---------- 3 26-FEB-13 0 Step 4. Identify problematic PL/SQL Line Number by PLSQL_PROFILER_DATA SQL> select line#, total_occur, total_time, min_time, max_time, 2 round(total_time/total_occur,0) avg_time 3 from plsql_profiler_data 4 where runid=1 and unit_number=3 5 order by avg_time desc; SQL> select line#, total_occur, total_time, min_time, max_time, 2 round(total_time/total_occur,0) avg_time 3 from plsql_profiler_data 4 where runid=1 and unit_number=3 5 order by avg_time desc; LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME AVG_TIME ---------- ----------- ---------- ---------- ---------- ---------- 4 1 113561 372 112488 113561 19 3 206807 96 193721 68936 6 1 19143 19143 19143 19143 17 1 11097 11097 11097 11097 1 1 10643 10643 10643 10643 9 14 14857 640 5963 1061 11 14 11386 673 1891 813 12 14 8631 553 673 617 10 14 6616 412 995 473 13 14 6024 369 496 430 16 1 336 336 336 336 14 14 4273 75 396 305 Now we have all the details required to get bottlenecks of infycle_adv_create_csv_sp. Now we know that line number 4,19 of infycle_adv_create_csv_spare the top two time consuming statements on an average (basis of single execution). We just need to check what code of lines are they. SQL> column text format a60; SQL> select line, text from user_source 2 where name='SP_CREATE_CSV' 3 and line in (4,19); LINE TEXT ---------- --------------------------------------------------------- 4 CURSOR data_c IS SELECT e.employee_id, 19 FOR i IN data_c --We can also Use Join SELECT u.runid , u.unit_number , u.unit_type , u.unit_owner , u.unit_name , d.line# , d.total_occur , d.total_time , d.min_time , d.max_time FROM plsql_profiler_units u JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number WHERE u.runid = 1 ORDER BY u.unit_number, d.line#;