INSERT INTO ... SELECT FROM t@dblink [message #592075] |
Fri, 02 August 2013 21:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Hi all,
I have a general question about using
INSERT INTO t
SELET *
FROM t@dblink
WHERE id = :a;
When I run this from two different servers using the same server for the link on both tests, which is external to both local servers, I get dramatically different performance, over an order an magnitude difference. Both local servers are identical hardware. The only difference is the physical location of the servers. This runs quickly when the local and remote servers are in the same server room in Silicon Valley. It runs slow when the local server is in London and the remote server is in Silicon Valley. I suggested to our systems and networking people that it has to be something about the networking. They assure me, however, that all the metrics on the network are within acceptable ranges. I know nothing about networking and only slightly more about system configurations or database administration. I am just an applications engineer who tries to push data around. Does anybody happen to know of anything Oracle related that could be causing the differences in performance and point me somewhere that would help me understand how to test for that? I'm not looking for anybody to do the work for me, just point me in the right direction, because I am at a loss.
Thanks,
Scott
|
|
|
|
|
Re: INSERT INTO ... SELECT FROM t@dblink [message #592082 is a reply to message #592075] |
Sat, 03 August 2013 02:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Another simple test:set autotrace on
INSERT INTO t
SELET *
FROM t@dblink
WHERE id = :a;
set autotrace off
that will show you some execution statistics which will indicate how many disc and memory reads were required. It will also show an execution plan, but be warned: the presence of a bind variable in your query may make the plan incorrect.
|
|
|
|
Re: INSERT INTO ... SELECT FROM t@dblink [message #592085 is a reply to message #592084] |
Sat, 03 August 2013 02:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You are right - but I think the figures for consistent gets and physical reads would be helpful. If very different, that would indicate a difference in what is happening at the remote site. If similar, then the difference in execution times would have to be caused by network or something local. I think!
The thing is, Scott, that session tracing is probably the best tool for this, but it needs a bit more skill to use than autotrace.
|
|
|
|