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.
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
you expected.
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
one go.
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.
Martin
