Recently two customers reported independent from each other that their LMTS/ASSM tablespace shows much “fragmentation”. What they meant was, that the repeatedly got errors like:
ORA-1688: unable to extend table SAPWBP./BIC/B0000256000
partition /BIC/B00002560000000000092 by 128 in
tablespace PSAPBWP
or
ORA-1654: unable to extend index SAPWBP.BALHDR~2 by 128 in
tablespace PSAPBWP
The errors occurred although there had been much (>3GB) free space in the tablespace.
It turned out that the tablespaces (in this case PSAPBWP) where configured with the default option, that is:
locally managed autoallocate segment space management auto
This means:
- the tablespace does the free space management in tablespace-local bitmap-structure,
- the extent sizes are determined by an oracle internal algorithm (that basically uses larger extents the larger an segment grows)
- the freeblock-management is automated as well.
These automatic features disburden the DBA from former regular tasks (like CHECK NEXT EXTENT) to optimize the storage settings for the database objects. One prominent effect was advertised to lead to lower fragmentation issues. And now the customers get the errors above. So what happened here?
As most of you know, extents are allocated in one chunk, so the free space block that should be used for a new extent has to be at least the size of the extent that should be created. In the customers case the free space should have been 128 blocks large (or larger). Obviously there was no such large free space block available at this moment in time, so the error message was thrown out.
But how could this have happened at all?
Why did all the automatic storage management features fail here?
Well, they didn’t.
Let’s see how the situation was created:
In my example I’ve just one small tablespace and four segments (a, b, c and d). As you may notice, this tablespace is also a default tablespace, so the extent sizes are chosen by the system.
(free space: 2 blocks, # of additional extents for segment a: 0)
Currently there are 2 free blocks available, the largest freeblock chunk is also 2 blocks and there is no freeblock available that would fit the next extent of segment a, since it already has a extent size of 8 blocks.
Anyhow, in this tablespace is not enough free space anyhow, so nobody would say: this is a mistake. Therefore, let’s free some space and drop segment d.
(free space: 18 blocks, # of additional extents for segment a: 0)
Now there are 18 blocks free, enough to store even two times the next extent size of segment a. But as the largest free chunk is only the size of 4 blocks, we would still get the ORA-1688 (if segment a would be a table).
So what can we do about this? Everything worked as it should and still there are many small bits of free space that are mostly unusable for the big segments in our tablespace. To make the free space useable again we would have to reorganize the tablespace – not so nice, isn’t it?
Shouldn’t LMTS prevent us from this?
No, it shouldn’t! LMTS with the auto allocate feature is the general purpose approach to extent storage in tablespaces. It’s a one size fits all approach that is – that cannot be optimal for every appliance.
To avoid situations like this there is another option available for locally managed tablespaces: EXTENT MANAGEMENT UNIFORM.
With this setting each and every extent in this tablespace will have the same size. Of course this is not what we want in case we have many small tables AND many large tables mixed together in one tablespace. In that case even for 1-row-tables a complete extent would be allocated and thus much space wasted,
But if we know in advance that there will be many very big segments to be stored (like it’s often the case for BW objects) than it makes sense to move these segments to a tablespace with UNIFORM extent size.
In my example I set the extent size to 8 blocks – let’s see how much free space is available then:
(free space: 2 blocks, # of additional extents for segment a: 0)
As you see now there are much fewer extents in the tablespace but all of them allocate the same number of blocks. Currently only three blocks are free so no segment in this tablespace could allocate an additional extent.
Let’s drop segment d again:
(free space: 19 blocks, # of additional extents for segment a: 2)
Now there are 19 (3 + 2*8) Blocks free and there are two freeblock chunks of a 8-block size. So not only one but two additional extents would be able to fit in this tablespace.
Due to the fixed extent size there is no way to “produce” unusable “holes” in your free space. Everything that got freed is useable again! BTW: this is exactly the same way MaxDB archieves reorganization-free operation. It allocates space always by single pages - so there are never free space areas that are too small.
Of course it would be a good idea to make the tablespace a multiple of the extents size to also get use of the 3 empty blocks at the end of the tablespace…
There are of course information about this in some SAP notes as well as in the documentation:
821687 - FAQ: Space utilization and fragmentation in Oracle
599694 - LMTS autoallocate: Extent allocation
214995 - Oracle locally-managed tablespaces in the SAP environment
KR Lars
- SAP Managed Tags:
2 Comments
