Re: Windows desktop sqlplus client faster than solaris sqlplus client

From: P Suresh <suresh.gm_at_gmail.com>
Date: Thu, 12 May 2011 17:05:37 -0700
Message-ID: <BANLkTimC_H+DAiYxEZH7QT2Pb85MDW4LBw_at_mail.gmail.com>

Hi team,

This was the issue, their NLS charecterset was different, after making them same the sql runs faster from solaris client also.

Database characterset is NLS_CHARACTERSET WE8MSWIN1252 NLS_NCHAR_CHARACTERSET AL16UTF16

Windows NLS_LANG is                  AMERICAN_AMERICA.WE8MSWIN1252
                Forms NLS_LANG is

AMERICAN_AMERICA.WE8MSWIN1252 How do we fix this in the Oracle Forms10g server? The default.env already has the NLS_LANG setting in it. Is there somewhere I need to set it in the tnsnames or sqlnet files? Can it be set in the tnsnames.ora file?

Thanks a lot to everyone for helping out and specially to Brandon for pinpointing the root cause!

Suresh

On Thu, May 12, 2011 at 4:34 PM, P Suresh <suresh.gm_at_gmail.com> wrote:
> Thanks for the tips Brandon, will compare the NLS settings in both the clients.
>
> Suresh
>
> On Thu, May 12, 2011 at 3:34 PM, Allen, Brandon
> <Brandon.Allen_at_oneneck.com> wrote:
>> Just a couple guesses: check the character sets of the clients to make sure there isn't some odd conversion problem causing it, and check to make sure it's getting hard parsed in both cases to be sure you're comparing apples to apples.
>>
>> Regards,
>> Brandon
>>
>>
>>
>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of P Suresh
>> Sent: Thursday, May 12, 2011 2:56 PM
>> To: oracle-l_at_freelists.org
>> Subject: Windows desktop sqlplus client faster than solaris sqlplus client
>>
>> Hi Gurus,
>>
>> One particular sql runs in less than a sec when run from sqlplus
>> client on desktop whereas it takes 85 seconds for it to complete when
>> run directly on the solaris 10 box where they have this
>> 11g database installed. Since it is the same sql, tuning the sql can
>> be ruled out I think. The execution plans are the same and the cost
>> and cardinality are identical.
>>
>> In the 10046 traces, I see it has taken 80 seconds for parsing and
>> fetch for the solaris client but just 2 seconds for the deskptop
>> sqlplus client; I was unable to read which step consumed more time.
>> And it takes another 131 seconds until completion for the bad one
>> whereas the good one completes in another 25 seconds.
>>
>> One difference I see is in the service name SERVICE NAME:(SYS$USERS)
>> for desktop client and SERVICE NAME:(olisdev.leg.state.or.us) for
>> solaris client.
>>
>> The only difference in 10046 traces is here:
>>
>> Solaris sqlplus client
>> WAIT #2: nam='SQL*Net message from client' ela= 140591934 driver
>> id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6200280507074
>> CLOSE #2:c=0,e=749,dep=0,type=0,tim=6200280508169
>>
>> Windows desktop sqlplus client
>> WAIT #2: nam='SQL*Net message from client' ela= 221491 driver
>> id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6199138319534
>> CLOSE #2:c=0,e=95,dep=0,type=0,tim=6199138320012
>>
>> Can someone please help how to deteremine why the same sql takes more
>> time in solaris sqlplus client? Please let me know if you need to see
>> the complete 10046 traces. Will network/listener/solaris dtraces help?
>>
>> Thanks a lot,
>> Suresh
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>> Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
>>
>

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

Received on Thu May 12 2011 - 19:05:37 CDT