Several useful views of Oracle Optimization

To retain the Session information more effectively, Oracle10g adds a v $ session_wait_history view to record the last 10 waiting events of the Active Session.

To retain the Session information more effectively, Oracle10g adds a v $ session_wait_history view to record the last 10 waiting events of the Active Session.

Purpose: Do not stick to the script to understand the meaning of the view, so that you can use them freely.
Including: V $ SYSTEM_EVENT/V $ SESSION_EVENT/V $ SESSION_WAIT/V $ WAITSTAT
#17389

V $ SYSTEM_EVENT (Statistics)
After the instance is started, all the statistical information generated (not displayed if there is no wait) waiting for event Events to wait for wait.
SQL> desc v $ system_event
Name
------------------------
EVENT
TOTAL_WAITS -- total number of waits
TOTAL_TIMEOUTS -- number of waiting timeouts
TIME_WAITED-total waiting time, in the unit of 1% seconds (most important)
AVERAGE_WAIT-average waiting time, total waiting time/total waiting times
TIME_WAITED_MICRO -- the total wait time in microseconds is 1/1000000

Common WAITS:
Latch free
Enqueue
Free buffer waits
Buffer busy waits: View V $ WAITSTAT
Db file sequential read
Db file scattered read

Script:
Select * from
(Select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
From v $ system_event order by TIME_WAITED desc)
Where rownum <21;

Discover bottlenecks
1. Statspack lists idle events at the end.
2. Examine the time spent waiting for different events.
3. Examine the average time for each wait also, because some waits (like log file switch completion) might happen only periodically, but cause a big performance hit when they happen.

V $ SESSION_EVENT (Statistics)
The field has one more sid than V $ SYSTEM_EVENT, providing statistics on all the events that have been waiting for each session. The currently waiting information is in v $ session_wait.

V $ SESSION_WAIT (current)
What is the waiting status for each active session (v $ session) of the system currently? (if no resources are waiting, the last waiting status is displayed)
• SID: session id
• EVENT: Event the session is currently waiting for, or the last event the session had to wait
• WAIT_TIME: Time (in hundredths of a second) that the session waited for the event; if the WAIT_TIME is 0, then the session is currently waiting for the event
• SEQ #: Gets incremented with every wait of the session
• P1, P2, P3: Wait event specific details for the wait
• P1TEXT, P2TEXT, P3TEXT: Description of P1, P2, P3 for the given event

WAIT_TIME Meaning Waiting
> 0 Time waited in the last wait (in 10 MS clock ticks) No
0 Session is currently waiting for this event Yes
-1 Time waited in the last wait was less than 10 MS No
-2 Timing is not enabled No

Script:
Select * from
(Select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
From v $ session_wait
Where SID = & THE_SID
Order by wait_time desc)
Where rownum <21;