Hi All,
Oracl 19c
When using v$active_session_history generally count(*) is used for elapse time. Why sum(time_waited) doesn't give the correct result? The sql query run in 9 secs.
Why SUM(TIME_WAITED)/1000000 doesn't give approximately 9 secs?
When using DBA_HIST_ACTIVE_SESS_HISTORY should we multiply with 10?
TIME_WAITED
NUMBER
If SESSION_STATE = WAITING, then the time that the session actually spent waiting for that event (in microseconds). This column is set for waits that were in progress at the time the sample was taken.
If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.
SELECT
a.SESSION_STATE,
a.SESSION_TYPE,
a.WAIT_TIME,
a.TIME_WAITED,
a.event,
a.SQL_PLAN_OPERATION,
a.SQL_PLAN_OPTIONS
FROM v$active_session_history a where a.SESSION_ID = 522 and a.SESSION_SERIAL# = 46615;
WAITING FOREGROUND 0 80111 db file scattered read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 4419 db file sequential read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 3952 db file scattered read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 68741 db file scattered read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 6695 db file scattered read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 131303 db file sequential read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 101160 db file scattered read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 153635 db file sequential read INDEX FAST FULL SCAN
WAITING FOREGROUND 0 6815 db file sequential read INDEX FAST FULL SCAN
SELECT
count(*), sum(a.WAIT_TIME), sum(a.TIME_WAITED)
FROM v$active_session_history a where a.SESSION_ID = 522 and a.SESSION_SERIAL# = 46615;
9 0 556831
Thanks in advance