Home » SQL & PL/SQL » SQL & PL/SQL » Query hanging
Query hanging [message #281154] Thu, 15 November 2007 22:22 Go to next message
gattu8883
Messages: 2
Registered: November 2007
Junior Member
Hi

i am running one SELECT query in database that query is hanging for infinte time untill i dont kill that session.

We are all using materialized views on that database on those same tables.Refreshing cycle is 5 min.

what can be the reason for that.
Re: Query hanging [message #281159 is a reply to message #281154] Thu, 15 November 2007 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>i am running one SELECT query in database that query is hanging for infinite time until i {don't?} kill that session.
In Oracle, readers do not block writers & writers do not block readers.

Post the SELECT that you claim is "hanging" along with details about ALL tables used by this SELECT statement.
Re: Query hanging [message #281202 is a reply to message #281154] Fri, 16 November 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate a trace on the session executing the query to see what it is doing.

Regards
Michel
Re: Query hanging [message #281517 is a reply to message #281202] Sat, 17 November 2007 19:39 Go to previous messageGo to next message
gattu8883
Messages: 2
Registered: November 2007
Junior Member
it is showing liabrary cache lock,actually oracle process was gathering some index statistics on those table.Can it create the hung problem,there was also a PGA leak
Re: Query hanging [message #281520 is a reply to message #281154] Sat, 17 November 2007 20:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
From a different window/session while the "hang" is happening do:
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

If rows are returned, you've identified the culprit.
If no rows are returned, then the search continues.
Re: Query hanging [message #281546 is a reply to message #281517] Sun, 18 November 2007 01:58 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, library cache lock may be the problem. Execute Ana's query.
Not the PGA leak (which is a bug in certain version which one you didn't provide with 4 decimals as requested in the guidelines).

Regards
Michel

[Updated on: Sun, 18 November 2007 01:59]

Report message to a moderator

Previous Topic: Package Errors
Next Topic: why i can get this error?
Goto Forum:
  


Current Time: Mon Dec 05 02:59:41 CST 2016

Total time taken to generate the page: 0.11033 seconds