Samstag, 3. Dezember 2011

Enhancing the profiler data...

The next step would be to create a page based on the profiler data. But looking on the retrieved data shows that it is not very usefull in the moment.


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;

Source

I test the changes as user HR


begin
  profiler.start_profiler;
  test;
  profiler.stop_profiler;
end;
/

and unfortunatly it works.

to be continued...

Keine Kommentare:

Kommentar veröffentlichen