My institution provides us with Amazon Relational Database Servers so we can
migrate from on-prem to cloud.
I've started the process of migrating our databases (on premises are Oracle
19c, same as the Amazon Oracle RDS I’m getting).
The DB tools I use (Oracle SQL Developer and DBVisualizer) are working as
expected with the instances I have, but trying to run my Perl code using
DBI/DBD::Oracle/Oracle Instant Client is giving me extreme delays creating the
DB handle; running a simple test program to time the process shows the program
taking a minute or longer to create the database handle.
It works, eventually, so the problem isn’t, afaik, the perl script.
It’s quite simple I get time(), create a db handle, get time() again, and
calculate the difference between original time and the second time.
I do this for each step of the test :
create handle $dbh = DBI->connect("dbi:Oracle:$dbnamec", $login, $dbpassc,
{RaiseError=>1});
The db name are straightforward “host:<hostname>;sid=<sid>"
create cursor counting the number of rows in a table, execute, fetch the result
and print, timing it our between each step.
I'm very consistently getting 61 seconds to just create the database handle,
once connected it performs as expected.
Does anyone else here deal with this Amazon service and is there anything
unexpected I need to do to connect?
(the local support folks are woefully unfamiliar with perl, so I figured I’d
ask here first..
Output of the script:
./connectiontest.pl
Cloud Start time is 1743187931
cloud connect time is 61
cloud prepare time is 0
cloud execute time is 0
There are 463 resources in cloud
cloud fetch time is 0
Local Start time is 1743187992
local connect time is 0
local prepare time is 0
local execute time is 0
local fetch time is 0
There are 463 resources locally
This is for a web application so this kind of issue is a non-starter…
This is a fragmnent of the test script, anonymized
#!/usr/bin/perl
use strict;
use DBI;
my $login="xxxxxxxxxx";
my $dbpassc="xxxxxxxxx";
my $dbpassl="xxxxxxxxx";
my $dbnamel="host=xxxxxxx.pharmacy.arizona.edu;sid=xxxxxxx";
my $dbnamec="host=xxxxxxxx.us-west-2.rds.amazonaws.com;sid=xxxxxxx";
#set up db connection
my ($time, $otime, $etime);
$time=time();
$otime=$time;
print "Cloud Start time is $time\n";
my $ldac = DBI->connect("dbi:Oracle:$dbnamec", $login, $dbpassc,
{RaiseError=>1});
$time =time();
$etime= $time-$otime;
$otime=$time;
print "cloud connect time is $etime\n";
...
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
