martin-evans.me.uk Always finish the day on a success

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 been using DBD::Oracle for a fairly major application here for some time. Most of the code is actually in Oracle procedures but some remaining code is in Perl. Since we upgraded to Oracle 11Gr2 DBD::Oracle's 26exex_array.t test has always failed. I've not been too bothered about this since we don't use the execute_array method (implemented in DBI for most drivers but implemented in DBD::Oracle directly). However, testing has unearthed some worrying results.

Just hit a rather annoying problem in DBD::Oracle 1.24a today. Our application contains all the logic in database procedures and in some cases database result-sets are returned to our Perl code via a reference cursor. Due to problems with ora_auto_lobs not working on reference cursors in previous DBD::Oracle releases we generally use Data Interface for Lob Locators where ora_lob_length returns a lobs sie and ora_lob_read is used to retrieve it.

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: