Re: Problem with Oracle collections/objects

On 21/12/11 13:16, Martin J. Evans wrote:
>> On 21/12/11 13:05, Charles Jardine wrote:

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

Certainly less sign of memory leakage.

Incidentally by accident I ran my first test example with perl 5.10.0 and then I reran it when I realised on I'd made that mistake and my test example speeded up from 11 to 12 seconds to 7 to 8 seconds. No idea what causes that.

With the real code we also see less memory usage as you'd expect.

Thanks

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