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: [EMAIL PROTECTED]
phone: 0043 1 711 32 / 4572
