DBMS_SYSTEM.KCFRMS

 I tested this 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>