John, One way for you to execute sql thousands of times from perl is to do it in your loop.
The other way is for the Oracle server to do it for you in its loop. Sometimes an Oracle Upgrade will cause the Oracle Optimizer to change - this can cause SQL that "worked fine" to start behaving differently. A SQL join is translated to one or more nested loops by the Oracle Optimizer - SQL is an algebraic notation but computers don't really "do" algebra, they do nested loops. Which table is selected first and which is second (and third and so-on). If you have functions they may execute SQL once per row, but this may be in the outer loop (seldom) or in the inner loop (often). The Oracle SQL Optimizer plan can also change when the table statistics are recalculated - which doesn't require an Oracle upgrade. EXPLAIN PLAN is your way to see which table gets looped over in which order as the table statistics and the Oracle optimizer are RIGHT NOW. Some more hints: EXPLAIN PLAN https://grokbase.com/t/perl/dbi-users/094mcz7m5w/an-explain-plan-for-oracle-queries-with-placeholders https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf DBI DEBUG https://www.easysoft.com/developer/languages/perl/dbi-debugging.html https://www.perlmonks.org/bare/?node_id=90692 DBI-trace() https://www.effectiveperlprogramming.com/2010/04/use-dbi_trace-to-follow-dbis-work/ The error you are getting may be a result of a client/server incompatibility (if the server version changed and your client version did not) - you could try an alternate Oracle DBD plug-in / class. Alternate Oracle DBD plugin https://github.com/perl5-dbi/DBD-Oracle Can be installed with cpanm using a git url (but not with cpan) https://metacpan.org/pod/cpanm you will need oracle C libraries to build - it won't be easy Alternate Oracle DBD plugin - Oracle JDBC https://metacpan.org/pod/JDBC https://metacpan.org/pod/distribution/DBD-JDBC/JDBC.pod#Starting-the-server you will need a jvm installed - and oracle's jdbc jars - it might be easier (or not) you can get a free jvm here: https://sdkman.io/ From: JohnD Blackburn <johnd.blackb...@au.abb.com> Sent: Wednesday, February 12, 2020 12:56 AM To: dbi-users@perl.org Subject: [EXTERNAL] Perl script excessively executing statement 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 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.