Re: Index freelists

> Very interesting points, Tanel.  I've discussed a similar situation with
> one of the local Oracle consultants here when 10G and ASSM/ASM were
> announced
> (re-announced?) early last year.

ASSM should be able to relieve the segment header contention problem, since
free blocks are managed by BMBs. But yeah, ASSM has its own problems,
discussed here few times as well.
Also, since highwater mark information is still stored in segment header
block, the segment header may still remain as bottleneck in extreme cases.

> We both reached the conclusion the best approach to contention problems in
> situations like these (header block contention) is to hash partition as
much
> as we can (and dare) and forget about fine-tuning segment-headers,
freelists
> and all that jazz.  Much better bang-for-buck of our time.  Same applies
to
> RAC and clustered situations where there may be block (or row) contention.
>
> Just hash partition the table + indexes over 50 or so splits and be done
> with it.  99 times out of 100 it's all you need to do, the remaining 1%
> you ask a guru to sort it out.  Would you agree?

I think this would be an easy solution, however when you've just hash
partitioned your index, range scans over it will be more inefficient if you
have to scan across partitions...

I would start from design & SQL writing phase, to see whether it is possible
to create concatenated indexes the way that inserts (also other DML) would
be spread up in the index by its first column(s).
For one example, when indexing call start time column in telco billing
applications call detail record table, I'd put the customer account id
column first in the index, so all inserts with monotonically increasing date
values would be spread across the index.

But as you say, you could get more bang per buck by just hash partitioning
the indexes in 99% cases - if it gives you what you need without serious
drawbacks (including future drawbacks when the system grows), then why not..

Tanel.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: