{"id":531,"date":"2023-05-27T13:15:58","date_gmt":"2023-05-27T13:15:58","guid":{"rendered":"https:\/\/maboc.nl\/?p=531"},"modified":"2023-05-30T18:33:18","modified_gmt":"2023-05-30T18:33:18","slug":"system-statistics","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=531","title":{"rendered":"Oracle system statistics"},"content":{"rendered":"

Oracle System Statistics<\/strong><\/p>\n

When I look at the system statistics I’m not really getting any wiser. There a re really a lot of statistics:<\/p>\n

\r\nSQL> select count(*) from v$sysstat;\r\n\r\n  COUNT(*)\r\n----------\r\n      2252\r\n\r\nSQL>\r\n<\/pre>\n
Above from a Oracle 21c instance. I do not want to see them all.<\/p>\n
I would like to see the change in the statistics over time.
\nThere are probably a lot of ways which lead me to this, but I decided to make something myself.<\/p>\n
Script is to be found at: Github<\/a><\/p>\n
First get some explanation and input arguments in place:<\/p>\n
\r\n-- Name        : sysstatd.sql\r\n-- Author      : Martijn Bos\r\n-- Input       :     : the number of seconds to wait\r\n--                : name which the statistics to display should look like\r\n-- Description : Displays the delta for system statistics over time\r\n--               Only shows the statistics that changed in the interval\r\n--               Otherwise the list get way to long\r\n-- Usage       : @sysstatd  \r\n--\r\n--               Getting the delta in statistics named %get% in a 10 second interval\r\n--               @sysstat 10 get\r\n--\r\n--               Getting the delta in statistics for ALL statistics for a 60 second interval\r\n--               @sysstatd 60 %\r\n-- ---------------------------------------------------------------------\r\n-- 2023-05-26 : MB : Initial Version\r\n\r\n@settings\r\n\r\ndefine _WAIT=&1\r\ndefine _STAT=&2\r\n\r\n<\/pre>\n
Declare some types and variables<\/p>\n
\r\ndeclare\r\n  cursor statc is select sn.name,\r\n                         ss.class,\r\n                         ss.value\r\n                  from   v$sysstat ss,\r\n                         v$statname sn\r\n                  where ss.statistic#=sn.statistic#;\r\n\r\n  stat_row statc%ROWTYPE;\r\n\r\n  type stat_type is record(stat_name varchar2(100),\r\n                           stat_class varchar2(100),\r\n                           value_start number,\r\n                           value_end number);\r\n  stat stat_type;\r\n\r\n  type stat_table_type is table of stat_type;\r\n  stat_table stat_table_type:=stat_table_type();\r\n  tmp varchar2(128);\r\n<\/pre>\n
NOow gather the first set of statitics<\/p>\n
\r\nbegin\r\n  dbms_output.enable(100000);\r\n\r\n-------------------------\r\n-- Gather first set of samples\r\n-------------------------\r\n  open statc;\r\n\r\n  loop\r\n    fetch statc into stat_row;\r\n    exit when statc%NOTFOUND;\r\n\r\n    stat:=stat_type(stat_row.name, stat_row.class, stat_row.value, 0);\r\n    stat_table.extend();\r\n    stat_table(stat_table.count):=stat;\r\n\r\n  end loop;\r\n\r\n  close statc;\r\n\r\n<\/pre>\n
As a temporary storage for the values a plsql-table is used.<\/p>\n
Wait the specified number of seconds<\/p>\n
\r\n-------------------------\r\n-- Wait for a certain amount of seconds\r\n-------------------------\r\n  dbms_lock.sleep(&_WAIT);\r\n<\/pre>\n
And get the second set of statistics<\/p>\n
\r\n-------------------------\r\n-- Gather second set of samples\r\n-------------------------\r\n  open statc;\r\n\r\n  loop\r\n    fetch statc into stat_row;\r\n    exit when statc%NOTFOUND;\r\n\r\n    for s in stat_table.FIRST .. stat_table.LAST\r\n    loop\r\n      if stat_table(s).stat_name=stat_row.name\r\n      then\r\n        stat_table(s).value_end:=stat_row.value;\r\n      end if;\r\n    end loop;\r\n  end loop;\r\n\r\n  close statc;\r\n\r\n<\/pre>\n
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).<\/p>\n
Now display the results:<\/p>\n
\r\n-------------------------\r\n--  Display results\r\n-------------------------\r\n  dbms_output.put_line(rpad('Class',64,' ')||rpad('Event',64,' ')||rpad('Start',20,' ')||rpad('End',20,' ')||rpad('Delta',20, ' '));\r\n  dbms_output.put_line(rpad('-',188,'-'));\r\n\r\n  for s in stat_table.FIRST .. stat_table.LAST\r\n  loop\r\n    tmp:='';\r\n    if(((stat_table(s).value_start!=stat_table(s).value_end)) AND (lower(stat_table(s).stat_name) like lower('%&_STAT%')))\r\n    then\r\n      if (bitand(stat_table(s).stat_class,1)=1)\r\n      then\r\n        tmp:='1 - User;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,2)=2)\r\n      then\r\n        tmp:=tmp||'2 - Redo;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,4)=4)\r\n      then\r\n        tmp:=tmp||'4 - Enqueue;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,8)=8)\r\n      then\r\n        tmp:=tmp||'8 - Cache;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,16)=16)\r\n      then\r\n        tmp:=tmp||'16 - OS;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,32)=32)\r\n      then\r\n        tmp:=tmp||'32 - RAC;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,64)=64)\r\n      then\r\n        tmp:=tmp||'64 - SQL;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,128)=128)\r\n      then\r\n        tmp:='128 - Debug;';\r\n      end if;\r\n\r\n      if (bitand(stat_table(s).stat_class,256)=256)\r\n      then\r\n        tmp:=tmp||'256 - Instance Level;';\r\n      end if;\r\n\r\n      dbms_output.put_line(\r\n                            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,' ')\r\n                          );\r\n    end if;\r\n  end loop;\r\n\r\nend;\r\n\/\r\n<\/pre>\n
A statistic will be displayed if the end_value != start_value and the name of the statistic looks (like) the second argument.<\/p>\n
The above code may seem a bit overkill, but this is a nice and clear way to decode the event-class.<\/p>\n
Let ‘s see this in action.
\nI want to see the delta of the values of all statistics with “redo” in the name over an 10 second interval:<\/p>\n
\r\nSQL> @sysstatd 10 redo\r\nClass         Event                          Start        End           Delta\r\n------------------------------------------------------------------------------\r\n2 - Redo;     redo entries                   120082856    120082866     10\r\n2 - Redo;     redo size                      30992703104  30992704144   1040\r\n2 - Redo;     redo wastage                   237539404    237543324     3920\r\n2 - Redo;     redo write active strands      655441       655451        10\r\n2 - Redo;     redo writes                    652345       652355        10\r\n2 - Redo;     redo writes adaptive all       652345       652355        10\r\n2 - Redo;     redo blocks written            63171374     63171384      10\r\n2 - Redo;     redo write size count (   4KB) 604304       604314        10\r\n2 - Redo;     redo write time                374823       374825        2\r\n128 - Debug;  redo write time (usec)         3748232491   3748253193    20702\r\n128 - Debug;  redo write gather time         11893458     11893492      34\r\n128 - Debug;  redo write schedule time       13409046     13409107      61\r\n128 - Debug;  redo write issue time          13632878     13632944      66\r\n128 - Debug;  redo write finish time         3746979859   3747000549    20690\r\n128 - Debug;  redo write total time          3765973402   3765994266    20864\r\n\r\n<\/pre>\n
Or maybe :
\nOver a 5 second interval I wat to see the delta of the values of all statistics with “get” in the name:<\/p>\n
\r\nSQL> @sysstatd 5 get\r\nClass         Event                          Start        End           Delta\r\n-------------------------------------------------------------------------------\r\n8 - Cache;    consistent gets                        1702844855   1702844863    8\r\n8 - Cache;    consistent gets from cache             1692928193   1692928201    8\r\n8 - Cache;    consistent gets pin                    1255976491   1255976495    4\r\n8 - Cache;    consistent gets pin (fastpath)         255075190    1255075194    4\r\n8 - Cache;    consistent gets examination            436951702    436951706     4\r\n8 - Cache;    consistent gets examination (fastpath) 277730247    277730251     4\r\n32 - RAC;     calls to get snapshot scn: kcmgss      306882834    306882839     5\r\n128 - Debug;  no work - consistent read gets         1238951220   1238951224    4\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,91],"tags":[24,90,92],"class_list":["post-531","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","tag-oracle","tag-performance","tag-plsql"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/531","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=531"}],"version-history":[{"count":14,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/531\/revisions"}],"predecessor-version":[{"id":546,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/531\/revisions\/546"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=531"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=531"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=531"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}