After upgrading to Oracle 12c, 19c, database connections take a long time (10 sec or more), before the connections are established. The connection time was less than 0.5 sec in 11g.
This is caused by a setting in the used Oracle Clusterware/Grid Infrastructure service names for the database.
The AQ HA notifications is configured as true in the service like:
$ srvctl config service -s PROD_JAVA-APP -d PROD_DB
Service name: PROD_JAVA-APP
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true <<<<<<<
Global: false
Commit Outcome: false
.../...
This feature was in use or required in 11g. But is deprecated, although still working, in Oracle 12c and higher.
According the Oracle 12 Database Development Guide :
—
“An important component of high availability is a notification mechanism called Fast Application Notification (FAN). FAN notifies other processes about configuration and service level information that includes service status changes, such as UP or DOWN events.
FAN provides the ability to immediately terminate an active transaction when an instance or server fails. FAN integrated Oracle clients receive the events and respond.
When you use Oracle Database 10g or Oracle Database 11g OCI or ODP.NET clients, you must enable Oracle Advanced Queuing (AQ) HA notifications for your Cluster services.
Beginning with Oracle Database 12c Release 1 (12.1), ONS is the primary notification mechanism for a new client (Oracle Database 12c Release 1 (12.1)) and a new server (Oracle Database 12c Release 1 (12.1)), while the AQ HA notification feature is deprecated and maintained only for backward compatibility when there is an old client (pre-Oracle Database 12c Release 1 (12.1)) or old server (pre-Oracle Database 12c Release 1 (12.1)).”
—
The new default setting in Oracle 12c, 19c, is false. But upgraded environments still have the original setting. After an upgrade the AQ HA setting can be disabled to false. Now connection time should be as fast as on the previous release:
$ srvctl modify service -s PROD_JAVA-APP -d PROD_DB -q false
I appreciate the information you’ve provided.
Could you please elaborate on where and how you have observed delays in established connections? Your insights and suggestions in this regard would be appreciated.
I noticed the delay using SQLPlus connecting to the servicename. De delay showed as elapsed time before receiving the database banner and prompt.
I appreciate the information you’ve provided.
The article is quite informative and well-written.
Could you please elaborate on where and how you have observed delays in established connections? Your insights and suggestions in this regard would be appreciated.
Do you understand why this setting has slowed down so much between releases? or is Oracle trying to force us away from the AQ based notification like this?