Using DBMS_PROFILER to check PL/SQL performance on Oracle Database

After installing DBMS_PROFILER you can use the following to gather the statistics about you code.

declare
  l_id number;
begin
  dbms_profiler.start_profiler('my code run on ' || to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'), run_number => l_id);

  call_your_package.procedure;

  dbms_profiler.stop_profiler;
  dbms_output.put_line(l_id);
end;
/

At this point the statistics were saved to the following tables: plsql_profiler_data, plsql_profiler_runs, plsql_profiler_units.

No you can use the below query to get detailed information:

SELECT ds.line, p.total_occur, p.total_time Msec, ds.text
FROM dba_source ds, (
  SELECT ppu.unit_owner, ppu.unit_name, ppu.unit_type, ppd.line#, ppd.total_occur,
         ppd.total_time/1000000 total_time
  FROM plsql_profiler_data ppd, plsql_profiler_units ppu
  WHERE ppu.runid = 7
  AND ppu.runid = ppd.runid
  AND ppu.unit_number = ppd.unit_number) p
WHERE ds.owner = p.unit_owner(+)
AND ds.name = p.unit_name(+)
AND ds.type = p.unit_type(+)
AND ds.line = p.line#(+)
AND ds.name = 'PACKAGE_NAME'
AND ds.owner = 'PACKAGE_OWNER'
AND p.total_time > 0
ORDER BY p.total_time desc;