RE: Perl script excessively executing statement
The DBA pulled info from some cache that showed the SQL statement from the
script was executed 12610 times.
So if I were to add an “or die $!” statement after the fetchrow_array(), that
might fix things? (or prevent it from trying to continue if there are errors?)
Or catch errors after the execute statement?
From: Christopher Jones
Sent: Thursday, 13 February 2020 12:56 PM
To: dbi-users@perl.org; JohnD Blackburn
Subject: Re: Perl script excessively executing statement
CAUTION: This email originated from outside of the organization. Do not click
links or open attachments unless you recognize the sender and know the content
is safe.
On 13/2/20 11:13 am, JohnD Blackburn wrote:
Is that a behavior of DBI or DBD::Oracle?
Maybe your script, if you are blindly looping when it gets an error? Overall,
I don't think there is enough information to point directly at a cause.
Presumably the DBA meant that a SQL statement (of some kind) was e
xecuted 12610 times; not that your script was invoked that number of times.
My script says prepare or die, so any retries would have had to come directly
from the DBD::Oracle module
Script basically says:
use DBD::Oracle;
my $dbh = DBI->connect("dbi:Oracle:$dbSID", $user, $passwd, {AutoCommit => 0 });
my $statement = execute();
while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {
}
$arraySelect->finish();
$dbh->disconnect();
I don’t work much with the DBD::Oracle module or perl and what I have is just
reworked from scripts others have written.
Is there parameters for the DBD::Oracle functions that can affect their
behavior? If this is behavior of the execute function, I really need to be able
to reign it in to limit its impact if it ever does it again. Not knowing why
the issue triggered in the 1st place, I don’t know how to reproduce it to test
if any mitigations are sufficient.
You can set sqlnet.ora parameters to bound the time taken for connection and
statement execution. Refer to
https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration
and
https://oracle.github.io/node-oracledb/doc/api.html#connectionha
since the sqlnet.ora settings will be the same for DBD::Oracle - the network
layer is common across all the C-based drivers. Depending on your
requirements, you may want to sleep between retries.
Chris
Cheers,
John
From: Geoffrey Rommel
Sent: Thursday, 13 February 2020 2:56 AM
To: JohnD Blackburn
Cc: dbi-users@perl.org
Subject: Re: Perl script excessively executing statement
CAUTION: This email originated from outside of the organization. Do not click
links or open attachments unless you recognize the sender and know the content
is safe.
I don't work with Oracle, but here's a guess. Maybe the database was
unresponsive before your script started running, not as a result of it. If so,
maybe your script tried to prepare the statement, failed, and retried 12000
times. Eventually the DBA noticed the problem and restarted the database, at
which time your script was terminated along with everything else.
On Tue, Feb 11, 2020 at 11:56 PM JohnD Blackburn
mailto:johnd.blackb...@au.abb.com>> wrote:
Hi all,
I have a perl script in my monitoring system that has been working for months
without an issue.
Basically, it connects to an Oracle 12c database, prepares a statement, then it
executes the statement, then it has a while loop to process the returned rows.
So under normal conditions the statement is executed once every 5 minutes.
Now on Friday last week, it did something really strange which I cannot account
for the behaviour.
According to the DBA, the statement in the script was executed 12610 times over
a 50 minute period causing the database to become non-responsive. The DBAs
also stated that the script only connected to the database once at the
beginning of the 50 minute period. Average execution time of the statement was
0.26 seconds.
According to the log for my script, the script only executed once at the
beginning of the 50 minute period, and then after that, returned to executing
every 5 minutes.
Since that incident, the statememt in question has only
Re: Perl script excessively executing statement
On 13/2/20 11:13 am, JohnD Blackburn wrote:
Is that a behavior of DBI or DBD::Oracle?
Maybe your script, if you are blindly looping when it gets an error? Overall, I don't think there is enough information to point directly at a
cause. Presumably the DBA meant that a SQL statement (of some kind) was executed 12610 times; not that your script was invoked that number of times.
My script says prepare or die, so any retries would have had to come directly
from the DBD::Oracle module
Script basically says:
use DBD::Oracle;
my $dbh = DBI->connect("dbi:Oracle:$dbSID", $user, $passwd, {AutoCommit => 0 });
my $statement = prepare($statement) or die $!;
$arraySelect->execute();
while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {
}
$arraySelect->finish();
$dbh->disconnect();
I don’t work much with the DBD::Oracle module or perl and what I have is just
reworked from scripts others have written.
Is there parameters for the DBD::Oracle functions that can affect their behavior? If this is behavior of the execute function, I really need to be
able to reign it in to limit its impact if it ever does it again. Not knowing why the issue triggered in the 1^st place, I don’t know how to
reproduce it to test if any mitigations are sufficient.
You can set sqlnet.ora parameters to bound the time taken for connection and statement execution. Refer to
https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration and
https://oracle.github.io/node-oracledb/doc/api.html#connectionha since the sqlnet.ora settings will be the same for DBD::Oracle - the network layer is
common across all the C-based drivers. Depending on your requirements, you may want to sleep between retries.
Chris
Cheers,
John
*From:* Geoffrey Rommel
*Sent:* Thursday, 13 February 2020 2:56 AM
*To:* JohnD Blackburn
*Cc:* dbi-users@perl.org
*Subject:* Re: Perl script excessively executing statement
*CAUTION:*This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know
the content is safe.
I don't work with Oracle, but here's a guess. Maybe the database was unresponsive before your script started running, not as a result of it. If so,
maybe your script tried to prepare the statement, failed, and retried 12000 times. Eventually the DBA noticed the problem and restarted the
database, at which time your script was terminated along with everything else.
On Tue, Feb 11, 2020 at 11:56 PM JohnD Blackburn mailto:johnd.blackb...@au.abb.com>> wrote:
Hi all,
I have a perl script in my monitoring system that has been working for
months without an issue.
Basically, it connects to an Oracle 12c database, prepares a statement,
then it executes the statement, then it has a while loop to process the
returned rows.
So under normal conditions the statement is executed once every 5 minutes.
Now on Friday last week, it did something really strange which I cannot
account for the behaviour.
According to the DBA, the statement in the script was executed 12610 times
over a 50 minute period causing the database to become
non-responsive. The DBAs also stated that the script only connected to the
database once at the beginning of the 50 minute period. Average
execution time of the statement was 0.26 seconds.
According to the log for my script, the script only executed once at the
beginning of the 50 minute period, and then after that, returned to
executing every 5 minutes.
Since that incident, the statememt in question has only executed the
expected 12 times per hour.
I have yet to find a satisfactory reason the SQL statement from this perl
script executed so many times in the 50 minute period.
Script is running on an Oracle Linux 7.7 server with;
* oracle 12c client installed
* perl 5.16.3
* perl-DBI 1.627-4 ( from Oracle Linix Latest yum repository)
* perl-DBD-ODBC 1.50.-3 (from EPEL)
* DBD::Oracle 1.80 (from CPAN)
Oracle 12 database is on a remote server.
Anyone have any ideas why the SQL statement would have been executed 12000+
times in a 50minute period, when the script and its schedule should
not have executed the SQL any more frequiently than 12 times an hour?
Regards,
John
--
https://twitter.com/ghrd
RE: Perl script excessively executing statement
Is that a behavior of DBI or DBD::Oracle?
My script says prepare or die, so any retries would have had to come directly
from the DBD::Oracle module
Script basically says:
use DBD::Oracle;
my $dbh = DBI->connect("dbi:Oracle:$dbSID", $user, $passwd, {AutoCommit => 0 });
my $statement = execute();
while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {
}
$arraySelect->finish();
$dbh->disconnect();
I don’t work much with the DBD::Oracle module or perl and what I have is just
reworked from scripts others have written.
Is there parameters for the DBD::Oracle functions that can affect their
behavior? If this is behavior of the execute function, I really need to be able
to reign it in to limit its impact if it ever does it again. Not knowing why
the issue triggered in the 1st place, I don’t know how to reproduce it to test
if any mitigations are sufficient.
Cheers,
John
From: Geoffrey Rommel
Sent: Thursday, 13 February 2020 2:56 AM
To: JohnD Blackburn
Cc: dbi-users@perl.org
Subject: Re: Perl script excessively executing statement
CAUTION: This email originated from outside of the organization. Do not click
links or open attachments unless you recognize the sender and know the content
is safe.
I don't work with Oracle, but here's a guess. Maybe the database was
unresponsive before your script started running, not as a result of it. If so,
maybe your script tried to prepare the statement, failed, and retried 12000
times. Eventually the DBA noticed the problem and restarted the database, at
which time your script was terminated along with everything else.
On Tue, Feb 11, 2020 at 11:56 PM JohnD Blackburn
mailto:johnd.blackb...@au.abb.com>> wrote:
Hi all,
I have a perl script in my monitoring system that has been working for months
without an issue.
Basically, it connects to an Oracle 12c database, prepares a statement, then it
executes the statement, then it has a while loop to process the returned rows.
So under normal conditions the statement is executed once every 5 minutes.
Now on Friday last week, it did something really strange which I cannot account
for the behaviour.
According to the DBA, the statement in the script was executed 12610 times over
a 50 minute period causing the database to become non-responsive. The DBAs
also stated that the script only connected to the database once at the
beginning of the 50 minute period. Average execution time of the statement was
0.26 seconds.
According to the log for my script, the script only executed once at the
beginning of the 50 minute period, and then after that, returned to executing
every 5 minutes.
Since that incident, the statememt in question has only executed the expected
12 times per hour.
I have yet to find a satisfactory reason the SQL statement from this perl
script executed so many times in the 50 minute period.
Script is running on an Oracle Linux 7.7 server with;
* oracle 12c client installed
* perl 5.16.3
* perl-DBI 1.627-4 ( from Oracle Linix Latest yum repository)
* perl-DBD-ODBC 1.50.-3 (from EPEL)
* DBD::Oracle 1.80 (from CPAN)
Oracle 12 database is on a remote server.
Anyone have any ideas why the SQL statement would have been executed 12000+
times in a 50minute period, when the script and its schedule should not have
executed the SQL any more frequiently than 12 times an hour?
Regards,
John
Re: Perl script excessively executing statement
I don't work with Oracle, but here's a guess. Maybe the database was unresponsive before your script started running, not as a result of it. If so, maybe your script tried to prepare the statement, failed, and retried 12000 times. Eventually the DBA noticed the problem and restarted the database, at which time your script was terminated along with everything else. On Tue, Feb 11, 2020 at 11:56 PM JohnD Blackburn wrote: > Hi all, > > > > I have a perl script in my monitoring system that has been working for > months without an issue. > > > > Basically, it connects to an Oracle 12c database, prepares a statement, > then it executes the statement, then it has a while loop to process the > returned rows. > > > > So under normal conditions the statement is executed once every 5 minutes. > > > > Now on Friday last week, it did something really strange which I cannot > account for the behaviour. > > > > According to the DBA, the statement in the script was executed 12610 times > over a 50 minute period causing the database to become non-responsive. The > DBAs also stated that the script only connected to the database once at the > beginning of the 50 minute period. Average execution time of the statement > was 0.26 seconds. > > > > According to the log for my script, the script only executed once at the > beginning of the 50 minute period, and then after that, returned to > executing every 5 minutes. > > > > Since that incident, the statememt in question has only executed the > expected 12 times per hour. > > > > I have yet to find a satisfactory reason the SQL statement from this perl > script executed so many times in the 50 minute period. > > > > Script is running on an Oracle Linux 7.7 server with; > >- oracle 12c client installed >- perl 5.16.3 >- perl-DBI 1.627-4 ( from Oracle Linix Latest yum repository) >- perl-DBD-ODBC 1.50.-3 (from EPEL) >- DBD::Oracle 1.80 (from CPAN) > > > > Oracle 12 database is on a remote server. > > > > Anyone have any ideas why the SQL statement would have been executed > 12000+ times in a 50minute period, when the script and its schedule should > not have executed the SQL any more frequiently than 12 times an hour? > > > > Regards, > > John > > > > >
Re: Wrt. [DBD-AnyData] Repository cleanup (#1)
Nigel, > Am 11.02.2020 um 17:47 schrieb Nigel Horne,, : > > I like that module and will be sad to see it go. If you want I'll happily > take it over. it doesn't work anymore. And there is a drop-in replacement. I don't know what's the expectation. Can you explain that a bit? DBD::AnyData is there if anyone has old software which needs the old module with the old API sing with old perl and old DBI. > -Nigel > > On 11/2/20, 11:45, "Jens Rehsack" wrote: > >Hi Olivier, > > >wrt. https://github.com/rehsack/DBD-AnyData/pull/1 > >In case you really need DBD::AnyData and DBD::AnyData2 won't do at all, > please let's sit together (virtually ^^) and figure out how to help you. >DBD::AnyData won't work with recent DBI (I'd say for any DBI newer than > 2012) - and likely even before then ;) > >IIRC, it is marked deprecated on CPAN. > >Best regards >-- >Jens Rehsack - rehs...@gmail.com Best regards, -- Jens Rehsack - rehs...@gmail.com signature.asc Description: Message signed with OpenPGP
