Well partial answer for you Martin.
Seems Cast Array and Collect are new extended SQL commands which allow you to
directly select embedded types with SQL.
So in this case we are mixing our metaphores.
DBD::Oracle will do the casting for you and your code is trying to do the
casting as well so in the end it gets muddleded. The CAST and crew doing its
work on the back end my DBD::ORacle code playing about in the front.
This I could see as a problem as some one who wants 'CAST' to work will find it
doesn't which is bad. Though with embeded objects you have no need to use them.
Will have to put in some code someplace to detect these new sql commands and
ignor the built in DBD::Oracle object.
No time for it today though.
One way to check this theory is give it a try with an early version of
DBD::Oracle that does not have the object stuff built in. I think 19 will do
it.
So iIf you can run your test below on a 1.19 install against 10g box and see
what you get?
>From what I see we are just running out of itter resouses in the OCI client so
>it has slows down untill some are freed up, I have seen this before with
>embedded objects when they go very deep with many types. One has to remeber
>that the Varray, object and type are not really what one thinks they are.
>When you get into the actulle implimetation on the DB level you will see they
>are just flat releational tables. So a Varray in a table like this table(id
>number ,my_Varray number(6) ) would actully be two tables one with ID and a
>ref object field that points to another table that contains the ref and next
>to it you varray values. Not the most efficient way to store your data and
>why it has largely been ignored by most DBA since it came out way back in
>oracle 8
I will play a little more when I get some time and see if I can rewrite the
code to not use the CAST and array
Cheers
John
> From: byter...@hotmail.com
> To: martin.ev...@easysoft.com; dbi-dev@perl.org
> CC: dbi-us...@perl.org
> Subject: RE: Problem with Oracle collections/objects
> Date: Tue, 13 Dec 2011 06:42:12 -0500
>
>
> 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-dev@perl.org
> > CC: dbi-us...@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-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
> >
>
