Oracle Training Oracle Support
Oracle Training
SQL Tuning Consulting
Oracle Tuning Consulting
Data Warehouse Consulting
Oracle Project Management
Oracle Security Assessment
Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Internals Magazine
Oracle Links
Oracle Monitoring
Remote Support Benefits
Remote Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices
 

Free Oracle Tips


 
HTML Text AOL
 
 

Automatic Segment Space Management

October 16,  2003
Don Burleson

 

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. 

 

 
 

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational