RE: Addressing buffer buisy waits in 10g with ASSM
Date: Mon, 17 Sep 2012 22:06:11 -0400
Message-ID: <01a401cd9542$2d862a80$88927f80$_at_rsiz.com>
Without the statements driving the busy waits we can only speculate.
If you're spinning up index entries off a sequence the solution might be quite different than if you're splitting a lot of leaf blocks by filling in or lengthening a column in a multi-column index or simply inserting a lot of entries in a small range of an existing index.
If you never will need (careful about that prediction!) a range scan on a monotonically increasing key, you might try a reverse index.
Possibly moving the index to a non-ASSM tablespace might improve performance with your additional free lists idea. I'm trying to recall whether ASSM was really ready for prime time in 10gR2. (Not breaking much is very different from performing well under stress.)
If there is a safe column (or you can add one and create a cover view excluding the dummy rows), it is sometimes possible to pre-flate index structures with dummy rows in non-peak windows, but I wouldn't do that lightly. If you cannot predict the keys you'll need for real rows and manage cleaning out the dummies you'll just be creating a problem. The idea is to create a lot of index blocks as empty as you can make them without Oracle cleaning them up so you're not allocating them at peak load. If they end up significantly bigger after your peak with the insertions going on then you'll pay extra for having a lower density of row references per block for all future range scans and full index scans until you rebuild the index. But if you have another wave of insertions flowing in, you're not going to want to rebuild to reclaim the intentionally generated space. Overall this costs more computer resources, but if your load ebbs and flows you might not care about extra load at ebb.
What will help you? That would require concrete study of your actual case.
Good luck.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Saad Khan
Sent: Monday, September 17, 2012 12:40 PM
To: oracle-l_at_freelists.org
Subject: Re: Addressing buffer buisy waits in 10g with ASSM
Thanks for such a informative link but its asking to partition indexes if there are buffer buisy waits on indexes (which there are in my case). Right now I'm not in a position to partition any segments. Any other ideas?
Help appreciated.
On Sun, Sep 16, 2012 at 2:38 PM, Toon Koppelaars < toon.koppelaars_at_rulegen.com> wrote:
> Study this:
>
>
> https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-
> buffer-busy-wait
>
>
> and see if that helps you.
>
> On Sun, Sep 16, 2012 at 6:48 PM, Saad Khan <saad4u_at_gmail.com> wrote:
>
>> Hi all,
>> We've a Oracle 10gR2 environment on AIX 6.1. I'm seeing a lot of
>> buffer buisy waits and mostly on data segment headers. When I try to
>> add more freelists to the buisy segments, it doesnt let me do that
>> being in ASSM mode.
>>
>> What are the other options that I can utilize in overcoming this?
>>
>> Any help is appreciated.
>>
>> Thanks.
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Toon Koppelaars
> RuleGen BV
> Toon.Koppelaars_at_RuleGen.com
> www.RuleGen.com
> TheHelsinkiDeclaration.blogspot.com
>
> (co)Author: "Applied Mathematics for Database Professionals"
> www.rulegen.com/am4dp-backcover-text
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 17 2012 - 21:06:11 CDT