Re: indexes reserve too much extents

Frederic Hoornaert wrote:
> Well,
>
> I guess i have to say thanks to all of you who tried to supply me with an
> answer to my question, but it didn't really help me any further.
>
> I see a couple of people are questionning about the number of indices on the
> table :
> Let's say the application is somewhere in the middle of OLTP and a
> warehouse.
> The table I was talking about, the one with the 30 indices, has about
> 5,600,000 records at this time. Another 20,000 records are added each day.
> I have 21 foreign keys. Knowing that Oracle doesn't put indices on the FK's,
> we did it ourselves.
> Then we have 1 PK and 8 indices for perfomance reasons.
> 21 FK + 1 PK + 8 extra = 30
> I could drop the FK indices, but that would really make our referential
> integrity suffer ...

Data warehouses can benefit from a judicious bit of de-normalisation. This sounds like a prime candidate (though of course, site unseen, one can never be sure). Put another way, the words "over designed" spring to mind. But that is probably a discussion for another day.

[snip]

> I have been looking around a bit on the internet, and found multiple site
> where DBA's state that a segment should NEVER have more then 1024 extents.

Well, that's a load of old whooey, really. If you're using data dictionary managed tablespace, then sql.bsq actually says you should never have more than 5 extents, but index cluster theory says you can get away with a couple of hundred (depending on your db_block_size) without cluster chaining on the data dictionary. You then have to ask whether dd cluster chaining is actually a noticeable thing when you have, say, 1000 extents (it probably isn't). And the extent-map-in-a-single-segment-block issue means an 8K block size database isn't going to complain with up to 505 extents per segment (and you wouldn't notice the complaints even with, say, 1 to 2000 extents or more). DDL is about the only thing that really complains badly with lots of extents (drop table commands taking days to complete, for example). Presumably, you're not in the business of dropping segments left, right and centre all the time?

If you use locally managed tablespace, then the sky really is the limit (well a couple of thousand does no harm, although the extent map issue still applies -and is still largely negligible at the few thousand limit).

But regardless, your segments have a few tens of extents at most. You could comfortably increase their number without incurring the wrath of extent numbers at all, whatever your tablespace type.

> After doing a bit of a bit of math, I found that the extent sizes I'm using
> (128K, 4M, 128M) were very well suited for our database. If I would be using
> automatic allocation, I would eventually also end up with large extent sizes
> because of the number of inserts ...

You have an absolutely enormous leap from 4M to 128M. The equivalent steps in Oracle's autoallocate algorithm would be 1 to 8 to 64 to 256M. Finer granularity, actually.

I don't quite understand how you can now post "I found the extent sizes I'm using were very well suited for our database" given the actual content of your original post, which strongly suggests they are too big, and with too few steps between the increments.

> Now, back to my problem with the size allocated to the indices.
> When I take one particular index, which has 65,536 blocks (or 8 extents), I
> see :
> extent size = 64M / 8192 blocks

I also don't understand how you can now suddenly have 64M extents given the three extent sizes you just quoted above.

> USED_BLOCKS = 65536
> * 8192 = 536,870,912 bytes assigned
> BTREE_SPACE = 177,058,656
> PCT_USED = 90
I think you need to post a little more carefully, because you can't actually set PCT_USED for an index (it is zero by default and cannot be altered). If you mean PCT_FREE, then the problem is solved, is it not? Whatever, it's difficult to supply good diagnostic advice when the symptoms are reported in a way that is meaningless. I'm guessing this is NOT PCTFREE, because your index data is occupying 33% of your index blocks, not 10% as such a setting would imply. But this particular bit of information is meaningless for an index, so I don't know where you're getting it from, and I can't guess what might be going on.

> USED_SPACE = 158,912,042
>
> The database is Oracle 10g on Windows 2003 Server
> The data has been load by SQL Loader, and indexes have been rebuild and
> analyzed to do my calcs
> The tables and indexes have default PARRALLEL degree
> No extra data has been inserted yet.
> all tablespaces are managed locally, with uniform extent size, and ASM
> (automatic segment management)

Well, that won't be helping matters either. ASSM (Automatic Segment Storage Management) is, for a start, different from 10g's ASM (Automatic Storage Management) and you need to be very careful about what acronym you use when posting, otherwise people are liable to get the wrong idea. On the basis that you used the word "segment" in your description, I assume you're using ASSM, and that has a postentially very big space overhead (see posts here passim at google, though Richard Foote will disagree, which is fine). ASSM is great for eliminating freelist contention, but if you haven't *got* freelist contention, or the threat of it, why pay the space and memory penalties it demands?

>
> My question :

If you just stick to Oracle's standard extent sizes, set PCT_FREE sensibly and stop using ASSM, what happens then? (Start with one index being rebuilt into a suitablly-built new tablespace, analyze index X validate structure, and report the output of INDEX_STATS).

It is always possible you're encountering a 10g-specific "new feature", but let's just start with diagnostic basics, shall we?

Regards
HJR Received on Tue May 04 2004 - 03:35:52 CDT