Database Locks When accessed from a remote client

[TsiouTsiou]

We have an oracle database which is being updated using an oracle form very often. We are using Oracle Forms 6i and Oracle 8.0.6. The in-house users can update the database simultaneously without any data locks that cause delays. However, we have some remote users in a different country that access out database via internet. When they update data (they update the same size of data as the local users) they cause long delays that make local users unable to run their updates because they have to wait for very long times.

Because all these users , local and not, access the same table, I cannot understand why there aren;t any delays when only the local users update and they only occur when the remote users access the system. any help will be very appreciated.
Thanks

 

[Turkbear]

Hi, Check out the latency of their remote connections..Since Oracle 'waits' for a response from the client, any delay in the communications stream will slow that transaction down causing longer-lasting locks.

 

[TsiouTsiou]

Thanks for that. How can I check the latency? Is there a way of doing this?
One solution I thought of is to create a new table that will be only used by the remote users. Do you think this will solve the problem?

 

[Turkbear]

Hi,
Try pinging the remote locations and check the time elapsed
until a return..
Then have the remote locations try a tnsping and report the round trip time.

This will vary greatly however, since it is affected by everything 'in the pipe' between you and them...

I'm sure there are better methods, check with your network admins...

 

[sem]

Is it producable behaviour? I think that somebody on that site may start updating records and then just closes form incorrectly (or looses HTTP connection) without commiting changes. After some period this DATABASE connection will be released on application or database server side, but this period may depend on your settings. Can you check whether anybody's really working when you suffer from locked rows?

Regards, Dima

 

[TsiouTsiou]

Hi, it seems to be that the table is locked when sql loader is run. How can I change the behaviour of loader so that the table does not lock? Currenlty I am using direct path, what is the syntax to use conventional path instead?

 

[sem]

Just omit DIRECT parameter.

Regards, Dima

 

[TsiouTsiou]

What I use at the moment is

sqlldr80 control=loader.ctl userid=itmv1/itmv1@devel_ufs

I am not using the direct parameter at all. Should I just add DIRECT=FALSE at the end?
And using conventional path will it make the load slower, but the table willbe accessible i.e. not locked during this time?

Thanks.

 

[sem]

So why did you say that you used direct path??? Apparently you didn't. And after all your posts I'm not quite sure you have locks at all. Did you check v$lock view or it's just you assumption?

Regards, Dima

 

[TsiouTsiou]

What I wrote is what I have been told myself and I am trying to solve a problem I don't know a lot about. Apparently when this load is run from remote users the table locks. I haven't seen it happening myself. I thought that it was direct load because I thought that direct=true is set by default, sorry for the mistake.
I am trying to gather as much info as possible maybe from people who have come across this problem before.
Thanks!

 

[sem]

So how about locks? Dozen of other reasons may cause performance to degrade.

Regards, Dima