Chapter 4. Introduction to the Oracle Database 89
working on it. In general, PGA contains a private SQL area (storing bind
information and run-time structures that are associated with a shared SQL area)
and a session memory that holds session specific variables, such as logon
information. If Oracle is using shared servers, this memory area is shared.
The run-time area of PGA can also be used as a work area for complex queries
making use of memory-intensive operators like the following ones:
Sort operations, like ORDER BY, GROUP BY, ROLLUP, and WINDOW
If the session connects to a dedicated Oracle server, PGA is automatically
managed. The database administrator just needs to set the
PGA_AGGREGATE_TARGET initialization parameter to the maximum amount
of memory he wants Oracle to use for client processes. Oracle ensures that the
total PGA allocated to all processes never exceeds this value.
4.1.2 Logical storage structures
An Oracle Database is made up of several logical storage structures, including
data blocks, extents and segments, tablespaces, and schema objects.
The actual physical storage space in the datafiles is logically allocated and
deallocated in the form of Oracle data blocks. Data blocks are the smallest unit of
I/O in an Oracle Database. Oracle reserves a portion of each block for
maintaining information, such as the address of all the rows contained in the
block and the type of information stored in the block. This overhead is normally in
the range of 84 to 107 bytes.
An extent is a collection of contiguous data blocks. A table is comprised of one or
more extents. The very first extent of a table is known as the
initial extent. When
the data blocks of the initial extent become full, Oracle allocates an
. The incremental extent does not have to be the same size (in bytes) as
the initial extent.
A segment is the collection of extents that contain all of the data for a particular
logical storage structure in a tablespace, such as a table or index. There are four
different types of segments, each corresponding to a specific logical storage
Data segments store all the data contained in a table, partition, or cluster.
Likewise, index segments store all the data contained in an index. For backward
compatibility, rollback segments hold the previous contents of an Oracle data
block prior to any change made by a particular transaction. If any part of the
transaction should not complete successfully, the information contained in the
rollback segments is used to restore the data to its previous state. From Oracle9i
onwards, this is achieved by using
automatic undo and undo tablespaces, which
allow better control and use of the server resources.
Rollback segments are also used to provide
read-consistency. There are two
different types of read-consistency:
statement-level and transaction-level.
Statement-level read consistency ensures that all of the data returned by an
individual query comes from a specific point in time: the point at which the query
started. This guarantees that the query does not see changes to the data made
by other transactions that have committed since the query began. This is the
default level of read-consistency provided by Oracle.
In addition, Oracle offers the option of enforcing transaction-level read
consistency. Transaction-level read consistency ensures that all queries made
within the same transaction do not see changes made by queries outside of that
transaction but can see changes made within the transaction itself. These are
Temporary segments are used as temporary workspaces during intermediate
stages of a query’s execution. They are typically used for sort operations that
cannot be performed in memory. The following types of queries may require a