Yanick and I have been trying to keep on top of DBD::Oracle RTs but the time I have to do this is short. There are also some issues I don't feel in a position to investigate. There are 35 outstanding RTs which is a significant improvement on 2 years ago when it was over 50 but that is still a depressing number in my mind.
I devoted some time to try and reduce the massive DBD::Oracle rt list today. Some issues should be fixed now (see below) but in the mean time we have a number of stalled issues and some issues more than 1 year old and some more than 3 years old!
Yanick has just released a new development release of DBD::Oracle DBD-Oracle-1.43_00. As I mentioned in my blog a week or so ago, this release has a huge number of lines of code changed to remove the use of DBIS (see Changes to make DBIS more efficient and speeding up XS_DBI_dispatch()). This release removes the last few DBIS calls. As a result, if you are using a Perl built for threads (useithreads=define) this release is significantly faster than previous releases.
I've seen CPU usage nearly halved on fetches of large numbers of rows and I've had reports where people are doing lots of different selects for small numbers of rows of up to 10* quicker.
If you depend on DBD::Oracle you are strongly advised to try this version as this was over a 160K diff when I checked it in. If you notice anything broken please post an example to the dbi-users list.
A list of changes in this release is:
Thanks to Dave Mitchell for finding this, see Changes to make DBIS more efficient and speeding up XS_DBI_dispatch(). If you are using a Perl built with thread support (useithreads=define) a number of DBDs use DBIS macros from DBI which are expensive in a threaded Perl and no longer neccessary. The threads referenced above have all the gory details.
This week I've attempted to remove all DBIS usage from DBD::Oracle and have managed in all but a couple of cases. If you upgrade to DBI 1.618 and get the latest DBD::Oracle from subversion trunk (see How to create a patch using Subversion you could see a pretty substantial speed up.
Recently at $work I've been battling with some Perl code which retrieves data from Oracle via DBD::Oracle and a package function which returns a reference cursor. As I've mentioned before in this blog, the user has no select privilege on the database but can call package procedures/functions which return reference cursors and hence data from the database.
The query we have a problem with attempts to return multiple rows but one column is actually a list of primary keys from another table:
I've reported Support binding of integers so they are returned as IVs at rt for DBD::Oracle.
It appears that DBD::Oracle restricts the RowCacheSize to 128, why? I know how much memory I can afford to use for retrieving rows better than DBD::Oracle and as far as I can see this could be set a lot higher.
Just spent a few hours trying to find out why the RowCacheSize attribute in DBD::Oracle does not seem to have any affect at all. The following code demonstrates the problem: