RE: Is an INACTIVE session executing a statement?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Martin Berger [martin.a.berger_at_gmail.com] 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 11.2.0.4 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.

Martin

On Wed, Feb 19, 2014 at 1:33 PM, Kim Berg Hansen <kibeha_at_gmail.com<mailto:kibeha_at_gmail.com>> 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 ;-)

/Kim

On Wed, Feb 19, 2014 at 1:15 PM, Martin Berger <martin.a.berger_at_gmail.com<mailto:martin.a.berger_at_gmail.com>> 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 11.2.0.4 environment.

Nevertheless thank you for the tests and response!

Martin

On Wed, Feb 19, 2014 at 12:46 PM, Kim Berg Hansen <kibeha_at_gmail.com<mailto:kibeha_at_gmail.com>> 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:

BANNER



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

Hope that is of help to you ;-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com<mailto:kibeha_at_gmail.com>
_at_kibeha

On Wed, Feb 19, 2014 at 12:42 PM, Kim Berg Hansen <kibeha_at_gmail.com<mailto:kibeha_at_gmail.com>> 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.

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com<mailto:kibeha_at_gmail.com>
_at_kibeha

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

Regards

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 19 2014 - 19:09:31 CET

Original text of this message