I am aware that this is not a question for Toad, maybe more a client thing, but still… here is the problem:
I recently changed server for our hosted Oracle DB server. The new database server have 8 cores, 16 GB RAM, runs on Windows 2003R2 64bit with Oracle 64-bit 10.2.0.3.0. Everything is lightning-fast on this new server. Running scripts is just breathtaking.
Then I have an import/export server that I have also installed Toad on.
Server is Windows 2008R2 64-bit with 8 cores and 16 GB RAM. I have installed 32-bit Oracle client on that one, because I (maybe mistakenly) read somewhere that Toad need 32-bit.
I then installed Toad V10.6.
My problem now is that although Toad works, running scripts is so painfully slow that I generally give up. If a script has been lingering on for 20 minutes, I stop it and run it directly on server and it takes maybe 5 seconds!
Obviously something is wrong. Also running things on the client machine in sqlplus is slow, so it is not Toad that is the problem.
My questions:
Can this be because I am running a 32-bit Oracle client on a 64-bit OS that talks to a 64-bit Oracle? Should I try to install 64-bit Oracle client? Will Toad 10.6 still work?
If this has nothing to do with it, what can I check on client machine as well as on Oracle server to get the speed up? Remember that I am not talking about small differences, I am talking about scripts can be executed on server in 5 seconds, that on the client server takes 20 minutes. The servers are in the same subnet and have gigabit connection between them.
Any hints on how to fix this would be fantastic, thanks!
I tried to stop Symantec Endpoint Protection on the server, but it still is way too slow.
I tried with a simple “Select * from Company” on the client with Toad. It took 49 seconds.
Tried the same thing on Oracle server: 12 seconds.
In my old environment I didn’t have these differences at all, but then everything was 32-bit, not a mix like I have now.
Is there anything on server side that I can try to change. Right now I am letting Oracle decide SGA and PGA values automatically for instance. Target set to 7+7 GB = 14 GB of RAM out of 16. Can this have an effect on clients? Remember, server itself works like a bat out of hell…
Obviously something is! wrong. Also running things on the
client machine in sqlplus is slow, so it is not Toad that is
the problem.
My questions:
Can this be because I am running a 32-bit Oracle client on a
64-bit OS that talks to a 64-bit Oracle?
No.
Should I try to install 64-bit Oracle client?
You can, but Toad cannot use it yet. The reason being that the
development package that Toad is built with doesn't (yet) provide a 64
bit output, so Toad itself is 32 bit and therefore needs 32 bit Oracle
client.
Will Toad 10.6 still work?
Yes, but only if you leave the 32 bit client installed as well.
If this has nothing to do with it, what can I check on
client machine as well as on Oracle server to get the speed
up? Remember that I am not talking about small differences,
I am talking about scripts can be executed on server in 5
seconds, that on the client server takes 20 minutes. The
servers are in the same subnet and have gigabit connection
between them.
The difference between running "remote" and running "on server" are
simple, communications between the two locations. You can see this by
running a trace of a statement executed on one server and then again on
the other - on the remote session's trace file, you will most likely see
lots of SQL*Net message to/from client events in the WAIT lines of the
trace file.
You may well have Gigabit networking but are you sure it is actually
running gigabit - it may have knocked itself downs to 100 or even 10
megabits due to a configuration fault or a switch or router in the
network that itself is running at that speed and so on.
Your session on the server is also most likely running bequeath (or
whatever it is called this week) while remote has to be using TCP I
imagine. TCP always hits the network, even if running on the same
machine as the database, bequeath never hits the network.
You may be able to increate the size of the TCP packet payload with a
network type setting in your tnsnames.ora (I think!) TDU and/or SDU (or
something like that - it's early, I'm caffeine low at the moment!)
Any hints on how to fix this would be fantastic, thanks!
Any hints, like the ones above are pure guesswork. Once you have a trace
file with WAITS turned on (use dbms_support.start_trace_in_session(sid,
serial#, waits => true) and dbms_stop_trace_in_session(sid, serial#)
afterwards) you will know where the delays are.
Have fun.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk