The SQL statement is only executed once within the Perl script. There is no
additional executions of the query. The query only selects from tables. It
was the entire query that was executed according to statspack 12000 times, not
sub-components of it. So seems as if some mechanism within DBI, DBD, or Oracle
was at fault.
Here is the statement that Statspack reported the excessive executions on:
select
prog_name, dstrct_code, Start_Date, Start_Time, Runtime_Minutes,
Median_Elapsed, Std_Deviation, Count_Instances, (4*Std_Deviation+Median_Elapsed
+ 5) as THRESHOLD, UUID
from
(
with history as
( select prog_name,
dstrct_code,
round(sum(elapsed_minutes),5) as Elapsed_Minutes,
round(avg(elapsed_minutes),5) as Average_Elapsed,
round(median(elapsed_minutes),5) as Median_Elapsed,
round(stddev(elapsed_minutes),5) as Std_Deviation,
count(*) as Count_Instances
from (
select prog_name,
dstrct_code,
1440 *
(to_date(stop_date||stop_time_hhmmss,'YYYYMMDDHH24MISS') -
to_date(start_date||start_time_hhmmss,'YYYYMMDDHH24MISS')) as Elapsed_Minutes
from ellipse.msf085 History
)
group by prog_name, dstrct_code
)
select Executing.prog_name, Executing.dstrct_code, Executing.start_date,
Executing.start_time,
round((1440 * ((cast(SYSTIMESTAMP at time zone
NVL(trim(tz.district_time_zone),sessiontimezone) as date))
- to_date(Executing.start_date || Executing.start_time,
'YYYYMMDDHH24MI') ) ),5) as RUNTIME_MINUTES,
History.Median_elapsed, History.Count_Instances,
History.Std_Deviation,
Executing.UUID
from Ellipse.MSF080 Executing
inner join History on (history.dstrct_code = Executing.dstrct_code and
History.prog_name = Executing.prog_name)
inner join Ellipse.msf000_dc0002 tz on (tz.dstrct_code =
Executing.dstrct_code)
where Executing.process_Status = 'E'
)
order by 1,2,3,4,5
It is not being executed inside a loop. Only loop is a while loop calling
fetchrow_array, and there is no subsequent SQL being executed by the script
inside the loop. The version of the database and Oracle client has not been
changed. Optimizer adaptive features are disabled in this database.
I will have a look at the links you provided for DBI debug. Although this was
the only instance we know about where this query was executed excessively,
maybe it is happening more frequently than we realize and debugging might
provide some answers.
Thanks for your reply
Regards,
John
-----Original Message-----
From: Fennell, Brian <fenne...@radial.com>
Sent: Saturday, 15 February 2020 5:47 AM
To: JohnD Blackburn <johnd.blackb...@au.abb.com>; 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.
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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgrokbase.com%2Ft%2Fperl%2Fdbi-users%2F094mcz7m5w%2Fan-explain-plan-for-oracle-queries-with-placeholders&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272927190&sdata=aLJqdmPbot3GZJiLhGQ9YOfFt%2F8T75SYMMoHIDkuKe0%3D&reserved=0
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.oracle.com%2Ftechnetwork%2Fdatabase%2Fbi-datawarehousing%2Ftwp-explain-the-explain-plan-052011-393674.pdf&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272927190&sdata=ws%2Fz9T0NP3RTNB7xktNLPmn38OOwQC06dADwTU4Pyug%3D&reserved=0
DBI DEBUG
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.easysoft.com%2Fdeveloper%2Flanguages%2Fperl%2Fdbi-debugging.html&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=tiWNDpHgdFW8ewLJZIHMN5WuB5J%2FuJj57ukqMS3Noy0%3D&reserved=0
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.perlmonks.org%2Fbare%2F%3Fnode_id%3D90692&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=QfuTMTNwFD2v%2BYZnKFcLoKkKi4f27Ie0lXpejVLElPQ%3D&reserved=0
DBI-trace()
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.effectiveperlprogramming.com%2F2010%2F04%2Fuse-dbi_trace-to-follow-dbis-work%2F&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=Vv8VMU7pYUrlb8XJS9Ck%2B0ydrdH%2Bodmd94IohV2kACI%3D&reserved=0
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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fperl5-dbi%2FDBD-Oracle&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=%2F7PccI%2FmmzMBXEgFPhYSF1E5x%2FWwytbZUiS3ZZ4dbZU%3D&reserved=0
Can be installed with cpanm using a git url (but not with cpan)
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmetacpan.org%2Fpod%2Fcpanm&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=OIjH6YjhUXkmfmSUJRTGb4lI9NYv%2BBMjQ2qtypvS5mo%3D&reserved=0
you will need oracle C libraries to build - it won't be easy
Alternate Oracle DBD plugin - Oracle JDBC
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmetacpan.org%2Fpod%2FJDBC&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=%2FiB4%2BwQaLKvFbXrKRapwKg7BLNA%2BB21oOn3sLgjhc7U%3D&reserved=0
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmetacpan.org%2Fpod%2Fdistribution%2FDBD-JDBC%2FJDBC.pod%23Starting-the-server&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=BifJoH2iQEcQiDWnYFvewGEH5%2F3yofe1G%2BJ8pzStWW0%3D&reserved=0
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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsdkman.io%2F&data=02%7C01%7Cjohnd.blackburn%40au.abb.com%7Ce88a55c0922e4e14f3c108d7b186aad0%7C372ee9e09ce04033a64ac07073a91ecd%7C0%7C1%7C637173064272937183&sdata=%2BpdWVMtMoTYyqbBRHpfefL8QkdX4XGQRRUQYm%2BdCuI0%3D&reserved=0
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.
