From: susana73@hotmail.com
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools
Subject: Re: Please Help: table locking
Date: Tue, 12 Dec 2000 03:56:34 GMT
Organization: Deja.com - Before you buy.
Lines: 76
Message-ID: <9147lh$idq$1@nnrp1.deja.com>
References: <913s5g$9dm$1@nnrp1.deja.com> <913tjl$aks$1@nnrp1.deja.com> <9142a6$ebk$1@nnrp1.deja.com> <91431g$es9$1@nnrp1.deja.com> <914421$fmm$1@nnrp1.deja.com>
NNTP-Posting-Host: 209.0.11.254
X-Article-Creation-Date: Tue Dec 12 03:56:34 2000 GMT
X-Http-User-Agent: Mozilla/4.75 [en] (WinNT; U)
X-Http-Proxy: 1.0 x62.deja.com:80 (Squid/1.1.22) for client 209.0.11.254
X-MyDeja-Info: XMYDJUIDsusana73


In article <914421$fmm$1@nnrp1.deja.com>,
  Mike Krolewski <mkrolewski@rosetta.org> wrote:
> In article <91431g$es9$1@nnrp1.deja.com>,
>   Mike Krolewski <mkrolewski@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@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.