3 Replies Latest reply: Aug 8, 2013 3:45 PM by PRaju RSS

    How to increase performance of remote database connection

    PRaju

      Guru,

       

       

      We are accessing remote database objects from internal database using DB_LINK.

      ex, SELECT * FROM MTL_SYSTEM_ITEMS@ERP_WORLD

       

      Few cases we are creating synonyms for remote databse objects using DBLINK.

      ex, CREATE OR REPLACE PUBLIC SYNONYM MTL_SYSTEM_ITEMS FOR MTL_SYSTEM_ITEMS@ERP_WORLD;

       

      Question ?

      whats is the difference between above two cases ? will it create any performance improvment.

       

      Regards,

      Raju

        • 1. Re: How to increase performance of remote database connection
          PRaju

          Any suggestion more appreciated

          • 2. Re: How to increase performance of remote database connection
            Billy~Verreynne

            No difference. How would a synonym make things faster?

             

            The complete remote table is read by the remote database, and then transmitted over the communication channel (typically TCP/IP) between local and remote databases.

             

            Why would this be fast? The MTU size of an IP packet is 1500 bytes. Then there are issues such as available network bandwidth, and network latency. Depending on the network infrastructure and distance between local and remote databases, there can be numerous switches, routers and firewalls.

             

            The data send is also not compressed.

             

            So if you select 1GB worth of data from the remote database via a db link from the local database - it means 1GB plus overheads that need to be transmitted.

             

            Networks are almost always shared infrastructure. Which means that the 1GB row data transfer competes with web traffic (perhaps youtube videos or VoIP), e-mail traffic, Windows domain controller noise, LDAP server traffic, other database traffic, etc.

             

            So why would do you expect this to be fast?

            • 3. Re: How to increase performance of remote database connection
              PRaju

              Even though Option2 making additional layer to Database access, its complex task , Hence decide to go with option1.