Tuning and Monitoring RMAN and the Automatic Diagnostic Workflow - OCP: Oracle Database 12c Administrator Certified Professional - page 259

3 Where sid in (select sid from v$session
4 Where program like '%rman%');
SID SERIAL# EVENT SECONDS_IN_WAIT
---------- ---------- ------------------------------ ---------------
121 269 RMAN backup & recovery I/O 2
129 415 SQL*Net message from client 63
130 270 SQL*Net message from client 8
Here you see that the backup-and-recovery I/O on SID 121 appears to be a problem.
It's been waiting 2 seconds, which is a long time for an I/O request. Note that the two
other wait events are considered idle waits and are likely not a problem. Later we might
run the query again and see something like this:
SID SERIAL# EVENT SECONDS_IN_WAIT
---------- ---------- ------------------------------ ---------------
121 269 control file sequential read 3
129 415 SQL*Net message from client 3
130 270 SQL*Net message from client 3
The control-file sequential read is now the main wait.
The V$SESSION view lists waits that are occurring at that moment. We could query
V$SESSION_WAIT_HISTORY and find out all waits for the session since it started, as shown here:
SQL> Select sid, event, wait_time
2 From v$session_wait_history
3 Where sid in (select sid from v$session
4 Where program like '%rman%')
5 And wait_time>0;
SID EVENT WAIT_TIME
---------- ------------------------------ ----------
121 RMAN backup & recovery I/O 11
129 SQL*Net message from client 1
129 SQL*Net message from client 2
129 SQL*Net message from client 2
130 SQL*Net message from client 400
130 SQL*Net message from client 200
130 SQL*Net message from client 100
130 SQL*Net message from client 766
This gives us the cumulative wait times for a given session. We might wait for a few
moments and run the query again. Perhaps we would get these results:
SID EVENT WAIT_TIME
---------- ------------------------------ ----------
121 RMAN backup & recovery I/O 85