Thanks for the question, Luiz.
Asked: October 14, 2020 - 6:04 pm UTC
Answered by: Connor McDonald - Last updated: October 21, 2020 - 3:42 am UTC
Category: Database Administration - Version: Oracle 12.1
Viewed 100+ times
Some times an aleatory select statement stop working without a clear reason.
Some times the statement is inside procedure, some times it is executed directly from ODAC FOR .NET 4
Then only thing in common it was always executed by ODAC client, so I never got this problem from one of my jobs !
When I check gv$sesion the session is active
When I check plan using DBMS_XPLAN.DISPLAY_CURSOR I it is the best one
When I simulate via pl/sql the query works fine with the very same plan
When I kill the session and execute again from the ODAC , the same problem happen
When I kill the session, SYS.DBMS_SHARED_POOL.PURGE(ADDRESS || ', ' || HASH_VALUE, 'C'), and execute again from the ODAC then BINGO the problem is SOLVED.
...however we know that it will happen again ...
What kind of evidence am I missing?
did you ever see this kind of behavior?
and we said...
Presumably it is "stuck" on something.
Next time this happens take a look at
- v$active_session_history for the session
- v$session_wait_history for the session
and maybe throw a trace on the session with dbms_monitor.session_trace_enable
That will let you dig deeper into whether it is actually doing productive work or perhaps "spinning" (ie stuck in a cpu loop)
and you rated our response