PL/SQL dblink Timeout Issue During Data Copy from Remote Site

Question

I'm grappling with a PL/SQL data copy procedure that works flawlessly for multiple remote sites but times out on one specific site. Our AWR and ASH reports pinpoint 'virtual circuit' waits as the predominant bottleneck, accounting for a staggering 79% of the total database time.

The procedure's logic identifies absent rows in our database compared to a remote database and triggers data copying for missing entries. While other sites' data copy tasks wrap up in under a minute, this particular site's operation is subject to frustrating timeouts.

Enclosed below is a snippet from our AWR report, which underscores the extensive wait times under the 'virtual circuit wait' event: AWR report

I will greatly appreciate any insights or recommended diagnostic approaches from the wisdom of this community.

Thanks in advance for your time and help.

Whenever I run this procedure manually for the problematic site, it takes at least five minutes to run. I just ran it on the test database, and it took about 22 minutes total. This is probably longer than normal because it hasn’t copied all of the data in almost a week. When I run it for any other site It completes in less than a minute.

Answer 1

This is likely because the remote database is configured to use shared server connections instead of dedicated server connections for client connections. You can either reconfigure the remote database to use only dedicated server connections, or try placing a "SERVER=DEDICATED" parameter in your local database tnsnames.ora or client connection string in the database link to force a dedicated connection:

net_service_name= 
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)
    (SERVER=dedicated)))

See here: https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/local-naming-parameters-in-tns-ora-file.html#GUID-351FF338-16CC-43A3-9F2D-78937015DC02