Front page | perl.dbi.dev |
Postings from December 2011
Re: Problem with Oracle collections/objects
From: Martin J. Evans
December 12, 2011 12:24
Re: Problem with Oracle collections/objects
Message ID: 4EE662E0.email@example.com
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:
>>>> 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.
>>> Do you find, as I do, that you can chase your problem away by
>>> adding 'NOT FINAL' to the end of each of the specifications
>>> of the two objects (as opposed to collections)? I find this
>>> helps with my version of the code. This intrigues me.
>>> I would expect non-final types to be handled worse, not better.
>> The problem does appear to go away when NOT FINAL is added.
>> However, it is slower when not final is used.
>> I'd still like to track this down.
> I wasn't suggesting NOT FINAL as possible solution to your problem.
I must have misunderstood when you said "Do you find, as I do...".
Anyway, adding NOT FINAL does make the problem go away; we've done 1000s
of iterations of that test code without any problem and our test system
with the real types and SQL no longer exhibits the problem, although it
is a little slower.
> It is a waste of resources to declare an object type NON FINAL unless
> you actually need to use sub-types. I asked you to test NON FINAL
> because I had expected to make matters worse, and was very surprised
> when it did not.
As above, I must have misunderstood. I thought you were suggesting
adding NOT FINAL made the problem go way but that was contrary to what
> When I run your test script, the first 36 iterations are quick
> (2 secs) and the 37th is slow (45 secs).
Similar here, except for actual timings.
> I have found I can chase the problem away in two different
> ways. The slow-down does not occur if I change the script so it
> uses the same statement handle for all iterations rather than
> preparing a new one each time. This suggests that, if there is
> a resource drain of any sort, the drain might be associated with
> prepare/destroy rather than with fetch.
ok, that is useful to know. I can experiment with this and as you say it
may indicate the sth destruction is taking a long time. However, if you
profile the code all the time goes in fetch.
> Another way removing the slow-down is to re-authenticate,
> with $dbh->func('uuuu', 'pppp', 'reauthenticate'), after
> each iteration. This causes Oracle to create a new session
> within the existing connection. Re-authentication is much
> faster than creating a new connection. This might provide
> a workaround for you.
Interesting. We have other times when we reconnect right now -
especially on our dev system. We register for Oracle alerts but when a
package in the session is changed, the session is invalidated and the
wait_any etc calls are invalidated so we reconnect and reissue the
wait_one/wait_any calls. I did not know about reauthenticate; that might
prove useful in other areas.
> All that said, I still don't have any real clue as to what
> is going on. I had suspected that the pin counts on TDOs
> (Type Descriptor Objects) might be a problem, but adding
> the OCIObjectUnpin calls I thought might be necessary made
> no difference.
> I shall stare at the prepare/destroy code a bit more before
> I give up.
Many thanks Charles.
I'm juggling a few problems right now but we are not going to let this
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.