Database Versus Instance

People, even experienced DBAs, dealing with Oracle often confuse the idea of what a database is with what an instance is.

Oracle Server

The files, processes, and memory structures, whether used when processing SQL statements or not, make up the Oracle Server. The Oracle Server consists of the Oracle instance and the Oracle database.

Database

An Oracle database consists of the physical datafiles and the other physical supporting files that assist in the controlling of the access and operation. The collection of data is treated as a single unit. To interact with these files, and the information in these files, you need the assistance of the operating system (for resources such as memory and I/O subsystem) and a set of specific Oracle-created processes.

Instance

An Oracle instance, the set of Oracle-created processes and specific memory allocation that allow users to access the information stored in the database's datafiles, performs the work necessary for the efficient maintenance of the system and the efficient access and manipulation of the information resident in the database.

Although you often hear people talking about a database being "up" or "down" (or…shudder…"crashed" for that matter), it really means that the instance is up, down, or crashed. Physical files can be corrupt or deleted, but only the processes of the instance can be up or down.

Data Blocks

The smallest logical component of an Oracle database is the Data Block, often also referred to as Oracle blocks, and the smallest unit of space allocation possible. Data Blocks are defined in terms numbers of bytes, for example 2KB, 4KB, or 8KB. The database's standard block size is set in the DB_BLOCK_SIZE initialization parameter and is allocated in powers of 2KB, typically up to 32KB.

Although you add space to a database in bytes, kilobytes, or even megabytes or gigabytes, because a data block is the smallest unit of storage that Oracle can manage or retrieve in a read operation, it is important that you work with your system administrator to determine the disk block size (covered in the following section) of the operating system and make the data block size a multiple of the operating system's disk block size. This allows you to take best advantage of the underlying operating system's capability to read and write larger chunks of data to disk in a single I/O operation.

It is important to note that in prior releases, Oracle allowed only one block size to be in a database. There was no facility for allowing multiple block sizes in the same database. Oracle has provided much flexibility and ease of management in providing this capability.

Data blocks are made up of the following pieces (see Figure 1.1):

• Block header A 48-bit integer used for checking the block integrity

• Free space The amount of space left in the block that can be allocated to additional rows of row data, or to expansion of existing rows

• Row data The data stored in the tables to which the given data block belongs

Figure 1.1. Components of Oracle data blocks.

Block Size Choices

Carefully consider the purpose of your database at its creation time. Its purpose will impact the standard block size that you will want, and the standard block size is difficult to change after the database's creation and requires a total rebuild of the database to make this change. Typically, smaller block sizes are used for systems that have smaller rows, and you have inserts, updates, and deletes occurring frequently, as in an Online Transaction Processing (OLTP) system. Larger block sizes are usually used for systems where there are many large queries but less frequent updates, as in a data warehouse or an Operational Data Store (ODS) or a Decision Support System (DSS).

Although Oracle's default block size, if the DB_BLOCK_SIZE is left unset, is 2KB, even a busy OLTP environment would benefit from a block size of around 8KB.

Oracle's default block size is 2KB if the DB_BLOCK_SIZE initialization parameter is left unset in a manual database creation. In an Oracle database created with the Creation Assistant, the default block size is 8KB if not specified.

If you find that you have need for multiple block sizes in a database, with release 9i, Oracle provides that capability as well. You can now allocate four nonstandard block sizes in addition to the database's standard block size. It is important to remember that you have to also configure the corresponding DB_nK_CACHE_SIZE to go along with the nonstandard block sizes that you intend to use.

Cache sizes can be set to any size but will be rounded to the nearest granule. A granule is either 4MB for a System Global Area (SGA) with a total size less than 128MB, or 16MB for a larger SGA. We cover the SGA later in this chapter. The standard buffer cache is set using the DB_CACHE_SIZE parameter and, if left unset, defaults to 48MB. Individual buffer caches (covered in more detail later in the chapter) need to be defined for each nonstandard block size before that block size can be used to define a tablespace. These additional buffer caches are set up with the following initialization parameter.

A database with an 8KB standard block size might have the following nonstandard buffer cache sizes:

• DB_2K_CACHE_SIZE = 16MB

• DB_4K_CACHE_SIZE = 16MB

• DB_16K_CACHE_SIZE = 16MB

• DB_32K_CACHE_SIZE = 16MB

Again, each cache to be used must have at least one granule assigned to it, and the instance has to be restarted before the changes to these parameters can take effect.

Although Oracle 9i has been widely publicized as a dynamic database with parameters that mean that the instance no longer has to be restarted for changes to take place, this is not necessarily true in all cases. A significant number of initialization parameters still are static and do require the data-base to restart for their changes to take place. DB_nK_CACHE_SIZE is one such parameter.

Before a DB_nK_CACHE_SIZE parameter can be reset back to 0MB, you need to make sure that there are no online tablespaces using the corresponding nK block size.

Now you can create a tablespace using the nonstandard block size:

 CREATE TABLESPACE my_smallblock_tblsp BLOCKSIZE 2048; 

The ability to add four additional nonstandard block sizes to the database, in addition to the standard data block size, gives you a total of five distinct block sizes to use in a single database and instance.

Disk Blocks

The disks on which the Oracle blocks reside are also divided into areas of contiguous storage of bytes, called disk blocks, or OS blocks. Typically, these blocks are referred to by the number of bytes involved rather than the number of KB. Different operating systems have different standard block sizes. Some are 1024 (1KB), some 512 (.5KB), and others 4096 (4KB) or even 32768 (32KB) or more.

Extents

Several contiguous data blocks make up an extent. Oracle allocates an extent only if it can find sufficient contiguous data blocks to allow for the extent to be created in a single allocation.

Tablespace type plays a part in determining how Oracle allocates extents. Dictionary managed tablespaces allow you to specify the beginning allocation of space and the future increments as necessary.

The CREATE TABLESPACE statement, for a dictionary managed tablespace, includes the storage parameter specifications starting with Default Storage and including the keywords INITIAL and NEXT, MINEXTENTS, MAXEXTENTS, and PCTINCREASE.

 CREATE TABLESPACE mytblspace     DATAFILE '/mydatabases/mydb1/mytblspace01.dbf' SIZE 500M     DEFAULT STORAGE (         INITIAL 50K         NEXT 50K         MINEXTENTS 2         MAXEXTENTS 50         PCTINCREASE 0); 

Locally managed tablespaces use a simpler method, allocating uniform extent sized, chosen automatically by the database. Not only do you not have to worry about setting the initial and next extent size, if you do set the size in an allocation, Oracle ignores what you set.

All database objects get allocated a certain amount of space when they are created. This initial space allocation is called the initial extent. After an extent is allocated to a table or an index, the extent remains allocated to that particular object until either that object is dropped from the database, at which time, the extents revert back to the pool free space waiting to be allocated in the database, or the object is truncated with the DROP STORAGE clause or the REUSE STORAGE clause associated with the truncate command.

Segments

Segments are made up of two or more data blocks that are themselves allocated to an extent that is allocated to a specific logical structure. A single segment can span multiple datafiles belonging to the tablespace to which the segment also belongs. Table 1.1 provides a summary of the common segment types along with their descriptions.

Table 1.1. Common Segment Type Descriptions

Segment Type	Description
Data segment (or table segment)	All nonclustered tables have data segments; all table data is stored in the extents in the data blocks of data segments. Each partition in a partitioned table has a data segment, and each cluster has a data segment. In the case of a cluster, the data of every table that is a member of the cluster is stored in that cluster's data segment. These segments contain a header block that serves as a space directory for the segment. Table segments hold onto their previous storage settings until either they are dropped or the table is truncated with the DROP STORAGE clause or the REUSE STORAGE clause associated with the truncate command.
Index segment	Each index has an index segment in which all its data is stored. For partitioned indexes, each partition has its own index segment. These segments hold onto the space that has been allocated to them until they are dropped or until the index is truncated with the DROP STORAGE clause or the REUSE STORAGE clause associated with the truncate command. If they are sized incorrectly, fragmentation can occur and slow the database performance.
Temporary segment	Temporary segments are created by Oracle only when a SQL statement is run that needs a temporary work area (typically used for sort work that can't be run in memory) in which to complete its execution. When the statement requiring that space finishes execution, the extents in the temporary segment are returned to the system for future use. These should never be created in the system tablespace.
Rollback segment	If you are operating your 9i instance in automatic undo management mode, the database server transparently manages undo space using tablespaces. If you are still operating your 9i instance in manual undo management mode, one or more rollback segments need to be created and managed by the database administrator to temporarily store the information that allows the instance to undo any active transactions. Regardless of the mode of undo management, the segments in question are used during database recovery, to generate a read-consistent picture of the data in the database or to roll back any uncommitted user transactions or for queries that need a read-consistent view of the data.
LOBnn segment	If there are LOBs (or large objects) such as text documents, videos, or images, these segments store pointers to the large objects in the datafiles.
Cache segment	If a table has a nested table as a column, a cache segment is used to store the user-defined embedded table.
Bootstrap segment	This segment is created by the sql.bsq when the database is created.

Each database object created has its own segmentevery table, every index, everythingand every segment is named after the object it was created for. Any space that gets allocated for these segments remains allocated, even if the object is truncated (unless you specify either TRUNCATE with REUSE STORAGE or trUNCATE with DROP STORAGE), until that object is deleted from the database.