Home » RDBMS Server » Performance Tuning » Coalesion on the tablespaces
Coalesion on the tablespaces [message #65614] Tue, 09 November 2004 00:16 Go to next message
Abhijit
Messages: 7
Registered: November 2002
Junior Member
Operating System - Sun Solaris 2.8

Oracle Database Version - 8.1.7

 

select TABLESPACE_NAME ,PERCENT_EXTENTS_COALESCED from DBA_FREE_SPACE_COALESCED;

when we run this command logged in as system,it is recommended that all the tablespaces should be 100% coalesced.
The factor coalesion depends on the insert/update & delete from the tablespaces.
The coalesion of the tablespaces goes to less than 40% for many of the tablespaces frequently on weekly basis. If I need to reduce the coalesion on the tablespaces what precautions should I take ?
Re: Coalesion on the tablespaces [message #65615 is a reply to message #65614] Tue, 09 November 2004 02:36 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>If I need to reduce the coalesion on the tablespaces what precautions should I take ?

USE LMT ( Locally managed tablespaces) instead of DMT ( Dictionary managed tablespaces).
THere is NO fragmentation. NO coalescation is required.

If you cannot switch to LMT,
always have the tablespaces with these values

pctincrease =0
initial=next = SomeConstantValue

By coalescing oracle will take the adjacent free space and joins into One biggie chunk (of free space).
But the effect of Coalesce will be very less ( less than 5%?? or so). a 100% perfect system is IDEAL.
If you cant do the above ( LMT/pctincrease) the tablespace will got backk to previous again. if so startover.
Previous Topic: Space allocation to TEMP LOB
Next Topic: how to tuning a hierachical query having "or" conditions
Goto Forum:
  


Current Time: Tue Sep 29 13:05:09 CDT 2020