Remote connection - affecting speed?

[Welshbird]

Hi

I connect to a 10.1.0.2.0 Oracle server which is in mainland Europe, whilst I'm in the UK.

The same queries take different times to run, by some margin (sometimes 3 minutes, sometimes an hour), and my techie department over 'there', tell me that this server is dedicated and no-one else is using to run things.

Does this mean that its the connection which is causing the delays? I don;t really understand how the Oracle architecture works, but I would have assumed that if I run a query typed on my laptop in the UK from the UK, then the 'work' is being done by the server in Europe, and I just get the end result. If this is not a 'select' query, and is not passing information back to me, can the connectiong be slowing it down?

(Our whole office is sharing the connection, as it also connects us to email, firewall, mainframe etc).

Any thoughts or explainations would be really appreciated - or if anyone can point me at a website which would help me that would be great too.

Thanks guys....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Thargy]

Fee,

hello again, welcome to 2006.

This could be a real tin of worms that I'm opening - sigh.

First of all, you need to separate out the connection issue and the "dedicated" server issue. I've come across so-called dedicated servers that were anything but.

Server Questions
Is the server really dedicated?
Although you may be the only user of the database on the server, is that really the only oracle instance on it? It's very unusual to have a server for just one instance. If other software (of any description) is running on that box, naturally it will affect performance.

Are there any batch jobs running?
For example, if you run a query at the same time as someone schedules a dbms_job, performance will likely suffer.

Are any routine large jobs running?

I believe it is seminal that you are not running a select query. since you set the DML running on the remote server, the connection has nothing to do with it, unless you are firing a huge sequence of insert statements across the network.

Can you be more specific about what exactly it is you're doing?

As a final word of advice, this is such a general question, that I doubt you'll find a web site to help. This sort of thing is why humans get paid to run computer systems.

Regards

John H



Grinding away at things Oracular

 

[Welshbird]

Well, I'm doing lots of different things really - as is the nature of the world...

My database holds lots of pharmaceutical data which is updated weekly, and I do basically whatever a client requests. Generally this is creating tables from select queries, in various formats - they may be group by summaries, or they may be created using analytical functions (especially ntile with partitions) and then when I have eventually finished with 'playing' I use SQL Tools to bring back the summaried version as a CSV and finish to deliver.

It does seem that maybe the dedication of the box is a little lacking (!), but as other things we use slow up based on the connection I just wondered really if this was relevant to Oracle too or not.

(I just remember in a past life doing similar things on an MS-SQL box in a remote domain, and that was always faster to use terminal services, and then to ru nthe query on QA on the remote box which was local to the DB server, rather than using QA local to me. - I just wondered if the same principle would apply to Oracle too).

Thanks again for your help and advice! And have a great 2006.

(Am just off to try and persuade the boss that more training would be good this year!)
x

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Thargy]

Fee,

I see.

I have used mstsc myself. I had thought of suggesting that you connect directly, and run the task locally. If the execution times all come out about the same, then the delay is bringing back the CSV file from Europe via the network.

If it's a whopper, then because you share the pipe with all the users for all of your data, this would elegantly explain the widely variable performance.

If you can run the same job locally a few times, this should prove enlightening.

Regards

John H

Grinding away at things Oracular