Just another version of Tom Kyte’s runstats (runstats_pkg)

articles: 

I want to share my modifications of Tom Kyte's runstats package, which include:


  • Any number of runs sets for analyzing

  • Standalone: No need to create other objects

  • Ability to specify session SID for statistics gathering

  • Ability to specify what to gather: latches, stats or both

  • Separate mask filters for output by statname and latchname

  • Ability to specify difference percentage for output separately for latches and statistics

  • More accurate with some statistics because of avoiding global temporary table usage, but less in several others because of collections usage


Link to package: https://github.com/xtender/xt_runstats

Output example:

SQL> begin
2 -- little example which shows difference between "fast dual" and "full table scan dual":
3 -- http://docs.oracle.com/cd/E11882_01/server.112/e17118/queries009.htm#SQLRF20036
4 xt_runstats.init(p_latches => false);
5 -- 1:
6 for r in (select * from dual connect by level<=1e3) loop
7 null;
8 end loop;
9 xt_runstats.snap;
10
11 -- 2:
12 for r in (select 'X' dummy from dual connect by level<=1e3) loop
13 null;
14 end loop;
15 xt_runstats.snap;
16 xt_runstats.print(p_stats_mask => '%gets%');
17 end;
18 /
################ Results: ##################
Run # 01 ran in 0 hsecs
Run # 02 ran in 0 hsecs
###########################################################################
Statistics | Run # 1 | Run # 2
###########################################################################
consistent gets......................... | 3 | 0
consistent gets from cache.............. | 3 | 0
consistent gets from cache (fastpath)... | 3 | 0
no work - consistent read gets.......... | 1 | 0
###########################################################################
-

Some usage examples:
1. for own session:

begin
xt_runstats.init();
[some_code_1]
xt_runstats.snap();
[some_code_2]
xt_runstats.snap();
...
[some_code_N]
xt_runstats.snap();
-- result output:
xt_runstats.print();
end;

2. for session with sid = N

begin xt_runstats.init(N); end;
...[after a while]
begin xt_runstats.snap; end;
...[one more if needed...]
begin xt_runstats.snap; end;
-- result output:
begin xt_runstats.print(); end;

3. Latches only:

xt_runstats.init(p_stats=>false);

4. Print stats with name like '%gets%':

xt_runstats.print(p_stats_mask=>'%gets%');

5. Print latches which differ by 30% or more and stats differ by 15% or more:

xt_runstats.print( p_lat_diff_pct=>30, p_sta_diff_pct => 15);