have an index this will cause a table lock on the referenced table. So
table lock and cause probs.
> Oracle does row level locking so from the looks of your statements it
> seems a bit unlikely that these two statements are locking each other
> out. You should also check v$session_wait to figure out what your
> session is waiting on. It will show an 'enqueue' wait if it is indeed
> waiting on the other row. If one is indeed waiting on the other then
> I'm betting with the earlier comment that you have a corrupted index.
> The following query will show you if you the 'blocker' if you do have
> one query blocking another.
> column lock_type format a24
> column blocking_others format a20
> column mode_held format A20
> select session_id,
> lock_type,
> mode_held,
> blocking_others
> from sys.dba_lock
> where blocking_others != 'Not Blocking'
> /
> The following query will show those being blocked:
> column lock_id1 format a8
> column lock_id2 format a8
> break on lock_id1 skip
> select lock_id1,
> lock_id2,
> session_id
> from sys.dba_lock
> where blocking_others = 'Not Blocking'
> and lock_type = 'Transaction'
> group by lock_id1, lock_id2, session_id
> /
> That should take care of your problem.
> Geoffrey Bray
> > > > > There are 800k rows. Tablespace is 20GB. Extents of the
> 'object'
> > > > table
> > > > > is 2000 extents(i know this is not optimized and I'll fix this
> > > later).
> > > > > There is a combined PK on object(id,plan). There are no other
> > users
> > > > on
> > > > > line. To update one row should take a second, so I am sure it
> > > hangs.
> > > > > Thanks!
> > > > > Susan
> > > > > Sent via Deja.com http://www.deja.com/
> > > > > Before you buy.
> > > > wow. A puzzler. Best guess is that you have corrupted indexes on
> the
> > > > table. You must be adding data to the system quite regularly, so I
> > am
> > > > assuming that this table has a lot of transactions against it. Are
> > you
> > > > doing any deletes? or other updates? How often? How many? Are
> there
> > > any
> > > > other indexes on the table?
> > > > I am thinking you could drop all indexes for this table and
> rebuild
> > > > them. With 800K rows, this could take some time.
> > > > Another idea is run explain plan on the query. It should pick up
> the
> > > > index. If it does not use the index, try analysing the table.
> > > > --
> > > Here is a strange idea. You start each of these updates and then the
> > > application hung, so you quit the application on PC/desktop. Then
> you
> > > tried this again, etc. Check the users active in you system. You may
> > > have a whole bunch of users all logged in still. Often when a user
> > > terminates his program in a less than orderly fashion, Oracle
> > maintains
> > > the connection for some time. If you find these users, try killing
> > > these sessions.
> > > --
> > > Michael Krolewski
> > > Rosetta Inpharmatics
> > > Ususual disclaimers
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > Hi Michael,
> > Thanks for the ideas. However, I think it's a locking problem as the
> > query on v$lock table clearly shows a lock. Would a bad index cause
> > table lock?
> > The other strange thing is it doesn't hang all the time, only couple
> > times a week. My application does update data on 'object' table all
> day
> > long. The same update statements run pretty well couple thoudsand
> times
> > until it got hang. So it's pretty weird.
> > Thanks,
> > Susan
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> Sent via Deja.com
> http://www.deja.com/
by