Wait_time and time_waited columns of v$active_session_history

Recently, I haven't touched informix,oracle tuning for a long time. Today, my friend asked me what I thought of v$active_session_history 's wait_time and time_waited. I only remember using it, but I didn't think of it at all. Hastily verified it.

The wait_time column, which is 0 for events that are waiting at the time of sampling. Corresponds to the session_state column. Lines that are not zero are doing CPU

The time_waited column is the waiting time for this wait. If the timeout of this type of waiting time is more than 1 second, then if the waiting time exceeds 1 second, the time_waited of this sample is 0. Set the waiting time when the waiting time is last sampled. Unit 1/1000000 seconds. In other words, a line with a time_waited of 0 does not need to be read, because the wait time is reflected later, and it is the same wait as the last line.

Here is a SQL that I used before

SELECT EVENT

COUNT (*) TOTAL_WAITS

TRUNC (SUM (TIME_WAITED) / 1000000) SECONDS_IN_WAIT

FROM V$ACTIVE_SESSION_HISTORY

WHERE SESSION_STATE = 'WAITING'

AND TIME_WAITED > 0

AND SAMPLE_ID BETWEEN 1800000 AND 2000000

AND SESSION_ID = 144,

AND SQL_ID = 'cgb71p2hy4zfj'

GROUP BY EVENT

In fact, v$active_session_history is the statistics of all the waits encountered by a statement while running, which can partly replace 10046 to view the waits encountered by SQL.