Re: NLS_LANG=AMERICAN_AMERICA.AL32UTF8 in bash script in Cygwin

From: Mark Strickland <strickland.mark_at_gmail.com>
Date: Sun, 5 Sep 2010 14:34:59 -0700
Message-ID: <AANLkTim2oEb7EqpaE-h++Ceoxrpp5Mh3ramtJ=rB1YW9_at_mail.gmail.com>

Thanks for you reply, Martin. There was also one other person who replied privately. Much appreciated.

I did end up finding a few things on Metalink:

http://forums.oracle.com/forums/thread.jspa?messageID=4551845&tstart=0

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocTitle=Bug%206350579%20-%20SQLPLUS%20spooling%20with%20trimspool%20and%20linesize%20is%20slow&bmDocType=PATCH&bmDocID=6350579.8&from=BOOKMARK&viewingMode=1143&bmDocDsrc=KB%29%29

Both of which lead to a bug which is fixed in a 10.2.0.4 patch. As a result, I discovered that the combination of a wide linesize and trimspool on is the problem. When I don't set those two parameters, the queries run quickly and the Arabic is preserved. I now need to apply the patch that supposedly fixes this bug. If necessary, I'll also try 11gR2. I think I can use that version. The source databases are on 10gR2 on Unix with the AL32UTF8 characterset. I query those databases to csv files on the Windows box, then load the csv files into a database on the Windows box (can't use database links...long story).

I also tested the script with WE8MSWIN1252, which ran quickly, but then I lost the Arabic, and I tested the Arabic charactersets which likewise clobbered the Arabic.

Always an adventure,
Mark

On Sat, Sep 4, 2010 at 12:52 AM, Martin Busik <martin.busik_at_busik.de> wrote:

> Hello,
>
> > Without NLS_LANG set, the
> > query takes 3-6
> > seconds to run and returns 48848 rows and results in 1136
> > buffer gets and
> > 1081 phys reads. With NLS_LANG set, it takes 70-90 seconds.
>
> Check out which character set is used on the client side when NLS_LANG
> is not set - actually, the client has to use something.
>
> Does your query use "order by"? With different character sets there are
> different
> orders, you might replace "order by column" by
>
> "order by NLSSORT(column, 'NLS_SORT = BINARY') " to see, if there is any
> difference.
>
> Last, but not least, as far as i remember, with an OCI-client, the
> character
> set
> conversion occurs on the client side (compared to a jdbc-thin client, which
> does the conversion
> on the server side). Try to set a client trace (see
> http://www.orafaq.com/wiki/Sqlnet.ora), and
> find out if there is anything suspect.
>
> Cheers,
> Martin
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Sep 05 2010 - 16:34:59 CDT