Re: SQL_ID missing from v$session when session is "ACTIVE"
From: Nenad Noveljic <nenad.noveljic_at_gmail.com>
Date: Thu, 22 Jul 2021 18:53:40 +0200
Message-Id: <3C667255-FF11-4D31-9BA2-A0226FB2BDDC_at_gmail.com>
>> 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
>>
Date: Thu, 22 Jul 2021 18:53:40 +0200
Message-Id: <3C667255-FF11-4D31-9BA2-A0226FB2BDDC_at_gmail.com>
Hi Kyle,
Is the SQL in parse?
Can you get a stack?
Best regards,
Nenad
Von meinem iPhone gesendet
> Am 22.07.2021 um 17:54 schrieb Andy Sayer <andysayer_at_gmail.com>: > > > 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-lReceived on Thu Jul 22 2021 - 18:53:40 CEST