René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

December 26, 2005: On comparing multiple SQL statements' statistics

When autotrace is set within SQL*Plus, statistics about a DML statement will be shown when the statement has finished. This is quite a usable feature to compare different SQL statements regarding their performance.
However, I miss the ability to compare multiple SQL statements. statspack comes closer, but still is not what I want. Therefore, I have written a package to do what I need.
First, I need to give myself (that is: RENE) the necessary grants by sys:
connect / as sysdba

grant select on v_$sesstat to rene;
grant select on v_$session to rene;
grant select on v_$statname to rene;

connect rene/rene
Then, I need a table that stores the statistics in which I am interested. The column calib_value is used to calibrate the package. It basically stores the overhead for the stat# in question when running the package.
create global temporary table interesting_stats (
  stat#       number  ( 3) primary key,
  name        varchar2(64) not null,
  calib_value number       default 0
)
on commit preserve rows;
I also need a table that stores the statistics' values for each statement that I want to compare. When I start and end a run, the current value from v$sesstat will be stored in value. is_start is set to 'y' when a run is started and set to 'n' when a run is stopped. snap is increased for each run.
create global temporary table stats (
  stat#      number(4)  not null, -- foreign key not allowed in ggt
  snap       number(2)  not null,
  is_start   char(1)    not null check (is_start in ('y', 'n')),
  value      number     not null
)
on commit preserve rows;
In order to make the subtracting a bit easier in the package, I create a view that holds the difference between a stat#'s start and stop value:
create view stats_view as
  select
    stat#,
    snap,
    end_snap.value - start_snap.value value
  from
    stats start_snap join stats end_snap using (stat#, snap)
  where start_snap.is_start = 'y' and
        end_snap  .is_start = 'n';

Finally, I can create the package.
create package sql_stats as

  procedure init;

  procedure add_stat(statname in varchar2);
  procedure add_autotrace_stats;

  procedure calibrate;

  procedure start_snap;
  procedure stop_snap;

  procedure show(col_width in number := 10);

end sql_stats;
/
It will be clear a bit further down how the package's interface must be used.
Here's the package body:
create package body sql_stats as
  
  current_snap number(2);
  my_sid       v$sesstat.sid%type;

  procedure init is begin

    current_snap :=  0;

    select sid
      into my_sid
      from v$session
     where audsid = sys_context('userenv', 'SESSIONID');

    delete from interesting_stats;
    delete from stats;

  end init;

  procedure add_stat(statname in varchar2) is begin

    insert into interesting_stats (stat#, name)
    select statistic#, name 
      from v$statname
     where name = statname;

  end add_stat;

  procedure add_autotrace_stats is begin

    add_stat('recursive calls');
    add_stat('db block gets');
    add_stat('consistent gets');
    add_stat('physical reads');
    add_stat('redo size');
    add_stat('bytes sent via SQL*Net to client');
    add_stat('bytes received via SQL*Net from client');
    add_stat('SQL*Net roundtrips to/from client');
    add_stat('sorts (memory)');
    add_stat('sorts (disk)');

  end add_autotrace_stats;

  procedure calibrate is begin

    delete from stats;

    current_snap := 0;

    start_snap;
    stop_snap;

    start_snap;
    stop_snap;

    update interesting_stats i set
      calib_value = (
        select value from stats_view s 
         where 
           s.stat# = i.stat# and
           s.snap  = 2
      );

    delete stats;
    current_snap := 0;

  end calibrate;

  procedure fill_stat(is_start_ in stats.is_start%type) is begin

    insert  into stats (stat#, snap, is_start, value)
     select i.stat#, current_snap, is_start_, v.value
       from v$sesstat v join interesting_stats i on (i.stat# = v.statistic#)
      where sid = my_sid;

  end fill_stat;

  procedure start_snap is begin
    current_snap := current_snap + 1;

    fill_stat('y');
  end start_snap;

  procedure stop_snap is begin
    fill_stat('n');
  end stop_snap;

  procedure show(col_width in number := 10) is 
    max_len_stat_name number(2); 

    line_1            varchar2(255);
    line_2            varchar2(255);

  begin

    select max(length(name)) + 2 
      into max_len_stat_name
      from (
        select name from interesting_stats union
        select 'statistic' from dual);

    dbms_output.new_line;
    dbms_output.put(rpad('statistic', max_len_stat_name));

    for s in 1 .. current_snap loop
      dbms_output.put(to_char(s, lpad('9',col_width,'9')));
    end loop;

    dbms_output.new_line;

    dbms_output.put_line(rpad('-', max_len_stat_name+1 + current_snap*(col_width+1), '-'));

    for st in (
      select 
        name,
        stat#,
        calib_value
      from
        interesting_stats
    ) loop

      line_1 := rpad(st.name, max_len_stat_name, '.');
      line_2 := rpad(' '    , max_len_stat_name + 1);

      for sn in (
        select
          min             (value-st.calib_value) over ()        min_value, 
          max             (value-st.calib_value) over ()        max_value,
          ratio_to_report (value-st.calib_value) over () * 100  perc,
          value - st.calib_value                                value
        from
          stats_view
        where
          stat# = st.stat#
        order by snap

      ) loop

        line_1 := line_1 || to_char(sn.value, rpad('9', col_width, '9'));

        line_2 := line_2 || rpad(' ', col_width-7) || substr(
                            case sn.value when sn.min_value then '*'
                                          when sn.max_value then '!'
                                          else ' ' end,1,1);

        line_2 := line_2 || case when sn.perc is null 
                                 then '   n/a ' 
                                 else to_char(sn.perc,    '990.0') || '%'
                            end;

      end loop;

      dbms_output.put_line(line_1);
      dbms_output.put_line(line_2);
      dbms_output.new_line;

    end loop;

  end show;

end sql_stats;
/
Finally, I can use the package. First, I need to call init on the package:
exec sql_stats.init;
Then, I have to specify what statisics I am interested in:
begin
  sql_stats.add_stat('db block gets');
  sql_stats.add_stat('consistent gets');
  sql_stats.add_stat('physical reads');
  sql_stats.add_stat('redo size');
  sql_stats.add_stat('DB time');
end;
/
I also need to calibrate the package. This makes sure that the package's overhead is not counted when I show the statistics.
exec sql_stats.calibrate;
Now, I am going to compare three SQL statements. Each statement I compare is enclosed in a call to start_snap and stop_snap.
exec sql_stats.start_snap;
create table foohoo as 
  select b.* from user_objects a cross join user_objects b 
   where rownum < 100000;
exec sql_stats.stop_snap;

exec sql_stats.start_snap;
delete foohoo;
exec sql_stats.stop_snap;

exec sql_stats.start_snap;
select * from dual;
exec sql_stats.stop_snap;
The statistics for the statements are:
exec sql_stats.show;
statistic                  1          2          3
---------------------------------------------------
db block gets....       2447     111205          0
                         2.2%   !  97.8%   *   0.0%

consistent gets..       2204       1480          3
                     !  59.8%      40.1%   *   0.1%

physical reads...          0       1376          0
                     *   0.0%   ! 100.0%   *   0.0%

redo size........     114620   36131920          0
                         0.3%   !  99.7%   *   0.0%

DB time..........         28        248          0
                        10.1%   !  89.9%   *   0.0%
The output shows the number for each snap I did and for each statistic I specified along with the percentage of the number for the statistic. The worst percentage of a statistic is prepended with a !, the best statistic is prepended with a *. When a statistic has multiple best or worst percentages, multiple ! or * can occur (as is the case here for the statistic physical reads).

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.