Previous | Table of Contents | Next

Page 88

the buffer cache. If another user session requests the same data, the before image stored in the rollback segment is returned (this is called a consistent read). When the session that is making the change commits, the rollback segment entry is marked invalid.

Multiple user sessions can share a single rollback segment. Each rollback segment is made up of at least two extents. When a transaction starts, the user session gets an exclusive lock on an available extent in an available rollback segment. Transaction information is then written to the rollback segment. If the transaction fills the first extent, it allocates another extent. If another extent is unavailable, the rollback segment automatically allocates another extent to itself, which the user session grabs. This is called rollback segment extension. Because extent allocation affects performance, your goal should be to enable all transactions to run without allocating new extents.

If the rollback segment is unable to allocate another extent (either because the maximum number of extents has been reached for the rollback segment or there are no more free extents in the rollback segment tablespace), an error occurs and the transaction is rolled back. This commonly occurs in large data loads, where online rollback segments do not provide sufficient space to store all of the rollback information for the transaction.

See Chapter 21 for more information on creating and administering rollback segments.

The DBA_ROLLBACK_SEGS view contains information on rollback segments.

Table Clusters

A table cluster is a database object that physically groups tables that are often used together within the same data blocks. The clustering of tables is most effective when you're dealing with tables that are often joined together in queries. A table cluster stores the cluster key (the column used to join the tables together), as well as the values of the columns in the clustered tables. Because the tables in the cluster are stored together in the same database blocks, I/O is reduced when working with the clusters.

Hash Clusters

Hash clusters are the final option for database storage. In a hash cluster, tables are organized based upon a hash value derived by applying the hash function to the primary key values of the tables. To retrieve data from the hash cluster, the hash function is applied to the key value requested. The resulting hash value gives Oracle the block in the hash cluster where the data is stored.

Using hash clusters can significantly reduce the I/O required to retrieve rows from a table. There are several drawbacks to using hash clusters, however. See Chapter 21 for more information on creating and administering hash clusters.

Page 89

Using the Oracle Data Dictionary

The data dictionary is the repository of information on all of the objects stored in the database. It is used by the Oracle RDBMS to retrieve object and security information and by the end-users and DBAs to look up database information. It holds information on the database objects and segments in the database, such as tables, views, indexes, packages, and procedures. The data dictionary is read only; you should NEVER attempt to manually update or change any of the information in any of the data dictionary tables. It consists of four parts: the internal RDBMS (X$) tables, the data dictionary tables, the dynamic performance (V$) views, and the data dictionary views.

Internal RDBMS (X$) Tables

At the heart of the Oracle database are the so-called internal RDBMS (X$) tables—the tables used by the Oracle RDBMS to keep track of internal database information. The X$ tables are cryptically named, undocumented, and nearly impossible to decipher. Most of them are not designed to be used directly by the DBAs or end users. Nonetheless, they contain valuable information. Many undocumented or internal statistics and configurations can be found only in the X$ tables.

The easiest way to decipher what is stored in a particular X$ table is to work backward from a known data dictionary table. The SQL*Plus autotrace feature is invaluable for this work. For example, to determine where the information in V$SGASTAT is really stored, you can perform the following analysis:

From the output of the SQL trace, you can decipher the data dictionary base tables from which the information for the view is extracted. Querying the X$ tables found in this manner often produces surprising information.

Page 90

Data Dictionary Tables

The data dictionary tables hold information for tables, indexes, constraints, and all other database constructs. They are owned by SYS and are created by running the SQL.BSQ script (which happens automatically during database creation). They are easily identified by the trailing dollar sign at the end of their names (tab$, seg$, cons$, and so on). Most of the information found in the data dictionary tables can be found in the data dictionary views, but certain applications and queries still benefit from using the information contained in the base tables.

The columns and tables of the data dictionary are well documented in the SQL.BSQ file. This file is found in the $ORACLE_HOME/dbs directory. By familiarizing yourself with the contents of SQL.BSQ, you can gain a better understanding of how the Oracle RDBMS actually stores the data dictionary and database information.

Dynamic Performance (V$) Views

The dynamic performance (V$) views are the mainstay of the Oracle DBA. These views contain runtime performance and statistic information on a large number of database functions. They are also fairly readable (as opposed to the X$ tables) and are meant to be used by the DBA to diagnose and troubleshoot problems. Documentation on most V$ views can be found in the Oracle Reference Manual, supplied on your Oracle server media.

Note that the V$ views are actually public synonyms to the V_$ views, owned by SYS. This is important to note when writing stored procedures or functions that read the V$ tables. It is often necessary to reference or grant privileges to the base V_$ view rather than the V$ public synonym.

Data Dictionary Views

The data dictionary views are views created on the X$ and data dictionary tables and are meant to be queried and used by end users and DBAs. They are divided into three categories—the DBA_, ALL_, and USER_ views. The DBA_ views contain information on all objects in the database. For example, DBA_TABLES contains information on all tables created. The ALL_ views contain information on all objects to which the user querying the table has access. The USER_ views contain information on all objects the user querying the table owns.

Other Database Objects

There are several other objects stored in the database that are not rightfully classified as segments but should be discussed nonetheless. They include views, sequences, synonyms, triggers, database links, and stored packages, procedures and functions. They are described in the following sections.

Views

Views are stored SQL statements that can be queried. A view is used for security reasons to hide certain data (such as an HR view that shows only first name, last name, and address

Previous | Table of Contents | Next