Re: cache buffer chains/where in code
Date: Wed, 16 Dec 2009 14:42:53 -0500
Message-ID: <52a152eb0912161142o341edb35s6640d5206cf894f8_at_mail.gmail.com>
Not sure if you so all my posts, in my case it was an OS scalability bug, which was showing up as latch waits in Oracle.
2009/12/16 <Laimutis.Nedzinskas_at_seb.lt>
> good good. It was just a check.
>
> Ok, next tip:
>
> In my experience queries attacking the same buffer(s) show up in ASH and
> AWR reports as being executed lot of times and being quite heavy on buffer
> gets.
> Especially watch out for the *same query* executed lot of times at the
> *same time* by *many sessions*.
> Once I just proofed a simple case:
>
> for a given query taking hundreds buffers gets per row and returning about
> 15 rows
> and for a given hardware
> it took about 30 parallel sessions to explode "cache buffer chains" waits.
> Up to 30 sessions the server performed ok.
> The same query managed to perform about 60 sessions in parallel on a better
> server.
>
> This is the point: scalability works only this far for a particular load on
> that particular hardware.
>
> The other point is to look into application design and eliminate many
> database sessions doing essentially the same task again and again.
> I my case it was many user sessions polling the same data at some 2-3
> seconds interval just to get "real time" data display on their screens.
> One day number of users increased to the point where server just stood
> still.
>
> The solution was both to reconsider polling interval and the design in
> general and to tune the query.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------------------
>
> Please consider the environment before printing this e-mail
>
>
>
> Christo Kutrovsky
> <kutrovsky.oracle
> _at_gmail.com> To
> Laimutis.Nedzinskas_at_seb.lt
> 2009.12.11 17:20 cc
> oracle-l <oracle-l_at_freelists.org>
> Subject
> Re: cache buffer chains/where in
> code
>
>
>
>
>
>
>
>
>
>
> There are no connects/disconnects at that time.
>
> On Fri, Dec 11, 2009 at 2:16 AM, <Laimutis.Nedzinskas_at_seb.lt> wrote:.
> hehe, I concur.
> And by the same chance are there many connects and specially
> disconnects
> going on in a short time?
>
>
> ---------------------------------------------------------------------------------
>
>
> Please consider the environment before printing this e-mail
>
>
>
> Greg Rahn
> <greg_at_structuredd
> ata.org>
> To
> Sent by: Christo Kutrovsky
> oracle-l-bounce_at_f <kutrovsky.oracle_at_gmail.com>
> reelists.org
> cc
> Martin Berger
> <martin.a.berger_at_gmail.com>,
> Tanel
> 2009.12.11 01:24 Poder <tanel_at_poderc.com>,
> oracle-l
> <oracle-l_at_freelists.org>
>
> Subject
> Please respond to Re: cache buffer chains/where
> in
> greg_at_structuredda code
> ta.org
>
>
>
>
>
>
>
>
>
> By chance is this using UFS file system and not using directio
> (forcedirectio)?
>
> On Thu, Dec 10, 2009 at 11:45 AM, Christo Kutrovsky
> <kutrovsky.oracle_at_gmail.com> wrote:
> > I traced down the problem to a Solaris 10 BUG. bug_id 6642475, has
> to do
> > with kernel locks when trying to allocate contiguous memory. The
> code is
> > inefficient, and the workaround is to disable it via echo
> > "pg_contig_disable/W 1" | mdb -kw.
> >
> > I hope this helps someone out there. I don't know in what release
> it is
> > resolved.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> --
> Christo Kutrovsky
> Senior Consultant
> Pythian.com
> I blog at http://www.pythian.com/blogs/
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Christo Kutrovsky Senior Consultant Pythian.com I blog at http://www.pythian.com/blogs/ -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 16 2009 - 13:42:53 CST