Re: state vs status

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Thu, 2 Aug 2018 17:26:20 -0400
Message-ID: <CAMHX9JJso-qVou6bT3C7N9YQXPmxbjMD+Vbh9z9d6GJ-HaqscQ_at_mail.gmail.com>

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_at_equian.com> 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. ****

>


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 02 2018 - 23:26:20 CEST