From my point of view it is not enough to know that line# 4 in unit 2 has been executed 101 times in a total time of 4412 nano seconds. I need to know the source code to find out that the code should be only executed once and that the excution should not need so much time at all.
In general there are two possible ways to combine source code and profiler data. I could fetch the data on the fly when needed, or retrieve it during the profiler run and store it in the database for later usage. The drawback of the first approach is, that you simply display invalid information for historical data whenever the code changed. So I will definatly go for the scond approach. I will place the needed logic in the MAINTENANCE_DB schema and prevent the direct usage of the dbms_profiler package.
I revoke some privileges from the user HR (run as user MAINTENANCE_DB)
REVOKE ALL ON sys.dbms_profiler FROM hr; REVOKE ALL ON plsql_profiler_data FROM hr; REVOKE ALL ON plsql_profiler_units FROM hr; REVOKE ALL ON plsql_profiler_data FROM hr; REVOKE SELECT ON plsql_profiler_runnumber FROM hr;
to prevent direct access to the dbms_profiler package. Please drop the private synonyms as well.
I create a new package in the MAINTENANCE_DB schema to be able to start profiling again as user HR.
create or replace PACKAGE profiler AS --wrap dbms_profiler.start_profiler PROCEDURE start_profiler( run_comment_in IN VARCHAR2 := TO_CHAR( sysdate, 'DD.MM.YYYY HH24:MI:SS')); --wrap dbms_profiler.stop_profiler PROCEDURE stop_profiler; END profiler; / CREATE OR REPLACE PACKAGE BODY PROFILER AS PROCEDURE start_profiler( run_comment_in IN VARCHAR2 := TO_CHAR( sysdate, 'DD.MM.YYYY HH24:MI:SS')) AS BEGIN dbms_profiler.start_profiler( run_comment_in); END start_profiler; PROCEDURE stop_profiler AS BEGIN dbms_profiler.stop_profiler; END stop_profiler; END PROFILER; GRANT EXECUTE ON profiler to hr; CREATE OR REPLACE SYNONYM hr.profiler FOR profiler;
I test the changes as user HR
begin profiler.start_profiler; test; profiler.stop_profiler; end; /
and unfortunatly it works.
to be continued...