Skip to Main Content
  • Questions
  • Query never finish without clear reason

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Luiz.

Asked: October 14, 2020 - 6:04 pm UTC

Last updated: October 21, 2020 - 3:42 am UTC

Version: Oracle 12.1

Viewed 1000+ times

You Asked

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 Connor 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)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Stuck session

A reader, October 21, 2020 - 6:48 am UTC


As Connor suggested tracing will definitely help a lot.

But an active indication just does not cut the cake.

Maybe it is blocked by someone, resource manager throttling it, etc.

Check "event" in v$session that should give you an idea on what it is doing. V$SESSION_LONGOPS may be of help if it is doing a long running operation.

Hope it helps.

Cheers!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.