ORA-00060 self-deadlock started happening during well established queries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-00060 self-deadlock started happening during well established queries

  1. #1
    Join Date
    Apr 2007
    Posts
    3

    ORA-00060 self-deadlock started happening during well established queries

    In the last few days we have started getting ORA-00060 errors on queries used to populate summary tables in our data warehouse - an 8i databse on Windows 2000 v5 sp4. The problem seems to be caused when a large number of rows are selected and a group by clause included. There are no other sessions running and the trace file shows the session as locking itself out. My first thoughts are something to do with temporary sort tables, but don't know exactly what to look at. Any help most gratefully received.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2001
    Posts
    200
    Does it mean the group by clause has just been added? Does it work without the group by? is this a stand alone code or some pl/sql? Is an update somewhere in there?
    Life is what is happening today while you were planning tomorrow.

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    Check the Trace file that is generated, it will have more information regarding what was locked.

    When you perform a large select one thing that happens is a delayed block cleanout. This cuases locks to be taken out on the block. But a sql query such as yours should certainly run to completion without self deadlocking.

    Also self deadlocks could be due to a bug in oracle: (fixed in 8.1.7.4)
    2479110 - SELF DEADLOCK WAITING ON "LIBRARY CACHE PIN" ON AUTO RECOMPILATION OF PLSQL

    Try running utlrp.sql and re-executing your select.

  4. #4
    Join Date
    Apr 2007
    Posts
    3
    Ndisang

    Thanks for your reply. It has been running each week for the last year or so with no problems as part of a PL/SQL packaged procedure to populate a summary table from a load table. But I am able to reproduce the error by just running the query used in the insert into statement. The query contains a group by clause. If I remove the group by clause, it runs ok, returning 1766999 rows.

    Any ideas?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by FrancisB
    Ndisang

    Thanks for your reply. It has been running each week for the last year or so with no problems as part of a PL/SQL packaged procedure to populate a summary table from a load table. ...Any ideas?
    What changed?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Sep 2001
    Posts
    200
    Quote Originally Posted by FrancisB
    Ndisang

    Thanks for your reply. It has been running each week for the last year or so with no problems as part of a PL/SQL packaged procedure to populate a summary table from a load table. But I am able to reproduce the error by just running the query used in the insert into statement. The query contains a group by clause. If I remove the group by clause, it runs ok, returning 1766999 rows.

    Any ideas?
    Well you seem to answer your question...if you remove the group by, then it runs fine...Shouldn't the group by be the culprit then? Was the group by added recently?... I then you have the weigh the cost vs benefit of using the group by.
    Life is what is happening today while you were planning tomorrow.

  7. #7
    Join Date
    Apr 2007
    Posts
    3
    As far as I am aware, the only thing that has changed within the last week is the creation of a large table and related indexes in a new tablespace. The query has had the group by in since it was first implemented over a year ago. It's only just started to cause problems this week.

  8. #8
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    So what is the exact version of your database < 8.1.7.4?
    Assistance is Futile...

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There could be some simple root cause for this problem.

    My question is:
    Do you run the "GROUP BY" SQL after loading data into the big table?
    If yes, then run select count(*) from using full table scan. This will clean out dirty blocks. Finally, run the"GROUP BY" SQL.

    2 Option:
    If the table is big, then "GROUP BY" clause may create many extents in the temp tablespace. What's the extent size of your temp tablespace?
    If it's small, then drop and recreate with large extent size (16MB or 32MB). I'm not sure this solve your problem. Try it one time.

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width