ASSM and RAC Advantages
The performance and manageability gains provided by Oracle10g data
management features are particularly noticeable in a Real
Application Cluster (RAC) environment, especially the creation of
multiple freelist groups for each node in the RAC cluster.
The ASSM eliminates the need to alter the number of freelists
and freelist groups when new instances are brought online,
thereby saving the downtime associated with such table
reorganizations. It also avoids the tuning effort previously
required for multiple instance environments.
See here for
ASSM
internals and how
bitmap freelists compare to manual freelist management and here
to see why
Oracle ASSM Performance can be
bad for high DML applications.
An Oracle internal benchmark comparing the performance of automatic
and manual segment space management showed that the ASSM feature
provided a 35% performance gain over an optimally tuned segment
using the manual mode. This benchmark was conducted on a two node
Real Application Cluster database by inserting about 3 million rows
in a table. More details are available from Oracle MOSC Note
180608.1.
The new dbms_space.space_usage
procedure can be used for reporting the space position in BMB
segments. This procedure provides the space usage ratio within each
block. The
block_count.sql script below can be used to show how to get
information about the blocks. It uses the
dbms_space.space_usage
procedure to examine the blocks within the specified table and
count-up the free space ranges for all data blocks in the table. The
block_count.sql script
below can be used to show how to get information about the blocks:
DECLARE
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
SEE CODE DEPOT FOR FULL SCRIPTS
BEGIN
dbms_space.space_usage ('SYSTEM', 'TEST', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks,
v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks,
v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
The script yields the following output:
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 9
Where:
FS1 means 0-25% free space within a block
FS2 means 25-50% free space within a block
FS3 means 50-75% free space within a block
FS4 means 75-100% free space within a block
In summary, the Automatic Segment Space Management and the new
online space management packages significantly facilitate
space-related management tasks of Oracle DBAs in Oracle10g,
especially in RAC environments.
Conclusion
The Automatic Segment Space Management (ASSM) functionality introduced in Oracle Database 10g
is a powerful tool that can be used by the Oracle DBA to facilitate
the automation of routine work and to make the database as
self-managing as possible.
The key points of this chapter include:
-
The bitmap freelists of ASSM greatly improve
simultaneous insert concurrency.
-
The DBA may need to manually control freelist un-linking and relinking to reduce row fragmentation and improve
performance.
-
ASSM removes the need to specify freelist groups
in RAC.
-
The new dbms_space procedures allow the DBA to see growth trends within specific
objects.
The next chapter gives interesting insights into techniques and
approaches of using AWR for tuning and management tasks of data
warehouse systems.
