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);