Previous | Table of Contents | Next

Page 83

Oracle Blocks The lowest level of database storage that we can manipulate is the Oracle block, which is the smallest unit of storage the server can address. It should not be confused with the operating system block—although an Oracle block is made up of operating system blocks, they are not the same.

All data access is performed in terms of the Oracle block. The size of the Oracle block is the number of bytes the RDBMS will read and write from the datafiles in one I/O. Database object sizes and blocks in memory caches are also set in terms of Oracle blocks (although some views display storage size in bytes, this is only for readability).

NOTE
The Oracle block size is set for a database upon database creation and can never be changed. If at a later time you decide that a larger (or smaller) block size is necessary, you will have to rebuild the entire database from scratch.

Each Oracle block contains space for header information, future updates of data in the block, and the actual rows stored in the block. The block header contains information such as the database segments that have rows in the block, how many transactions can access the block at one time, and so on. Each block allocates a certain amount of space for future updates to the rows stored in the block. If an update occurs that causes the original row to grow, this free space is used.

The availability of a block to accept new rows is controlled by the PCTFREE and PCTUSED storage settings. The PCTFREE parameter allocates the percentage of block space to set aside for updates of data. For example, if a block has a PCTFREE value of 30%, 70% of the block space will be used for new rows. When 70% of the block has been filled, Oracle will take the block off the free list and use the remaining 30% of space to handle updates of rows within the block that require more space.

The PCTUSED parameter specifies how much space must be freed in a block (through deletions of rows within the block, or updates that reduce the amount of space needed to store a row) before the block will be placed back on the free list.

PCTFREE and PCTUSED work together to ensure that a block has enough space to handle future storage requirements, and also to make sure the block does not flip-flop the free list for the block on and off. PCTFREE and PCTUSED values should not equal 100%, or the value of the parameters is lost. See Chapter 21, "Managing Database Storage," for more information on tuning Oracle blocks.

Because it determines the number of bytes read from the datafiles during a single I/O operation, the database block size is a very important tuning consideration. On-Line Transaction Processing (OLTP) applications benefit from a smaller block size (4 or 8KB). Because these applications typically read and write small amounts of data during each transaction, smaller block sizes are more efficient in terms of I/O performance.

For warehousing or Decision Support System (DSS) databases, larger block sizes can boost performance tremendously. These types of applications are usually dealing with large amounts of data in a single transaction and are often concerned with response times measuring in minutes rather than seconds.

Page 84

By setting your block size, you are adjusting the amount of data read into the buffer cache for any one I/O. When you make your decision on the optimum block size, you must keep in mind the specific types of data access your applications will be performing, as well as the amount of data you are likely to store in a single row. A database block size that is too big will read more data into the buffer cache than necessary, while a block size that is too small will result in row chaining.

User Database Objects

User database objects are those objects that are not used exclusively by the Oracle RDBMS. All of the objects, with the exception of the datafiles, are logical database objects, meaning that they only have form and definition in the context of the Oracle server. (Because the datafiles are actual operating system files, we call them physical objects.)

User database objects include datafiles, extents, tablespaces, and database segments. An explanation of each of these items follows.

DatafilesThe Oracle datafiles are the only physical database objects. Each datafile is allocated to one tablespace and holds the actual data stored in that tablespace. The datafile is an actual file in the file system and can be monitored and manipulated like any other operating system file. The data stored within the datafile is in an Oracle binary format and as such is unreadable by anything other than the Oracle RDBMS.

Datafiles are created using the CREATE TABLESPACE or ALTER TABLESPACE SQL command. A datafile is sized according to the size specified in the create statement, not the amount of data stored in it. For example, a datafile created with a size of 10MB uses the full 10MB of space whether it contains one or one million rows.

As of Oracle 7.3, the DBA is able to dynamically grow and shrink Oracle datafiles. However, the datafiles cannot be shrunk smaller than the high water mark of the datafile. Datafiles can also be taken offline individually for backups or other database operations.

You can access the DBA_DATA_FILES and V$DATAFILE views for information on the datafiles defined for the database. The following query displays the mapping between database files and tablespaces:


SELECT tablespace_name, file_name, bytes
FROM dba_data_files
ORDER BY tablespace_name, file_name;

ExtentsAn extent is a unit of storage made up of one or more contiguous Oracle blocks. Each database segment is composed of one or more extents. Each extent in a database segment can be of the same or different size. As of Oracle 7.3, the maximum number of extents a single database object can have is dependent on the Oracle block size.

A database segment is allocated extents upon object creation, as specified by the storage clause of the create command. When a segment can no longer fit new data into its currently allocated extents, it must allocate another extent. The size of the next extent allocated is dependent upon quite a few factors, some of which are not immediately apparent. Managing the allocation of

Previous | Table of Contents | Next