Home » RDBMS Server » Performance Tuning » INSERT INTO ... SELECT FROM t@dblink
INSERT INTO ... SELECT FROM t@dblink [message #592075] Fri, 02 August 2013 21:23 Go to next message
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 #592079 is a reply to message #592075] Sat, 03 August 2013 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
connect .../...
exec dbms_session.session_trace_enable;
INSERT INTO t
SELET *
FROM t@dblink
WHERE id = :a;
disconnect

Do the same for both cases and use tkprof to analyze the trace files.

Regards
Michel

[Updated on: Sat, 03 August 2013 00:28]

Report message to a moderator

Re: INSERT INTO ... SELECT FROM t@dblink [message #592080 is a reply to message #592079] Sat, 03 August 2013 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Performance Tuning Guide
Chapter 20 Using Application Tracing Tools

Regards
Michel
Re: INSERT INTO ... SELECT FROM t@dblink [message #592082 is a reply to message #592075] Sat, 03 August 2013 02:03 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #592084 is a reply to message #592082] Sat, 03 August 2013 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was my first idea but autotrace does not give time in net statistics just bytes and rountrips.
SQL> set autotrace on statistics
SQL> select * from dual;
D
-
X

1 row selected.


Statistics
------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

Regards
Michel
Re: INSERT INTO ... SELECT FROM t@dblink [message #592085 is a reply to message #592084] Sat, 03 August 2013 02:25 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: INSERT INTO ... SELECT FROM t@dblink [message #592093 is a reply to message #592085] Sat, 03 August 2013 05:16 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree!

Regards
Michel
Previous Topic: Performance tunning
Next Topic: Analyze stats running long
Goto Forum:
  


Current Time: Thu Mar 28 14:32:45 CDT 2024