The Application DBA  

Tom Kyte's Runstats Utility

Overview

This is a modified version of Tom Kyte's Runstats utility. Tom's version is available here:

http://asktom.oracle.com/runstats.html

This version has been modified with installation code to make it easier to install, better documentation, a version and help procedure and a new option to print out a summary of statistics information rather than all of the latch and statistics. This option has been called 'WORKLOAD' and is invoked with a new parameter in the rs_stop procedure.

The New Code

The new Runstats code is available here.

Improved Installation

The installation has been improved. Now you can run the script and all of the installation will be done for you. The caveats are that you will need access to a SYSDBA account to grant the appropriate privileges (you needed that anyway, but now it's in the script) and you need an account for the Runstats objects (you also needed that as well, but now that's in the script also). The following shows an installation once a "Runstats" schema has been identified and a SYSDBA account is known.

/u01/app/oracle$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 31 11:09:14 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> @runstats
>> This script requires an account to install the RUNSTATS utility.
>> 
>> Please exit this script (using Control-C or break) if you
>> do not wish to continue.
>> 
>> Press [Return] to continue.

Enter the ORACLE userid for the RUNSTATS account: system

Enter the ORACLE password for the RUNSTATS account:

Connected.
>> If the previous connect failed, please exit this script
>> (using Control-C or break) and restart, otherwise press [Return]

>> The following system privileges be granted to the runstats schema:
>> 
>> GRANT SELECT ON sys.v_$statname TO runstats_schema
>> GRANT SELECT ON sys.v_$mystat TO runstats_schema
>> GRANT SELECT ON sys.v_$timer TO runstats_schema
>> GRANT SELECT ON sys.v_$latch TO runstats_schema
>> GRANT CREATE TABLE TO runstats_schema
>> GRANT CREATE PROCEDURE TO runstats_schema
>> GRANT CREATE PUBLIC SYNONYM TO runstats_schema
>> 
>> Requires a privileged (SYSDBA) account to create these privileges.
>> 
>> Please exit this script (using Control-C or break) if you
>> do not wish to continue.
>> 
>> Press [Return] to continue.

Enter a SYSDBA ORACLE userid: sys

Enter the SYSDBA ORACLE password:

Connected.
>> If the previous connect failed, please exit this script
>> (using Control-C or break) and restart, otherwise press [Return]

old 1: GRANT SELECT ON sys.v_$statname TO &runstats_usr
new 1: GRANT SELECT ON sys.v_$statname TO system

Grant succeeded.

old 1: GRANT SELECT ON sys.v_$mystat TO &runstats_usr
new 1: GRANT SELECT ON sys.v_$mystat TO system

Grant succeeded.

old 1: GRANT SELECT ON sys.v_$timer TO &runstats_usr
new 1: GRANT SELECT ON sys.v_$timer TO system

Grant succeeded.

old 1: GRANT SELECT ON sys.v_$latch TO &runstats_usr
new 1: GRANT SELECT ON sys.v_$latch TO system

Grant succeeded.

old 1: GRANT CREATE TABLE TO &runstats_usr
new 1: GRANT CREATE TABLE TO system

Grant succeeded.

old 1: GRANT CREATE PROCEDURE TO &runstats_usr
new 1: GRANT CREATE PROCEDURE TO system

Grant succeeded.

old 1: GRANT CREATE PUBLIC SYNONYM TO &runstats_usr
new 1: GRANT CREATE PUBLIC SYNONYM TO system

Grant succeeded.

Connected.
SQL> --
SQL> DROP TABLE run_stats;

Table dropped.

SQL> --
SQL> CREATE GLOBAL TEMPORARY TABLE run_stats
2 (
3 runid varchar2(15),
4 name varchar2(80),
5 value int
6 )
7 ON COMMIT PRESERVE ROWS;

Table created.

SQL> --
SQL> CREATE OR REPLACE VIEW stats
2 AS
3 SELECT
4 'STAT...' || a.name name, b.value
5 FROM
6 v$statname a,
7 v$mystat b
8 WHERE
9 a.statistic# = b.statistic#
10 UNION ALL
11 SELECT
12 'LATCH.' || name, gets
13 FROM
14 v$latch
15 UNION ALL
16 SELECT
17 'STAT...Elapsed Time',
18 hsecs
19 FROM
20 v$timer;

View created.

SQL> --
SQL> DELETE FROM run_stats;

0 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> --
SQL> CREATE OR REPLACE PACKAGE runstats_pkg
2 AS
3 PROCEDURE rs_start;
4 --
5 PROCEDURE rs_middle;
6 --
7 PROCEDURE rs_stop(
8 p_difference_threshold IN NUMBER DEFAULT 0,
9 p_output IN VARCHAR2 DEFAULT NULL);
10 --
11 PROCEDURE version;
12 --
13 PROCEDURE help;
14 END runstats_pkg;
15 /

Package created.

SQL> --
SQL> CREATE OR REPLACE PACKAGE BODY runstats_pkg
2 AS
3 g_start NUMBER;
4 g_run1 NUMBER;
5 g_run2 NUMBER;
6 --
7 g_version_txt VARCHAR2(60)
8 := 'runstats - Version 1.0, January 29, 2008';
9 --
10 -- Procedure to mark the start of the two runs
11 --
12 PROCEDURE rs_start
13 IS
14 BEGIN
15 DELETE FROM run_stats;
16 --
17 INSERT INTO run_stats
18 SELECT 'before', stats.*
19 FROM stats;
20 --
21 g_start := DBMS_UTILITY.get_time;
22 END rs_start;
23 --
24 -- Procedure to run between the two runs
25 --
26 PROCEDURE rs_middle
27 IS
28 BEGIN
29 g_run1 := (DBMS_UTILITY.get_time - g_start);
30 --
31 INSERT INTO run_stats
32 SELECT 'after 1', stats.*
33 FROM stats;
34 g_start := DBMS_UTILITY.get_time;
35 END rs_middle;
36 --
37 -- Procedure to run after the two runs
38 --
39 PROCEDURE rs_stop(
40 p_difference_threshold IN NUMBER DEFAULT 0,
41 p_output IN VARCHAR2 DEFAULT NULL)
42 IS
43 BEGIN
44 g_run2 := (DBMS_UTILITY.get_time - g_start);
45 --
46 DBMS_OUTPUT.put_line
47 ( 'Run1 ran in ' || g_run1 || ' hsecs' );
48 DBMS_OUTPUT.put_line
49 ( 'Run2 ran in ' || g_run2 || ' hsecs' );
50 DBMS_OUTPUT.put_line
51 ( 'run 1 ran in ' || ROUND(g_run1/g_run2*100,2) ||
52 '% of the time' );
53 DBMS_OUTPUT.put_line( CHR(9) );
54 --
55 INSERT INTO run_stats
56 SELECT 'after 2', stats.*
57 FROM stats;
58 --
59 DBMS_OUTPUT.put_line
60 ( rpad( 'Name', 40 ) || lpad( 'Run1', 12 ) ||
61 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
62 --
63 -- Output choice
64 --
65 IF p_output = 'WORKLOAD' THEN
66 FOR x IN
67 ( SELECT
68 RPAD( a.name, 40 ) ||
69 TO_CHAR( b.value-a.value, '999,999,999' ) ||
70 TO_CHAR( c.value-b.value, '999,999,999' ) ||
71 TO_CHAR( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
72 FROM
73 run_stats a,
74 run_stats b,
75 run_stats c
76 WHERE
77 a.name = b.name
78 AND b.name = c.name
79 AND a.runid = 'before'
80 AND b.runid = 'after 1'
81 AND c.runid = 'after 2'
82 AND ABS( (c.value-b.value) - (b.value-a.value) )
83 > p_difference_threshold
84 AND c.name IN
85 (
86 'STAT...Elapsed Time',
87 'STAT...DB Time',
88 'STAT...CPU used by this session',
89 'STAT...parse time cpu',
90 'STAT...recursive cpu usage',
91 'STAT...session logical reads',
92 'STAT...physical reads',
93 'STAT...physical reads cache',
94 'STAT...physical reads direct',
95 'STAT...sorts (disk)',
96 'STAT...sorts (memory)',
97 'STAT...sorts (rows)',
98 'STAT...queries parallelized',
99 'STAT...redo size',
100 'STAT...user commits'
101 )
102 ORDER BY
103 ABS( (c.value-b.value)-(b.value-a.value))
104 ) LOOP
105 DBMS_OUTPUT.put_line( x.data );
106 END LOOP;
107 ELSE
108 -- Assume the default of NULL, all stats will be displayed
109 FOR x IN
110 ( SELECT
111 RPAD( a.name, 40 ) ||
112 TO_CHAR( b.value-a.value, '999,999,999' ) ||
113 TO_CHAR( c.value-b.value, '999,999,999' ) ||
114 TO_CHAR( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
115 FROM
116 run_stats a,
117 run_stats b,
118 run_stats c
119 WHERE
120 a.name = b.name
121 AND b.name = c.name
122 AND a.runid = 'before'
123 AND b.runid = 'after 1'
124 AND c.runid = 'after 2'
125 AND ABS( (c.value-b.value) - (b.value-a.value) )
126 > p_difference_threshold
127 ORDER BY
128 ABS( (c.value-b.value)-(b.value-a.value))
129 ) LOOP
130 DBMS_OUTPUT.put_line( x.data );
131 END LOOP;
132 END IF;
133 --
134 DBMS_OUTPUT.put_line( CHR(9) );
135 DBMS_OUTPUT.put_line
136 ( 'Run1 latches total versus runs -- difference and pct' );
137 DBMS_OUTPUT.put_line
138 ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
139 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
140 --
141 FOR x IN
142 ( SELECT
143 TO_CHAR( run1, '999,999,999' ) ||
144 TO_CHAR( run2, '999,999,999' ) ||
145 TO_CHAR( diff, '999,999,999' ) ||
146 TO_CHAR( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
147 FROM
148 (
149 SELECT
150 SUM(b.value-a.value) run1,
151 SUM(c.value-b.value) run2,
152 SUM( (c.value-b.value)-(b.value-a.value)) diff
153 FROM
154 run_stats a,
155 run_stats b,
156 run_stats c
157 WHERE
158 a.name = b.name
159 AND b.name = c.name
160 AND a.runid = 'before'
161 AND b.runid = 'after 1'
162 AND c.runid = 'after 2'
163 AND a.name like 'LATCH%'
164 )
165 ) LOOP
166 DBMS_OUTPUT.put_line( x.data );
167 END LOOP;
168 END rs_stop;
169 --
170 -- Display version
171 --
172 PROCEDURE version
173 IS
174 --
175 BEGIN
176 IF LENGTH(g_version_txt) > 0 THEN
177 dbms_output.put_line(' ');
178 dbms_output.put_line(g_version_txt);
179 END IF;
180 --
181 END version;
182 --
183 -- Display help
184 --
185 PROCEDURE help
186 IS
187 --
188 -- Lists help menu
189 --
190 BEGIN
191 DBMS_OUTPUT.put_line(CHR(9));
192 DBMS_OUTPUT.PUT_LINE(g_version_txt);
193 DBMS_OUTPUT.put_line(CHR(9));
194 DBMS_OUTPUT.PUT_LINE('Procedure rs_start');
195 DBMS_OUTPUT.PUT_LINE(CHR(9)||'Run to mark the start of the test');
196 DBMS_OUTPUT.put_line(CHR(9));
197 DBMS_OUTPUT.PUT_LINE('Procedure rs_middle');
198 DBMS_OUTPUT.PUT_LINE(CHR(9)||'Run to mark the middle of the test');
199 DBMS_OUTPUT.put_line(CHR(9));
200 DBMS_OUTPUT.PUT_LINE('Procedure rs_stop');
201 DBMS_OUTPUT.PUT_LINE(CHR(9)||'Run to mark the end of the test');
202 DBMS_OUTPUT.put_line(CHR(9));
203 DBMS_OUTPUT.PUT_LINE('Parameters:');
204 DBMS_OUTPUT.PUT_LINE(CHR(9)||'p_difference_threshold - Controls the output. Only stats greater');
205 DBMS_OUTPUT.PUT_LINE(CHR(9)||'than p_difference_threshold will be displayed.');
206 DBMS_OUTPUT.put_line(CHR(9));
207 DBMS_OUTPUT.PUT_LINE(CHR(9)||'p_output - Controls stats displayed.');
208 DBMS_OUTPUT.PUT_LINE(CHR(9)||' Default is NULL, all stats displayed.');
209 DBMS_OUTPUT.PUT_LINE(CHR(9)||' WORKLOAD, only workload related stats are displayed.');
210 --
211 DBMS_OUTPUT.put_line(CHR(9));
212 DBMS_OUTPUT.PUT_LINE('Example:');
213 DBMS_OUTPUT.PUT_LINE(CHR(9)||'Add the following calls to your test code:');
214 DBMS_OUTPUT.PUT_LINE(CHR(9)||' exec runStats_pkg.rs_start;');
215 DBMS_OUTPUT.PUT_LINE(CHR(9)||' exec runStats_pkg.rs_middle;');
216 DBMS_OUTPUT.PUT_LINE(CHR(9)||' exec runStats_pkg.rs_stop;');
217 --
218 DBMS_OUTPUT.put_line(CHR(9));
219 DBMS_OUTPUT.PUT_LINE('NOTE: In SQL*Plus set the following for best results:');
220 DBMS_OUTPUT.put_line(CHR(9));
221 DBMS_OUTPUT.PUT_LINE(CHR(9)||'Before 10g: SET SERVEROUTPUT ON SIZE 1000000');
222 DBMS_OUTPUT.PUT_LINE(CHR(9)||'10g or later: SET SERVEROUTPUT ON');
223 END help;
224 --
225 END runstats_pkg;
226 /

Package body created.

SQL> --
SQL> -- Grant privileges on runstats objects
SQL> --
SQL> SET escape "^";
SQL> CREATE PUBLIC SYNONYM runstats_pkg FOR &runstats_usr^.runstats_pkg;
old 1: CREATE PUBLIC SYNONYM runstats_pkg FOR &runstats_usr^.runstats_pkg
new 1: CREATE PUBLIC SYNONYM runstats_pkg FOR system.runstats_pkg

Synonym created.

SQL> GRANT EXECUTE ON runstats_pkg TO PUBLIC;

Grant succeeded.

SQL> --
SQL> EXIT;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
/u01/app/oracle$
New Procedures
Two new procedures have been added to the runstats_pkg. One is called VERSION and will display the utility version. The second is called HELP and will display the syntax and a short help description for the utility.
SQL> set serveroutput on
SQL> exec runstats_pkg.version;
runstats - Version 1.0, January 29, 2008

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.help;

runstats - Version 1.0, January 29, 2008

Procedure rs_start
Run to mark the start of the test

Procedure rs_middle
Run to mark the middle of the test

Procedure rs_stop
Run to mark the end of the test

Parameters:
p_difference_threshold - Controls the output. Only stats greater
than p_difference_threshold will be displayed.

p_output - Controls stats displayed.
Default is NULL, all stats displayed.
WORKLOAD, only workload related stats are displayed.

Example:
Add the following calls to your test code:
exec runStats_pkg.rs_start;
exec runStats_pkg.rs_middle;
exec runStats_pkg.rs_stop;

NOTE: In SQL*Plus set the following for best results:

Before 10g: SET SERVEROUTPUT ON SIZE 1000000
10g or later: SET SERVEROUTPUT ON

PL/SQL procedure successfully completed.

SQL>


Workload Option

The following is taken from a run using the WORKLOAD option for the rs_stop procedure. The default is NULL and leaves the rs_stop in its original state. When the WORKLOAD option is used a subset of key statistics is output to make it much easier to judge the relative workload impact of the two runs.

SQL> exec runstats_pkg.rs_stop(0,'WORKLOAD');
Run1 ran in 13894 hsecs
Run2 ran in 71202 hsecs
run 1 ran in 19.51% of the time

Name                                  Run1         Run2          Diff
STAT...queries parallelized              1            0            -1
STAT...parse time cpu                    5            1            -4
STAT...physical reads              593,922      593,892           -30
STAT...sorts (memory)                   38            2           -36
STAT...redo size                     2,996        2,368          -628
STAT...session logical reads       598,394      596,820        -1,574
STAT...sorts (rows)                 89,235       86,681        -2,554
STAT...CPU used by this session     29,355       21,943        -7,412
STAT...recursive cpu usage          29,335           29       -29,306
STAT...Elapsed Time                 13,923       71,229        57,306
STAT...physical reads cache             30      593,892       593,862
STAT...physical reads direct       593,892            0      -593,892

Run1 latches total versus runs -- difference and pct
Run1       Run2        Diff            Pct
5,163,091  28,958,959  23,795,868      17.83%

PL/SQL procedure successfully completed.

SQL>

 

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.