Re: NLS_LANG=AMERICAN_AMERICA.AL32UTF8 in bash script in Cygwin
- From: Mark Strickland <strickland.mark@xxxxxxxxx>
- To: martin.busik@xxxxxxxx
- Date: Tue, 7 Sep 2010 14:07:31 -0700
Solution!
I applied the 10.2.0.5 patchset and that fixed the bug. My test query takes
10 seconds now, a dramatic improvement.
Mark
On Sun, Sep 5, 2010 at 2:34 PM, Mark Strickland
<strickland.mark@xxxxxxxxx>wrote:
> 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@xxxxxxxx>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
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
Other related posts:
