Re: Problem with Oracle collections/objects

On 13/12/11 13:42, John Scoles wrote:
>
> Well partial answer for you Martin.

Thanks for looking at this.

> Seems Cast Array and Collect are new extended SQL commands which
> allow you to directly select embedded types with SQL.

Just FYI I didn't write that 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.

hmm, I didn't know this and I'm still not sure exactly what you mean.

> 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?

I can probably try that.

  
> 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

There is no data stored in the database like this at all. That code is just an example. The code using the types and collection is attempting to turn 3 tables in the database into structured lists:

table m (id integer primary key)
table e (id integer primary key)
table me (me_id integer primary key, m_id integer, e_id integer)
   foreign keys m_id and e_id back into table m and e

then output:

m_id1 me_id1, me_id2, me_id3
m_id2 me_id4, me_id2, me_id3

that is, for each m there are multiple e's and the relationship is table me. The result is converted into JSON and stored in a file. IIRC we tried loads of other ways of doing it but:

1. if the list of me_ids got too big they would not fit into a varchar (32K limit).
2. we had to split them up on the outside in Perl on a ',' and that wasted a load of time
  
e.g., wm_concat

> 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

Can you point me at that code please.
  
> Cheers John

Thanks again John.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

>
>> From: [email protected] To: [email protected];
>> [email protected] CC: [email protected] 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: [email protected] To: [email protected];
>>> [email protected] CC: [email protected] 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:
>>>> [email protected] To: [email protected] CC:
>>>> [email protected] 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
>>>
>>
>