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

Hi all,

There is also new action "trace" (oradebug doc action trace), so you can do like this:
oradebug event immediate trace("&_DATE sqlid [%] shortstack: %\n", sqlid(),shortstack())

But looks like it works just once and all repeated executions do not write to trace. At least on 19.11... don't know why...

On Fri, Jul 23, 2021 at 3:17 AM 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
>>>
>>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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