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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >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 >
