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.

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?

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.

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?

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?

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.

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.

So what is the exact version of your database < 8.1.7.4?

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.

All times are GMT -4. The time now is 10:26 PM.