Loading

How to Get Rid of High Latency Issue When Interacting With Oracle RAC Database Through Database Connector

Udgivelsesdato: Mar 2, 2024
Løsning

SYMPTOM

You have been deploying an application on CloudHub, CloudHub 2.0, or On-Prem Mule Runtime, which implements a JDBC query through the Database connector that connects to an Oracle RAC Database.

You're experiencing latency when trying to reach the DB from Mule, which is also correlated with metrics from some performance measuring tools (APM) that are telling you the operations are taking longer than expected to complete, e.g. around 2-3 seconds.

On the other hand, when issuing the same statements locally (via Sql*Plus or any other JDBC client like Squirrel), you experience a response time in the order of milliseconds.

This condition is affecting your runtime, and is turning your head into potential scalability issues when under heavy-load scenarios.

CAUSE

Oracle*RAC (tm) Database requires a special type of connection to the backend DNS-SCAN component, which is responsible for receiving the request, deciding which node will be attending the request, and monitoring traffic. This connection is called "GridLink" in application servers like Weblogic. By using GridLink datasource, your connections become more reliable, robust and scalable and show a high performance than normal TCP/IP connections.

Oracle also recommends specifying a full GridLink URL when connecting to a RAC database because of the aforementioned benefits.

Sometimes, this configuration remains overlooked when setting the initial properties for the JDBC connection, and leads to poor DB performance because the standard TCP/IP configuration does not make use of the following features that are only available through GridLink configuration:

- ONS: Oracle Notification Service. This service "listens" for incoming events from the SCAN component, which tells the client the individual health state of the nodes. Knowing that, the client is able to invoke the SCAN in a more efficient way.

- FCF: Fast Connection Failover. This feature allows for rapid connection to an adjacent RAC DB instance if the current one was dying for some reason. This feature is also known as "Transparent Failover" because the client application is not aware of such a problem, and everything is handled between the JDBC driver and the SCAN node / nodes (if configured in multi-SCAN mode).

SOLUTION

Mule 3:

The solution is to switch from "Database configuration parameters" to "Database URL" under Oracle Configuration's General tab in Database Connector, and then specify the URL as shown below: 
jdbc:oracle:thin:${oracle.user}/${oracle.password}@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${oracle.host})(PORT=${oracle.port})))(CONNECT_DATA=(SERVICE_NAME=${oracle.service})))

Where ${oracle.user} and ${oracle.password} are your DB credentials, ${oracle.host} and ${oracle.port } are the VIP and port where the DNS-SCAN is listening to; ${oracle.service} is the name of the service to be used on the RAC DB.

If there are more than one DNS-SCAN in place (multi-SCAN configuration), with an active-standby configuration (to address site contingencies, for example),  you should supply each of the addresses as shown in the following example: 
jdbc:oracle:thin:${oracle.user}/${oracle.password}@(DESCRIPTION=(ENABLE=BROKEN)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${dns.scan1})(PORT=${oracle.port}))(ADDRESS=(PROTOCOL=TCP)(HOST=${dns.scan2})(PORT=${oracle.port}))(CONNECT_DATA=(SERVICE_NAME=${oracle.service})))

In this case, ${dns.scan1} corresponds to the VIP of the active SCAN node, and ${dns.scan2} corresponds to the VIP of the standby SCAN node. Note the FAILOVER directive to indicate that if the first node is not reachable, a failover to the second is needed.

Also, please take note of the ENABLE=BROKEN parameter.  It's an important parameter that enables keep-alive functionality at the TNS connection, which is important when there is some firewall sitting between your Mule instance and the DB itself which could be dropping traffic due to inactivity timers being expired.

Mule 4:

1. Choose Generic Connection in Database Config.
2. Config the URL in the same way as documented for Mule 3 above, except that the database user and password are configured explicitly in the User and Password fields.
Vidensartikelnummer

001118435

 
Indlæser
Salesforce Help | Article