Very slow executes with utf8 integer parameters in DBD::Oracle
Very slow executes with utf8 integer parameters in DBD::Oracle
am 14.09.2007 19:03:15 von Martin.Evans
I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle
when using utf8 and I need to as my data is utf8 in Perl.
Grossly simplified my code does:
o select integer_primary_key_field from table
o prepare(select from another_table where field = ?)
o execute($inter_primary_key_value_retrieved_from_select)
This query is vastly more complex than this really
Even though the field retrieved from the first table is an integer when
I look at it, Perl has utf8 flag set. When these utf8 encoded integers
are then passed into the execute for a select on another table the
execute takes 0.7s. Now that may not sound a lot to you but this query
gets runs a lot. If I downgrade the integer parameter with
utf8::downgrade before passing it to execute the execute takes 0.01s.
When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think
Oracle has decided it cannot use an index on the column.
I tried binding the parameter as ora_number but that does not help. The
only thing which seems to work is to downgrade the parameter from utf8.
Any ideas?
Thanks.
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: Very slow executes with utf8 integer parameters in DBD::Oracle
am 17.09.2007 10:20:54 von hjp
--4bRzO86E/ozDv8r1
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
On 2007-09-14 18:03:15 +0100, Martin Evans wrote:
> I have NLS_LANG=3DAMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle=
when=20
> using utf8 and I need to as my data is utf8 in Perl.
>=20
> Grossly simplified my code does:
>=20
> o select integer_primary_key_field from table
> o prepare(select from another_table where field =3D ?)
> o execute($inter_primary_key_value_retrieved_from_select)
> This query is vastly more complex than this really
>=20
> Even though the field retrieved from the first table is an integer when I=
look=20
> at it, Perl has utf8 flag set. When these utf8 encoded integers are then =
passed=20
> into the execute for a select on another table the execute takes 0.7s. No=
w that=20
> may not sound a lot to you but this query gets runs a lot. If I downgrade=
the=20
> integer parameter with utf8::downgrade before passing it to execute the e=
xecute=20
> takes 0.01s.
>=20
> When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think O=
racle=20
> has decided it cannot use an index on the column.
>=20
> I tried binding the parameter as ora_number but that does not help. The o=
nly=20
> thing which seems to work is to downgrade the parameter from utf8.
>=20
> Any ideas?
This may be the same problem I ran into a few weeks ago. See
http://www.mail-archive.com/dbi-users@perl.org/msg30138.html
I have a patch for this but I still haven't gotten around to testing it,
so even though it's only a few lines I don't want to post it yet. Feel
free to contact me off-list if you want to try it.
The workaround which I actually use in production code is to set
NLS_NCHAR=3DUS7ASCII. Of course this isn't a good idea if you have
nvarchar2 or nclob columns. Explicitely binding with the correct cs_form
also works:
$sth->bind_param(1, $name, { ora_csform =3D> SQLCS_IMPLICIT });
hp
--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
--4bRzO86E/ozDv8r1
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFG7jjmMdFfQa64PCwRAsurAJ9aZ8eyw34EZlDrP5AXwzEfCT0UjACg gdiv
NjvWEQm2R20jAgfaFO8k02s=
=DfMx
-----END PGP SIGNATURE-----
--4bRzO86E/ozDv8r1--
Re: Very slow executes with utf8 integer parameters in DBD::Oracle
am 17.09.2007 13:27:58 von Martin.Evans
Peter J. Holzer wrote:
> On 2007-09-14 18:03:15 +0100, Martin Evans wrote:
>> I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle when
>> using utf8 and I need to as my data is utf8 in Perl.
>>
>> Grossly simplified my code does:
>>
>> o select integer_primary_key_field from table
>> o prepare(select from another_table where field = ?)
>> o execute($inter_primary_key_value_retrieved_from_select)
>> This query is vastly more complex than this really
>>
>> Even though the field retrieved from the first table is an integer when I look
>> at it, Perl has utf8 flag set. When these utf8 encoded integers are then passed
>> into the execute for a select on another table the execute takes 0.7s. Now that
>> may not sound a lot to you but this query gets runs a lot. If I downgrade the
>> integer parameter with utf8::downgrade before passing it to execute the execute
>> takes 0.01s.
>>
>> When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think Oracle
>> has decided it cannot use an index on the column.
>>
>> I tried binding the parameter as ora_number but that does not help. The only
>> thing which seems to work is to downgrade the parameter from utf8.
>>
>> Any ideas?
>
> This may be the same problem I ran into a few weeks ago. See
> http://www.mail-archive.com/dbi-users@perl.org/msg30138.html
>
> I have a patch for this but I still haven't gotten around to testing it,
> so even though it's only a few lines I don't want to post it yet. Feel
> free to contact me off-list if you want to try it.
>
> The workaround which I actually use in production code is to set
> NLS_NCHAR=US7ASCII. Of course this isn't a good idea if you have
> nvarchar2 or nclob columns. Explicitely binding with the correct cs_form
> also works:
>
> $sth->bind_param(1, $name, { ora_csform => SQLCS_IMPLICIT });
>
>
> hp
>
Thank you Peter. Rather stupidly, I had marked the post your refer to as
particularly noteworthy but forgot I'd seen it.
My time for the query has come down from at best .7s (some were a lot
worse) to 0.035s - a huge difference. All I've done to achieve this is:
1) bound the select columns which are integers as ORA_NUMBER (which I
don't think is having an real affect as the numbers I get back are still
marked utf-8).
2) added SQLCS_IMPLICIT to the bound parameters which are numbers (keys
in my case).
Like you found, when I look at the QEP, I find Oracle is doing a lot of
different things now including the creation of a view and use of a index
it was not using before.
All my data is utf8 so this problem probably exists elsewhere as well.
Does anyone know what the disadvantage of changing DBD::Oracle to
default to SQLCS_IMPLICIT instead of SQLCS_NCHAR is?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: help please
am 02.10.2007 17:00:45 von samuel_zheng
Hi, Can Somebody help me?This is my perl scrip:#!/usr/local/bin/perl use
CGI;use CGI::Carp "fatalsToBrowser";use DBI;use strict;my $q = new CGI;my
$fname;my $sql;my $ordnum=$q->param('ordernum');my
$usertype=$q->param('usertype');my $starter =<綪æ³è«
content="text/html;charset=big5">
Language="JavaScript">window.moveTo(0,0);window.resizeTo(scr een.width,screen.height);STARTERmy
$header_details=<
SRC="orderheader.pl?ordnum=$ordnum&usertype='$usertype'" NAME="Orderheader"
SCROLLING=AUTO framespacing=0 marginheight=0 marginwidth=0 NORESIZE>
SRC="orderdetails.pl?ordnum=$ordnum&usertype='$usertype'"
NAME="Orderdetails" SCROLLING=AUTO>
SRC="orderdetails.pl?ordnum=$ordnum&usertype='$usertype'"
NAME="Orderdetails" SCROLLING=AUTO>HEAD_DETAILprint
$starter;print $header_details;print $q->end_html();exit(0);it was working
before but it suddenly printed out the following in my IE:
ROWS="35%,55%,10%">
SCROLLING=AUTO framespacing=0 marginheight=0 marginwidth=0 NORESIZE>
SCROLLING=AUTO>
SCROLLING=AUTO>