Skip to Main Content
  • Questions

  • Query never finish without clear reason

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add 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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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