This is a special case. Oracle Forms uses multiple session contexts per
Oracle process and switches between them. And at the time of switching
between sessions in a process, the process itself is on CPU, doing the
switching. But later on when the "primary" application session becomes idle
by waiting for the next incoming message over SQL*Net, it doesn't clear
that other "secondary" session's wait state field. So V$SESSION lies to you
in this special case.
You can reproduce this test using autotrace as SET AUTOTRACE TRACE STAT
also creates a secondary session (for querying V$SESSTAT) so that the V$
queries would not cumulatively affect your application session's metrics.
Here's an example of querying all sessions by OS PID 30473 (sqlplus without
autotrace enabled yet):
SQL> l
1 SELECT sid,status,state,event,top_level_call_name,logon_time
2 FROM v$session s,v$toplevelcall c
3 WHERE s.top_level_call# = c.top_level_call#
4* AND paddr = (SELECT addr FROM v$process WHERE spid=*30473*)
SQL> /
SID STATUS STATE EVENT
TOP_LEVEL_CALL_NAME LOGON_TIME
---------- -------- ------------------- ------------------------------
-------------------- -------------------
14 INACTIVE WAITING SQL*Net message from client
FETCH 2018-07-27 14:41:14
Now enable autotrace:
SQL> -- SET AUTOTRACE TRACE STAT in the other session
SQL>
SQL> SELECT sid,status,state,event,top_level_call_name,logon_time
2 FROM v$session s,v$toplevelcall c
3 WHERE s.top_level_call# = c.top_level_call#
4 AND paddr = (SELECT addr FROM v$process WHERE spid=*30473*)
5 /
SID STATUS STATE EVENT
TOP_LEVEL_CALL_NAME LOGON_TIME
---------- -------- ------------------- ------------------------------
-------------------- -------------------
* 10* INACTIVE WAITED SHORT TIME SQL*Net message from client
SES OPS (80) 2018-07-27 14:53:45
*14* INACTIVE WAITING SQL*Net message from client
FETCH 2018-07-27 14:41:14
Suddenly there's another session (SID=10) that is owned by the same process
30473. Two sessions under the same process! The LOGON_TIME values are also
different as I enabled autotrace well after logging in with the primary
session.
While switching away from a session makes it correctly INACTIVE (see the "SES
OPS (80)" being the last OPI call before the session 10 became inactive),
the autotrace session 10-s STATE is left as WAITED SHORT TIME (which
usually means ON CPU) even after the primary session 14 went off CPU.
--
Tanel Poder
https://blog.tanelpoder.com
On Wed, Aug 1, 2018 at 10:33 AM Matt Adams <MAdams@xxxxxxxxxx> wrote:
I have multiple oracle forms connections to a 11.2.0.4 database.
The status is inactive, the state is ‘WAITED SHORT TIME’ (for “SQL*Net
from client”) and the SQL_ID is NULL.
The status implies that it is idle, but the state implies that it is
doing something (because it’s not waiting), but I can’t figure out what it
is doing.
Is there any way to determine what’s going on with this session?
Matt
**** This communication may contain privileged and/or confidential
information. If you are not the intended recipient, you are hereby notified
that disclosing, copying, or distributing of the contents is strictly
prohibited. If you have received this message in error, please contact the
sender immediately and destroy any copies of this document. ****
