Re: cache buffer chains/where in code
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Dec 2009 11:30:30 +0200
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
@gmail.com> To
Laimutis.Nedzinskas@xxxxxx
2009.12.11 17:20 cc
oracle-l <oracle-l@xxxxxxxxxxxxx>
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@xxxxxx> 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@structuredd
ata.org>
To
Sent by: Christo Kutrovsky
oracle-l-bounce@f <kutrovsky.oracle@xxxxxxxxx>
reelists.org
cc
Martin Berger
<martin.a.berger@xxxxxxxxx>,
Tanel
2009.12.11 01:24 Poder <tanel@xxxxxxxxxx>,
oracle-l
<oracle-l@xxxxxxxxxxxxx>
Subject
Please respond to Re: cache buffer chains/where
in
greg@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@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
Christo Kutrovsky
Senior Consultant
Pythian.com
I blog at http://www.pythian.com/blogs/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
