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-dev@perl.org
> CC: dbi-us...@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
