If you are trying to execute one SQL statement against the database for each loop iteration – you are doing it wrong.
You are adding a round trip for every row which will be slow for the client and waist time and other resources on the server. Look on the net for other approaches using NVL and “in” and DECODE. Learn about Oracle inline-views, and clever uses of joins – Learn the difference between “union all” and “union” and how to use DUAL and UNION ALL to create a dynamic-view that can be joined with other tables. Try to figure out how you can do the whole thing in a single SQL statement, then just process the results one row at time. Learn about how to bind variables instead of doing dynamically generated SQL. Learn about how oracle implements UPSERT (insert + update). Hunt down relevant articles on “Ask Tom”. You will have to think harder but your code will be faster and your DBA happier. And you will be a wiser professional. Ask the DBA for help if you are having a hard time figuring it out or share your problem and your solution here and ask for alternatives (you left out most of your code – security concerns?). From: JohnD Blackburn <johnd.blackb...@au.abb.com> Sent: Thursday, February 13, 2020 1:59 AM To: Christopher Jones <christopher.jo...@oracle.com>; dbi-users@perl.org Subject: [EXTERNAL] 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 <christopher.jo...@oracle.com<mailto:christopher.jo...@oracle.com>> Sent: Thursday, 13 February 2020 12:56 PM To: dbi-users@perl.org<mailto:dbi-users@perl.org>; JohnD Blackburn <johnd.blackb...@au.abb.com<mailto:johnd.blackb...@au.abb.com>> 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 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<https://urldefense.proofpoint.com/v2/url?u=https-3A__eur03.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Fcx-2Doracle.readthedocs.io-252Fen-252Flatest-252Fuser-5Fguide-252Fha.html-2523network-2Dconfiguration-26data-3D02-257C01-257Cjohnd.blackburn-2540au.abb.com-257C75ee9e961c994c802fe708d7b030497c-257C372ee9e09ce04033a64ac07073a91ecd-257C0-257C0-257C637171593755922211-26sdata-3DwVBUPnUXOnie2z9yYwGPwC4J-252FtiDtMUXl1DXAjKZtyo-253D-26reserved-3D0&d=DwMGaQ&c=ukcTAPl5KduEyRvXoL9XMA&r=0EHe68Ki46dgcWiA0JMjgV97dO_1ZKSBkQjMhiDvNg4&m=eTYZSq6Vb362v-t0y-oZeWFoQiPoRGJNQO4DhxguDXE&s=jOEfAb0cL_vx5m2o6rFu3BH8zKp97CHp7KtW8sLMBm0&e=> and https://oracle.github.io/node-oracledb/doc/api.html#connectionha<https://urldefense.proofpoint.com/v2/url?u=https-3A__eur03.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Foracle.github.io-252Fnode-2Doracledb-252Fdoc-252Fapi.html-2523connectionha-26data-3D02-257C01-257Cjohnd.blackburn-2540au.abb.com-257C75ee9e961c994c802fe708d7b030497c-257C372ee9e09ce04033a64ac07073a91ecd-257C0-257C0-257C637171593755932204-26sdata-3DiZPYDmpjTj-252FPw-252FShQqdiSQIccucpjLqO1chp-252BQq9I4Q-253D-26reserved-3D0&d=DwMGaQ&c=ukcTAPl5KduEyRvXoL9XMA&r=0EHe68Ki46dgcWiA0JMjgV97dO_1ZKSBkQjMhiDvNg4&m=eTYZSq6Vb362v-t0y-oZeWFoQiPoRGJNQO4DhxguDXE&s=4Q7U6LmFtJl8YNEeYPfh5DxjTmL2MKjom232qbLCJsM&e=> 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 <wgrom...@gmail.com><mailto:wgrom...@gmail.com> Sent: Thursday, 13 February 2020 2:56 AM To: JohnD Blackburn <johnd.blackb...@au.abb.com><mailto:johnd.blackb...@au.abb.com> Cc: dbi-users@perl.org<mailto: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 <johnd.blackb...@au.abb.com<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<https://urldefense.proofpoint.com/v2/url?u=https-3A__eur03.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Ftwitter.com-252Fghrd-26data-3D02-257C01-257Cjohnd.blackburn-2540au.abb.com-257C75ee9e961c994c802fe708d7b030497c-257C372ee9e09ce04033a64ac07073a91ecd-257C0-257C0-257C637171593755932204-26sdata-3DAbyo5RrMO4nqfXYOSiOkqPhP573KSnTL9qqgeXuNYYI-253D-26reserved-3D0&d=DwMGaQ&c=ukcTAPl5KduEyRvXoL9XMA&r=0EHe68Ki46dgcWiA0JMjgV97dO_1ZKSBkQjMhiDvNg4&m=eTYZSq6Vb362v-t0y-oZeWFoQiPoRGJNQO4DhxguDXE&s=whFY_lfIkHZ27JbVss3F6mAum4rDT6bcg7PBAf9vN6s&e=> The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates.
