Re: SQL_ID missing from v$session when session is "ACTIVE"

From: kyle Hailey <kylelf_at_gmail.com>
Date: Thu, 22 Jul 2021 18:40:13 -0700
Message-ID: <CADsdiQhoWPE-F+LvtSp62n=Ac3EMNtqwXCqtebaHkD=C17Xh0A_at_mail.gmail.com>

ASH helps some but still some missing pieces

SQL_ID SESSION P HP E PE PC B OTHER

• ------- -- -- -- -- -- -- -------------------

      ON CPU PA       ON CPU EX       ON CPU       ON CPU       ON CPU       ON CPU 4       ON CPU 4       ON CPU       ON CPU       ON CPU       ON CPU  select
 SQL_ID,
 session_state,

decode(IN_PARSE,'N','','PA')  P,
decode(IN_HARD_PARSE,'N','','HP') HP,
decode(IN_SQL_EXECUTION,'N','','EX') E,
decode(IN_PLSQL_EXECUTION,'N','','PE') PE,
decode(IN_PLSQL_COMPILATION,'N','','PC') PC,
decode(IN_BIND,'N','','BI') B,
decode(IN_CONNECTION_MGMT,'N',' ','1')||' '||
decode(IN_PLSQL_RPC,'N',' ','2')||' '||
decode(IN_JAVA_EXECUTION,'N',' ','3')||' '||
decode(IN_CURSOR_CLOSE,'N',' ','4')||' '||
decode(IN_SEQUENCE_LOAD,'N',' ','5')||' '||
decode(IN_INMEMORY_QUERY,'N',' ','6')||' '||
decode(IN_INMEMORY_POPULATE,'N',' ','7')||' '||
decode(IN_INMEMORY_PREPOPULATE,'N',' ','8')||' '||
decode(IN_INMEMORY_REPOPULATE,'N',' ','9')||' '||
decode(IN_INMEMORY_TREPOPULATE,'N',' ','0')  other

 from v$active_session_history where rownum < 1000   and session_state='ON CPU'
and SQL_ID is NULL;

On Thu, Jul 22, 2021 at 5:16 PM kyle Hailey <kylelf_at_gmail.com> wrote:

>
> yes, could be parsing.Will try to check with ASH. Was just sampling
> v$session myself.
>
> Kyle
>
>
> On Thu, Jul 22, 2021 at 8:54 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Some DDL won’t appear there, I’ve seen this with grants especially.
>>
>> Funnily enough, my first suggestion to Nenad in a recent thread would be
>> my first go to if you want to see the SQL (and that chain has a bunch of
>> other more advanced methods)
>>
>> Oradebug setospid <spid>
>> Oradebug current_sql
>>
>> Hope that helps,
>> Andrew
>>
>> On Thu, 22 Jul 2021 at 16:36, kyle Hailey <kylelf_at_gmail.com> wrote:
>>
>>>
>>>
>>> I'm used to SQL_ID missing from v$session when it's a background process
>>> like LGWR but for user sessions I always expect to find a SQL_ID for a
>>> session that is "ACTIVE".
>>> Anyone know of examples of why an user session would be missing SQL_ID
>>> when ACTIVE?
>>>
>>>
>>> select sid||':'||serial# session_id,
>>> username,
>>> s.sql_id||':'||sql_child_number sqlid,
>>> SQL_ADDRESS,
>>> SQL_HASH_VALUE,
>>> command,
>>> decode(state, 'WAITING', wait_class||':'||event, 'CPU') event
>>> from v$session s
>>> where
>>> (( s.wait_time != 0 /* on CPU */ and s.status='ACTIVE' /*
>>> ACTIVE */)
>>> or
>>> s.wait_class != 'Idle'
>>> )
>>> /
>>>
>>>
>>> SESSION_ID USERNAME
>>>
>>> ---------- ------------------------------
>>>
>>> SQLID SQL_ADDRESS
>>>
>>> ------------------------------------------------------ ----------------
>>>
>>> SQL_HASH_VALUE COMMAND
>>>
>>> -------------- ----------
>>>
>>> EVENT
>>>
>>>
>>> --------------------------------------------------------------------------------
>>>
>>> 36:13933 KYLELF
>>>
>>> : 00
>>>
>>> 0 0
>>>
>>> SQL*Net message from client
>>>
>>> CPU
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 23 2021 - 03:40:13 CEST