As a database administrator you spend a lot of time observing and setting various storage parameter
values for your application tables in order to gain performance benefits. Which task would you
perform to reduce this overhead?
A.
Coalesce the application tables at a regular interval.
B.
Export and Import the application tables a regular interval.
C.
Drop and recreate the application tables at a regular interval.
D.
Move the application tables to a tablespace with less space usage.
E.
Distribute the application tables across multiple tablespaces depending on usage.
F.
Move the application tables to automatic segment space management tablespace.
We need to reduce the overhead, so we enable ASSM
Automatic free space management is only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bitmapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.
Prior to Oracle 9i, each block had to be read so the freelist could be checked to see if there was room in the block. In 9i, the bitmap can be checked reducing the number of blocks read unneccessarily. The bitmap is constantly kept up to date with changes to the block making freespace management easier and reducing wasted space as blocks can be kept fuller since the overhead of freelist processing has been reduced.
FREELIST
=============================
Every table has some free space. The free space is either BELOW the high water mark (the
point at which data has been inserted into the table) or ABOVE the HWM. Blocks above the
HWM are “just there” — they are not managed on a free list of any sort — we just know
that every block above block N in a given extent is above the HWM (and hence, free to be
used).
The free space BELOW the HWM is maintained in freelists.
We want to cut down on having to set various storage parameter looking for space