Re: Please Help: table locking

From: <susana73_at_hotmail.com>
Date: Tue, 12 Dec 2000 03:56:34 GMT
Message-ID: <9147lh$idq$1_at_nnrp1.deja.com>


[Quoted] 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,

[Quoted] 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?

[Quoted] 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 [Quoted] long. The same update statements run pretty well couple thoudsand times [Quoted] until it got hang. So it's pretty weird.

Thanks,
Susan

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 12 2000 - 04:56:34 CET

Original text of this message