Well I am able to recreate it which is a good thing. Like you said about 52
iterations then it begins to gobble up memory in both oracle and perl so me
think i is running out of resources.
I will give it a closer look
Cheers
John
> From: byter...@hotmail.com
> To: martin.ev...@easysoft.com; dbi-...@perl.org
> CC: dbi-users@perl.org
> Subject: RE: Problem with Oracle collections/objects
> Date: Sun, 11 Dec 2011 12:00:12 -0500
>
>
> Well I wrote the code in DBD::Oracle for embedded objectes/types so I am the
> one to blame for that. From the SQL and connection string below looks like
> you are using the Oralce sql extensions to get the data back and not the
> native DBD::Oracle/OCI object selections so we might be looking at something
> else in DBD::Oracle. Will have to load this puppy up and have a look at the
> verbose trace
>
> Will have to wait till monday though swamped with SlJs here today
>
> Cheers
> John Scoles
>
> > Date: Fri, 9 Dec 2011 14:01:43 +0000
> > From: martin.ev...@easysoft.com
> > To: dbi-...@perl.org
> > CC: dbi-users@perl.org
> > Subject: Problem with Oracle collections/objects
> >
> > 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
>
