This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
management is a simpler and more efficient way of managing space
within a segment. It completely eliminates any need to specify and
tune the pctused, freelists, and freelist groups storage parameters
for schema objects created in the tablespace. If any of these
attributes are specified, they are ignored.
For example, the following
statement creates tablespace mytbs1 with automatic segment-space
DATAFILE '/u01/oracle/data/mytbs01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
When an object such as a table
or index is created using the locally managed tablespace, with
automatic segment-space management enabled, there is no need to
specify the pctfree or freelists.
The in-segment free/used space
is tracked using bitmaps as opposed to the free lists. When the
locally managed tablespace cannot be used, and therefore the
automatic management space feature cannot be used, the remaining
option is to depend on the traditional method of managing free lists
and free lists groups. The free list method of RAC will be covered
in a later part of this section.
management offers the following benefits:
* It provides administrative
ease of use by avoiding the specification of storage parameters.
* It is a good method for
handling objects with varying row sizes.
* It provides better run-time
adjustment for variations in concurrent access and avoids tedious
* It provides better
multi-instance behavior in terms of performance/space utilization.
However, note that this
automatic feature of segment space management is available only with
locally managed tablespaces and their objects. A new column called
SEGMENT_SPACE_MANAGEMENT has been added to the dba_tablespaces view
to indicate the segment space management mode used by a tablespace.
Use the Oracle procedure
dbms_space.space_usage to provide the space usage ratio within each
block in the Bitmap Managed Block (BMB) segments. It provides
information regarding the number of blocks in a segment with the
following range of free space.
0-25% free space
within a block
25-50% free space within a
50-75% free space within a
75-100% free space within a
RAC Related Advantages
The performance and
manageability gains provided by the automatic segment space
management feature are particularly noticeable in a Real Application
Cluster environment. It 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.
An Oracle internal benchmark
comparing the performance of automatic and manual segment space
management, conducted on a two node Real Application Cluster
database by inserting about 3 million rows in a table, showed that
automatic segment space management provided a 35% performance gain
over an optimally tuned segment (8 freelist groups, 20 freelists)
using the manual mode. For more details, refer to Oracle
MOSC Note 180608.1.
Use the new
dbms_space.space_usage procedure for reporting the space position in
BMB segments. This procedure provides the space usage ratio within
each block. It is preferred over the dbms_space.free_blocks
procedure. Here is an SQL example of how to get information about
-- Copyright ? 2012 by Rampant TechPress
('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,
see code depot for full
script v_fs4_blocks, v_fs4_bytes,
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
= '||v_fs3_blocks); dbms_output.put_line('FS4
= '||v_fs4_blocks); dbms_output.put_line('Full
= '||v_full_blocks); end;