0077-0079

Previous Table of Contents Next

Page 77

Distributed Databases

A distributed database is one logical database that is implemented as two or more physical databases on either the same machine or separate machines thousands of miles away. The system's designers decide where the tables should physically reside.

Each physical database has its own instance and sets of files, and the machines on which the databases reside are connected over a network. The location of tables can be made transparent to the application using database links and synonyms.

Oracle enables a transaction and even a single statement to access tables on two or more distributed databases. This does not necessitate any more coding by the application developers.

A distributed transaction is a transaction that modifies tables on more than one database and then expects all the changes to be committed. If there is any kind of failure, all the changes on all the databases are rolled back. A distributed transaction can involve many Oracle databases and only one non-Oracle database. The Oracle two-phase commit mechanism controls the synchronization of commits across all databases and can automatically roll back changes on all the databases if any kind of failure should occur. The RECO background process synchronizes this operation.

In addition to this functionality, Oracle also provides the capability to replicate tables from one database to others. This is called creating a snapshot of the table.

You create a snapshot with the CREATE SNAPSHOT command on the database where you want to have the copy of the data. The Oracle RDBMS software automatically sends down any changes made to the master copy of the table to each of the snapshot copies at user -defined intervals, without any manual intervention.

The snapshot mechanism enables you to make updates to the snapshot copy of the table, in which case the changes are sent from the copy table back to the master table.

Chapter 55, "Networking," discusses distributed databases in greater detail.

National Language Support

Oracle's national language support (NLS) enables users to use the database in their own languages. It provides the following functions:

• Support for different encoding schemes, so that data created with an encoding scheme on one machine can be processed and displayed on another. You define the character set to be used for storing data on the database as part of the CREATE DATABASE statement. For example, data created on a database with the 7-bit U.S. ASCII standard character set can be displayed on a machine connected to the same database using the Chinese GB2312-8 character set. Translation tables within the national language provide this support.

Page 78

• Control over the language used for server error and informational messages, numbers , dates, currency formats, and the starting day of the week.
• Support for linguistic sort to ensure the characters appear in the correct order next to each other in a sort .
• Definition of a NLS language either for the database as a whole or at the session level. With changes to the session parameters but without any changes to the Oracle user account, you could run some sessions with English, others German, others French, and so on, if the same Oracle username is connected to the database with many different sessions.

You can add support for new languages using the NLS*WorkBench product, which essentially maintains translation tables for interpreting input from the user and for displaying output.

When it comes to delivering application systems in different languages, the most important part of the user interface is the different prompts, boilerplate , and messages from the application. Currently, the application developers themselves define how the boilerplate, prompts, and messages from the application system change from one language to another. Oracle is working on a translation product to make this task easier.

Data Warehousing

A data warehouse is a database targeted for decision support and will include very large amounts of historical, summarized, and consolidated data (gigabytes to terabytes in size ). Much of the activity on the database will be query-oriented involving large and complex joins across many tables. A major consideration will be the I/O and CPU load required to perform these operations in an acceptable time frame.

Oracle8 offers the following major options for a data warehouse application:

• Parallel Server option for the database server
• Parallel load of data into SQL*Loader
• Parallel queries and parallel index creation
• STAR optimizer hint to force large tables to be joined last in a query
• Parallel table creations when based on existing data
• Partitions and partition views (with the optimizer being able to ignore entire partitions if appropriate for the query)

These options, once properly set up and configured, will ensure that the time taken to load, query and execute operations on large data is acceptable.

Page 79

Following a SQL Statement Through the
Architecture

This section brings together major parts of the Oracle architecture and follows the steps that a typical SQL statement might go through to be executed. We use a simple scenario with both the Oracle SQL*Plus tool and the Oracle database server machine on a UNIX box without any networking involved. Using a single task configuration, the Oracle instance has just started.

The following shows some of the steps involved in executing SQL statements.

1. The user executes the SQL*Plus tool and enters the Oracle username and password.
2. Oracle validates the username and password against the data dictionary and sends a response to the user process to indicate connection.
3. The user enters a SELECT statement.
4. Oracle must translate the SELECT before it executes it so the Oracle parser and optimizer is called. If any user has issued exactly the same statement before, the parsed version might be in the shared pool area in memory. Oracle uses the parsed version, so no extra parsing is done for this statement.
5. To translate the SELECT statement, Oracle must obtain the names of the objects, privileges, and other information from the data dictionary. The data dictionary cache area in the shared pool in the SGA does not have the information on the tables, so parsing of the SELECT statement is suspended while the information is read in.
6. Oracle runs a recursive SQL statement (a system-generated statement) to load information about the objects from the data dictionary tables in the database files into the data dictionary cache in memory.
7. Parsing of the original user SELECT statement resumes, and Oracle constructs an optimization plan to control the way the statement runs.
8. The statement accesses a table. Assume the Oracle blocks for the table are not in the database buffer cache in the SGA. The required Oracle blocks are read in from the database files and held in the cache area of the SGA.
9. Oracle runs the statement and returns the results to the user.
10. The user issues an UPDATE statement to modify some of the fields on the rows he's just selected. Because the data dictionary cache already has the information about the table in memory, no more recursive SQL is generated ( assuming that the information has not been flushed out by another process requiring space in the cache area). Also, the Oracle blocks for the table are in the database buffer cache, so you won't do another disk I/O to read these blocks in.
11. Oracle locks the rows to be updated.

Previous Table of Contents Next