Re: Dealock on Rollback Segment (I think)

>
> >Recreating the indexes (or any indexes AT ALL) are a BAD idea in this
 case.
> >If the app does in fact update every row then index maint. is going
 to be
> >HORRENDOUS.
>
> This could certainly be taken as a useful hint - i.e. consider
> dropping any heavily affected indexes on the table before
> doing the update then rebuild them afterwards.
>

I will approach our developers and see if this is possible and bebeficial.

> >As far as I know MAXTRANS still defaults to 255, so that's probably
 not it
> >unless the consultant specifically over-rode it (it might help to see
 the
> >table creation script)
>
> There is USUALLY less risk of a low initrans on an index causing
> a problem, but if its blocks are well packed before the 8 processes
> start running, then the fact that a block is allowed to have up to
> 255 ITL entries is irrelevant - there may simple be insufficient room
> to create them - it would be interesting to know whether the 8
 processes
> did one big transaction each (which would increase the risk
 significantly)
> or a number of small rowid ranges.
>

Each of the 8 process is commiting periodically. I believe every 10,000 rows. So, they are doing their group in smaller chunks. And yes, maxtrans for the table and indexes is 255. I just checked the indexes, and they were created with a pctfree of 1. This would lead me to believe that the blocks are well packed.

> >My guess is that the partitioning did not work on ROWID range like
 the
> >consultant thought it did. Again, it would help to see the table
 creation
>
> Always the first thing to suspect - but I was giving the consultant
 and
> the poster the benefit of the doubt and assuming that they had done
> some careful testing. It is also quite an easy thing to do anyway and
> fairly well documented.
>
> The main reason for thinking that it is not a data deadlock is in the
> last three lines that I copied:
>
> >> >Rows waited on:
> >> >Session 25: no row
> >> >Session 27: no row
>
> If the table were involved, the two rowids causing the problem should
> appear here.

These processes were run a number of times, over a two week period, in our test database. The problem first occured in our production database. The test database was created on a seperate server from an OS backup of the production server. The tables and indexes are identical. The only differences would be the current data in production and the other production activity that was not present in the test environment.

It is the fact shown above, that no row is involved in the deadlock, that has me puzzled. Additionaly, according to OEM Top Sessions, the lock causing the problem is being held on a rollback segment. I am not sure if that is normal or if it could provide a clue as to what the problem is.

Thanks for your continued help.

Patrick

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 29 2000 - 08:25:09 CST