Re: (alleged-)Null-Operation speeds up DBD::Oracle by factor 25
- From: scoles@xxxxxxxxxxx (John Scoles)
- Date: Tue, 27 Feb 2007 07:00:53 -0500
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" <andi@xxxxxxxxxx> wrote in message
news:45E32C7E.3070407@xxxxxxxxxxxxx
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@xxxxxxxxxx
phone: 0043 1 711 32 / 4572
.
- References:
- (alleged-)Null-Operation speeds up DBD::Oracle by factor 25
- From: Andreas Behal
- (alleged-)Null-Operation speeds up DBD::Oracle by factor 25
- Prev by Date: (alleged-)Null-Operation speeds up DBD::Oracle by factor 25
- Next by Date: Re: DBD::Oracle-1.19 installation error
- Previous by thread: (alleged-)Null-Operation speeds up DBD::Oracle by factor 25
- Index(es):
Relevant Pages
|
