Re: Q: Reorg an SAP tablespace

Vince Laurent wrote:
> A bazillion years ago - give or take - we started with SAP.
> Right now one of the tablespaces, PSAPODSD, is 71G with about 6G free. The problem is it is SO fragmented that when the developers
> go to load more data that there isn't enough free space in the chunks it is looking for.
>
> My suggestion to the developers is to let me have some downtime and move it to a locally managed tablespace with same size extents
> so the 'chunks' it would be looking for would all the be same size. They are warm to the idea so I may get downtime soon.
>
> My question to the group is how to best size the tablespace?
>
> create tablespace PSAPODSD
> datafile 'c:\oradata\blahblah.dbf' size 2G
> extent management local
> uniform size ???K;
>
> The individiual tables that make up this tablespace range in sizes from 17G to 16K. For example:
>
> Object Type Tablespace KBytes
> /BIC/AZRC_O5000 TABLE PSAPODSD 5,488,656
> /BIC/B00002570000000000002 TABLE PART PSAPODSD 1,679,376
> /BIC/AZCO_O5000 TABLE PSAPODSD 1,515,536
> . . . .
> . . . .
> . . . .
> /BIC/B00003650000000000002 TABLE PART PSAPODSD 16
> /BIC/B00003640000000000002 TABLE PART PSAPODSD 16
> /BIC/B00003630000000000002 TABLE PART PSAPODSD 16
> Total 65,071,488
>
> Suggestions on how to tackle this? I thought of trying to pull the small ones out but since the developers told me that the names
> chnage (/BIC/blah blah) from dataload to dataload that moving them to a seperate tablespace wouldn't probably be worth it since it
> would not be predictable.
>
> I'm open for suggestions!
> Thanks!
> -----------------------------------------------------
> Come race with us!
> http://www.mgpmrc.org

It appears to me you may be best off 'wasting' space internally (allocating an extent far larger than required) for the smaller tables to ensure you have sufficient space and extent size to accomodate the larger objects. 1 MB extents might be a bit extreme for the smaller tables (although perfectly acceptable for the larger objects) whereas 64K extents would waste as little space as possible on the smaller tables but create a possibly unruly number of extents for the 1 gig, 5 gig and larger tables; for the /BIC/AZRC_O5000 table a 64K uniform extent size would create 85761 extents -- something I wouldn't care to manage. A 500K uniform extent size would waste what could be called 'considerable space' on the 16K objects (fully 484K more than the object requires) but would reduce the number of extents for /BIC/AZRC_O5000 to 10978. Your situation is not an easy one for which to provide a solution, as providing a suitable extent size for the smaller objects makes the larger ones possibly unmanageable (relatively speaking) while tailoring the extent size to make the most efficient use of space for the gigabyte-sized tables causes the 16K tables to occupy extents which could be considered virtually empty (1M extents would occupy 1008K more space than the data would need).

Were it me making this choice I would, knowing how the developers are managing (or not managing) the space, choose 500K uniform extents. Anything smaller and you can create up to 86000 extents for your largest table (well, largest of the ones you've chosen to display for our benefit) and I would much rather manage a table of that size with ~11000 extents of 500K each. Other opinions may vary; I am simply trying to make the situation less 'hideous' (for want of a better word) in terms of data management.

My two cents.

David Fitzjarrell Received on Thu May 25 2006 - 15:11:06 CDT