Why is dblink between two instances on same platform slow?

I am trying to determine why a database link between two instances on the same platform is extremely slow as compared to a link to a remote database. I have set the listener and tnsnames to use IPC, but it does not resolve the issue.

IPC should be faster than TCP/IP for communicating with two databases on the same server. The only real way to find out what is causing your problems is to generate a trace of your network traffic. This can be done by adding the following lines to your SQLNET.ORA file:

trace_level_client=support
trace_file_client=cli
trace_directory_client=/my_oracle_home/network/trace
trace_unique_client=true

Now examine the contents of $ORACLE_HOME/network/trace. You'll need to look in the trace files to determine which one was generated by your specific session. This can be done quite easily by using grep on your SQL statements.

Once you have identified the trace file, you'll want to open a TAR with Oracle Support. They can best analyze the contents of your trace file and help you sort out your difficulties.

For More Information

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.