Hi,
One of the databases I'm working with is suffering from massive library cache contention (cursor: pin S, cursor: pin S wait on X, latch free totalling to about 85-90% of database time during 1-2 hour outbursts which happen once every few weeks).
ASH shows that most of these events are linked to sql id which doesn't exist anywhere: V$SQL, V$ACTIVE_SESSION_HISTORY, DBA_HIST_SQLTEXT etc. all showed nothing.
Today, I caught this problem in real time, and found the offending SQL. However, V$SQL is showing very odd figures for it, which explain why the SQL is not seen anywhere in the AWR report.
I wonder if anyone has seen anything like this and has an explanation and/or advice...
SQL_ID RUNTIME_MEM SORTS LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING FETCHES EXECUTIONS PX_SERVERS_EXECUTIONS END_OF_FETCH_COUNT USERS_EXECUTING LOADS
b5jgpar3b58xq 0 0 0 1 14 0 0 0 0 1 1
Best regards,
Nikolay