{"id":1014,"date":"2006-07-10T12:00:00","date_gmt":"2006-07-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1014"},"modified":"2006-07-10T12:00:00","modified_gmt":"2006-07-10T12:00:00","slug":"being-open-minded","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/07\/10\/being-open-minded\/","title":{"rendered":"Being Open-minded"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

<\/p>\n

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 😉<\/p>\n

The following deadlock is not an ORACLE error. It is adeadlock due to user error in the design of an applicationor from issuing incorrect ad-hoc SQL. The followinginformation may aid in determining the deadlock:<\/pre>\n
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.<\/p>\n
However, as I looked at the trace file, this section bothered me …<\/p>\n
<\/p>\n
Deadlock graph:                       ---------Blocker(s)--------  ---------Waiter(s)---------Resource Name          process session holds waits  process session holds waitsTX-00310008-00004b13        43     847     X             47     649           STX-0027002d-000133e7        47     649     X             43     847           S<\/pre>\n
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.<\/p>\n
Eventually I started to suspect that this might be an ITL deadlock, as described in Metalink Note 62354.1<\/strong><\/a>. Oracle Support also suggested this might be the issue so this afternoon I used<\/p>\n
<\/p>\n
alter table table1 move initrans 6;alter index index1 rebuild online;alter index index2 rebuild online;<\/pre>\n
and we’ll see if we’ve fixed the problem.<\/p>\n<\/p>\n
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.? <\/strong><\/p>\n
Interestingly, when I was hunting around on Google for more info, a certain lilac-suited gentleman showed up<\/a>. 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 ….<\/p>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading Being Open-minded<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1014","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1028,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/20\/whats-a-development-dba\/","url_meta":{"origin":1014,"position":0},"title":"What’s a ‘Development DBA’?","date":"July 20, 2006","format":false,"excerpt":"I theory, this should be a much more straightforward, less contentious question than my previous - 'What's a Data Warehouse DBA?'A development DBA looks after the development (and test) databases. It truly is as simple as that, but depends on the nature of the project you're working on and the\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1042,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/04\/log-buffer-4\/","url_meta":{"origin":1014,"position":1},"title":"Log Buffer #4","date":"August 4, 2006","format":false,"excerpt":"Welcome to the fourth edition of Log Buffer.Maybe it's the summer holiday season and DBA-land is a little quiet, but navel-gazing seems popular this week. During a conference keynote speech by Ray Lane I attended earlier this year, he highlighted how much the software industry likes to talk about itself\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":948,"url":"http:\/\/orcldoug.com\/blog\/2005\/08\/22\/i-love-this-site\/","url_meta":{"origin":1014,"position":2},"title":"I *love* this site!","date":"August 22, 2005","format":false,"excerpt":"http:\/\/angrydba.com\/There's more to it than meets the eye and the purpose it serves depends what mood I'm in ...1) Reminds me of the worst aspects of some DBAs I still come across. I'll be able to point them to this from now on so that they can see how things\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":875,"url":"http:\/\/orcldoug.com\/blog\/2006\/01\/25\/application-security\/","url_meta":{"origin":1014,"position":3},"title":"Application Security","date":"January 25, 2006","format":false,"excerpt":"Some conversations are a recurring experience of DBA life.DBA [to software vendor] - 'So why does the application schema owner need to have the DBA role privilege?' Vendor - 'The installation procedure requires it' DBA - 'So can we revoke it after the installation?' Vendor - 'It hasn't been tested\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1011,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/04\/whats-a-data-warehouse-dba\/","url_meta":{"origin":1014,"position":4},"title":"What’s a ‘Data Warehouse DBA’?","date":"July 4, 2006","format":false,"excerpt":"I've seen that question asked often in forums and mail groups. It's easy to understand why there's so much confusion because, for most DBAs, if you've seen one database, you've seen them all. Or rather, you haven't seen any of them. What I mean is that every database that lands\u2026","rel":"","context":"With 14 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1529,"url":"http:\/\/orcldoug.com\/blog\/2009\/10\/14\/oow-2009-unconference-session-ha-dba\/","url_meta":{"origin":1014,"position":5},"title":"OOW 2009 – Unconference Session – HA DBA","date":"October 14, 2009","format":false,"excerpt":"At times like this I supposed I should (cough) tweet because, as Alex G pointed out to me last night, I'm far too late with this blog post so no-one will see it in time. Of course, if everyone would just shut the **** up and stop tweeting for 5\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1014","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1014"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1014\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1014"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1014"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1014"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}