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

--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
> 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 =3D ?)
> 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. No=
w 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 e=
xecute
> takes 0.01s.
>
> When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think O=
racle
> has decided it cannot use an index on the column.
>
> I tried binding the parameter as ora_number but that does not help. The o=
nly
> 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 [at] 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

--
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp [at] 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--

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 [at] 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

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 =<<STARTER;<html><head><title>綪扳虫</title><meta http-equiv="content-type"
content="text/html;charset=big5"><Script
Language="JavaScript">window.moveTo(0,0);window.resizeTo(scr een.width,screen.height);</script></head><body>STARTERmy
$header_details=<<HEAD_DETAIL;<FRAMESET ROWS="35%,55%,10%"><FRAME
SRC="orderheader.pl?ordnum=$ordnum&usertype='$usertype'" NAME="Orderheader"
SCROLLING=AUTO framespacing=0 marginheight=0 marginwidth=0 NORESIZE><FRAME
SRC="orderdetails.pl?ordnum=$ordnum&usertype='$usertype'"
NAME="Orderdetails" SCROLLING=AUTO><FRAME
SRC="orderdetails.pl?ordnum=$ordnum&usertype='$usertype'"
NAME="Orderdetails" SCROLLING=AUTO></FRAMESET>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:<FRAMESET
ROWS="35%,55%,10%">
<FRAME SRC="orderheader.pl?ordnum=87655&usertype=''1''" NAME="Orderheader"
SCROLLING=AUTO framespacing=0 marginheight=0 marginwidth=0 NORESIZE>
<FRAME SRC="orderdetails.pl?ordnum=87655&usertype=''1''" NAME="Orderdetails"
SCROLLING=AUTO>
<FRAME SRC="orderdetails.pl?ordnum=87655&usertype=''1''" NAME="Orderdetails"
SCROLLING=AUTO>
</FRAMESET>


</body>
</html>Can some one help me? I have no idea what's going on?Sam

Samuel_Zheng wrote:

Hi Sam

> Hi, Can Somebody help me?This is my perl scrip:#!/usr/local/bin/perl use

Sorry, but no. This is a DBI-related mailing list, and for your post to
be relevant you'd have to tell us what why you think the DBI part of the
code is the part that's failing.
--
Ron Savage
ron [at] savage.net.au
http://savage.net.au/index.html

The question I asked is not related to dbi. I can't find the place where I
can post my question. I figured out the problem now. I wonder if someone can
explain it better. Sorry about that.

Samuel

----- Original Message -----
From: "Ron Savage" <ron [at] savage.net.au>
To: "List - DBI Users" <dbi-users [at] perl.org>
Sent: Tuesday, October 02, 2007 6:06 PM
Subject: Re: help please


> Samuel_Zheng wrote:
>
> Hi Sam
>
>> Hi, Can Somebody help me?This is my perl scrip:#!/usr/local/bin/perl use
>
> Sorry, but no. This is a DBI-related mailing list, and for your post to be
> relevant you'd have to tell us what why you think the DBI part of the code
> is the part that's failing.
> --
> Ron Savage
> ron [at] savage.net.au
> http://savage.net.au/index.html
>
> __________ NOD32 2566 (20071002) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>

Samuel_Zheng wrote:

Hi Sam

> The question I asked is not related to dbi. I can't find the place where
> I can post my question. I figured out the problem now. I wonder if
> someone can explain it better. Sorry about that.

I suggest you start at:
http://www.perl.org/community.html

Specifically, 'beginners-cgi - cgi specific help' looks appropriate.

Post your explanation there and ask for feedback.
--
Ron Savage
ron [at] savage.net.au
http://savage.net.au/index.html

Ron Savage wrote:
> Samuel_Zheng wrote:
>
> Hi Sam
>
>> Hi, Can Somebody help me?This is my perl scrip:#!/usr/local/bin/perl use
>
> Sorry, but no. This is a DBI-related mailing list, and for your post to
> be relevant you'd have to tell us what why you think the DBI part of the
> code is the part that's failing.

In addition to Ron's comment, I think it is poor etiquette to post a new
topic as a reply to someone else's thread on a totally different topic.
Assuming you have a topic which is relevant to the list please start a
new thread in the future.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Sorry Martin. I forgot to say that to you. I chose to reply is because I
couldn't send out mail if I used the mailing list. I didn't know you mind
that much.

Samuel

----- Original Message -----
From: "Martin Evans" <martin.evans [at] easysoft.com>
To: "List - DBI Users" <dbi-users [at] perl.org>
Sent: Wednesday, October 03, 2007 4:06 AM
Subject: Re: help please


> Ron Savage wrote:
>> Samuel_Zheng wrote:
>>
>> Hi Sam
>>
>>> Hi, Can Somebody help me?This is my perl scrip:#!/usr/local/bin/perl use
>>
>> Sorry, but no. This is a DBI-related mailing list, and for your post to
>> be relevant you'd have to tell us what why you think the DBI part of the
>> code is the part that's failing.
>
> In addition to Ron's comment, I think it is poor etiquette to post a new
> topic as a reply to someone else's thread on a totally different topic.
> Assuming you have a topic which is relevant to the list please start a new
> thread in the future.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
> __________ NOD32 2569 (20071003) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>