Re: Problem with Oracle collections/objects

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