Rem profrep.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998. All Rights Reserved.
Rem
Rem    NAME
Rem      profrep.sql
Rem
Rem    DESCRIPTION
Rem      PL/SQL Profiler reporting utilities
Rem
Rem    NOTES
Rem      The reporting procedures expect server output to be set on
Rem      Some of the rollup functions commit the transaction
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ciyer       10/09/98 - profiler table structure change
Rem    astocks     06/26/98 - Improve exception handling when run out of tables
Rem    astocks     04/13/98 - PL/SQL utilities to write a report from the accum
Rem    astocks     04/13/98 - Created
Rem

-- First create the views used in the reporting package
--
create or replace view plsql_profiler_grand_total as
  select sum(total_time) as grand_total from plsql_profiler_units;

create or replace view plsql_profiler_units_cross_run as
  select unit_owner, unit_name, unit_type, sum(total_time) as total_time
    from plsql_profiler_units group by unit_owner, unit_name, unit_type;

create or replace view plsql_profiler_lines_cross_run as
  select p1.unit_owner as unit_owner, p1.unit_name as unit_name, 
    p1.unit_type as unit_type, 
    p2.line# as line#,
    sum(p2.total_occur) as total_occur,
    sum(p2.total_time) as total_time, 
    min(p2.min_time) as min_time,
    max(p2.max_time) as max_time
  from plsql_profiler_units p1, plsql_profiler_data p2
  where p1.runid=p2.runid and p1.unit_number = p2.unit_number
  group by p1.unit_owner, p1.unit_name, p1.unit_type, p2.line#;

create or replace view plsql_profiler_notexec_lines as
  select owner, name, type, line, text, total_occur
  from all_source t1, plsql_profiler_lines_cross_run t2
  where t2.total_occur = 0 and t2.unit_owner = owner
    and t2.unit_name = name and t2.unit_type = type and t2.line# = line
  order by line asc;


create or replace package prof_report_utilities
  authid current_user is

  -- Routines to roll up profile information from line level to unit level
  --
  procedure rollup_unit(run_number IN number, unit IN number);
  procedure rollup_run(run_number IN number);
  procedure rollup_all_runs;

  -- Routines to print a report, treating each run separately
  --
  procedure print_unit(run_number IN number, unit IN number);
  procedure print_run(run_number IN number);
  procedure print_detailed_report;

  -- Routine to print a single report including information from each run
  --
  procedure print_summarized_report;

  -- Set size of window for reports
  procedure set_window_size(window_size IN pls_integer);

end prof_report_utilities;
/
show errors;

create or replace
package body prof_report_utilities is

  -- the reports print 'window' lines of source around lines with profiler
  -- data, otherwise skipping lines with no data. This is useful when
  -- viewing data for units with sparse profiler data.
  --
  window pls_integer := 10;
  last_line_printed number := 999999999;

  cursor c2(run number, unit number,
            owner_name varchar2, unit_name varchar2, unit_type varchar2) is
    select line, text, total_occur, total_time, min_time, max_time
      from all_source, plsql_profiler_data
      where runid (+) = run and unit_number (+) = unit
        and owner = owner_name and name = unit_name and type = unit_type
        and plsql_profiler_data.line# (+) = line
      order by line asc;

  -- c2tab contains the window of lines around any line with interesting
  -- data.
  type c2tab_t is table of c2%rowtype index by binary_integer;
  c2tab    c2tab_t;
  -- index into the window where previous row was inserted
  prev_row pls_integer := 0;

  -- compute the total time spent executing this unit - the sum of the
  -- time spent executing lines in this unit (for this run)
  --
  procedure rollup_unit(run_number IN number, unit IN number) is
  begin
    update plsql_profiler_units set total_time =
      (select sum(total_time) from plsql_profiler_data
        where runid = run_number and unit_number = unit);
    commit;
  exception
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put('Exception Number:  ');
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(' raised In Rollup_Unit!! (Will be resignalled)');
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      raise;                    
  end rollup_unit;

  -- rollup all units for the given run
  --
  procedure rollup_run(run_number IN number) is
    cursor cunits(run_number number) is
      select unit_number from plsql_profiler_units 
        -- only select those units which have not been rolled up yet
        where runid = run_number and total_time = 0
        order by unit_number asc;
  begin
    for unitrec in cunits(run_number) loop
      rollup_unit(run_number, unitrec.unit_number);
    end loop;
  exception
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put('Exception Number:  ');
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(' raised In Rollup_Run!! (Will be resignalled)');
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      raise;                    
  end rollup_run;

  procedure rollup_all_runs is 
    cursor crunid is 
      select runid from plsql_profiler_runs order by runid asc;
  begin
    for runidrec in crunid loop
      rollup_run(runidrec.runid);
    end loop crunid;
  end rollup_all_runs;

  --
  -- Reporting functions
  --

  -- Format and print information on a unit
  --
  procedure print_unit_header(run_number IN number, unit IN number) is
    cursor cuhdr(run_number number, unit number) is
      select * from plsql_profiler_units 
        where runid = run_number and unit_number = unit;
    unit_row cuhdr%rowtype;
  begin
    -- fetch data for the given unit
    open cuhdr(run_number, unit);
    fetch cuhdr into unit_row;
    close cuhdr;

    -- format and print the data
    dbms_output.put('Unit #');
    dbms_output.put(unit_row.unit_number);
    dbms_output.put(': ');
    dbms_output.put(unit_row.unit_owner || '.' || unit_row.unit_name);
    dbms_output.put(' - Total time: ');
    dbms_output.put(to_char(unit_row.total_time/1000000000, '99999.99'));
    dbms_output.put_line(' seconds');
  end print_unit_header;

  -- Format and print information on a run
  --
  procedure print_run_header(run_number IN number) is
    cursor crun(run_number number) is 
      select * from plsql_profiler_runs where runid = run_number;
    runidrec crun%rowtype;
  begin
    open crun(run_number);
    fetch crun into runidrec;
    close crun;

    dbms_output.new_line();
    dbms_output.new_line();
    dbms_output.put('===========================');
    dbms_output.put('Results for run #');
    dbms_output.put(runidrec.runid);
    dbms_output.put(' made on ');
    dbms_output.put(to_char(runidrec.run_date, 'DD-MON-YY HH24:MI:SS'));
    dbms_output.put_line(' =========================');
    if (runidrec.run_comment is not null) then
      dbms_output.put(' (');
      dbms_output.put(runidrec.run_comment);
      dbms_output.put(') ');
    end if;
    dbms_output.put('Run total time: ');
    dbms_output.put(to_char(runidrec.run_total_time/1000000000, '99999.99'));
    dbms_output.put_line(' seconds');
    if (runidrec.run_system_info is not null) then
      dbms_output.put_line(runidrec.run_system_info);
    end if;
  end print_run_header;
     
  -- 
  -- Routines for formatting and printing profiler data
  --
  
  -- Format and print one line of data and source
  --
  procedure print_line(line number, lcount number,
                       running_total number, source varchar2) is
    outline    varchar2(200);         -- temp buffer to hold output
    cline      varchar2(40);          -- number of times this line was executed
    total_time varchar2(40);          -- total time executing this line
    ave_time   varchar2(40);          -- average time for this line
    ave_nano   number;
  begin
    outline := to_char(line, '99G999');

    -- format and store away the count and running total
    if (lcount is not null) then
      cline := to_char(lcount, '99G999G999');
    end if;

    if (running_total is not null) then
      total_time := substr(to_char(running_total/1000000000), 1, 9);
    end if;

    -- compute average time executing this line and stash it away
    if (lcount > 0) then
      ave_nano := running_total/lcount;
      ave_time := substr(to_char(ave_nano/1000000000), 1, 9);
    end if;

    -- now put together all the data, the source line and output it
    --
    outline := outline || ' ' || cline || '   ' ||
               total_time || '  ' || ave_time || ' ';

    if source is not null then 
      outline := rpad(outline, 55) || substr(source, 1, (length(source) - 1));
    end if;

    dbms_output.put_line(outline);
  end print_line;

  -- insert a c2 row into the window
  --
  procedure insert_into_window(c2row c2%rowtype) is
    next_row pls_integer;
  begin
    next_row := mod((prev_row + 1), window);
    c2tab(next_row) := c2row;
    prev_row := next_row;
  end insert_into_window;

  -- clear out the window (for reuse later)
  --
  procedure clear_window is
    empty_tab c2tab_t;
  begin
    -- throw away table
    c2tab := empty_tab;
    prev_row := 0;
  end clear_window;

  -- print the window and throw it away
  --
  procedure print_window(start_separator IN boolean) is
    next_row  pls_integer;
    iter      pls_integer;
    c2row     c2%rowtype;
    first_line boolean := true;
    ct	      number := c2tab.count;
  begin

    if (window <= 0) then
      return;
    end if;

    -- compute first row
    next_row := mod((prev_row + 1), window);

    -- Detect the case where the window hasn't wrapped around yet
    if (not c2tab.exists(next_row)) then
      next_row := c2tab.next(next_row);
      if (next_row is NULL) then 
	next_row := c2tab.first; 
      end if; 
    end if;

    for iter in 1..window loop
      exit when (ct <= 0);

      if (c2tab.exists(next_row)) then
        c2row := c2tab(next_row);
	if (first_line and (last_line_printed < c2row.line-1)) then
	  dbms_output.put_line('.');
	  dbms_output.put_line('.');
	  dbms_output.put_line('.');
	end if;
	first_line := false;
        print_line(c2row.line, c2row.total_occur,
                   c2row.total_time, c2row.text);
	last_line_printed := c2row.line;
	ct := ct - 1;
      end if;
      next_row := mod((next_row + 1), window);
    end loop;

    if (not start_separator) then
      last_line_printed := 999999999;
    end if;
    clear_window;
  end print_window;
  
  procedure print_unit(run_number number, unit number) is
    cursor cuhdr(run number, unit number) is
      select * from plsql_profiler_units
        where runid = run and unit_number = unit;

    unit_row    cuhdr%rowtype;
    joined_row  c2%rowtype;
    lcount      number;

    -- print a trailing window after the last interesting line
    print_trailing_window boolean := false;
    trail_count pls_integer := 0;

  begin
    rollup_unit(run_number, unit);

    -- fetch unit name and type information
    open cuhdr(run_number, unit);
    fetch cuhdr into unit_row;
    close cuhdr;

    open c2(run_number, unit,
            unit_row.unit_owner, unit_row.unit_name, unit_row.unit_type);
    loop
      fetch c2 into joined_row;
      exit when c2%notfound;
      lcount := joined_row.total_occur;

      -- if there is interesting data at this line, print its prefix window
      -- and the data itself; else stash away this line c2tab - it may get
      -- printed as part of another line's window
      if (lcount is not null and lcount <> 0) then
        print_window (start_separator => false);
        print_line(joined_row.line, joined_row.total_occur,
                   joined_row.total_time, joined_row.text);
        print_trailing_window := true;
        trail_count := 0;
      else
        insert_into_window(joined_row);
        -- if we are now accumulating rows after a row with data, increment
        -- count of rows accumulated since last interesting row. if we have
        -- accumulated a window full of data, print it out.
        if (print_trailing_window) then
          trail_count := trail_count + 1;
          if (trail_count = window) then
            print_window(start_separator => true);
            print_trailing_window := false;
          end if;
        end if;
      end if;
    end loop;
    close c2;
    -- if the window isn't empty, print it out.
    if (print_trailing_window) then
      print_window(start_separator => false);
      print_trailing_window := false;
    end if;
    clear_window;
  exception
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put_line('Exception Raised In Print_Unit!!');
      dbms_output.put('Exception Number:  ');
      dbms_output.put_line(sqlcode);
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
  end print_unit;

  procedure print_run(run_number number) is
    cursor cunits(run_number number) is
      select unit_number from plsql_profiler_units 
        where runid = run_number order by unit_number asc;
  begin
    print_run_header(run_number);
    rollup_run(run_number);

    for unitrec in cunits(run_number) loop
      print_unit_header(run_number, unitrec.unit_number);
      print_unit(run_number, unitrec.unit_number);
    end loop;
  exception
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put('Exception Number:  ');
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(' raised In Print_Run!!');
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
  end print_run;

  procedure print_detailed_report is
    cursor crunid is 
      select runid from plsql_profiler_runs order by runid asc;
  begin
    dbms_output.enable(999999);

    dbms_output.put('=================================');
    dbms_output.put('trace info'); 
    dbms_output.put_line('=================================');
   
    rollup_all_runs();

    for runidrec in crunid loop
      print_run(runidrec.runid);
    end loop crunid;

    dbms_output.new_line;
    dbms_output.put('======================================');
    dbms_output.put_line('======================================');
  exception 
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put('Exception Number:  ');
      dbms_output.put(sqlcode); 
      dbms_output.put_line(' raised in Print_Detailed_Report!!');
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
  end print_detailed_report;

  procedure print_summarized_unit(owner_name varchar2,
                                  unit_name varchar2, unit_type  varchar2) is
    cursor c3(uowner varchar2, uname varchar2, utype varchar2) is
      select line, text, total_occur, total_time, min_time, max_time
        from all_source t1, plsql_profiler_lines_cross_run t2
        where owner = uowner and name = uname and type = utype
           and t2.unit_owner (+) = owner and t2.unit_name (+) = name
           and t2.unit_type (+) = type and t2.line# (+) = line
        order by line asc;

    datarec c3%rowtype;
    lcount  number;

    -- print a trailing window after the last interesting line
    print_trailing_window boolean := false;
    trail_count pls_integer := 0;
  begin
    open c3(owner_name, unit_name, unit_type);
    loop
      fetch c3 into datarec;
      exit when c3%notfound;
      lcount := datarec.total_occur;

      if (lcount is not null and lcount <> 0) then
        print_window (start_separator => false);
        print_line(datarec.line,
                   datarec.total_occur,
                   datarec.total_time,
                   datarec.text);
        print_trailing_window := true;
        trail_count := 0;
      else
        insert_into_window(datarec);
        -- if we are now accumulating rows after a row with data, increment
        -- count of rows accumulated since last interesting row. if we have
        -- accumulated a window full of data, print it out.
        if (print_trailing_window) then
          trail_count := trail_count + 1;
          if (trail_count = window) then
            print_window(start_separator => true);
            print_trailing_window := false;
          end if;
        end if;
      end if;
    end loop;
    close c3;
    -- if the window isn't empty, print it out.
    if (print_trailing_window) then
      print_window(start_separator => false);
      print_trailing_window := false;
    end if;
    clear_window;
  exception
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put('Exception Number:  ');
      dbms_output.put(sqlcode);
      dbms_output.put_line(' raised In Print_Summarized_Unit!!');
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
  end print_summarized_unit;

  procedure print_summarized_report is
    cursor cunits is
      select unit_owner, unit_name, unit_type
        from plsql_profiler_units_cross_run order by unit_owner, unit_name asc;
  begin
    rollup_all_runs();
    dbms_output.enable(999999);

    dbms_output.put('=================================');
    dbms_output.put('Profiler report - all runs rolled up');
    dbms_output.put_line('=================================');

    for unitrec in cunits loop
      dbms_output.put('Unit ');
      dbms_output.put(unitrec.unit_name);
      dbms_output.put_line(':');
      print_summarized_unit(unitrec.unit_owner,
                            unitrec.unit_name, unitrec.unit_type);
    end loop;

    dbms_output.new_line;
    dbms_output.put('======================================');
    dbms_output.put_line('======================================');
  exception 
    when others then
      dbms_output.new_line;
      dbms_output.new_line;
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
      dbms_output.put_line('Exception Raised!!');
      dbms_output.put('Exception Number:  ');
      dbms_output.put_line(sqlcode);
      dbms_output.put('======================================');
      dbms_output.put_line('======================================');
  end print_summarized_report;

  -- Set size of window for reports
  procedure set_window_size(window_size IN pls_integer) is
  begin
    if (window_size < 0) then
      window := 999999999;
    else
      window := window_size;
    end if;
  end set_window_size;

end prof_report_utilities;
/
show errors;