| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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 OracleThis is an on Oracle article. The most current articles of this series can be found here.
|