Montag, 5. Dezember 2011

The basic package...

 First I restrict the profiler from being started mor then once. I define a package private variable to keep the actual runid an a variable to keep the number of start_profiler_calls.
  g_run_number BINARY_INTEGER;
  g_profiler_counter binary_integer := 0;

I only want to run profiler so I will prevent to have more then one instance. I could have paused and resumed the profiler as well, but I  don't like that. I will increase the number of profiler calls every time start_profiler is called and save the runid only if the number of calls is 1

...
  l_run_number BINARY_INTEGER;
BEGIN
  IF increase_profiler_counter = 1 THEN
    start_profiler(run_comment_in, l_run_number);
    set_run_number(l_run_number);
  END IF;
...

and decrease it every time stop_profiler is called. I only stop the profiler if the number of calls is 0

...
IF reduce_profiler_counter = 0 THEN
    dbms_profiler.stop_profiler;
...

Rolling up the runs data will aggregate the total_time values on unit and run level. Unfortunatly the provided dbms_profiler procedure will only work on unit level, so I have to rollup the data on runs level myself. And yes I use autonomous transactions.

...
  pragma autonomous_transaction;
BEGIN
  dbms_profiler.rollup_run(runid_in);
  UPDATE
    plsql_profiler_runs
  SET
    run_total_time =
    (
      SELECT
        SUM(total_time)
      FROM
        plsql_profiler_units
      WHERE
        runid = runid_in
    )
  WHERE
    runid = runid_in;
  COMMIT;
...

Source

We can now simplify the units view.

CREATE OR REPLACE FORCE VIEW plsql_profiler_units_vw( runid 
                                                     ,unit_number 
                                                     ,unit_type 
                                                     ,unit_name 
                                                     ,unit_timestamp 
                                                     ,total_time 
                                                     ,max_total_time 
                                                     ,max_total_time_unit)
AS
  SELECT units.runid ,
    units.unit_number ,
    unit_type ,
    unit_owner
    || '.'
    || unit_name AS unit_name ,
    TO_TIMESTAMP( TO_CHAR(
    CASE
      WHEN unit_type = 'ANONYMOUS BLOCK'
      THEN runs.run_date
      ELSE unit_timestamp
    END ,'DD.MM.YYYY HH24:MI:SS') ,'DD.MM.YYYY HH24:MI:SS') AS unit_timestamp ,
    total_time ,
    MAX(units.total_time) OVER (PARTITION BY units.runid)       AS max_total_time ,
    MAX(units.total_time) OVER (PARTITION BY units.unit_number) AS max_total_time_unit
  FROM plsql_profiler_units units
  JOIN plsql_profiler_runs runs
  ON units.runid = runs.runid;

Source

to be continued...

Keine Kommentare:

Kommentar veröffentlichen