Ask The Real Tom

October 12, 2009



Oracle Deadlock ORA-00060 – Wait for Shared Lock

Filed under: oracle — Tags: , , — admin @ 2:11 pm

The last tree weeks i was hounting a ora-60 deadlock.
The Oracle Supoort claimed it was an application problem.
The Developer stated that it’s impossible an application problem.

Verfrifiend the developers statment proved me to believe him. A row lock was impossibel.
The Trace looked like this:


information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-001c0049-000614c5 92 196 X 85 413 S
TX-00140014-0006def8 85 413 X 92 196 S
session 196: DID 0001-005C-0000000E session 413: DID 0001-0055-00000015
session 413: DID 0001-0055-00000015 session 196: DID 0001-005C-0000000E
Rows waited on:
Session 413: obj - rowid = 000D1ED1 - AADR7RABFAAAxTmAAA
(dictionary objn - 859857, file - 69, block - 201958, slot - 0)
Session 196: obj - rowid = 000D1ED5 - AADR7YABQAAAn43AAA
(dictionary objn - 859861, file - 80, block - 163383, slot - 0)

This deadlock was caused by two update statements.
But if would be a row deadlock the session should wait for an X excluive row lock, not an S sahred lock.
If it would be a classic ITL lock the block would be in the undo tablespace.

On metalink (or the new bad my oracle support) did not help, oracle support could not help.
The nessesary infomation if found here:
http://pjsrandom.wordpress.com/2006/02/28/hunting-deadlocks-part-2/
http://oracledoug.com/serendipity/index.php?/archives/1014-Being-Open-minded.html

Solution:
The table had a initrans of 2 and pct_free of 0
Since I changed that and moved the table the deadlocks are gone.

October 8, 2009



Check for ITL waits problem

Filed under: oracle — Tags: , , — admin @ 2:46 pm
     SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
     FROM v$segment_statistics t
     WHERE t.STATISTIC_NAME = 'ITL waits' 
     AND t.VALUE > 0;

May 25, 2009



Something about dynamic_sampling

Filed under: Uncategorized — Tags: , , — admin @ 8:34 am

RDBMS: 10.2.0.4

Situation:

I hat deadlock on a partitoned table between a parallel insert and a parallel update statement. On the SR at Oracle statet first that this is not possibel. But on my testcase a have seen exclusive TM locks on partitons.
In the oracle documentation can be seen that locking works differently on parallel DML.

SR Result:

Oracle stated that the dynamic_sampling hint of the update statement is does not improfe the query perfomance, which was wrong. It does imporve the query speed in my case.

The more interesting statement was that dynamic sampling aquires a eclusive TM lock. – OK, that not good.

Powered by WordPress