CODEWITHSUNDEEP
plsqloracle10gplsqldeveloper
M.Youssef
M.Youssef

Reputation: 148

Track execution time for each procedure and function in package

I have a package in oracle10g with multi procedures and functions.

Is there any way to track execution time for each procedure and function in the package without using dbms_utility.get_time in each one of them

Upvotes: 0

Views: 866

Answers (1)

Kevin Seymour
Kevin Seymour

Reputation: 807

You can use the Oracle PL/SQL profiler to do this.

CREATE OR REPLACE PROCEDURE proc_a IS
BEGIN
  dbms_lock.sleep(seconds => 3);
END proc_a;
/

Start and stop the profiler around your code.

DECLARE
  v_run_number BINARY_INTEGER;
BEGIN
  dbms_profiler.start_profiler(run_number => v_run_number);
  proc_a;
  dbms_profiler.stop_profiler;
END;
/
-- 3 seconds

You will get data into the plsql_profiler_% tables. A run is a single call of the profiler, units are the various procedures, function, packages and data is the run time, call occurrences, etc of each line of each unit.

SELECT *
  FROM plsql_profiler_runs r
 ORDER BY r.run_date DESC,
          r.runid    DESC;
-- runid = 3006
-- plsql_profiler_data
-- plsql_profiler_units

We use Allaround Automations PL/SQL Developer (great tool by the way), but I believe Oracle SQL Developer and Quest Toad also support viewing this data (and perhaps even handing the start and stop of the profiler for you: I know the tool we use does).

For a simple SQL based way to see the data I would recommend the following Metalink article. It provides a script you run and provide your run ID to and will generate you a nice HTML file with a report of your program(s).

Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)

enter image description here

Upvotes: 1

Related Questions