|
This chapter discusses the following topics:
- How Oracle manages storage
- The control you have over the amount and location of storage that is obtained
- System structures that use storage
A database object that uses storage is called a segment. The different types of segments include
- Data segments (tables)
- Index segments
- Clusters
- Rollback segments
- Temporary segments
- Cache segments
Cache segments are a special type of object used to do 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 that are stored within the Oracle data dictionary.
Once storage is allocated to an object, it is not used by any other object. For example, suppose that you allocate 5M of storage to a table. If you don't insert any rows in the table, the 5M 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 it.
The Oracle database consists of files on disk that are grouped together into objects called tablespaces. A tablespace is a logical object and consists 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 it 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 1K. On many UNIX and VAX VMS platforms, the default size is
2K. On IBM MVS systems, the default size is 4K.
The default size is sufficient for most database implementations. Thus, on a UNIX machine with a block size of 2K, a 5M database file has 2560 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; it is never be used to store objects. Therefore, 2559 Oracle blocks are available for storage.
Even though the size of an Oracle block might be 2K, the full 2K of storage is not used to store data. Approximately 80 bytes of the 2K—it varies with different storage parameters—is overhead storage used to manage the space within the block.
Likewise, 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.
 To improve the performance of reading Oracle blocks from disk, the database block size should be a multiple of the operating system block size.
When a segment such as a table is created, the storage parameters should be explicitly set 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 will use the default
tablespace defined for him.
Assuming that all the default settings are used, when a segment is created, it will occupy 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 them. 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, it uses another extent of Oracle blocks. This happens invisibly to the user
who is inserting rows—although a slight delay might occur 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.
The maximum number of extents that can be allocated depends on the size of the Oracle block. For a 2K Oracle block, the maximum number of extents that can be allocated is 121.
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. It may, in fact, 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 left-over
fragment is allocated to the table, which avoids having many small fragments of free space cluttering up the list of free space fragments within a tablespace.
For every table, Oracle keeps track of what the previous extent would be if no rounding had occurred.
Figure 12.1 shows a table with five extents. The storage parameters have default settings, and the block size is 2K.
Figure 12.1. A table with five extents.
Whenever a database segment allocates another extent, there is considerable overhead. This is because the data dictionary tables must be accessed to determine where and how much free storage is available. Likewise, 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.
There always is, however, some Òbackground noiseÓ on account from recursive SQL being generated as the Oracle background processes perform their work.
 As a general rule of thumb, when a database segment uses more than 10 extents, the table should be recreated with only one extent to improve the performance of full-table scan-type accesses.
Before you create a table, index, or cluster, you should calculate the amount of storage that the table will use. If possible, create the table with an initial extent that capable of holding the data for it. Doing
this minimizes the overhead of allocating additional extents and searching noncontiguous storage areas for data.
Generally, you want to calculate the amount of storage that a table or other segment will use and to make sure that the amount of storage is allocated to the object for its initial extent. This ensures that the object has the amount of storage it
requires when it is created and that the system overhead of allocating additional extents is reduced. In some cases, however, this wastes storage if it is allocated to an object but not used until some time in the future. Moreover, it might not be possible
to allocate the storage required into one initial extent. The Oracle blocks for an extent must be contiguous in one datafile for the tablespace in which the object has been created.
Suppose, for example, that you have the space required, but it is not in one contiguous storage area. One solution is to modify the storage parameters for the segment when it is created so that more than one extent is allocated when the table is
created. Another solution is to allocate storage manually by using the ALTER TABLE statement:
SQLPLUS> ALTER TABLE karam ALLOCATE EXTENT;
This statement allocates to the next extent. Its size is the same as what it would be if the extent were allocated automatically when more storage was required. That is, its size is larger than the previous extent by a given percentage increase
parameter. The default percentage increase parameter is 50 percent.
Deleting the rows of a table does not release the storage allocated to it. The rows disappear, but the table continues to use the storage allocated. To release the storage, drop or truncate the table. Dropping the table causes all the information about
its structure, the database triggers on it, and the constraints for it to be lost. The truncate command is a quick way of removing the rows of a table without modifying its structure. With the truncate SQL statement, you have the option to release all but
the initial extent. The truncate command is a data definition language (DDL) statement. When it is issued successfully, it cannot be rolled back.
The ROWID is an internal physical address for every row in every nonclustered table in the database. It is a unique identifier that is not repeated for any two rows across the whole database. The ROWID is a pseudo-column that never appears in the list
of columns when a table is described. It can be selected from any table, but it can never be modified.
The ROWID is allocated for the row when the row is first inserted. It remains for the row until the row is deleted or until the table is dropped.
SQLPLUS> SELECT ROWID FROM karam;
The ROWID is composed of three number components in hexadecimal notation for each row. The last part of the ROWID gives the file number in which the row is located. The file name can be obtained from the dba_data_files dictionary view. The first part of
the ROWID indicates the Oracle block within the file—each block within a database file is numbered consecutively starting at 1. The second part of the ROWID specifies the row within the block—because blocks can be many kilobytes in size, they
usually hold many rows.
If you know the ROWID for a row, you can use it to access the row. In fact, using the ROWID is the quickest way to get to any row in the database. For example,
SQLPLUS> SELECT mycol FROM karam WHERE ROWID = '000004E.00A3.00001' FOR UPDATE OF mycol;
This statement uses the ROWID to lock the row. You must always do a query to access the ROWID before you can use it. It is useful in cases when you access the row and later in the program logic need to update or lock it.
Don't assume that the ROWID remains the same across transactions. Another transaction might delete the row after one of your transactions completes.
You have learned about the default storage parameters that apply to segments created in the database. Often, however, you want to have more control over the storage parameters and the where storage is allocated, especially in production database. You
can specify a generic storage clause at the end of the CREATE statements when you create the database segments.
You can put a storage clause at the end of the CREATE statement for a database segment. The format is
STORAGE ( INITIAL xK
NEXT xK
MINEXTENTS x
MAXEXTENTS x
PCTINCREASE x).
The storage clause overrides the default storage parameters that would be used for the database segment. The initial extent parameter specifies the number of bytes, kilobytes, or megabytes of storage to allocate for the first extent of the database
segment. The next parameter specifies the amount of storage to allocate for the second extent for the segment. The values for the initial and next extent parameters are always specified in terms of the number of bytes, kilobytes, or megabytes rather than
the number of Oracle blocks. This is because the size of an Oracle block varies from platform to platform and even from one database on a machine to another. By specifying the storage in terms of bytes, kilobytes, or megabytes, you ensure that the same
amount of storage is always allocated, regardless of the size of the Oracle block.
The MINEXTENTS parameter specifies the number of extents that are allocated when the segment is created. The default is one for all segments except rollback segments, for which the default is two. The MAXEXTENTS parameter specifies the maximum number of
extents that the database object can ever allocate. If this number is reached, an error message is generated and the operation that caused the segment to grab another extent fails. It is unusual, however, to enable database segments—except rollback
segments—to use more than 10 extents.
You can modify the NEXT, MAXEXTENTS, and PCTINCREASE parameters after a table has been created by using the ALTER TABLE SQL statement.
You should perform a sizing estimate so that the storage parameters are correctly set for the database object, thereby minimizing the system overhead of allocating additional extents.
You can use the STORAGE parameter in the CREATE statement for database segments—tables, indexes, clusters, rollback segments, and so on. You can specify it as part of the CREATE TABLESPACE SQL statement to set the default storage parameter for
objects created in that tablespace. The tablespace default storage parameter is used only where the storage parameter has not been specified as part of the segment creation command.
In addition to the storage parameters that you can specify in the storage clause, you can specify the TABLESPACE, PCTFREE, and PCTUSED parameters for each database segment.
The TABLESPACE parameter controls the tablespace in which the database segment is created. It assumes that the user has quota privileges to create database objects in the tablespace. Users who want to create database segments must have resource
privileges to do so. For each user, you can specify the default tablespace—the default location in which his database segments are created if the TABLESPACE parameter is not specified at the end of the CREATE command for the segment.
When you create a database segment—tables, indexes, clusters, and rollback segments—you should use the TABLESPACE parameter as part of the CREATE statement so that it is explicit where storage is used for the object.
As a simple performance-tuning measure, put the tables and indexes into separate tablespaces and ensure that the files for the tablespaces are located on separate disk drives. This reduces the I/O bottleneck of
having to read and write both table data and index data from the same disk drive.
The PCTFREE parameter for a table controls how much of each Oracle block remains free to enable the rows to expand. By default, the PCTFREE parameter is 10 percent, so that 10 percent of every Oracle block for the table is left empty so that the block's
rows can expand into it. This area becomes used only when you expand rows with the UPDATE statement to increase the actual values being stored or to give values to columns that were previously null.
Oracle stores only the bytes required to store a data value. No padding out occurs. The only exception is a column that has been declared as CHAR, in which case padding with spaces occurs. CHAR declarations are not often used, however. Oracle attempts
to fit as many rows as possible into an Oracle data block until the PCTFREE limit is breached. Further rows are stored in the next available block for the table. If no more blocks are have enough storage free to accept a new row, another extent is
allocated to the table.
For a meaningful setting for the PCTFREE parameter, you need to know something about the behavior of the table. (Ideally, this information comes from the database designers.) Suppose that rows are inserted into the table and many columns are left with
null or small values. If the table will later be updated to give the columns values where they were previously null or to increase the actual amount of data held, you should set the PCTFREE parameter higher than 10 percent. The value depends on how much
you expect the rows to grow.
In another table, the rows might not increase much after they are inserted. In that case, leaving the PCTFREE parameter at 10 percent wastes space that will rarely be used. Reducing the value of PCTFREE—but not to zero—improves storage
utilization. Performance is improved because each Oracle block read from disk has more useful data in it.
The PCTUSED parameter is another parameter that you can set for a table. It sets a watermark level below which the amount of space used in a block must fall below before new rows are accepted into the block. Take a table whose PCTUSED value is set at 40
percent. As rows are deleted from the block—or even updated where the amount of storage used by the row is reduced—the storage freed is reused until at least 60 percent of the block is empty. The amount of storage used must fall below 40 percent;
in other words, more than 60 percent of the block is free. This parameter attempts to reduce the overhead of managing blocks that will accept new rows. For a static table in which not many rows are deleted or space freed within the block, you can set the
PCTUSED parameter fairly high—to perhaps 80 percent. That way, storage is reused as soon as soon as it becomes available in a block. For a busy transaction table in which many rows are inserted and deleted, you should set the PCTUSED figure
lower—to perhaps 20. That way, once a block can accept new rows, you know that it is fairly empty.
It is quite common to come across the situation in which many rows are deleted from a table and the table continues to allocate additional extents. This is usually the case when the amount of storage used in a
block does not fall below the PCTUSED value. Even though rows have been deleted from the table, the block does not accept new rows. Reduce the PCTUSED value so that more of the blocks are used for the new rows.
Another type of segment that uses storage on the database is the rollback segment, which is a system object that is created by the database administrator. It serves two purposes. First, for a transaction that makes changes, the old values of the changed
data are kept in the rollback segment, which enables you to reverse them if the rollback statement is issued instead of being committed. Second, the rollback segment provides a read-consistent view of the data. This means that even though you change the
data in a table and lock the rows, other users can still access the data. They see the old values of any rows until your transaction commits.
An Oracle database always has a rollback segment with the name SYSTEM and the segment type ROLLBACK. You should create additional rollback segments so that the SYSTEM rollback segment is reserved for recording the rollback information when changes are
made to the data dictionary system tables—usually with DDL statements or by means of system-generated recursive SQL statements. To determine the number and sizes of the rollback segments that are required, look at the dynamic performance tables.
As a rule of thumb, there should be one rollback segment for every five users who are likely to make changes at any one time. There should be between 10 and 20 extents for each rollback segment.
You can create two types of rollback segments: private and public. Private ones are more common and are the default type. A private rollback segment can be used only to record rollback information for transactions running against the instance to which
the rollback segment has been associated. A public rollback segment can be used to record information for any transaction running against any instance, which assumes that the parallel server version of the software is used and that many instances are
running against the same database.
Creating an additional rollback segment involves two steps. You first must create it, and then you must activate it. To create a rollback segment, use the following command:
SQLPLUS> CREATE ROLLBACK SEGMENT benisha;
You can specify the storage parameter and the tablespace at the end of the create statement in the same way as you specified the parameters for a table. However, after you create the rollback segment, you must enable it.
A rollback segment must have a minimum of two extents when it is created. Other database segments need to have a minimum of only one. In fact, rollback segments are usually created with many extents—by using the MINEXTENTS parameter in the storage
clause—because of how they are used. Each transaction making a change uses only one extent of the rollback segment, not the entire segment.
There are two ways to enable a rollback segment. To activate a rollback segment immediately, issue the following command:
SQLPLUS> ALTER ROLLBACK SEGMENT benisha ONLINE;
This command activates the rollback segment only until the instance is brought down again (the shutdown command is used in sqldba or the shutdown option is selected in the server manager tool to close the Oracle SGA memory area and close the Oracle
background processes).
The other way to activate a rollback segment is to modify the rollback_segments INIT.ORA parameter. The instance must be shut down and restarted before the init.ora parameter comes into effect. In practice, both activation methods are used so that the
rollback segment comes online immediately and is reactivated whenever the instance is restarted.
To check which rollback segments are currently activated, query the v$rollstat dynamic performance view. It shows only the activated rollback segments. The dba_rollback_segs data dictionary view shows all the
rollback segments, activated or not.
Like tables, rollback segments are allocated additional extents, as needed, automatically and without user intervention. For example, additional extents might be needed for a rollback segment when many users are making changes or when a batch update
operation causes a large amount of redo log information to be recorded.
Unlike tables, however, rollback segments can also be set to shrink in size. You can specify the OPTIMAL clause that is part of the storage clause when you create the rollback segment. This sets a high watermark size for the rollback segment. If the
rollback segment grows larger than this size, it automatically releases the additional extents that it was allocated until the amount of storage used falls below the optimal size.
Setting the optimal size is useful, for example, when you expect a monthly batch update to make the rollback segment grow and you want to reclaim the storage. Otherwise, it remains unused until the end of the next month. Don't set the optimal size too
low. If you do, making the rollback segment grow when it needs and then shrinking it back to the optimal size will involve too much system overhead.
You can query the v$rollstat dynamic performance view to see how many times a rollback segment has extended and shrunk.
All rollback segments should be created with the same extent sizes. This means that the initial and next extent parameters specified for the rollback segments are the same. The PCTINCREASE parameter cannot be specified.
You should create a tablespace just for rollback segments. One reason for this is that if a tablespace contains enabled rollback segments, it cannot be taken off line until the rollback segment is disabled.
A tablespace is the name given to a group of one or more database files. When objects are created, you can specify in which tablespace they will occupy storage. This gives you control over where and how much storage is used. You can specify the
amount of storage that users are allowed to use in each tablespace in the database.
An Oracle database always has a tablespace called SYSTEM. The first file that is created belongs to this tablespace. Because this is the first tablespace created, the data dictionary is created in this tablespace.
You can create additional tablespaces. To create tablespaces in additional to the SYSTEM tablespace, you must ensure that at least two rollback segments are enabled. One of them can be the SYSTEM rollback segment, which is automatically created. As long
as at least two rollback segments are enabled rollback, you can create as many tablespaces as you want.
To create a tablespace, issue the following SQL command. It is similar to how filenames are specified with the CREATE DATABASE command.
SQLPLUS> CREATE TABLESPACE kashmir DATAFILE 'disk01myfile1.dbs' SIZE 10M;
This command creates the tablespace and makes it immediately available. The name of the file in quotation marks is named just as you name files on the operating system—assumed in this case to be a UNIX file system. The size of the file is always
given in terms of bytes, kilobytes, or megabytes. After this command is issued, you will find a file at the specified location specified and with the specified size. Oracle automatically formats the file into Oracle blocks, which is the smallest unit in
which storage is allocated.
It is also possible to specify a default storage parameter that has the same syntax as the storage clause—for example, initial extent size, next extent size, percentage increase, and so on. This specifies the default storage that is used when an
object is created in the tablespace but when no storage clause is included along with the object definition.
You can create additional tablespaces in the same manner as described previously. You do not need to create further rollback segments as long as at least two rollback segments are still enabled.
The filename and size given for a tablespace might eventually fill up as the free Oracle blocks are allocated to the database segments. To increase the number of files that a tablespace can use, enter the following command. It adds another file to the
tablespace.
SQLPLUS> ALTER TABLESPACE kashmir ADD DATAFILE 'disk01myfile2.dbs' SIZE 10M;
The tablespace now has another 10M of storage to use. Objects already created in the tablespace can now allocate extents from the two files that belong to the tablespace.
To check the amount of free space remaining in a tablespace, query the dba_free_space data dictionary view. It lists the fragments of free space for each tablespace in the database.
You can drop a tablespace easily by issuing the following command:
SQLPLUS> DROP TABLESPACE kashmir;
This removes information about the tablespace from both the Oracle data dictionary and the control file. The next time the instance starts up, it will not attempt to open the file. This command, however, does not delete the file from the operating
system. The database administrator must do that at the operating system level.
If any objects are still using extents in the tablespace, the DROP command shown fails. You should check which objects are using storage in the tablespace by querying the dba_segments data dictionary view. You can use the following optional clause to
drop all the database objects in the tablespace before the tablespace is dropped:
SQLPLUS> DROP TABLESPACE kashmir INCLUDING CONTENTS;
This command fails if active rollback segments are still using storage in the tablespace. You must deactivate them before you remove the tablespace.
Temporary segments are database objects automatically created by Oracle when extra working space is needed. This is usually the case when large sort-type operations are performed. For example, when the order by, group by, and union clauses are run on a
large table, they might cause a temporary segment to be created.
It is difficult to see the temporary segments, for they are deleted automatically by the SMON background process as soon as they are no longer required. If you suspect that a temporary segment exists, query the dba_segments data dictionary view and look
for a TEMPORARY segment type. The v$sysstat dynamic performance table also shows how often temporary segments have been created since the instance was started.
To control where temporary segments are created on a user-by-user basis, issue the following command:
SQLPLUS> ALTER USER lave TEMPORARY TABLESPACE kashmir;
All users should be defined so that their temporary segments are created in the same tablespace. This ensures that free space fragmentation occurs in only one part of the database.
You can also control how the default tablespace is used. For example,
SQLPLUS> ALTER USER lave DEFAULT TABLESPACE kashmir;
means that when a user issues a CREATE statement and does not explicitly state where the object should be created, it is placed in the kashmir tablespace.
The dba_users data dictionary view shows the temporary and default tablespaces for all users.
You often have a large amount of storage has been allocated to a table, but you want to determine exactly how much storage the table actually uses and how much is empty. You might, for example, want to reclaim storage.
The ANALYZE command serves two purposes. By gathering statistics about the data, you can use the Oracle cost-based optimizer to make more intelligent decisions about how to run a statement against the table. Likewise, you can find out exactly how much
storage a table uses.
To analyze a table, use the following command:
SQLPLUS> ALTER TABLE taejen COMPUTE STATISTICS;
This command goes through all the data for the table and gathers information about how many Oracle blocks are used or free and what percentage of them contain data. This information is available in the dba_tables and dba_indexes data dictionary tables.
It can take time to gather a complete set of statistics for a large table. You might want to gather a representative sample of statistics instead. For example,
SQLPLUS> ALTER TABLE taejen ESTIMATE STATISTICS SAMPLE 20 PERCENT;
This command produces statistics based on a random sample of 20 percent of the rows.
If you do not want the cost-based optimizer to use the statistics that you gather, you can delete the statistics by issuing the following command:
SQLPLUS> ALTER TABLE taejen DELETE STATISTICS;
After you determine how much storage is used by the table, you can use the export and import utilities to recreate the table with the optimal storage parameters.
To estimate the amount of storage that a table will use, you should know the number of rows expected in the table and their average size. In addition, you need to know the overheads that Oracle will use. The first Oracle block of each datafile is an
overhead block, as is the first Oracle block of every database segment. Each block has at least 80 bytes of overhead, plus an additional 23 bytes for every increase in the MAXEXTENTS storage parameter beyond 1. There are five bytes of overhead for each row
and one byte of overhead for each column that has a value. Not all the remaining space in a block is used to store your data; some of the block remains free so that the rows can expand into it. This is the PCTFREE parameter, which you can specify when you
create the segment.
These overhead figures give you a rough idea of the amount of storage that you should give to a segment's initial extent. After you arrive at an estimate, add on a little more. Chapter 8 of the Oracle 7 server administrator's guide gives a more detailed
example of working out the amount of storage to allocated to a segment.
In this unit, you have seen how Oracle uses the files on the file system for the database, how the storage is managed logically in terms of tablespaces, and the different types of segments that can occupy storage within a tablespace. You have also
learned how storage is allocated to the segments when they are first created, when the segments need to grow, and how storage is released. You also saw the ROWID, which is the unique address for every row in a database.
| 
