Re: extent allocation

Daud wrote:

> Just a simple question. Sorry if this question has been asked many
> times.
>
> In a locally managed tablespace, if I have a table that has 100
> extents allocated per day vs a table that has 1 extent allocated per
> day, does it matter? I am trying to understand if extent allocation
> has some impact on performance. My guess is yes because the extent
> allocation map or whatever you call it needs to be updated everytime
> an extent is allocated to a segment. Is the impact really that big a
> deal?
> I know the number of extents allocated to a segment does not really
> matter in a LMT tablespace.
>
> thanks
> Daud

*ANY* extent allocation is a bad thing, whether in LMT or DMT. LMT costs for allocating an extent are vastly smaller than DMT ones, but they are not non-existent. Therefore, yes... it does matter that your segment is acquiring extents at a rate of knots.

Remember when segments acquire extents: just when a user is issuing an insert or update command. In other words, in the middle of a transaction. Therefore, that transaction *must* pause a moment, deal with the extent allocation, and then resume. Much better for the transaction if it can just get on and do what the user is asking it to do, because the space it needs is already there.

Hopefully, you have created a range of LMTs of varying extent sizes (say, 64K, 1M, 8M, 64M). If your segment is currently housed in a 1Mb extent tablespace, use the 'alter table blah move tablespace X' command, to bump it up into the 8Mb extent tablespace. Or, from an 8Mb tablespace to the 64Mb one. That will slow the rate of extent acquisition down considerably, and performance *will* benefit.

Regards
HJR Received on Sun Aug 25 2002 - 17:15:51 CDT