On 21/12/11 13:05, Charles Jardine wrote:
On 12/12/11 20:24, Martin J. Evans wrote:
On 12/12/2011 18:15, Charles Jardine wrote:
On 12/12/11 16:13, Martin J. Evans wrote:
On 09/12/11 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.
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.
The changes I have made so far do not fix your problem, but
the problem might provide me with a useful test case.
I shall stare at the prepare/destroy code a bit more before
I give up.
I'm juggling a few problems right now but we are not going to let this one go.
I think I have found the resource drain which causes your slow-down
problem. Buffers implicitly allocated in the cache by OCIDefineObject()
need to be deallocated explicitly by OCIObjectFree() 'when they are no
longer needed'. This is counter-intuitive, but it is documented
in the reference description of OCIDefineObject(), and has been since
Oracle 8.
I have attached a demonstration patch against the trunk for you to test.
This patch is not fit to go into service. We need a logging macro for
OCIObjectFree().
If at any stage, you want a tester for any changes you've made to the
code please put me in the front of the queue.
Now I have found this resource drain, I feel happier about submitting
a patch intended to remove all resource drains from the object/collection
stuff. Expect me to be offering you something bigger to test after the
Christmas break.
Many thanks Charles. I will test this out.
As this was failing so badly for us the production code is now actually pulling
a flat result-set and munging it in Perl into the arrays we required before
converting to JSON (currently taking 7s instead of 25s). This method does not
leak any memory, does not suffer from any performance degradation and is
actually faster even though it is pulling 10's of thousands more rows from the
database and having to process each one. Of course, the object/collection
method required a deep copy to get the actual data in Perl and that added time
too - I never really got to the bottom of why that was so.
Our overriding goal is speed so whatever method is faster and uses less
resources will win for us. I'll try your patch out and see where I get.
Thanks.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
