Problems arising from over-using DBLINK for system integration

Problems arising from over-using DBLINK for system integration

Excessive use of DBLINK for system integration may cause many problems, mainly due to the following:

1. A large amount of database resources are consumed;

Each time the local system connects to the remote system through DBLINK, a local session is generated. If the local session does not exit or is manually released, it can be automatically released only when the session times out, which wastes a lot of system resources.

2. prone to database bugs;

Using DBLINK in mass, local systems are vulnerable to Oracle errors such as ORA-02068, ORA-03113, ORA-02080, ORA-02054, and ORA-02050, most of which affect the business being processed by the current session

3. Poor performance;

Because DBLINK is used, the local system performs a full table scan on the remote database each time it obtains data, and all data is transmitted back to the local database, this results in lower performance and serious waste of current system resources. In particular, an SQL statement is called "performance killer", especially for OLTP databases, to fetch data from two dblinks.

4. Unstable data transmission, serious bandwidth occupation, and risk of packet loss;

DBLINK itself does not have data storage, monitoring, and other functions. It is transmitted through a database in the network. If data packet loss occurs during transmission, DBLINK itself will not find it, packet Loss can only be detected after the service is completed, which affects the services currently being processed. In addition, data queried through DBLINK is transmitted to the local database as if all the data were being processed, the data transmission volume is large and bandwidth usage is serious, which may cause network congestion.

5. Limited Oracle Data functionality

DBLINK itself does not support operations on large oracle LOB objects. When using PROCDURE, operations such as COMMIT may encounter errors;

6. poor scalability

If multiple systems use DBLINK for transmission, one or more DBLINK links or corresponding interface programs will be added for each added system. If one of the interfaces is modified, all interface programs in the associated system need to be modified;

7. Poor maintainability and low security

The user name, password, and IP address of the remote database must be stored in the local database for connection through DBLINK. For example, if the remote database is used for password modification or IP address change, the local database also needs to be modified. If there are many systems, each system must be modified at the same time. The local database administrator can view the remote data password and IP address, which affects system security.