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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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