Re: Problem with Oracle collections/objects

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.

-- 
Charles Jardine - Computing Service, University of Cambridge
[email protected]    Tel: +44 1223 334506, Fax: +44 1223 334679

Index: dbdimp.c
===================================================================
--- dbdimp.c	(revision 15051)
+++ dbdimp.c	(working copy)
@@ -3967,8 +3967,11 @@
 	sv_free(fbh->name_sv);
 	if (fbh->desc_h)
 	OCIDescriptorFree_log(fbh->desc_h, fbh->desc_t);
-	if (fbh->obj)
+	if (fbh->obj) {
+		if (fbh->obj->obj_value) 
+			OCIObjectFree(fbh->imp_sth->envhp, fbh->imp_sth->errhp, fbh->obj->obj_value, (ub2)0);
 		Safefree(fbh->obj);
+	}
 
 }