|
Automatic segment-space 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.
When you create a locally managed tablespace using the create TABLESPACE
statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and
used space within a segment is to be managed.
For example, the following statement creates tablespace mytbs1 with
automatic segment-space management:
CREATE TABLESPACE mytbs1
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 you cannot use the locally managed tablespace, and
therefore the automatic management space feature, you have to depend on the
traditional method of managing free lists and free lists groups.
Automatic segment-space 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 tuning methods.
- 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 block
50-75% free space within a block
75-100% free space within a block
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 Metalink 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. Let us look at an example (a SQL Block) of how to get information
about the blocks:
block_count.sql
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;
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;
It 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
 |
If you like Oracle tuning, check out my latest book
"Oracle
Tuning: The Definitive Reference".
It's 980 pages of hard-core tuning insights, tips and
scripts, and you can buy it direct from the publisher for 30%-off. |
|
