Tuesday, November 04, 2014

DBMS_SYSTEM.KCFRMS

I read about this in the book advertised at the end of this post. It was tested on Oracle 11.2. V$SESSION_EVENT holds similar information to V$SYSTEM_EVENT but it is broken down by session (only currently logged in sessions appear - there is no history). There is a MAX_WAIT column which shows the maximum time a session has had to wait for a particular event.There is no timestamp on this so you cannot tell when the longest wait took place. However, if you have a session which is about to start another step in a process, you can zeroise MAX_WAIT so you know the maximum wait time in that step once it has finished. This zeroises all MAX_WAIT values for all events in all sessions. It also resets MAXIORTM and MAXIOWTM in V$FILESTAT. I decided to give it a try. First I checked the current values:

SQL> conn / as sysdba
Connected.
SQL> select sum(max_wait) from v$session_event
  2  /
 
SUM(MAX_WAIT)
-------------
      1323729
 
SQL> select sum(maxiortm), sum(maxiowtm)
  2  from v$filestat
  3  /
 
SUM(MAXIORTM) SUM(MAXIOWTM)
------------- -------------
         2972          8171
 
SQL>

Then I ran the command to zeroise them:

SQL> exec dbms_system.kcfrms();
 
PL/SQL procedure successfully completed.
 
SQL>

Finally I checked the figures again:

SQL> select sum(max_wait) from v$session_event
  2  /
 
SUM(MAX_WAIT)
-------------
            0
 
SQL> select sum(maxiortm), sum(maxiowtm)
  2  from v$filestat
  3  /
 
SUM(MAXIORTM) SUM(MAXIOWTM)
------------- -------------
            0             0
 
SQL>

No comments: