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=trueThen start your process which uses the database link. After that is done, comment out or remove those lines from your SQLNET.ORA file to stop further trace files from being generated.
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.