Tablespace growth after DB reorg - Drastic

[Former Member]

Hi,

My environment is 4.6C, Oracle8i, HP UX 11i, SAPDBA

We perform archiving on regular basis(once in a year) and DB reorg after archiving to gain the space deleted by archiving and to eliminate fragmentation.

Recently, I did DB reorg in my production system by selecting top 20 large tables from PSAPBTABD tablespace.

Before, reorg the tablespace PSAPBTABD was 73% used. After reorganizing around 10 tables, the used % got reduced to 66%. But, with in 3 weeks gap, the PSAPBTABD tablespace has grown drastically and now the used % is back to 73%. We have 10 more tables left to perform DB reorganization in PSAPBTABD.

Could any one share your experience on the reason why PSAPBTABD`s growth is drastic after DB reorg? What should be done to avoid this drastic growth? In my next reorg of 10 tables, where should I concentrate to avoid drastic growth...

I suspect the next extend size which we choose during the DB reorg.

Please share your valuable experience on this regard.

Best regards,

Vinodh.

[fidel_vales · Accepted solution]

hi,

it is difficult to provide a good answer without a proper analysis ( and I do not think the forum is the best place to do a depth analysis )

You are on oracle 8i and you have used SAPDBA for the reorganization.

I assume you are using Dictionary managed tablespaces.

In this case, it is possible that the reorganization has set ( or left if it was already set ) a big next extent on the reorganized tables ( for example lets assume that the next extent is set to 1Gb ). In that case, if the 20 tables need a new extent soon after the reorganization, they will allocate 1 Gb each causing a growth of 20 Gb.

Some times it is not "worth" the reorganizations after the archiving. If the tables are not very static, then the "freed" space will be reused.

Oracle 8i is long "out of customer care support", I even think the Extended maintenance has finished. The recommendation would be to upgrade to a newer release and use locally managed tablespaces.

[Former Member · Accepted solution]

Hi Nisch & Fidel,

Thanks for your prompt reply.

Fidel, Yes we use DMTS and we change the next extend size manually during the DB reorg. We are aware of the outdated version and we are on our way to the upgrade

Mean time, I am just exploring the reason for this drastic growth.

Last time when we did the DB reorg, we didn`t face this kind of sudden growth!!!! This time it looks like our efforts are wasted on gaining the freespace

We are planning to perform our 3rd cycle of DB reorg this Sunday... I am not sure, whether the efforts will be worth in gaining the space.

regards,

Vinodh.

[Former Member · Accepted solution]

Thing is, are you running the job to adapt the next extents of the tables? If so, dependent on the size of the table, the next extent size will be altered from what you set it at during the reorg process.

From what was said about not doing a reorg, the free space may or may not be used depending on the values of your PCTUSED and FREELISTS parameters.

[Former Member · Accepted solution]

Hi Rob,

Yes, we have schedule the command <i><b>sapdba -next PSAP%</b></i> as a job to run on weekly basis. I think, this will change the next extend size irrespective of the next extend size what we changed during our DB reorg activity.

We didn`t change any values for PCTUSED and FREELISTs during our DB reorg. We only change the next extend size of the object.

regards,

Vinodh.

[Former Member · Accepted solution]

Yeah, no matter what you set the next extent job to, if you schedule in the 'adapt next extent' job then this will adapt extents for all objects in the PSAP* tablespaces as you've configured.

So, the next extent size is then determined by the actual size category of the table. If you are happy with your next extent sizes, stop the job, change your large next extents to a lower value. You can monitor tables with high number of extents and adjust manually in the time before you upgrade to a later version of Oracle.

As for deleting entries out of tables, if you weren't to reorg, the PCTUSED/FREELISTS just determines whether the free space in the oracle block will be reused - worth having a read up on it. In later versions of Oracle, handled by ASSM.

[Former Member]

Hi Vinod,

I have been doing DB reorganizations and has been fine, the reason is the database version, we run on oracle 9i and some on 10g where extent allocation is uniform. Space management is improved in 9i and I would suggest to upgrade to 9i or 10g if its easy.

I would advise you keep track of DBA_SEGMENTS view before reorganization, after reorganization and then periodically to compare and identify the object growth and extent allocations.

Cheers,

Nisch