CIS333 Database Administration

<< back to courseware demo page - This online lecture is for demonstration purposes

Demo Lecture

Oracle Architecture Components

Learning Objectives

To introduce the components in the Oracle architecture.
To describe the composition of the Oracle instance and the Oracle database.

Lecture Menu

DBA Tasks

Oracle Processes

Database Files

Stages in Processing a Query

Execute Phase Of A DML Statement

Rollback Segments

Commit Processing: Steps

Summary

Review Questions

Practice Test and Answers

DBA Tasks

The Oracle Enterprise Edition is an object relational database that is scaleable and easily manageable.
In the Oracle environment, the Database Administrators (DBAs) are responsible for managing the system and the users.
The major tasks:

- To install the Oracle server and the application tools, and

create databases.

- To ensure that the database is available to all the users.

- To create the logical storage structures, such as tablespaces, tables, views, and indexes required by the applications designed by the application developers

- To manage the physical storage structures, such as data files, control files, and redo log files.

- To allocate system storage and plan the future storage requirements for the database system.

- To manage storage space based on the design specifications. Based on the information given by application developers, the DBA also modifies the database structure when required.

- To manage the database users and control and monitor their actions.

- To enroll new users.

- To ensure the security of the system.

- To monitor and optimize the performance of the database to accommodate simultaneous access by many users.

- To plan and maintain backups of the database.

- To recover a database quickly.

Connecting with the Oracle server

To retrieve information from the Oracle database, users first establish a connection with the Oracle server.
There are three types of connections that a database user can establish with the Oracle server.
The three types of connections are local, two-tiered, and multitiered.
Using the local connection, a user directly logs on to the machine running the Oracle server.
An example of a local connection is when a user connects to a UNIX machine running the Oracle server and uses the Oracle SQL*Plus Worksheet utility to access the database.
Using the two-tiered connection, also known as the client-server connection, a user logs on to a machine that is directly connected to the machine running the Oracle server.
An example of the two-tiered connection is when a user runs an Oracle Forms application on a Windows personal computer to access an Oracle database residing on an NT server.
In the multitiered connection, the user's machine is connected to a network server.
The network server is connected through the network to the machine running the Oracle server.
An example of a multitiered connection is when a user runs a browser on a network computer to execute an application from an NT server. The NT server, in turn, retrieves data from an Oracle database residing on a UNIX server.

Oracle Processes

When a user is connecting to a machine running the Oracle server to access a database, two processes are involved. The processes are the user process and the server process.
A user process is created when a user starts a tool or application.
For example, a user process is created when a user starts an Oracle SQL*Plus Worksheet.
A user process would also be created when a user executes an application developed by using a tool, such as Oracle Forms.
A user process is a mechanism used to execute the code of an application program or an Oracle tool, such as Oracle Enterprise Manager Console.

Note: In a client-server model, the tool or the application executes on the client machine.

The server process is created when a user logs on to the Oracle server by specifying a username, a password, and a database name.
The server process is created on the same machine where the Oracle server is running.
A server process is a mechanism of interaction between the Oracle server and the user process that runs on the client.
It holds the operations requested by the user process and performs them on the Oracle server.
A communication pathway between a user process and the Oracle server is called a connection.
When the user starts to interact with the Oracle server, a connection is established.
The user can run the tool or the application on the same machine as the Oracle server.
In this case, the communication pathway is established using the interprocess communication mechanisms available on the server computer.
If the user runs the tool or the application on a client machine, network software is used to communicate between the user and the Oracle server.
A specific connection of a user to the Oracle server is called a session.
A session starts when the Oracle server validates the user. The session ends when the user logs out or if there is an abnormal termination.
Many concurrent sessions can run for a user who has simultaneously logged on from many tools, applications, windows, or workstations.

User Processes

To run an application tool, such as SQL*Plus, or an application developed by using a tool such as Oracle Forms, Oracle creates a user process.
A user process is also called the client.
In a client-server system, this process runs on the machine to which the user is directly logged on.
The user process starts when a tool is started by a user and it terminates when either the user logs out or there is an abnormal termination, such as a system failure.
The user process includes the User Program Interface (UPI).
A UPI is a mechanism used by a user process to communicate with a server process.
The UPI is a method of standard communication between any client tool or application and the Oracle software.
The UPI generates a call to the Oracle server when the user makes a request. Once the request is processed by the Oracle server, the result is sent to the user.

Server Process

The user requests are submitted by the user process to the server process.
The server process handles these user requests.
As a database administrator, you are responsible for administering these server processes and making sure that they have the resources they need.

The server process runs on the same machine on which the Oracle server runs.

Note: The user process may be running on a separate machine but the server process interacts closely with the Oracle server.

On a dedicated Oracle server, a server process handles a single user process.
The server process is created when a user requests a connection and is terminated when the user disconnects.
Each server process uses an area of memory called the Program Global Area (PGA).
A PGA is created when a server process is started.
The server process includes the Oracle Program Interface (OPI), which is used to communicate with the Oracle server at the request of a user process.
The server process returns the status information and the results to the user process.
Client-server systems separate the user and server processes and execute them on separate machines.
In the multithreaded server (MTS) configuration, several user processes share a single server process, unlike the dedicated server configuration.

System Global Area

The Oracle server consists of an Oracle instance and an Oracle database.
The Oracle instance consists of a memory region called the System Global Area (SGA).
The SGA contains data and the control information of the Oracle server.

An Oracle database allocates memory to the SGA when an instance starts, and deallocates it when the instance shuts down.
The memory region allocated to the SGA is shared. It should fit in the real memory that is nonswapped and nonpaged for performance reasons.
Each instance has its own SGA.
The SGA consists of a group of memory structures. These structures are created during instance startup.
The three main memory structures are the shared pool, the database buffer cache, and the redo log buffer.
There is also a large pool and a Java pool.
The shared pool is used to store information such as the most recently executed SQL statements and the most recently used data dictionary information.
The database buffer cache memory structure is used to store the most recently used user data.
The redo log buffer is used to record the changes made to the database.

Background Processes

An Oracle instance consists of an SGA and a set of Background Processes.
When an instance is started, the Oracle database creates a set of background processes for the instance.
The background processes are operating system processes.
Each Oracle instance may use several background processes, depending on the Oracle server configuration.
In Oracle8i, there are five mandatory background processes for each instance.
The mandatory background processes in Oracle are Database Writer (DBWn), Log Writer (LGWR), System Monitor (SMON), Process Monitor (PMON), and Checkpoint (CKPT).
The DBWn process writes the changed data from the database buffer cache to the data files.
The LGWR process records the changes that are registered in the redo log buffer to the redo log file. The changes are also referred to as the redo log data.
The SMON process performs an instance recovery during instance startup.
SMON also cleans up the temporary segments that are no longer in use and recovers the dead transactions skipped during a system failure.
SMON also coalesces fragmented free space in the data files.
The PMON process performs process recovery when a user process fails.
PMON is responsible for cleaning up the cache and unlocking the system resources held by the user process.
The CKPT process is responsible for updating database status information, such as database file headers.
This is done when changes in the database buffer cache are permanently recorded in the data files during a checkpoint event, such as during a log switch.

Optional Background

In addition to the mandatory background processes, there are optional background processes, including, Recoverer (RECO), Archiver (ARCn), Lock (LCKn), Dispatcher (Dnnn), Queue Monitor (QMNn) and Shared Server (Snnn).

RECO is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.
ARCn copies the online redo log files to archival storage as they become full. ARCn is active only when a database is in the ARCHIVELOG mode.
The LCKn processes are used for interinstance locking in Oracle Parallel Server in which multiple instances are running.
You can configure any LCKn process between LCK1 and LCK9.
The Dnnn processes are optional background processes. These processes are available only when a multithreaded server (MTS) configuration is used.
Dnnn processes enable the user processes to share limited server processes in a multithreaded server environment.
There can be any number of dispatcher processes between D000 and Dnnn.
The queue monitor process is used for Oracle Advanced Queuing, which monitors the message queues. You can configure as many as 10 queue monitor processes (QMNO through QMN9).
Oracle Advanced Queuing provides an infrastructure for distributed applications to communicate asynchronously using messages.
Oracle Advanced Queuing stores messages in queues for deferred retrieval and processing by the Oracle Server. The parameter AQ_TM_PROCESSES specifies the number of queue monitor processes.
Shared server processes provide the same functionality as the dedicated server processes, except that shared server processes are not associated with a specific user process.
You create shared server processes to manage connections to the database in a shared server configuration. The number of shared server processes that you can create ranges between the values of the parameters SHARED_SERVERS and MAX_SHARED_SERVERS.

Database Files

An Oracle database is a collection of data that stores and retrieves related information.
It consists of physical storage structures or files. One of the responsibilities of the DBA is to manage the files of the database.
To be able to manage the files requires that you know the types of files existing in a database and the information stored in them.
Three types of files that exist in a database are data files, redo log files, and control files.
In an Oracle database, there can be one or more data files. A data file stores the data dictionary, the user objects, and the before-images of the data blocks that are modified by the current transactions.
The data in a data file is read, when required, and stored in the database buffer cache.
The modified or new data is not immediately written to the data file. However, it is stored in the database buffer cache.
Another type of database file is the redo log file. Every Oracle database has at least two redo log groups, each having at least one redo log file.
The redo log files are used to record all the changes made to the data to ensure recovery of data in case of an instance failure.
To protect the redo log files against disk failures, Oracle supports multiplexed redo logs.
In this case, you can maintain two or more copies of the redo log files on different disks.
The copies of the redo log files maintained on different disks are called mirrored log files.
Each member in a log file group is a mirrored log file of the same file size.
The control files are database files that store the information required to maintain and verify database integrity.
They store the database structure, such as the database name, the time stamp of the database creation, plus the names and locations of the data files and redo log files.
The information stored in the control files is used to identify the data files and the redo log files, which must be opened when you open the database.
If the structure of the database is changed, the Oracle server automatically modifies the control files to reflect the changes.
If there is a failure, the control files are also used during database recovery.
Each Oracle database requires at least one control file. However, mirrored copies of the control file can be stored on different disks.

Nondatabase Files Used By The Oracle Server

The Oracle server uses the nondatabase files for operations such as instance startup and user authentication.
In addition to the database files, the nondatabase files are key physical storage structures of Oracle.
The three types of nondatabase files are parameter files, password files, and archived redo log files.
The parameter file is used to define the characteristics of an Oracle instance.
When the Oracle server starts up an instance, it uses the parameter file to get the instance configuration.
The instance configuration is specified by the initialization parameters in the parameter file.
The parameter file specifies the name of the database, the amount of memory to allocate, the names of control files, and the other system parameters.
Another type of nondatabase file is the password file. The password file is used to authenticate or validate the privileged database users.
The archived redo log file is another type of nondatabase file. It stores offline copies of the redo log files, which are used during recovery from a media failure.
Oracle creates archived redo log files by archiving the online redo log files that are full.
The online redo log files can be reused after they are archived.
If the database is in the ARCHIVELOG mode, the redo log files must be archived before they are reused in a cycle.
If automatic archiving is enabled, the redo log files are archived by the ARCO process.
In ARCHIVELOG mode, the database can be completely recovered from both instance and media failures.

Stages in Processing a Query

With all components established in the world of processing Oracle queries, let's look now at how Oracle processes queries.
There are several stages for processing an Oracle select statement. The operations involved in executing both select statements and DML statements fall into a general pattern shown in the following figure.

The specific flow of operation in processing a select statement is as follows:

1. Search shared pool The RDBMS will first attempt to determine if a copy of this parsed SQL statement exists in the library cache.

2. Validate statement The RDBMS accomplishes this step by checking SQL statement syntax.

3. Validate data sources The RDBMS ensures that all columns and tables referenced in this statement exist.

4. Acquire locks The RDBMS acquires parse locks on objects referenced in this statement so that their definitions don't change while the statement is parsed.

5. Check privileges The RDBMS ensures that the user attempting to execute this SQL statement has enough privileges in the database to do so.

6. Parse statement The RDBMS creates a parse tree, or execution plan, for the statement, and places it in the library cache, based on what Oracle believes is the optimal method for executing the SQL statement. This is a list of operations the RDBMS uses to obtain data. If a parse tree already exists for this statement, the RDBMS can omit this step.

7. Execute statement The RDBMS performs all processing to execute the select statement. At this point, the server process will retrieve data from disk into the buffer cache.

8. Fetch values from cursor Once the select statement has been executed, all data returned from Oracle is stored in the cursor. That data is then placed into bind variables, row by row, and returned to the user process.

When complete, both the statement execution plan and the data in blocks retrieved from disk stick around in the library cache and buffer cache, respectively, for a variable length of time, just in case that user or another one wants to execute the same select statement.
In multiuser application environments, a performance gain is achieved every time user processes execute the same select statement because the RDBMS spends less time parsing the statement, and the server process spends less time retrieving data.

Note: Depending on the amount of memory used for transferring the rows, one or more fetches may be required to transfer the results of a query to the user process.

Shared Pool Memory

The parse stage of query processing uses the shared pool memory region, which is a part of the SGA.
As a database administrator, you are responsible for setting the size of the shared pool memory areas.
The shared memory components of the shared pool are the library cache and the data dictionary cache.
The library cache stores information about the most recently used SQL statements.
It stores the execution plan, parse tree, and text of the statements.
The execution plan contains the steps determined by the optimizer to run the statement.
The information associated with a particular SQL statement, such as the parse tree and the execution plan, is stored in the library cache.
If the same statement is sent again by any user process before the execution plan is aged out, the parse tree and the execution plan in the library cache can be reused.
In addition to the parse tree, the library cache stores control structures, such as locks and library cache handles.
The data dictionary cache contains data dictionary information about the objects in the database, the structure of the database, and the column definitions.
The data dictionary cache also stores the valid user names, their passwords, and privileges for the database.
The data dictionary cache is also known as the row cache.

Database Buffer Cache

When a query is processed, the server process checks the database buffer cache, which is part of the SGA, for the data blocks required for processing.
You set the size of the database buffer cache.
The database buffer cache holds blocks of data read from the data files.
If the server process is unable to find the required block of data in the database buffer cache, it reads it from the data file and places a copy in the database buffer cache.
The database buffer cache is also called the buffer cache. It is used to store the most recently and frequently used data blocks.
The database buffer cache is a set of database buffers. The size of each buffer in the database buffer cache is equal to the size of a data block in the database.
The size of the database buffer cache is equal to the product of the number of buffers in the database buffer cache and the size of a data block in the database.
The size of a data block is specified by the DB_BLOCK_SIZE parameter.
The number of buffers in the database buffer cache is equal to the value of the DB_BLOCK_BUFFERS parameter.
The database buffer cache contains both modified and unmodified data blocks.
The Oracle server uses a Least Recently Used (LRU) algorithm to age out buffers that have not been accessed recently to make space for new blocks to be accommodated in the database buffer cache.

Program Global Area (PGA)

When a server process is started, the Oracle database allocates a Program Global Area (PGA).
The PGA is a memory buffer that contains the data and control information for a server process.

The PGA is also called the Process Global Area.
It is allocated when a server process starts and it is deallocated when the server process terminates.
The PGA is a writable and nonshareable memory region.
The contents of the PGA depend on the configuration of the Oracle server.
In a dedicated server configuration, the PGA has a sort area, session information, cursor state, and stack space.
The sort area is used for storing intermediate results during sorting, which may be necessary while rows are processed.
The session information specifies the user privileges for the current session.
The cursor state indicates the stage in the processing of various cursors that are currently being used by the session.
A cursor is a handle or pointer for the memory associated with a specific SQL statement.
Oracle uses work areas to execute the SQL statements and store the processing information.
A cursor enables Oracle to name a work area and access its stored information.
The PGA also contains the stack space. It stores the session variables and arrays.

Execute Phase Of A DML Statement

Data Manipulation Language (DML) statements, such as UPDATE, DELETE, and INSERT, are used to manipulate data.
By understanding how DML statements are processed, you can create the data buffer cache and rollback segments appropriately.
At this point, meet yet another "behind-the-scenes" player in Oracle transaction processing—the rollback segment.
The rollback segment is a database object in Oracle that stores old versions of data being changed by DML statements issued by the user process.
Rollback segments only store the old values, not the new values— the new values are stored in the object itself.
With this in mind, return to the processing of DML statements. There are several differences between how Oracle processes select statements and how it processes DML statements such as update, insert, and delete.
Though the operations involved in executing DML statements fall into the same general pattern as those for select statements.
The specific flow of operation in processing DML statements is as follows:

1. Parse statement

The RDBMS creates a parse free, or execution plan, for the statement and places it in the library cache. This is a list of operations the RDBMS uses to process the data change. If a parse tree already exists for this statement, the RDBMS can omit this step.

2. Execute statement

The RDBMS performs all processing to execute the DML statement. For update or delete statements, the server process will retrieve the data from disk into the buffer cache, implicitly acquire a lock on the data to be changed, and then make the specified data change in the buffer cache.
A lock is an Oracle internal resource that one user process acquires before updating or deleting existing data to prevent other users from doing the same thing.
For insert statements, the server process will retrieve a block from disk that has enough space available to house the new row of data, and will place that new row into the block. Also, part of executing the DML update or delete statement is writing the old and new versions of the data to the rollback segment acquired for that transaction.
A lock must be acquired on the rollback segment to write undo information to a rollback segment as well, in case the user decides to rollback his or her transactions.

3.Generate redo information

Recall from the prior lesson that the redo log buffer stores redo or data-change information produced as the result of DML operations running in user sessions.
After issuing DML statements, the user process must write a redo entry to the redo log buffer.
In this way, Oracle can recover a data change if damage is later done to the disk files containing Oracle data.

Rollback Segments

When you issue a DML statement to change data, the server process first saves the old values in a rollback segment.
Rollback segments are used to undo changes in case of a rollback transaction, and to ensure that other transactions do not see any uncommitted changes.
Database users cannot access or read rollback segments explicitly. However, the Oracle server can access them.
Each database contains one or more rollback segments. The Oracle server assigns a transaction automatically to the next available rollback segment.
The transaction assignment to a rollback segment occurs when you issue the first DML statement in the transaction.
The Oracle server never assigns read-only transactions to a rollback segment. However, transactions that contain only queries can read any rollback segment.
This rollback segment may contain old values that provide read-consistent results.
The number of transactions that one rollback segment can handle is set when the rollback segment is created. The maximum possible size is operating system-specific.
If required, rollback segments are used to undo the changes made to the data.
The information stored in the rollback segments is also used for read consistency. This ensures that the other transactions do not access the uncommitted changes made by a DML statement.
In addition, rollback segments are used to recover the database to a consistent state in case of failures.
Rollback segments reside in the data files, just like tables and indexes. Parts of these segments are read into the database buffer cache when required.

Redo Log Buffer

The redo log buffer records all the changes made to the data during DML statement processing.
As the database administrator, you are responsible for setting the size of the redo log buffer.
The redo log buffer is a part of the SGA. Its size in bytes is defined by the LOG_BUFFER parameter.
The redo log buffers store redo entries. A redo entry records which block changed, the location of the change, and the new value.

Note: The redo entry does not identify the type of block where the change is made. This means that it does not distinguish between a data block, an index block, and a rollback segment block.

The redo log buffer is used sequentially, and the changes made by one transaction are interleaved with changes made by other transactions.
The redo log buffer is a circular buffer, which is released for reuse after it is filled up. However, it is released only after all the existing redo entries are recorded in the online redo log files.
The size of the redo log buffer is fixed and it is created at the time of instance startup.

DBWn Background Process

The DBWn background process writes the data from the database buffer cache to the data files on the disk.
However, the DBWn process writes the data blocks to the data files only when certain events occur.
In this topic, you learn about these events.
One of the events that causes the DBWn process to write is when the number of dirty buffers reaches a threshold value.
A dirty buffer is a modified buffer in the database buffer cache that no longer matches the corresponding block on the disk.
As the number of dirty buffers increases, the number of free buffers decreases.
One of the tasks of the DBWn process is to keep enough free buffers in the database buffer cache for accommodating the data blocks read from the data files.
Writing dirty buffers to disk changes their status back to free buffers.
Another event that causes DBWn to write is when a server process is unable to locate a free reusable buffer after scanning a specified number of buffers.
When a timeout occurs, DBWn writes to the data files. A timeout occurs if DBWn is inactive for a specified amount of time, such as three seconds.
When a timeout occurs, DBWn searches a specified number of buffers on the LRU list and writes dirty buffers to the disk.
If the database is idle, DBWn eventually writes the entire buffer cache to the disk.
The DBWn process also writes when a DBWn checkpoint occurs. A DBWn checkpoint can be triggered by various events, such as the shutdown of the database by a DBA.
A DBWn checkpoint is a means of synchronizing the database buffer cache with the data file.

LGWR Background Process

The LGWR background process writes redo entries from the redo log buffer to the redo log files.
The LGWR process writes only when any one of four events occurs.
In this topic, you learn about these events.
One of the events that causes LGWR to write is when the redo log buffer is one-third full.
When a timeout occurs, LGWR writes to the redo log file. The default time for a timeout to occur is three seconds.
LGWR writes the redo log buffers to redo log files before the DBWn writes any modified blocks from the database buffer cache to the data files.
When a user process commits a transaction, LGWR writes the redo log buffer to the redo log file.
Often, LGWR writes redo log entries before a transaction is committed. However, these entries become permanent only if the transaction is later committed.

System Change Number (SCN)

The Oracle database assigns a System Change Number (SCN) to every transaction that is committed.

The SCN is used to identify the transaction. In this topic, you learn about the uses of the SCN.
When a transaction is committed, this is recorded in the rollback segment used by that transaction.
The SCN is assigned to the transaction and recorded in the rollback segment. The SCN is also recorded in the redo log file.
SCNs assigned to the transactions are unique for every transaction in the database.
An SCN is used as a time stamp by the Oracle database to synchronize data. It is a logical time stamp used to arrange the events in an order within a single instance and across all instances.
An SCN provides read consistency when data is retrieved from the data files.
Using an SCN, the Oracle database performs consistency checks without depending on the date and time of the operating system.
An SCN is used during the recovery of the database.
The Oracle database uses different schemes to generate SCNs, such as the lock SCN scheme and the Lamport SCN scheme.
You can examine the ALERT log file after an instance is started to determine which SCN generation scheme is selected.

Commit Processing: Steps

A transaction must be permanently recorded after it is processed. This is achieved by issuing a COMMIT statement.
A sequence of steps occurs when a COMMIT statement is issued. This topic explains the steps of the commit process.
First, a server process places a commit record, along with its SCN, in the redo log buffer.
Next, LGWR performs a contiguous write of all the redo log buffer entries, up to and including the commit record, to the redo log files.
Then, you can be assured that the changes will not be lost even in case of failures.
In the next step, the server process records the information that indicates the completion of the transaction, allowing the resource locks held on tables and rows to be released.
Finally, the server process informs the user process that the commit process is complete.
This completes the sequence of steps in the commit process. The commit process executed using these steps is referred to as the fast commit. Notice that only the redo log buffer is written to the disk.
Flushing of the dirty buffers to the data file is performed independently by DBWn and can take place either before or after the commit.
Only one single write is required per commit process. If multiple users simultaneously issue commits, Oracle piggybacks the commits into one write. This helps in achieving less than one I/O per commit on a busy system.

Fast Commit Mechanism

The Oracle database uses a fast commit mechanism, which ensures the recovery of changes in case of failures. In this topic, you learn about the advantages of the fast commit.
Another advantage of the fast commit is that it writes only the information needed to record the changes to the log files.
This saves time because writing to the data file requires whole blocks of data to be written instead of just the changed portion.
By using the fast commit, the database piggybacks commit requests. This enables multiple transactions to commit at the same time into a single write.
The fast commit requires only one synchronous write per transaction. This happens unless the redo log buffer is especially full. In that case, additional I/O might be necessary.
The size of the transaction does not affect the amount of time needed for an actual COMMIT operation.

Summary

· This lecture introduced you to Oracle database administration.

· It covered several topics, including an overview of the Oracle architecture, using SQL*Plus and OEM as administrative tools, managing the Oracle instance, starting and stopping the instance, and creating an Oracle database.

The overview of the various components of the Oracle database gave an idea of the background processes, memory structures, and disk resources that comprise the Oracle instance, and how they act together to allow users to access information.
Several memory structures exist on the Oracle database to improve performance on various areas of the database.
They include the System Global Area (SGA) and the Program Global Area (PGA).
The SGA, in turn, consists of several components: the buffer cache, the shared pool, and the redo log buffer.
Behind the scenes, there are several memory processes that move data between disk and memory or handle activities on Oracle's behalf.
The core background processes covered in this chapter were the database writer (DBWR or DBW0) for writing blocks to disk, the log writer (LGWR) for writing redo entries to disk, and the server for reading data from disk into the buffer cache for users.
You were introduced to the Oracle relational database management system (RDBMS), and learned how select and DML statements are processed.
The RDBMS translates SQL code, which defines results, into a step-by-step procedure for Oracle to use in obtaining that data.
You also learned about how users are connected to server processes, the differences and trade-offs involved in the dedicated server and the MTS architecture, and how listener processes and dispatchers are used to route user processes to servers.
Finally, you learned how commit statements are processed and that a commit being issued does not automatically make Oracle run right out and copy changed data back to disk.

Review Questions

1. What is the name of the main memory structure in Oracle, and what are its components? What is the function of the PGA?

2. Where is user session information stored in memory on the Oracle instance? How is its location determined?

3. How do user processes get connected to a server when dedicated servers are used?

4. How do user processes get connected to a server when MTS is used?

5. What are the performance implications of using shared vs. dedicated servers?

Practice Test & Answers

1. The initialization parameter used for defining the name of your Oracle database is: _______________________

2. In order to increase the size of a datafile, these keywords can be used so that Oracle can automatically add more space when necessary: ________________________

3. Once the database is created, the frequency with which you can alter the database's block size: _________________________

4. Of the database shutdown options, this one requires instance recovery the next time the database is started: _______________________

5. The user is trying to execute a select statement. Which of the following background processes will obtain data from disk for the user?

A. DBW0 B. LGWR C. SERVER D. USER E. DISPATCHER

6. Which component of the SGA stores parsed SQL statements used for process sharing?

A. Buffer cache B. Private SQL area C. Redo log buffer D. Library cache E. Row cache

7. Which of the following choices does not identify an aspect of shared server processing architecture?

A. Each user gets their own server process for data retrieval.

B. A dispatcher process is involved.

C. A listener process is involved.

D. The server process sits idle infrequently.

8. The initsid.ora parameter that indicates the size of each buffer in the buffer cache is the:

A. DB_BLOCK_BUFFERS B. BUFFER_SIZE C. DB_BLOCK_SIZE

D. ROLLBACK_SEGMENTS

Answers

1. DB_NAME 2. AUTOEXTEND ON 3. NEVER 4. ABORT 5.c

6.d 7.a 8. c

Required Readings:

Textbook: Chapter 1 “Oracle Overview and Architecture" of

“OCP: Oracle8i DBA Architecture & Administration and Backup & Study Guide”, Doug Stuns and Biju Thomas, Sybex.

ISBN 0-7821-2683-9.

Textbook: Chapter 2 “Oracle Overview and Architecture" of

“OCA/OCP Oracle9i DBA Fundamentals I Study Guide”, Biju Thomas and Bob Bryla, Sybex.

ISBN 0-7821-4063-7.