RE: Is an INACTIVE session executing a statement?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <martin.a.berger@xxxxxxxxx>, "'Jonathan Lewis'" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 19 Feb 2014 21:57:40 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@xxxxxxxxxxxxxxxxxx> 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
@jloracle
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Martin Berger [martin.a.berger@xxxxxxxxx]
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx
@kibeha
On Wed, Feb 19, 2014 at 12:42 PM, Kim Berg Hansen <kibeha@xxxxxxxxx> 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@xxxxxxxxx
@kibeha
Other related posts:
