Hi,
If anyone is around who wrote or has worked on the object/collections support
in DBD::Oracle I'd greatly appreciate it if you could take a quick look at this
problem as the code in DBD::Oracle for this has defeated me so far.
The problem is I have a query which uses types and collect and although it
works fine initially once it has been run a number of times in the same
connection it eventually goes from a 3s fetch time to a number of minutes. I
have reduced it to the example below.
I can run this code all day long in sqlplus without a problem so I don't think
it is an Oracle issue.
Changing the 3 numbers affects how many iterations it takes before it goes
wrong and how long the fetch eventually takes to retrieve the data. Initially
it seemed the calculation to find out which iteration it goes wrong on was
26550000 / objects fetched (the 3 numbers multipled together) gave the iteration
and that looked pretty close initially. Clutching at straws 2^31 / 26550000 is
suspiciously 80.
Here are some results with the 3 number variations:
inner middle outer iteration_fail fetch_time_change
10 100 500 53 8s->345s
5 10 500 76 4s->200s
20 100 500 37 12->632s
5 120 500 64 5s->247s
5 100 300 75 3s->121s
It seems the inner number determines how bad the fetch time increases and the
sum how many iterations it takes to reach the point where it goes wrong.
I'd greatly appreciate any ideas.
The code to reproduce is:
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=xxx.yyy.com;sid=xxx;',
'xxx', 'xxx',
# just so we can ctrl/c does not affect results
{ora_connect_with_default_signals => ['INT']});
eval {
$dbh->do(q/drop type TABLE_A/);
};
eval {
$dbh->do(q/drop type TABLE_B/);
};
eval {
$dbh->do(q/drop type RECORD_B/);
};
eval {
$dbh->do(q/drop type RECORD_A/);
};
$dbh->do(q/CREATE OR REPLACE TYPE RECORD_B AUTHID DEFINER AS OBJECT (ID INT)/);
$dbh->do(q/CREATE OR REPLACE TYPE TABLE_B IS TABLE OF RECORD_B/);
$dbh->do(q/CREATE OR REPLACE TYPE RECORD_A AUTHID DEFINER AS OBJECT (ID INT,
TBL TABLE_B)/);
$dbh->do(q/CREATE OR REPLACE TYPE TABLE_A IS TABLE OF RECORD_A/);
my $count = 0;
while () {
my $stime = time;
my $j = do_it ($dbh);
my $etime = time;
$count++;
print $count ." - ".($etime - $stime)." secs\n";
}
sub do_it {
my $dbh = shift;
my $sql = <<"EOT";
SELECT
LEVEL ID,
(
SELECT
CAST (COLLECT(RECORD_A(ID, ARRAY)) AS TABLE_A) AS ARRAY
FROM
(
SELECT
LEVEL ID,
(
SELECT CAST(COLLECT(RECORD_B(ID)) AS TABLE_B) AS ARRAY
FROM (SELECT LEVEL ID FROM dual CONNECT BY LEVEL <= 10)
) ARRAY
FROM
DUAL CONNECT BY LEVEL <= 100
)
) ARRAY
FROM
DUAL CONNECT BY LEVEL <= 500
EOT
my $s = $dbh->prepare($sql);
$s->execute;
my $r = $s->fetchall_arrayref;
return $r;
}
Thanks
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
