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 = <<END;
>
> 20 line select statement
>
> END
>
Somewhere you might want to tune ora_prefetch_rows or RowCacheSize, depending how many rows you expect the query to return.  This is unrelated to your 
question.
>
> my $arraySelect = $dbh->prepare($statement) or die $!;
>
> $arraySelect->execute();
>
> while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {
>
>     <perl code to process row of data which includes a couple of “if next” statement blocks with no reference to arraySelect inside the while loop>
>
> }
>
> $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 <[email protected]>
> *Sent:* Thursday, 13 February 2020 2:56 AM
> *To:* JohnD Blackburn <[email protected]>
> *Cc:* [email protected]
> *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 <[email protected] <mailto:[email protected]>> 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