Segment Management

This section helps you develop an approach to managing the segments – that is, the tables and indexes – of your Oracle database.

Note Note

This section discusses the approach to segment management.

For more information on how to perform segment management, see Segment Management with BR*Tools.

End of the note.

Prerequisites

With Oracle 9i, you can now perform table reorganization and index rebuild while the database remains online. This overcomes the limitations of the old reorganization procedure based on export/import, which is a time-consuming procedure with a risk of data loss. However, you still need to perform export/import for tables containing LONG or LONG RAW fields. But you can convert LONG and LONG RAW fields online using table reorganization in BRSPACE with Oracle 10g.

For more information on how the reorganization works, see Reorganization with the Redefinition Package.

How the Database Deteriorates

When installed for an SAP System, the Oracle database looks as follows:

Most of the tables and indexes of a tablespace are stored in only one extent.
Each tablespace consists of exactly one data file.

This initial database status can change as follows:

Additional extents
When more space for extra data is required, additional extents are allocated to the tables and indexes of a tablespace. The result might be poorer data access times.
Additional data files
When a tablespace is full - that, is there is not enough freespace to create a new extent - additional data files must be added (except if the AUTOEXTEND option is used).
Freespace fragmentation
Adding or deleting complete objects causes freespace fragmentation in a tablespace. Free storage space in data files is divided into smaller units. If these are smaller than one requested extent, the space is lost and cannot be used for storing data.

The Oracle system now automatically merges adjacent areas of free space, so this problem is less likely to occur than in the past. Also, this problem should not occur in locally managed tablespaces.
Internal fragmentation
This occurs if the fill level of the database blocks develops unevenly. The fill level of the individual blocks is initially identical. Inserting and deleting rows causes some blocks to be filled completely, while others remain relatively empty. As a result, space is used inefficiently.
Block chaining
If a data record does not fit into a database block, block chaining occurs. When the record is accessed, the system must then follow a chain from the first block of the data record to the further blocks. As a result, more time is needed for reading data from the disk.

Since SAP systems usually access table entries using an index, the above changes to the database do not normally significantly increase the time required to access data. However, such changes can increase run times for full-table scans.

Features

Reorganization
You can reorganize tables to move them to another tablespace or to recover space in the database and improve performance while the database is online. For more information on the approach to reorganization, see Reorganization and Reorganization Case Study. You can also use the reorganization function in BRSPACE with Oracle 10g to convert LONG and LONG RAW fields to CLOB and BLOB.
Rebuild indexes
You can rebuild fragmented indexes. This improve data access using indexes. For more information on how to rebuild indexes, see Rebuilding Indexes with BR*Tools.
Table export and import
This is the older method of reorganization. You must still use it for tables containing LONG or LONG RAW fields. For more information, see Export/Import.
Alter table
- Table monitoring
  With table monitoring Oracle automatically collects information on table updates. Although table monitoring has a small performance overhead, the overall effect is to improve performance because table statistics can be more up-to-date.
  
  Recommendation
  
  For Oracle 9i, we recommend you to turn on table monitoring for all tables. This greatly reduces the run time for update statistics with BRCONNECT because it can more quickly identify which tables need update statistics. Therefore, you can easily schedule update statistics to run daily.
  
  For Oracle 10g, this is not required because all tables are monitored by default.
  
  For more information on the approach to update statistics, see Update Statistics.
  
  End of the recommendation.
- Set parallel degree
  You can also use alter table to set the degree of parallelism for queries. A higher degree of parallelism improves performance on query statements.
  
  Caution
  
  Only set the parallel degree if told to by SAP support. Production systems normally run without parallelism.
  
  End of the caution.
- Shrink tables – as of Oracle 10g
  Shrinks table segments online and in-place. This frees unused space in the table segments.
For more information on how to alter tables, see Altering a Table with BR*Tools.
Alter index
- Coalesce index
  You can coalesce an index to deallocate internal free space. If space allocated to an index has never been used (that is, data has never been written to the data blocks), you can free such space for use by other objects in the tablespace. You can do this online without incurring downtime.
  
  However, remember that the objects for which you have deallocated free space might themselves soon require new extents as they grow with inserts and updates. Therefore, a more permanent solution is to reorganize affected objects and also, if necessary, extend the tablespace.
- Set parallel degree
  You can also use alter index to set the degree of parallelism for queries. A higher degree of parallelism improves performance on query statements.
  
  Caution
  
  Only set the parallel degree if told to by SAP support. Production systems normally run without parallelism.
  
  End of the caution.
- Shrink indexes — as of Oracle 10g
  Shrinks index segments online and in-place. This frees unused space in the index segments.
For more information on how to alter indexes, see Altering an Index with BR*Tools.