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;
whats is the difference between above two cases ? will it create any performance improvment.
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?