Being Open-minded

I think that one of the most important skills of a good DBA and one of the most difficult to maintain as your experience grows is to stay open-minded.

When I got into work this morning, there was an email waiting for me from a frustrated developer who wanted to know why one of the production batch jobs had failed twice in the past week with an ORA-00060 Deadlock detected error. He'd asked around a number of DBAs and wasn't getting too much joy out of them. I can understand that because deadlocks are normally down to poor application code and there's little a DBA can do to fix that after the fact. I said I'd see what I could do, though, and dug out the trace file that's generated automatically to have a look.

I emailed him the trace file with my comments. That's another thing that DBAs don't do often enough in my opinion - give the user more technical information so that they become more informed themselves and be part of any decision making. If your customers are developers then, the usual jokes aside, they're not usually idiots and should be able to understand well-explained fundamentals. This particular developer really appreciated the chance to see the trace file and it was no effort at all for me to supply it. It also helped that I could show him this section of the trace file

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

I admit my comments were very much along the lines of 'Not an Oracle problem .... bad application code .... Oracle will tell you the same.' I offered to open an SR with Oracle support though because this is an important job and saying 'bad code - tough' isn't a reasonable response.

However, as I looked at the trace file, this section bothered me ...

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00310008-00004b13        43     847     X             47     649           S
TX-0027002d-000133e7        47     649     X             43     847           S

If the code was at fault, I expected to see both sessions waiting on an X mode lock, but instead they were both waiting on S mode. I thought about it some more, did some more research on Metalink and thought about the batch job, which is run as 6 concurrent streams.

Eventually I started to suspect that this might be an ITL deadlock, as described in Metalink Note 62354.1. Oracle Support also suggested this might be the issue so this afternoon I used

alter table table1 move initrans 6;
alter index index1 rebuild online;
alter index index2 rebuild online;

and we'll see if we've fixed the problem.

Missing information added later .... This is a 9.2.0.7 instance and the value of INITRANS prior to the fix was? the default of 1. PCTFREE was left at the default value of 10, but might be increased later if this is an ongoing problem.?

Interestingly, when I was hunting around on Google for more info, a certain lilac-suited gentleman showed up. I'm sure he'll be delighted to be near the top of the list for something technical However, it's a shame I didn't remember these blogs before working this out for myself ....