3.7 Oracle at Work
To help you truly understand how all the disparate pieces of the
Oracle database work together, this section walks through an example
of the steps taken by the Oracle database to respond to a user
request. This example looks at a user who is adding new information
to the database (in other words, executing a transaction).
3.7.1 Oracle and Transactions
A transaction
is a work request from a client to insert, update, or delete data.
The statements that change data are a subset of the
SQL
language called Data Manipulation Language (DML). Transactions must
be handled in a way that guarantees their integrity. Although
Chapter 7 delves into transactions more deeply, we must visit a few basic
concepts relating to transactions now in order to understand the
example in this section:
- Transactions are logical and complete
In database terms, a transaction is a logical unit of work composed
of one or more data changes. A transaction consists of one or more
INSERT, UPDATE, and/or DELETE statements affecting data in multiple
tables. The entire set of changes must succeed or fail as a complete
unit of work. A transaction starts with the first DML statement and
ends with either a commit or a rollback.
- Commit or rollback
Once a user enters the data for his transaction, he can either
commit
the transaction to make the changes permanent or
roll
back the transaction to undo the changes.
- System Change Number (SCN)
A key factor in preserving database
integrity is an awareness of which transaction came first. For
example, if Oracle is to prevent a later transaction from unwittingly
overwriting an earlier transaction's changes, it
must know which transaction began first. The mechanism Oracle uses is
the System Change Number, a logical timestamp used to track the order
in which events occurred. Oracle also uses the SCN to implement
multiversion read consistency, which is described in detail in
Chapter 7.
- Rollback segments
Rollback
segments are structures in the Oracle database used to store
"undo" information for
transactions, in case of rollback. This undo information restores
database blocks to the state they were in before the transaction in
question started. When a transaction starts changing some data in a
block, it first writes the old image of the data to a rollback
segment. The information stored in a rollback segment is used for two
main purposes: to provide the information necessary to roll back a
transaction and to support multiversion read consistency.
A rollback segment is not the same as a redo log. The redo log is
used to log all transactions to the database and to recover the
database in the event of a system failure, while the rollback segment
provides rollback for transactions and read consistency.
Blocks of rollback segments are cached in the SGA just like blocks of
tables and indexes. If rollback segment blocks are unused for a
period of time, they may be aged out of the cache and written to the
disk.
Oracle9i
introduced automatic management of rollback segments. In previous
versions of the Oracle database, DBAs had to explicitly create and
manage rollback segments. In Oracle9i, you had
the option of specifying automatic management of all rollback
segments through the use of an UNDO tablespace. With automatic undo
management, you can also specify the length of time that you want to
keep undo information; this feature is very helpful if you plan on
using flashback queries, discussed in the following sidebar.
Oracle Database 10g
adds an undo management retention time advisor.
- Fast commits
Because redo
logs are written whenever a user commits an Oracle transaction, they
can be used to speed up database operations. When a user commits a
transaction, Oracle can do one of two things to get the changes into
the database on the disk:
Write all the database blocks the transaction changed to their
respective datafiles. Write only the redo information, which typically
involves much less I/O than writing the database blocks. This
recording of the changes can be replayed to reproduce all the
transaction's changes later, if they are needed due
to a failure.
In Oracle9i, rollback segments were also used to
implement a feature called Flashback Query.
Remember that rollback segments are used to provide a consistent
image of the data in your Oracle database at a previous point in
time. With Flashback Query, you can direct Oracle to
return the results for a SQL query at a specific point in time. For
instance, you could ask for a set of results from the database as of
two hours ago.
If you use this feature, you will have to size your rollback segments
to hold enough information to perform the Flashback Query. In
addition, you can query only for a point in the past. Despite a few
limitations of the feature, there are scenarios in which you might be
able to use a Flashback Query effectively, such as going back to a
point in time before a user made an error that resulted in a loss of
data.
Oracle Database 10g has greatly expanded its
flashback capabilities to include:
- FLASHBACK DATABASE
|
Returns (rolls back) the entire database to a particular point in
time. Can be used instead of point-in-time recovery in some
situations.
FLASHBACK TABLE
Returns a specific table to a specific point in time.
FLASHBACK TRANSACTION
Returns all the changes made by one specific transaction.
FLASHBACK DROP
Rolls back a drop operation. When an object is dropped, it is placed
in a Recycle Bin, so a user can simply un-drop the object to restore
it.
SELECT Flashback clauses
New clauses in the SELECT statement return all the versions of rows
(i.e., show changes to the rows) in a particular query over a span of
time.
|
To provide maximum performance without risking transactional
integrity, Oracle writes out only the redo information. When a user
commits a transaction, Oracle guarantees that the redo for those
changes writes to the redo logs on disk. The actual changed database
blocks will be written out to the datafiles later. If a failure
occurs before the changed blocks are flushed from the cache to the
datafiles, the redo logs will reproduce the changes in their
entirety. Because the slowest part of a computer system is the
physical disk, Oracle's fast-commit approach
minimizes the cost of committing a transaction and provides maximum
risk-free performance.
3.7.2 A Transaction, Step by Step
This simple example illustrates the
complete process of a transaction. The example uses the EMP table of
employee data, which is part of the traditional test schema shipped
with Oracle databases. In this example, an HR clerk wants to update
the name of an employee. The clerk retrieves the
employee's data from the database, updates the name,
and commits the transaction.
This example assumes that only one user is trying to update the
information for a row in the database. Because of this assumption, it
won't include the steps normally taken by Oracle to
protect the transaction from changes by other users, which are
detailed in Chapter 7.
Assume that the HR clerk already has the employee record on-screen
and so the database block containing the row for that employee is
already in the database buffer cache. The steps from this point would
be the following:
The user modifies the employee name on-screen and the client
application sends a SQL UPDATE statement over the network to the
server process.
The server process looks for an identical statement in the shared SQL
area of the shared pool. If it finds one, it reuses it. Otherwise, it
checks the statement for syntax and evaluates it to determine the
best way to execute it. This processing of the
SQL statement is called
parsing and optimizing. (The optimizer is
described in more detail in Chapter 4.) Once the processing is done,
the statement is cached in the shared SQL area.
The server process copies the old image of the employee data about to
be changed to a rollback segment. The old version of the employee
data is stored in the rollback segment in case the HR clerk cancels
or rolls back the transaction. Once the server process has written
the old employee data to a rollback segment, the server process
modifies the database block to change the employee name. The database
block is stored in the database cache at this time.
The server process records the changes to the rollback segment and
the database block in the redo log buffer in the SGA. The rollback
segment changes are part of the redo. This may seem a little odd, but
remember that redo is generated for all changes
resulting from the transaction. The contents of the rollback segment
have changed because the old employee data was written to the
rollback segment for undo purposes. This change to the contents of
the rollback segment is part of the transaction and therefore part of
the redo for that transaction.
The HR clerk commits the transaction.
The Log Writer (LGWR) process writes the redo information for the
entire transaction from the redo log buffer to the current redo log
file on disk. When the operating system confirms that the write to
the redo log file has successfully completed, the transaction is
considered committed.
The server process sends a message to the client confirming the
commit.
The user could have canceled or rolled back the transaction instead
of committing it, in which case the server process would have used
the old image of the employee data in the rollback segment to undo
the change to the database block.
Figure 3-8 shows the steps described here. Network traffic appears as
dotted lines.
|
No comments:
Post a Comment