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.

>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.

>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.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Dave Haas wrote in message <92gsbn$i5n$1_at_news3.cadvision.com>...


>Just my two bits:

>

>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.

>

>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)

>

>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

>...

>

>Dave

>

>

>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

>news:978039211.5428.0.nnrp-07.9e984b29_at_news.demon.co.uk...

>>

>> I suspect this is down to ITL deadlocks on

>> index blocks.  Although the table updates

>> can be isolated quite easily by using rowid

>> ranges (I assume this is what your consultant

>> did) you may still find that index entries for

>> 8 different rows, being updated by 8 different

>> processes, are all in the same index leaf block.

>>

>> If this happens, and you don't have enough

>> space for Oracle to create 8 separate transaction

>> entries in the leaf block, one process has to

>> wait for another process to commit and release

>> an ITL (interested transaction list) entry.

>>

>> If this happens in a number of different blocks,

>> you are eventually likely to end up with a deadlock

>> on the ITL, even though no two processes are

>> interested in the same rows.

>>

>> In this sort of circumstance, you need to recreate

>> the relevant indexes with INITRANS=8 - to match

>> the 8 concurrent processes you are running.

>>

>>

>>

>>

>> --

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Practical Oracle 8i:  Building Efficient Databases

>>

>> Publishers:  Addison-Wesley

>> See a first review at:

>> http://www.ixora.com.au/resources/index.htm#practical_8i

>> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

>>

>>

>>

>> buckeye714_at_my-deja.com wrote in message <92ftga$jq4$1_at_nnrp1.deja.com>...

>> >One of the 8 process get's an ora-00060.  I have included the Deadlock

>> >graph from the trac file below:

>> >

>> >*** 2000-12-28 09:13:15.153

>> >*** SESSION ID:(27.30638) 2000-12-28 09:13:15.088

>> >DEADLOCK DETECTED

>> >Current SQL statement for this session:

>> >UPDATE CUST SET

>> >CONTRIB_01=:b1,CONTRIB_02=:b2,CONTRIB_03=:b3,GROSS_CONTRIB=:b4,PROF=:b4,

>> >CACC_01=:b1,CACC_02=

>> >:b2,CACC_03=:b3,TOTAL_CACC=:b9,EQUITY_01=:b10,EQUITY_02=:b11,EQUITY_03=:

>> >b12,TOTAL_EQUITY=:b13,ROE_01=:b14,RO

>> >E_02=:b15,ROE_03=:b16,TOTAL_ROE=:b17,ROE_SEGMENT=:b18,PRI_DEP_BAL=:b19,P

>> >RI_LN_BAL=:b20 WHERE ROWID = :b21

>> >----- PL/SQL Call Stack -----

>> >  object      line  object

>> >  handle    number  name

>> >b3730844       135  package body CPDEV.UPD_AGG_CUST

>> >b371b0f0         1  anonymous block

>> >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:

>> >Deadlock graph:

>> >                       ---------Blocker(s)--------

>> >---------Waiter(s)---------

>> >Resource Name          process session holds waits  process session

>> >holds waits

>> >TX-00060026-0000a001        35      27     X             36      25

>> >S

>> >TX-0004002b-00009e12        36      25     X             35      27

>> >S

>> >session 27: DID 0001-0023-00000002      session 25: DID

>> >0001-0024-00000002

>> >session 25: DID 0001-0024-00000002      session 27: DID

>> >0001-0023-00000002

>> >Rows waited on:

>> >Session 25: no row

>> >Session 27: no row

>>

>>

>>

>

>