Re: Oracle Processes Stalling Problem

Have you see in the directory Oracle_home\rdbms\trace :

• if there is any ORAxxxx.TRC file dated recently : perhaps oracle has some problems and then, generate a trace file.
• if in the sidALRT.LOG file if there is some errors ?

  ---

Have you tried to generate a complete trace file, you have two way to do it :

• after the connect operation, you do : ALTER SESSION SET SQL_TRACE=TRUE
• or in the INIsid.ORA file, you set SQL_TRACE=TRUE and then you shutdown and startup the database.

So, in the directory Oracle_home\rdbms\trace, files ORAxxx.TRC would be created. You have to use the TKPROF utility to to have <<good>> files. You will see your SQL statement and internal SQL statement (user SYS). Perhaps, it's a way to check all yours SQL. But, you will not have the commit or rollback statement.
Take care if the database is in trace mode, for each connection/transaction, you will have a file.
If you have 500 transactions, you will have 500 ORAxxx.TRC files !

Have you tried to see dynamically the lock's on the database during the transactions ?

SELECT * FROM V$LOCK;
...

Robert Pritchard a écrit:

> That is pretty much the format we use for each transaction, althouhg each
> process connects to the database when it is initially started rather than at
> each transaction.
> Each transaction commits after it is complete.
>
> There are no errors occuring that I can find (and I've sent the logs from the
> stalled database to Oracle support and they have not found anything either)
> such as resource busy
>
> The process that hangs doesn't crash or report any error, it just sits there
> stuck on its select.
>
> There are no foreign keys defined on the table.
>
> Thanks
> Rob
> (feel free to email me direct if you have any suggestions, as I am totally
> clueless!).
>
> Jean-Claude Matarazzo wrote:
>
> > 1. Perhaps, you have a problem of transaction, how do you do your
> > transaction :
> > __________________________________________________________
> >
> > CONNECT user/password
> >
> > select * from YOUR_TABLE where a_condition FOR UPDATE OF a_column NOWAIT;
> >
> > IF there is an error ORA-0054 : resource busy .. your record is locked.
> > ELSE
> > update YOUR_TABLE set a_column=new_value where a_condition ;
> >
> > COMMIT;
> >
> > The first transaction begin at the connection and ended with COMMIT or
> > rollback.
> > After doing a commit or rollback, another transaction begin...
> >
> > All the errors are returned, are you sure ?
> >
> > 2. Have you foreign keys on the table you want to lock ?
> > ________________________________________
> >
> > Robert Pritchard a écrit:
> >
> > > I am currently attempting to track down a very puzzling bug in a oracle
> > > accessing system.
> > > The problem is that after a certain period of time the entire thing
> > > hangs up. This is based on Oracle 7.2.3 running on a HP 10.20 box with
> > > all the relevant recommended patches installed.
> > > The system itself comprises of a total of 44 tables which are accessed
> > > by a number of different processes (the database access is done using
> > > Pro C). The stall alwasy seems to occur on a SELECT FOR UPDATE on a
> > > particular table. The table in question is simply a counter containing
> > > three rows, which are updated by three seperate processes (and accessed
> > > but not altered by a fourth) after they have completed their relevant
> > > tasks. Has far as I can tell there are no conflicting locks, Oracle
> > > itself returns no deadlock or other error and the problem still occcurs
> > > when the NOWAIT parameter is included.
> > > This has had myself and Oracle support stumped for a while now. I have
> > > sent them all the trace logs and relevant lock table output etc so it is
> > > nothing obvious.
> > > I would appreciate any insight anyone can offer.
> > > Thanks
> > > Rob
Received on Thu Jan 28 1999 - 14:19:07 CST