Re: INITRANS and MAXTRANS are confusing me?

Following up on Richard Kuhler, 13 Mar 2003:

>
> I'll agree only if you qualify that statement to say 'as long as no
> block is out of transaction entries'. If there are 10 rows in a block
> and only room for 1 transaction entry then only 1 session can lock rows
> in that block. So Transaction 1 locks the first row and that's it, the
> rest of the rows cannot be locked now. All other transactions wanting
> to modify rows in that block must wait (or fail due to deadlock).

That,I'm sorry, is completely wrong. Or you are running a weird version of Oracle. OF COURSE you can have more than one process updating each a single row (or multiple rows) in a block. Provided they are not the SAME rows. It's so simple to prove I won't even bother. Otherwise it wouldn't be row locking! Something else is going "clunck".

INITRANS is a starting point, but the number of locks is dynamically expandable. What you probably have is very serious contention for the transaction entries, which is indeed a problem.

>
> As I said, we see it on a very regular basis in our large extremely
> parallel datawarehouse ETL processes.
>

You mentioned 60 odd processes updating, in another reply. I'd suggest you need to look at these things right up:

1- Look at your partitioning, why are those ETL parallel processes hitting all the same blocks in alternate sequience? They shouldn't. If like you say further on major processing is query and partitioning is geared for that, then look at the sequence of updating these rows. Avoid random updates, sort them out so they hit consecutive blocks. Across the parallel processes. There goes the deadlock out the window.

2- Set your FREELISTS and FRELIST GROUPS higher than default. That will take away a major cause of bottlenecking on blocks. You might have to consult with Jonathan for the optimal value. He claims that it should be a prime number and for good reason. But that might be version dependent. It shouldn't be a very high number, even at 60 odd updaters. This requires of course rebuild...

3- Once you licked the first two, then you jack up INITRANS just like Tanel said. That should be your last port of call for this type of tuning. Do it at same time as 2, to avoid a further rebuild.

> It's not obvious but you are missing something. Read Metalink
> Note:115467.1 "ORA-60 DEADLOCK DETECTED ON CONCURRENT INSERTS
> INITRANS/MAXTRANS". Basically transaction A has all the transaction
> entries from block 1 and is waiting on an entry from block 2 to free up,
> transaction B has all the transaction entries from block 2 and is
> waiting on an entry from block 1 to free up. They're deadlocked.

Ah, OK. Got it! Just an idea: have you tried looking at the sequence these updates are done? Ie, add some sorting to the incoming data for the ETL (maybe off-line?) and avoid the contention all together? I mean: your data is obviously ordered across blocks, at the very least on partition key order. There might be others you can use. Ascending of PK is always a good one, but not always possible. Timestamp? All you need is something that will sort the ETL load sequence so you always have block B being processed AFTER block A, no matter how many parallel ETL loads you have. Am I making this clear? I've tried this before in a VLDB with a similar problem and all deadlocks went away.

>
> All of our large tables are partitioned, but for query performance. We
> can't just try and stripe them to spread out the block update activity
> in an attempt to avoid transaction entry contention in the ETL process.
> The performance of the readers of the warehouse is far more important
> than ETL itself. If ETL fails we can resume it without much trouble but
> that doesn't mean it's not a nusance.

Agreed. I'd try to work around the order of arrival of the ETL first. Then look at rebuilding - which is always a nuisance...

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam