Database Reference
In-Depth Information
■
One of the long-lasting myths about Oracle Database is that freelist groups are useful only when real
application Clusters is in use. this is
wrong
. Freelist groups are useful in every database. i stress this point because
i have read and heard wrong information about this too many times.
Note
Contention for Undo Header and Undo Blocks
Contention for these types of blocks occurs in two situations. The first, and only for undo header blocks, is when
few undo segments are available and lots of transactions are concurrently committed (or initialized or rolled back).
This should be a problem only if you're using manual undo management. In other words, it usually happens if the
database administrator has manually created the rollback segments. To solve this problem, you should use automatic
undo management. The second situation is when several sessions modify and query the same blocks at the same
time. As a result, lots of consistent read blocks have to be created, and this requires you to access both the block and
its associated undo blocks. There's little that can be done about this situation, other than reducing the concurrency for
the data blocks, thereby reducing the ones for the undo blocks at the same time.
Contention for Extent Map Blocks
As discussed previously in the “Contention for Segment Header Blocks” section, the segment header blocks contain
a list of the extents that make up the segment. If the list doesn't fit in the segment header, it's distributed over
several blocks: the segment header block and one or more extent map blocks. Contention for an extent map block is
experienced when concurrent
INSERT
statements have to constantly allocate new extents. To solve this problem, you
should use bigger extents.
Contention for Freelist Blocks
As discussed in the “Contention for Segment Header Blocks” section, freelists can be moved into other blocks, called
freelist blocks
, by means of freelist groups. Contention for a freelist block is experienced when concurrent
DELETE
,
INSERT
, or
UPDATE
statements have to modify the freelists. To solve this problem, you should increase the number
of freelist groups. Another possibility is to use a tablespace with automatic segment space management instead of
freelist segment space management.
Data Compression
The common goal of compressing data is to save disk space. Since we are dealing with performance, this section
describes another advantage of data compression that is frequently forgotten: improving response time.
Concept
The idea of taking advantage of data compression to achieve better performance is based on a simple concept. If a
SQL statement has to process a lot of data through a full table (or partition) scan, it's likely that the main contributor
to its resource usage profile is related to disk I/O operations. In such a situation, decreasing the amount of data to be
