Just another version of Tom Kyte’s runstats (runstats_pkg)
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);
- xtender's blog
- Log in to post comments