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: byter...@hotmail.com To: martin.ev...@easysoft.com;
dbi-...@perl.org CC: dbi-users@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-...@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
