Re: Is an INACTIVE session executing a statement?
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: Kim Berg Hansen <kibeha@xxxxxxxxx>, Oracle-L oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Feb 2014 19:01:27 +0100
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
>>>>
>>>>
>>>>
>>>> On Wed, Feb 19, 2014 at 12:38 PM, Kim Berg Hansen <kibeha@xxxxxxxxx>wrote:
>>>>
>>>>> Nope, sorry - it does not seem to be the wait event.
>>>>> No idea, then ;-)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Regards
>>>>>
>>>>
Other related posts:
