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
c...@cam.ac.uk 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);
+ }
}
