| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Apr 29 2004 - 07:20:56 CDT
 |
 |