(alleged-)Null-Operation speeds up DBD::Oracle by factor 25

I Just discovered that when using:

$param = substr($param,0,length($param)); # apparently a NULLOP
$sth->execute($param);

instead of just:

$sth->execute($param);

is between 20 and 25 times faster in a select statement! no joke .. it
happens every time ... it's reproducable

Well, to gain this marvelous speedup you would need a setup like this
(the one we had on our machines):
DB: Oracle 10.2.0.1.0
DBD::Oracle 1.19
DBI 1.53
AIX 5.3
perl 5.8.2

The Database has to have:
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16 --> I'm not sure if
this changes anything

And Client has to set:
NLS_LANG=something_something.AL32UTF8
NLS_NCHAR= not set

When issuing a prepared-statement->execute() that you feed with an
Variable that has the UTF8-flag set (e.g. because that's the result of a
previous SQL-statement) then somehow Oracle takes ages for the statement
(It seems that the DB is uncapable of using indexes on the query for
some quirky reason).

The prepared statement will end up with ora_csform = SQLCS_NCHAR which
is an OCI-attribute on the OCI-bind handle and this seems to be the
cause of extremely long response times.

If you also suffer from this kind of misperformance you have the
following choices:

1- the substr variant: although this leaves the content of the variable
identically if clears the utf8-flag which helps as well (So: no real
NULLOP :-)
2- do some "Encode::utf8_downgrade" on the vars which obviously has the
same effect
3- rewrite all $sth->execute functions from:
$sth->execute($var);
to:
my $options = {};
$options->{ora_csform} = SQLCS_IMPLICIT; # --> root cause
$options->{TYPE} = SQL_VARCHAR;
$sth->bind_param(1, $var, $options);
$sth->execute();

4 - change the DBD C-Implementation (dbdimp.c): (around line 1375, in
function dbd_bind_ph() )
if (!csform && SvUTF8(phs->sv)) {
// /* try to default csform to avoid translation through non-unicode */
// if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR)) /* prefer NCHAR */
// csform = SQLCS_NCHAR;
// else if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))
// csform = SQLCS_IMPLICIT;

/* try to default csform to avoid translation through non-unicode */
if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT)) /* prefer
IMPLICIT! */
csform = SQLCS_IMPLICIT;
else if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR))
csform = SQLCS_NCHAR;

I'm not sure if it's a bug in DBD or just some 'unlucky setup' - and I'm
not sure if the change in dbdimp.c will not break other setups ... It
just works for us and maybe is useful if you ran into the same problems.

If anyone is able/willing to investigate to problem closer and maybe fix
it I would support him/her as much as I'm able to ..

regards

/Andi

--
Andreas Behal
IT-Services der Sozialversicherung Gmbh
Schiffamtsgasse 15
A-1020 Wien
mail: andi@rz.itsv.at
phone: 0043 1 711 32 / 4572

Re: (alleged-)Null-Operation speeds up DBD::Oracle by factor 25

That is a neat one.

Makes some sort of sense as you are telling OCI directly what is being put
into it. Rather that letting OCI figure out what is what.

Can you send me a empty table structure/SQL and test Perl code so I can
have a closer look into it.

I plan to be making another release at the end of March and if I can inclued
this that would be great. Would require a good deal of testing though,
against a number of Oracle clients.


"Andreas Behal" wrote in message
news:45E32C7E.3070407@rz.itsv.at...
>I Just discovered that when using:
>
> $param = substr($param,0,length($param)); # apparently a NULLOP
> $sth->execute($param);
>
> instead of just:
>
> $sth->execute($param);
>
> is between 20 and 25 times faster in a select statement! no joke .. it
> happens every time ... it's reproducable
>
> Well, to gain this marvelous speedup you would need a setup like this (the
> one we had on our machines):
> DB: Oracle 10.2.0.1.0
> DBD::Oracle 1.19
> DBI 1.53
> AIX 5.3
> perl 5.8.2
>
> The Database has to have:
> NLS_CHARACTERSET AL32UTF8
> NLS_NCHAR_CHARACTERSET AL16UTF16 --> I'm not sure if
> this changes anything
>
> And Client has to set:
> NLS_LANG=something_something.AL32UTF8
> NLS_NCHAR= not set
> When issuing a prepared-statement->execute() that you feed with an
> Variable that has the UTF8-flag set (e.g. because that's the result of a
> previous SQL-statement) then somehow Oracle takes ages for the statement
> (It seems that the DB is uncapable of using indexes on the query for some
> quirky reason).
>
> The prepared statement will end up with ora_csform = SQLCS_NCHAR which is
> an OCI-attribute on the OCI-bind handle and this seems to be the cause of
> extremely long response times.
>
> If you also suffer from this kind of misperformance you have the following
> choices:
>
> 1- the substr variant: although this leaves the content of the variable
> identically if clears the utf8-flag which helps as well (So: no real
> NULLOP :-)
> 2- do some "Encode::utf8_downgrade" on the vars which obviously has the
> same effect
> 3- rewrite all $sth->execute functions from:
> $sth->execute($var);
> to:
> my $options = {};
> $options->{ora_csform} = SQLCS_IMPLICIT; # --> root cause
> $options->{TYPE} = SQL_VARCHAR;
> $sth->bind_param(1, $var, $options);
> $sth->execute();
>
> 4 - change the DBD C-Implementation (dbdimp.c): (around line 1375, in
> function dbd_bind_ph() )
> if (!csform && SvUTF8(phs->sv)) {
> // /* try to default csform to avoid translation through non-unicode
> */
> // if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR)) /* prefer NCHAR */
> // csform = SQLCS_NCHAR;
> // else if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))
> // csform = SQLCS_IMPLICIT;
>
> /* try to default csform to avoid translation through non-unicode */
> if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT)) /* prefer
> IMPLICIT! */
> csform = SQLCS_IMPLICIT;
> else if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR))
> csform = SQLCS_NCHAR;
>
> I'm not sure if it's a bug in DBD or just some 'unlucky setup' - and I'm
> not sure if the change in dbdimp.c will not break other setups ... It just
> works for us and maybe is useful if you ran into the same problems.
>
> If anyone is able/willing to investigate to problem closer and maybe fix
> it I would support him/her as much as I'm able to ..
>
> regards
>
> /Andi
>
> --
> Andreas Behal
> IT-Services der Sozialversicherung Gmbh
> Schiffamtsgasse 15 A-1020 Wien mail: andi@rz.itsv.at
> phone: 0043 1 711 32 / 4572
>