Oracle System Statistics
When I look at the system statistics I’m not really getting any wiser. There a re really a lot of statistics:
SQL> select count(*) from v$sysstat;
COUNT(*)
----------
2252
SQL>
Above from a Oracle 21c instance. I do not want to see them all.
I would like to see the change in the statistics over time.
There are probably a lot of ways which lead me to this, but I decided to make something myself.
Script is to be found at: Github
First get some explanation and input arguments in place:
-- Name : sysstatd.sql -- Author : Martijn Bos -- Input :: the number of seconds to wait -- : name which the statistics to display should look like -- Description : Displays the delta for system statistics over time -- Only shows the statistics that changed in the interval -- Otherwise the list get way to long -- Usage : @sysstatd -- -- Getting the delta in statistics named %get% in a 10 second interval -- @sysstat 10 get -- -- Getting the delta in statistics for ALL statistics for a 60 second interval -- @sysstatd 60 % -- --------------------------------------------------------------------- -- 2023-05-26 : MB : Initial Version @settings define _WAIT=&1 define _STAT=&2
Declare some types and variables
declare
cursor statc is select sn.name,
ss.class,
ss.value
from v$sysstat ss,
v$statname sn
where ss.statistic#=sn.statistic#;
stat_row statc%ROWTYPE;
type stat_type is record(stat_name varchar2(100),
stat_class varchar2(100),
value_start number,
value_end number);
stat stat_type;
type stat_table_type is table of stat_type;
stat_table stat_table_type:=stat_table_type();
tmp varchar2(128);
NOow gather the first set of statitics
begin
dbms_output.enable(100000);
-------------------------
-- Gather first set of samples
-------------------------
open statc;
loop
fetch statc into stat_row;
exit when statc%NOTFOUND;
stat:=stat_type(stat_row.name, stat_row.class, stat_row.value, 0);
stat_table.extend();
stat_table(stat_table.count):=stat;
end loop;
close statc;
As a temporary storage for the values a plsql-table is used.
Wait the specified number of seconds
------------------------- -- Wait for a certain amount of seconds ------------------------- dbms_lock.sleep(&_WAIT);
And get the second set of statistics
-------------------------
-- Gather second set of samples
-------------------------
open statc;
loop
fetch statc into stat_row;
exit when statc%NOTFOUND;
for s in stat_table.FIRST .. stat_table.LAST
loop
if stat_table(s).stat_name=stat_row.name
then
stat_table(s).value_end:=stat_row.value;
end if;
end loop;
end loop;
close statc;
Above the statistic is searched in the temporary plsq-table. When found the the table is updated with current value of the statistic (end_value).
Now display the results:
-------------------------
-- Display results
-------------------------
dbms_output.put_line(rpad('Class',64,' ')||rpad('Event',64,' ')||rpad('Start',20,' ')||rpad('End',20,' ')||rpad('Delta',20, ' '));
dbms_output.put_line(rpad('-',188,'-'));
for s in stat_table.FIRST .. stat_table.LAST
loop
tmp:='';
if(((stat_table(s).value_start!=stat_table(s).value_end)) AND (lower(stat_table(s).stat_name) like lower('%&_STAT%')))
then
if (bitand(stat_table(s).stat_class,1)=1)
then
tmp:='1 - User;';
end if;
if (bitand(stat_table(s).stat_class,2)=2)
then
tmp:=tmp||'2 - Redo;';
end if;
if (bitand(stat_table(s).stat_class,4)=4)
then
tmp:=tmp||'4 - Enqueue;';
end if;
if (bitand(stat_table(s).stat_class,8)=8)
then
tmp:=tmp||'8 - Cache;';
end if;
if (bitand(stat_table(s).stat_class,16)=16)
then
tmp:=tmp||'16 - OS;';
end if;
if (bitand(stat_table(s).stat_class,32)=32)
then
tmp:=tmp||'32 - RAC;';
end if;
if (bitand(stat_table(s).stat_class,64)=64)
then
tmp:=tmp||'64 - SQL;';
end if;
if (bitand(stat_table(s).stat_class,128)=128)
then
tmp:='128 - Debug;';
end if;
if (bitand(stat_table(s).stat_class,256)=256)
then
tmp:=tmp||'256 - Instance Level;';
end if;
dbms_output.put_line(
rpad(tmp,64,' ')||rpad(stat_table(s).stat_name,64,' ')||rpad(stat_table(s).value_start,20,' ')||rpad(stat_table(s).value_end,20,' ')||rpad(stat_table(s).value_end-stat_table(s).value_start,20,' ')
);
end if;
end loop;
end;
/
A statistic will be displayed if the end_value != start_value and the name of the statistic looks (like) the second argument.
The above code may seem a bit overkill, but this is a nice and clear way to decode the event-class.
Let ‘s see this in action.
I want to see the delta of the values of all statistics with “redo” in the name over an 10 second interval:
SQL> @sysstatd 10 redo Class Event Start End Delta ------------------------------------------------------------------------------ 2 - Redo; redo entries 120082856 120082866 10 2 - Redo; redo size 30992703104 30992704144 1040 2 - Redo; redo wastage 237539404 237543324 3920 2 - Redo; redo write active strands 655441 655451 10 2 - Redo; redo writes 652345 652355 10 2 - Redo; redo writes adaptive all 652345 652355 10 2 - Redo; redo blocks written 63171374 63171384 10 2 - Redo; redo write size count ( 4KB) 604304 604314 10 2 - Redo; redo write time 374823 374825 2 128 - Debug; redo write time (usec) 3748232491 3748253193 20702 128 - Debug; redo write gather time 11893458 11893492 34 128 - Debug; redo write schedule time 13409046 13409107 61 128 - Debug; redo write issue time 13632878 13632944 66 128 - Debug; redo write finish time 3746979859 3747000549 20690 128 - Debug; redo write total time 3765973402 3765994266 20864
Or maybe :
Over a 5 second interval I wat to see the delta of the values of all statistics with “get” in the name:
SQL> @sysstatd 5 get Class Event Start End Delta ------------------------------------------------------------------------------- 8 - Cache; consistent gets 1702844855 1702844863 8 8 - Cache; consistent gets from cache 1692928193 1692928201 8 8 - Cache; consistent gets pin 1255976491 1255976495 4 8 - Cache; consistent gets pin (fastpath) 255075190 1255075194 4 8 - Cache; consistent gets examination 436951702 436951706 4 8 - Cache; consistent gets examination (fastpath) 277730247 277730251 4 32 - RAC; calls to get snapshot scn: kcmgss 306882834 306882839 5 128 - Debug; no work - consistent read gets 1238951220 1238951224 4 PL/SQL procedure successfully completed. SQL>