On 09/12/2011 18:02, Charles Jardine wrote:
On 09/12/11 14:01, Martin J. Evans wrote:
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.
Charles, I had you in mind when I wrote "If anyone is around who wrote
or has worked on" but I didn't want to pile the pressure on you (and
still don't) - thanks for answering.
I have been re-writing parts of the of the object/collections
support with a view to correcting aspects of the storage management.
The existing code has some store drains, and some incorrect
freeing of Perl SVs, which can lead to crashes.
ah, I was not aware of that - we currently use collections in one piece
of SQL but it is run a lot. I've not noticed the memory use going up but
then I've also not tested it properly for leaks as we never saw a reason
to do so.
The changes I have made so far do not fix your problem, but
the problem might provide me with a useful test case.
excellent - does that mean you've run my example and it exhibits the
problem I describe? I assume so.
Do you find, as I do, that you can chase your problem away by
adding 'NOT FINAL' to the end of each of the specifications
of the two objects (as opposed to collections)? I find this
helps with my version of the code. This intrigues me.
I would expect non-final types to be handled worse, not better.
Don't know. I'll have to try that., I'm not familiar with 'not final' so
I'll have to look that up (I didn't write the SQL we are using right now
and to be honest it is ALOT bigger and more complex then the example I
whittled it down to).
Many thanks for responding to my post. My colleague and I have spent
days just tracking down where this problem was in our system (as it
works for hours or days then goes wrong and between starting and going
wrong we've issued literally hundreds of thousands of SQL statements)
and a further day reproducing it in code not using our schema (which I
cannot post and is way too complicated anyway). The DBD::Oracle tracing
produces way too much output to help here but I'm still working on it
although a separate exercise is trying to remove the collections
altogether - I doubt that will produce something as efficient but then
again the current situation is only efficient for a few hours or days.
I would be happy to work with you and try and track this down. I'll try
'not final' but if you have any changes to DBD::Oracle in this area I
could test or help with please let me know. I'm still looking at the C
XS but if you've already improved it I might be wasting some time here.
I'm also usually around on #dbi irc if that helps.
Thanks again.
Martin
