Re: Performance with SCAN listener in 11g RAC

From: Lu Jiang <lu.jiang69_at_yahoo.com>
Date: Thu, 2 May 2013 08:41:55 -0700 (PDT)
Message-ID: <1367509315.93474.YahooMailNeo_at_web120003.mail.ne1.yahoo.com>

Thank you all for your messages.
The  comparison was based on the same node and had run the same query multiple times for each of the scenario. Just tried to run the query again, the time spend on SCAN connect and non-scna connection are same now -12 seconds.  
Regards,
Lu 
 

---

From: Andrew Kerber <andrew.kerber_at_gmail.com> To: Alan Bort <cicciuxdba_at_gmail.com> Cc: Lu Jiang <lu.jiang69_at_yahoo.com>; list <oracle-l_at_freelists.org> Sent: Thursday, May 2, 2013 10:25 AM
Subject: Re: Performance with SCAN listener in 11g RAC

There are a lot of reasons why it may be different.  As Guillermo notes, it could be a caching issue.  It could also be an issue with cache fusion, since using the scan listener you could be connecting to a different node. There are many reasons why the times could be different.  Remember the listener just hands off the connection request to the database, so you are really measuring query performance on the database no matter how you are connecting.  You might try looking at the instance you are connected to prior to running the query each time.

On Thu, May 2, 2013 at 9:20 AM, Guillermo Alan Bort <cicciuxdba_at_gmail.com>wrote:

> Did you run a trace on all the sessions? are you sure the query is exactly
> the same and that the generated plan is the same? are you clearing the
> buffer cache between runs? this sounds like the second result is cached and
> therefore faster...
> and to answer your question, no, there is no guideline for stats gathering
> in RAC vs single instance, the differences are usually somewhere in the
> application logic (stuff that manages concurrency) or the data model
> design.
>
> hth
>
> Alan.-
>
>
> On Thu, May 2, 2013 at 11:10 AM, Lu Jiang <lu.jiang69_at_yahoo.com> wrote:
>
> > Hi DBA gurus,
> >
> > I am do a performance test. Find that running same query using tns
> > connection string with SCAN host name is 5 seconds slow than using tns
> > connection string points to a specific host name (17seconds  VS. 12
> > seconds). Is this normal?
> > Is there are any special requirement to run gather schema stats tasks in
> a
> > RAC environment?
> > Could anyone shed light on these?
> >
> > Thanks,
> > Lu
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


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

Received on Thu May 02 2013 - 17:41:55 CEST