Re: Please Help: table locking

In article <914421$fmm$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
> In article <91431g$es9$1_at_nnrp1.deja.com>,
> Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
>
> > > 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
> mkrolewski_at_rosetta.org
> 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. Received on Mon Dec 11 2000 - 21:56:34 CST