RE: Is an INACTIVE session executing a statement?
Date: Wed, 19 Feb 2014 21:57:40 -0500
Message-ID: <079e01cf2de7$8626d1a0$927474e0$_at_rsiz.com>
If you there is a tractable number of reports run this way it may be useful to dump the output to a file local to the server (possibly on a specific "shared externally" drive to minimize hacking) and then let them get the report via <pick your favorite file transport method>.
Without being exactly sure what state sessions stuck in a sql*net more data or message from client state is, I can tell you it certainly is in a VULNERABLE state. A time out here, a disconnection or bad packet there - hilarity is just waiting to happen. The fact is they probably don't even still need to be connected to Oracle at that point. This is essentially what reports from the E-Biz CCMGR were invented to solve.
Good luck,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Martin Berger
Sent: Wednesday, February 19, 2014 3:09 PM
To: Jonathan Lewis
Cc: Kim Berg Hansen; Oracle-L oracle-l
Subject: Re: Is an INACTIVE session executing a statement?
Jonathan,
thank you for the hint for v$open_cursor. As it's only in UGA, there is no help for me.
You are exactly describing the situation I want to identify: idle clients which stuck in pagination. - In the case which caused problems there where several parallel processes "used" by that session. I know how to get this special case (via v$px_session) - but curious now I'd like to get a wide and general answer.
Maybe x$kgllk helps me to answer this question. At least I'm (re) learning again.
Martin
On Wed, Feb 19, 2014 at 7:09 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
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> 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> 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> 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
kibeha_at_gmail.com
_at_kibeha
On Wed, Feb 19, 2014 at 12:42 PM, Kim Berg Hansen <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
kibeha_at_gmail.com
_at_kibeha
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 20 2014 - 03:57:40 CET