Re: Is an INACTIVE session executing a statement?
Date: Wed, 19 Feb 2014 21:08:32 +0100
Message-ID: <CALH8A916yVr+cib2OxuQpqLmot8Q4u2T+15wQBu3=dxCuQQ0Kw_at_mail.gmail.com>
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
>>>>
>>>> http://dspsd.blogspot.com
>>>> 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
>>>>>
>>>>> http://dspsd.blogspot.com
>>>>> kibeha_at_gmail.com
>>>>> _at_kibeha
>>>>>
>>>>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 19 2014 - 21:08:32 CET