Faster DBD::Oracle if you are using a threaded Perl

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.

A simple benchmark with a fetchall_arrayref or a loop calling fetchrow_arrayref on a table with 1 million rows returns these results:

perl 5.14.2

Perl without threads:
1.42:
read: 83 wallclock secs (37.55 usr + 5.77 sys = 43.32 CPU) @ 0.23/s (n=10)
readperrow 85 wallclock secs (39.53 usr + 4.98 sys = 44.51 CPU) @ 0.22/s
(n=10)

subversion trunk:
read: 85 wallclock secs (40.23 usr + 6.22 sys = 46.45 CPU) @ 0.22/s (n=10)
readperrow: 85 wallclock secs (40.06 usr + 5.32 sys = 45.38 CPU) @ 0.22/s
(n=10)

Perl with threads:
1.42
read: 128 wallclock secs (86.11 usr + 5.41 sys = 91.52 CPU) @ 0.11/s (n=10)
readperrow: 137 wallclock secs (95.33 usr + 4.86 sys = 100.19 CPU) @ 0.10/s
(n=10)

subversion trunk:
read: 94 wallclock secs (52.55 usr + 5.68 sys = 58.23 CPU) @ 0.17/s (n=10)
readperrow: 104 wallclock secs (62.74 usr + 5.06 sys = 67.80 CPU) @ 0.15/s
(n=10)

So 92s of CPU time reduced to 58s with fetchall_arrayref and 100s reduced to 68s for the loop calling fetchrow_arrayref. That is certainly worth having. I'd guess it is so significant because DBD::Oracle contains a massive amount of trace code so it was making a lot of calls to DBIS->debug simply to find if tracing was enabled.

If you depend on DBD::Oracle you are strongly advised to try the version in subversion trunk 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.

Other DBDs could benefit in a similar fashion. I'd removed DBIS usage from DBD::ODBC some years ago but Tim spotted a few other changes to remove D_imp_xxx macro usage and I've commited those too (I doubt you'll see anything that much from them though).