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.

In the last few days we've suddenly started getting this error. The really weird thing is we get this when inserting data into a table! There was a trigger on the table we were inserting into so I disabled it but the problem did not go away. In fact it was easily reproducible with a simple insert into the "problem" table. Looking at the table in question it had around 500000 rows and included a clob but other than that nothing special. Since this is only a development system we deleted all the rows in the table and reenabled the trigger and the problem went away.

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.

I just upgraded from Ubuntu 9.10 Karmic Koala to 10.04 Lucid and my Oracle SQL Developer stopped working entirely. I didn't bother trying to look in to it as my SQL Developer was rather old anyway so I downloaded 2.1.1.64 and it didn't work either. Seeing the warnings on the SQL Developer page about supported JDKs I first checked what I had installed.