Question: What does the v$session
column last_call_et do?
Answer: According to Oracle, the
last_call_et column of v$session
"represents the elapsed time (in seconds) since the session
has become active. . . .If the session status
column is currently ACTIVE, then the value of
last_call_et represents the elapsed time in seconds
since the session has become active. If the session
status column
is currently INACTIVE, then the value of
last_call_et represents the elapsed time in seconds
since the session has become inactive."
In
plain English, last_call_et will tell you how long
a session has been running (in wall clock seconds), but this
depends on the setting for the optimizer_mode parameter (as
seen in the v$sql_plan view). If it is
optimizer_mode=all_rows (the default), the
last_call_et will reflect the total elapsed time, while
if you are using first_rows, the last_call_et
will re-set for each change in status. Here is a
query using last_call_et:
select
ses.username,
ses.machine,
ses.program,
ses.status,
ses.last_call_et,
sql.hash_value,
sql.sql_text
from
v$session ses,
v$sql
sql
where
ses.sql_hash_value = sql.hash_value
and
ses.type = 'USER';
If you are
running a DML (insert, update, delete) last_call_et
tells you how long an update has been running.
Of
course, a session many issue many DML and SQL statements so
care must be used when interpreting the last_call_et
column.
