Re: Perl script excessively executing statement

On 13/2/20 5:58 pm, JohnD Blackburn wrote:
>
> 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?
>
Error checking after each call is a good idea.

The prepare() (if my implementation refreshing skills are accurate) maps to an Oracle call that does a lightweight parse to check things like bind 
variables and quotes.  Most errors will come from the execute() - bogus syntax, wrong columns, DB space errors etc.

Chris


> *From:* Christopher Jones <[email protected]>
> *Sent:* Thursday, 13 February 2020 12:56 PM
> *To:* [email protected]; JohnD Blackburn <[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.
>
> 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 = <<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 
> <https://urldefense.com/v3/__https://eur03.safelinks.protection.outlook.com/?url=https*3A*2F*2Fcx-oracle.readthedocs.io*2Fen*2Flatest*2Fuser_guide*2Fha.html*23network-configuration&data=02*7C01*7Cjohnd.blackburn*40au.abb.com*7C75ee9e961c994c802fe708d7b030497c*7C372ee9e09ce04033a64ac07073a91ecd*7C0*7C0*7C637171593755922211&sdata=wVBUPnUXOnie2z9yYwGPwC4J*2FtiDtMUXl1DXAjKZtyo*3D&reserved=0__;JSUlJSUlJSUlJSUlJSUlJSUl!!GqivPVa7Brio!P9p6l_vepYYnGyu45_vqxFEUnEd_tpXLUZ8DxElcAZGs05cgs-IJxDx4g8H-qje3S1gj1g$> 
> and https://oracle.github.io/node-oracledb/doc/api.html#connectionha 
> <https://urldefense.com/v3/__https://eur03.safelinks.protection.outlook.com/?url=https*3A*2F*2Foracle.github.io*2Fnode-oracledb*2Fdoc*2Fapi.html*23connectionha&data=02*7C01*7Cjohnd.blackburn*40au.abb.com*7C75ee9e961c994c802fe708d7b030497c*7C372ee9e09ce04033a64ac07073a91ecd*7C0*7C0*7C637171593755932204&sdata=iZPYDmpjTj*2FPw*2FShQqdiSQIccucpjLqO1chp*2BQq9I4Q*3D&reserved=0__;JSUlJSUlJSUlJSUlJSUlJSUlJQ!!GqivPVa7Brio!P9p6l_vepYYnGyu45_vqxFEUnEd_tpXLUZ8DxElcAZGs05cgs-IJxDx4g8H-qjdP4_-wrg$> 
> 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]> <mailto:[email protected]>
>     *Sent:* Thursday, 13 February 2020 2:56 AM
>     *To:* JohnD Blackburn <[email protected]> <mailto:[email protected]>
>     *Cc:* [email protected] <mailto:[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  <https://urldefense.com/v3/__https://eur03.safelinks.protection.outlook.com/?url=https*3A*2F*2Ftwitter.com*2Fghrd&data=02*7C01*7Cjohnd.blackburn*40au.abb.com*7C75ee9e961c994c802fe708d7b030497c*7C372ee9e09ce04033a64ac07073a91ecd*7C0*7C0*7C637171593755932204&sdata=Abyo5RrMO4nqfXYOSiOkqPhP573KSnTL9qqgeXuNYYI*3D&reserved=0__;JSUlJSUlJSUlJSUlJQ!!GqivPVa7Brio!P9p6l_vepYYnGyu45_vqxFEUnEd_tpXLUZ8DxElcAZGs05cgs-IJxDx4g8H-qjexS_7R-A$>

-- 
https://twitter.com/ghrd