RE: Is an INACTIVE session executing a statement?

From: Jonathan Lewis <>
Date: Wed, 19 Feb 2014 18:09:31 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDE04F_at_exmbx05.thus.corp>

What you really need to be able to do is query v$sql_cursor to see the state of the cursor - the problem is that v$sql_cursor is a local view not a global one, so you can only query it when your session is not executing another statement.

The open/close problem may be because a session (from SQL*Plus) will do lazy closing - SQL*Plus doesn't close the current cursor until it opens the next one - on top of which you have possible interference effects from the session cursor cache anyway. (v$open_cursor / x$kgllk - where the cursor is held open because it has been executed enough times.)

One question you have to ask is "when is a session TRULY idle ?" and the answer is not obvious.

If you write some code to open a cursor and fetch the first row, how does the server know that you are not planning to fetch the next row almost immediately ? How can it spontaneously time out if you don't fetch the next row after five minutes ? From Oracle's perspective you are inactive because the server-side process is not in a database call (it's in SQL*Net message from client) but the cursor is in the FETCH state because there is no other state it can be in.

Jonathan Lewis

From: [] on behalf of Martin Berger [] Sent: 19 February 2014 18:01
To: Kim Berg Hansen; Oracle-L oracle-l
Subject: Re: Is an INACTIVE session executing a statement?

after some testing with Kim Berg Hansen I can only say I'm having a strange observation: in my env v$session keeps (sometimes) SQL_ID even the statement is closed. We compared v$session view for and 12.1 but there is no substantial difference there. Even Kim could not reproduce the problem, he always saw the SQL_ID disappear after the statement completed.

Maybe I'm hunting a different problem; at the moment I can not give more details to hunt down my observations.


On Wed, Feb 19, 2014 at 1:33 PM, Kim Berg Hansen <<>> wrote: Hmmm...

My tests used a table with 280 enames in it (emp cross join 20 dummy rows) - I wanted something big enough to be sure sql-plus didn't fetch it all in one go so PAUSE would keep it in "fetching" state.

Either there's some inconsistency in updating 11.2 x$ structures for sql_id that was "fixed" in 12.1. Or the view v$session has been "fixed" in 12.1 - there's a possibility (just barely) you might find something if comparing v$session source from 11.2 to 12.1 - maybe some filtering on a column in some x$ table has been added in 12.1? Or something completely different ;-)


On Wed, Feb 19, 2014 at 1:15 PM, Martin Berger <<>> wrote: Hi Kim,

I have kind of inconsistent results regarding SQL_ID:

for the statements "select * from dba_objects;" and "select sysdate from dual;" I still have sql_id PRESENT for this session, even when the statement is finished (which I assume when the Prompt is back to "SQL>"); but the statement "select sql_id from v$session where sid=551"(in the test-sqlplus!!) wiped out sql_id and it's empty afterwards. So this is kind of inconsistent in my environment.

Nevertheless thank you for the tests and response!


On Wed, Feb 19, 2014 at 12:46 PM, Kim Berg Hansen <<>> wrote: And further, when the SQL*Plus session has fetched the last rows but is still pausing output, the SQL_ID does become null. At least this seems to be the behaviour on:


Oracle Database 12c Enterprise Edition Release - 64bit Production PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production NLSRTL Version - Production

Hope that is of help to you ;-)


Kim Berg Hansen<>

On Wed, Feb 19, 2014 at 12:42 PM, Kim Berg Hansen <<>> wrote: Aha! Look at v$session.SQL_ID

At least in my test it was populated as long as the session is fetching and null when it is done and truly idle.


Kim Berg Hansen<>

On Wed, Feb 19, 2014 at 12:38 PM, Kim Berg Hansen <<>> wrote: Nope, sorry - it does not seem to be the wait event. No idea, then ;-)


Received on Wed Feb 19 2014 - 19:09:31 CET

Original text of this message