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

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