0469-0472

Previous Table of Contents Next

Page 469

CHAPTER 20

Managing Disk Space

IN THIS CHAPTER

• Obtaining Object Storage 470
• Using the ROWID in Oracle8 474
• Using the Storage Clause 475
• Using Rollback Segments 478
• Using Tablespaces 480
• Using Temporary Segments 482
• Analyzing Storage 483
• Estimating Storage for a Table 484

Page 470

This chapter discusses the following topics:

• Oracle's management of storage
• Controlling the amount and location of storage
• System structures that use storage

Obtaining Object Storage

A database object that uses storage is called a segment. The different types of segments follow:

• Data segments (tables)
• Index segments
• Clusters
• Rollback segments
• Temporary segments
• Cache segments

Cache segments are special types of objects used to perform the initial load of the data dictionary caches in the data dictionary area of the shared pool area in the SGA.

Objects such as synonyms, views, and database links do not use storage in their own right. They are only definitions stored within the Oracle data dictionary.

After storage is allocated to an object, it is not used by any other object. Suppose that you allocate 5MB of storage to a table. If you don't insert any rows into the table, the 5MB of storage remains allocated and empty until you drop or truncate the table. It makes no difference whether you delete rows from the table; the storage remains allocated to the table.

Oracle Blocks in the Database Files

The Oracle database consists of files on disk grouped together into objects called tablespaces. A tablespace is a logical object consisting of one or more database files somewhere on disk. Whenever you want to create a segment such as a table, you can specify the tablespace in which the object uses storage. The object uses storage in the files that belong to the tablespace. In fact, a table or other object can use storage in more than one data file that belongs to a tablespace.

The storage within database files is managed in terms of Oracle blocks, which are the smallest units in which storage is used by any database object. The actual size of an Oracle block is determined when a database is created. After the block is set, it cannot be modified. To change the default block size, set the DB_BLOCK SIZE parameter in the INIT.ORA file before you issue the CREATE DATABASE command. On a PC-DOS platform, the default size is 1KB. On many UNIX and VAX VMS platforms, the default size is 2KB. On IBM MVS systems, the default size is 4KB.

Page 471

The default size is sufficient for most database implementations . Therefore, on a UNIX machine with a block size of 2KB, a 5MB database file has 2,560 Oracle block units of storage that can be allocated. The first Oracle block is an overhead block used to keep the database file in synch with the rest of the database files and the control file; this block should never be used to store objects. Therefore, 2,559 Oracle blocks are available for storage.

Even though the size of an Oracle block might be 2KB, the full 2KB of storage is not used to store data. Approximately 80 bytes of the 2KB ”it varies with different storage parameters ”provide overhead storage used to manage the space within the block. Similarly, the first Oracle block allocated to a segment is not used to hold data. Instead, it is used to manage the segment's storage. Table, index, and cluster sizing formulas take this storage into account.

The Oracle blocks for a tablespace are allocated to segments or are free space fragments that can be allocated to segments when required. To see the list of free space fragments for a tablespace, access the dba_free_space data dictionary view.

NOTE

To improve the performance of reading Oracle blocks from disk, the database block size should be a multiple of the operating system block size. If the operating system block size were 1KB, for example, the Oracle block size should be 2KB or 4KB.

Segments and Extents

When a segment such as a table is created, the storage parameters should be set explicitly for the segment. If no storage parameters are in effect, the segment uses the default storage parameters, which you can specify by each tablespace so that objects created in different tablespaces use different default storage parameters. For each user, a tablespace can be identified as a default tablespace. Therefore, if the tablespace clause is omitted from a CREATE statement, the user uses the default tablespace defined for him.

Assuming that all the default settings are used when a segment is created, it occupies a contiguous set of Oracle blocks in the database files when the CREATE statement succeeds. The segment has those Oracle blocks allocated to it and waits for data to occupy those blocks. The first set of contiguous Oracle blocks allocated to the segment is called the segment's initial extent.

Suppose that the segment is a table. As rows are inserted into the table, they use the storage in the Oracle blocks allocated to the table in the table's initial extent until no more rows can be inserted into those Oracle blocks. After all the Oracle blocks of the first extent have no more room for any rows, the table automatically uses more Oracle blocks in the tablespace as further rows are inserted into the table. That is, the table uses another extent of Oracle blocks. This process happens invisibly to the user who is inserting rows ”although a slight delay might occur

Page 472

on some systems as another extent is allocated to the segment. The process repeats until the table reaches the maximum number of extents that can be allocated or until no more free space is available to allocate to the table. Additional extents allocated to the segment after the first extent are called next extents for the object.

NOTE

The maximum number of extents that can be allocated depends on the size of the Oracle block. For a 2KB Oracle block, for example, the maximum number of extents that can be allocated is 121.

Growth Patterns

If you use the default settings, the first extent for a table has five Oracle blocks. The second extent allocated also has five Oracle blocks. Each additional extent, by default, is 50 percent larger than the previous one. In fact, it may be slightly higher because the amount of storage used is rounded up to the nearest five blocks. Further rounding up occurs if a fragment smaller than five Oracle blocks remains after storage from a free space fragment has been allocated. This otherwise leftover fragment is allocated to the table, which enables you to avoid having many small fragments of free space cluttering up the list of free space fragments within a tablespace.

NOTE

For every table, Oracle keeps track of what the previous extent would be if no rounding had occurred. This ensures that the next extents to be allocated do not take into account the rounding that was done.

Figure 20.1 shows a table with five extents. The storage parameters have default settings, and the block size is 2KB.

Whenever a database segment allocates another extent, considerable overhead is involved. This is because the data dictionary tables must be accessed to determine where and how much free storage is available. Similarly, the data dictionary must be updated because free storage has been used. The dictionary information about the segment that uses the storage is updated to record the fact that the segment now has another extent. This access to the data dictionary is performed by using recursive SQL ” system-generated SQL produced in the background for a number of reasons, including extent allocation. You can use the recursive SQL statistic in the v$ sysstat dynamic performance table to determine the amount of recursive SQL being generated. Some background noise always is present, however, because recursive SQL is generated as the Oracle background processes perform their work.

Previous Table of Contents Next