Run Profiler, Execution Plan.....on Package

if you're using the standard profiler then something like this...

BEGIN
    Dbms_Profiler.start_profiler('jknj72');  -- it's not required, but helpful to give your profile runs distinct names
    Your_package.Your_Procedure;
    dbms_profiler.stop_profiler;
END;

Then you can query the results with something like this...

SELECT   u.unit_type, u.unit_owner, u.unit_name, d.line#, d.total_occur,
         TO_CHAR(d.total_time / 1000000000, '990.999999') total_seconds,
         TO_CHAR(d.min_time / 1000000000, '990.999999') min_seconds,
         TO_CHAR(d.max_time / 1000000000, '990.999999') max_seconds,
         TO_CHAR(d.total_time / 1000000000 / d.total_occur,
                 '990.999999'
                ) avg_seconds,
         (SELECT TRIM(s.text)
            FROM dba_source s
           WHERE u.unit_owner = s.owner
             AND u.unit_type = s.TYPE
             AND u.unit_name = s.NAME
             AND d.line# = s.line) "text (as of now)",
         TO_CHAR(r.run_total_time / 1000000000,
                 '9990.999'
                ) total_profiled_time
    FROM plsql_profiler_runs r, plsql_profiler_data d, plsql_profiler_units u
   WHERE r.run_comment = 'jknj72'
     AND d.runid = r.runid
     AND u.runid = d.runid
     AND d.unit_number = u.unit_number
     AND d.total_occur != 0
ORDER BY d.total_time DESC

Select all Open in new window

if you're using the hierarchical profiler then you'll need access to the db server


BEGIN
    DBMS_HPROF.start_profiling (location => 'YOUR_PROFILING_DIRECTORY',  filename => 'jknj72.txt');
    Your_package.Your_Procedure;
    DBMS_HPROF.stop_profiling;
END;

Then you can use   plshprof   to generate a report on your output file.

plshprof -output  jknj72.report.txt  jknj72.txt