418
Chapter 8

Managing Consistency and Concurrency
In the following example, you lock the EMPLOYEES and DEPARTMENTS tables at the highest
possible level, EXCLUSIVE:
SQL> lock table hr.employees, hr.departments
2 in exclusive mode;
Table(s) Locked.
Until the transaction with the LOCK statement either commits or rolls back, only queries are
allowed on the EMPLOYEES or DEPARTMENTS tables.
In the sections that follow, we will review the lock modes, as well as show you how to
avoid the lock enqueue process and terminate the command if the requested resource is
already locked.
Lock Modes
Lock modes provide a way for you to specify how much and what kinds of access other users
have on tables that you are using in DML commands. In Table 8.2, you can see the types of
locks that can be obtained at the table level.
Manual lock requests wait in the same queue as implicit locks and are satisfied in a first in,
first out (FIFO) manner as each request releases the lock with either an implicit or explicit
COMMIT or ROLLBACK.
TABLE 8.2 Table Lock Modes
Table Lock Mode Description
ROW SHARE Permits concurrent access to the locked table, but prohibits other
users from locking the entire table for exclusive access.
ROW EXCLUSIVE Same as ROW SHARE, but also prohibits locking in SHARE mode. This type
of lock is obtained automatically with standard DML commands such
as UPDATE, INSERT, or DELETE.
SHARE Permits concurrent queries but prohibits updates to the table; this
mode is required to create an index on a table and is automatically
obtained when using the CREATE INDEX statement.

lowed if the requested resource is not yet available. NOWAIT can also be used in the SELECT … FOR
UPDATE statement.
Detecting and Resolving Lock Conflicts
Although locks are a common and sometimes unavoidable occurrence in many databases, they
are usually resolved by waiting in the queue. In some cases, you may need to resolve the lock
problem manually (for example, if a user makes an update at 4:59
P.M. and does not perform
a COMMIT before leaving for the day).
In the next few sections, we will describe in more detail some of the reasons that lock con-
flicts occur and how to detect lock conflicts and discuss a more specific and serious type of lock
conflict: a deadlock.
Understanding Lock Conflicts
In addition to the proverbial user who makes a change at 4:59 P.M. and forgets to perform a COMMIT
before leaving for the day, other more typical lock conflicts are caused by long-running transactions
that perform hundreds, thousands, or even hundreds of thousands of DML commands in the
4367.book Page 419 Monday, October 4, 2004 2:19 PM
420
Chapter 8

Managing Consistency and Concurrency
overnight batch run but are not finished updating the tables when the normal business day starts.
The uncommitted transactions from the overnight batch jobs may lock tables that need to be
updated by clerical staff during the business day, causing a lock conflict.
Another typical cause of lock conflicts is using unnecessarily high locking levels. In the side-
bar “Packaged Applications and Locking” earlier in this chapter, we described a third-party
application that routinely locked resources at the table level instead of at the row level to be
compatible with every SQL-based database on the market. Developers may unnecessarily code
updates to tables with higher locking levels than required by Oracle 10g.
Detecting Lock Conflicts
Detecting locks in Oracle 10g using the EM Database Control makes your job easy; no need to

Cause A privileged user has killed your session and you are no longer logged on to the database.
Action Log in again if you want to continue working.
As it turns out, the users were not always performing a COMMIT before they left for lunch; the other
users who were trying to finish their work could not complete their updates because the rows of
the tables were still locked in a transaction that had not yet been committed. They called the DBA,
who identified the locking sessions and canceled them, generating the ORA-0002 message for
the canceled session.
Oracle error messages are not always clear, and the detailed description of the error message
doesn’t always help, but at least it provides a starting point for investigating a problem. Make sure
that the users can access the Oracle error messages, either via the Internet at www.oracle.com or via
an internal shared directory containing all the Oracle documentation for the installation options at
your site.
4367.book Page 421 Monday, October 4, 2004 2:19 PM
422
Chapter 8

Managing Consistency and Concurrency
A more serious type of lock conflict is a deadlock. A deadlock is a special type of lock con-
flict in which two or more users are waiting for a resource locked by the other users. As a
result, neither transaction can complete without some kind of intervention: the session that
first detects a deadlock rolls back the statement waiting on the resource with the error mes-
sage ORA-00060: Deadlock detected while waiting for resource.
In Table 8.3, two sessions are attempting to update a row locked by the other session.
After the error message is issued at 11:45, the second UPDATE for Session 1 does not succeed;
however, the second UPDATE for Session 2 completes, and the user in Session 2 can now submit
another DML statement or issue a COMMIT or ROLLBACK. The user in Session 1 will have to re-
issue the second UPDATE.
Summary
In this chapter, we presented the undo tablespace and its importance for the two types of database
users: those who want to query a table and receive consistent results, and those who want to make

In the second part of the chapter, we showed you how to monitor resource locks within a
transaction, both at the row level and the table level. Although Oracle usually manages locks at
the minimum level to ensure that two sessions do not try to simultaneously update the same row
in a table, you can explicitly lock a table at a number of levels. In addition, you can lock a subset
of rows in a table to prevent updates or locks from other transactions with the FOR UPDATE
clause in the SELECT statement.
Finally, we presented some reasons that lock conflicts occur and how to resolve them; a spe-
cial kind of lock conflict, called a deadlock, occurs when two users are waiting on a resource
locked by the other user. Deadlocks, unlike other types of lock conflicts, are resolved quickly
and automatically by Oracle long before any manual lock resolution is attempted.
Exam Essentials
Know the purpose of the Undo Advisor. Optimize the UNDO_RETENTION parameter as well
as the size of the undo tablespace by using Undo Advisor. Use the graph on the Undo Advisor
screen to perform what-if analyses given the undo retention requirements.
Be able to monitor locking and resolve lock conflicts. Identify the reasons for database lock
conflicts, and explain how to resolve them. Show an example of a more serious type of lock con-
flict, a deadlock.
List the features supported by undo data in an undo tablespace. Enumerate the four primary
uses for undo data: rollback, read consistency, database recovery, and flashback operations.
Show how the rollback requirements for users that perform long transactions can interfere with
read consistency required for query users. Be able to identify and use the method to preserve
expired undo at the expense of transactions.
Summarize the steps for monitoring, configuring, and administering the undo tablespace. Set the
initialization parameters required to use an undo tablespace. Be able to review the status of the undo
tablespace using EM Database Control, and use the Undo Advisor to resize the undo tablespace
when conditions warrant it. Alter the initialization parameter UNDO_RETENTION to configure how
long undo information needs to be retained for long-running queries.
List the types of lock modes available when locking a table. Identify the locks available,
from least restrictive to most restrictive. Be able to request a lock with either a LOCK or SELECT
statement and return immediately if the lock is not available.

A. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=MEMORY;
B. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
C. ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=MEMORY;
D. ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE;
E. ALTER SYSTEM SET UNDO_TABLESPACE=RBS1 SCOPE=BOTH;
4. Guaranteed undo retention can be specified for which of the following objects?
A. A tablespace
B. A table
C. The database
D. A transaction
E. The instance
4367.book Page 424 Monday, October 4, 2004 2:19 PM
Review Questions
425
5. Which dynamic performance view can help you adjust the size of an undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
6. Which of the following lock modes permits concurrent queries on a table but prohibits updates
to the locked table?
A. ROW SHARE
B. ROW EXCLUSIVE
C. EXCLUSIVE
D. SHARE ROW EXCLUSIVE
E. SHARE
7. The highest level at which a user can request a lock is the ________ level.
A. Schema
B. Table
C. Row

formance view V$TRANSACTION to which other dynamic performance view?
A. V$ROLLSTAT
B. V$ROLLNAME
C. V$UNDOSTAT
D. V$TRANSACTION_ENQUEUE
10. Select the statement that is not true regarding undo tablespaces.
A. Undo tablespaces will not be created if they are not specified in the CREATE DATABASE command.
B. Two undo tablespaces can be active if a new undo tablespace was specified and the old one
contains pending transactions.
C. You can switch from one undo tablespace to another while the database is online.
D. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.
11. To resolve a lock conflict, which of the following methods can you use? (Choose two.)
A. Oracle automatically resolves the lock after a short but predefined time period by killing the
session that is holding the lock.
B. The DBA can kill the session holding the lock.
C. The user can either roll back or commit the transaction that is holding the lock.
D. Oracle automatically resolves the lock after a short but predefined period by killing the ses-
sion that is requesting the lock.
12. If all extents in an undo segment fill up, which of the following occurs next? (Choose all that apply.)
A. A new extent is allocated in the undo segment if all existing extents still contain active trans-
action data.
B. Other transactions using the segment are moved to another existing segment with enough
free space.
C. A new undo segment is created, and the transaction that filled up the undo segment is moved
in its entirety to another undo segment.
D. The first extent in the segment is reused if the undo data in the first extent is not needed.
E. The transaction that filled up the undo segment spills over to another undo segment.
13. Which of the following commands returns control to the user immediately if a table is already
locked by another user?
A. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE WAIT DEFERRED;

where state=’IA’ and
county=’JOHNSON’;
update customer set region=’H’
where state=’IA’ and
county=’JOHNSON’;
10:01
10:05 update customer set mgr=201
where state=’WI’ and
county=’GRANT’;
4367.book Page 427 Monday, October 4, 2004 2:19 PM
428
Chapter 8

Managing Consistency and Concurrency
17. Undo data in an undo tablespace is not used for which of the following purposes?
A. Providing users with read-consistent queries
B. Rolling forward after an instance failure
C. Flashback queries
D. Recovering from a failed transaction
E. Restoring original data when a ROLLBACK is issued
18. Which dynamic performance view shows which transactions are assigned to which undo seg-
ment in the undo tablespace?
A. V$TRANSACTION
B. V$ROLLSTAT
C. V$SESSION
D. V$UNDOSTAT
19. The user SCOTT runs a query at 8:25
A.M. that receives an ORA-01555: Snapshot too old error
after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly
sized. At 10:15

4. A. Guaranteed undo retention can be set at the tablespace level by using the RETENTION
GUARANTEE clause with either the CREATE TABLESPACE or ALTER TABLESPACE command. Only
undo tablespaces can have this attribute.
5. A. When database activity is at its peak, the V$UNDOSTAT view, in conjunction with the value for
UNDO_RETENTION and DB_BLOCK_SIZE, can be used to calculate an optimal undo tablespace
size. Also, the Undo Advisor in the EM Database Control can provide the same optimal
tablespace size in a GUI environment.
6. E. SHARE mode permits concurrent queries but prohibits updates to the locked table. SHARE
mode is required to create an index on the table.
7. B. The highest level at which a user can request a lock is the table level; the only other lock level
available to a user is a row level lock. Users cannot lock at the block or schema level.
8. D. At 11:45, both sessions are waiting for the row locked by the other session. Within a short
but predetermined amount of time, Oracle rolls back the statement that detected the deadlock,
which could be either session and is not dependent on when each of the transactions started or
attempted to update rows locked by other users.
9. B. The column XIDUSN in the view V$TRANSACTION can be joined with the column USN in
V$ROLLNAME to retrieve the column NAME in V$ROLLNAME containing the rollback segment name.
10. A. If an undo tablespace is not explicitly created in the CREATE DATABASE command, Oracle
automatically creates one with the name SYS_UNDOTBS.
11. B, C. Locks are resolved at the user level by either committing or rolling back the transaction
holding the lock. Also, the DBA can kill the session holding the lock as a last resort.
12. A, D. If a transaction fills up an undo segment, either a new extent is allocated for the undo seg-
ment or other extents in the segment are reused if the undo data in those extents is no longer
needed by other transactions using the same undo segment. Transactions cannot cross segment
boundaries in an undo tablespace nor can they move to another segment.
13. B. Regardless of the type of lock requested, NOWAIT is required if you want the command with
the lock request to terminate immediately if a lock is already held on the table.
4367.book Page 429 Monday, October 4, 2004 2:19 PM
430
Chapter 8

G

:
ADMINISTRATION I EXAM OBJECTIVES
COVERED IN THIS CHAPTER:


Performance Monitoring


Troubleshoot invalid and unusable objects.


Gather optimizer statistics.


View performance metrics.


React to performance issues.


Proactive Maintenance


Set warning and critical alert thresholds.


Collect and use baseline metrics.


g

(Oracle 10

g

), however, several new features allow you to easily col-
lect and analyze database performance statistics and proactively respond to problems when they
are detected in the database. These new features include the Automatic Workload Repository
(AWR), Automated Database Diagnostic Monitoring (ADDM), and the Oracle 10

g

Tuning and
Diagnostic Advisors.
In this chapter, we will look at these features in detail.

Proactive Database Maintenance

You can monitor your systems for management and performance problems in essentially two
ways: reactively and proactively.

Reactive monitoring

involves monitoring a database environment after a performance or
management issue has arisen. For example, you start gathering performance statistics using
third-party tools, Enterprise Manager, or home-grown scripts after users call to tell you that the
system is slow. Obviously, this type of monitoring leaves a lot to be desired, because a problem
has already arisen and the users of the system are already impacted. You can use the techniques
discussed in this chapter for reactive monitoring, but they are most effective when used to per-


433

directly from the System Global Area (SGA). The MMON process does most of the work by
waking up every 60 minutes and gathering statistical information from the data dictionary views,
dynamic performance views, and optimizer and then storing this information in the database.
The tables that store these statistics are called the

Automatic Workload Repository (AWR)

. These
tables are owned by the user

SYSMAN

and are stored in the

SYSAUX

tablespace.
To activate the AWR feature, you must set the PFILE/SPFILE parameter

STATISTICS_LEVELto the appropriate value. The values assigned to this parameter determine the depth of the sta-
tistics that the MMON process gathers. Table 9.1 shows the values that can be assigned to the

STATISTICS_LEVEL


30 days = 43,200 minutes.

You can also change the AWR collection interval, retention period, and collection depth
using the EM Database Control. Choose Administration 

Automatic Workload Repository Edit on the main screen to open the Edit Settings screen shown in Figure 9.1.

TABLE 9.1

Specifying Statistics Collection Levels

Collection Level Description

BASIC

Disables the AWR and most other diagnostic monitoring and advisory
activities. Few database statistics are gathered at each collection inter-
val when operating the instance in this mode.

TYPICAL

Activates the standard level of collection activity. This is the default
value for AWR and is appropriate for most environments.

ALL

Captures all the statistics gathered by the TYPICAL collection level, plus


Changing the AWR statistics collection level

4367.book Page 434 Monday, October 4, 2004 2:19 PM

Proactive Database Maintenance

435

Take care when specifying the AWR statistics collection interval. Gathering
snapshots too frequently requires additional space in the

SYSAUX

tablespace

and adds additional database overhead each time the statistics are collected.

Once AWR snapshots are taken and stored in the database, the Automatic Database Diag-
nostic feature uses the statistics as described in the next section.

Automatic Database Diagnostic Monitoring

Following each AWR statistics collection process, the

Automated Database Diagnostic Moni-
toring (ADDM)

feature automatically analyzes the gathered statistics and compares them to the
statistics gathered by the previous two AWR snapshots. By comparing the current statistics to

explain how you can use this utility to monitor and manage database storage, security, and per-
formance. We’ll begin by examining the EM Database Control tools that you can use to view
the results of ADDM analysis.

Baselines are discussed later in this chapter in the section “ADDM Alerts.”

4367.book Page 435 Monday, October 4, 2004 2:19 PM

436

Chapter 9


Proactive Database Maintenance and Performance Monitoring

Using EM Database Control to View ADDM Analysis

EM Database Control graphically displays the results of the ADDM analysis on several screens,
including:


The Performance Findings link under the Diagnostic Summary section of the EM Database
Control main screen


The Performance tab of the EM Database Control main screen


The ADDM screen located by clicking the Advisor Central link at the bottom of the EM
Database Control main screen


Stripe and mirror (also known as SAME) all datafiles across multiple disk drives.


Increase the number of physical disk drives.


Consider implementing Oracle’s Automatic Storage Management feature.

The SAME, or Stripe and Mirror Everything, methodology suggested in Fig-
ure 9.5 refers to a database file configuration strategy that is described in this
white paper on the Oracle Technology Network:

/>
deploy/availability/pdf/OOW2000_same_ppt.pdf

.

Figure 9.4 also shows that a large portion of our I/O problems are related to specific database
tables or indexes: “Individual database segments responsible for significant physical I/O were
found.” Clicking this link displays the detailed ADDM findings shown in Figure 9.6.
ADDM has essentially identified the

SALES_HISTORY

table as the source of excessive
I/O and recommends that you run the Segment Advisor utility against this table to generate
recommendations for improving its performance. The Segment Advisor is described later in
this section.


read of contiguous blocks from a datafile into the buffer cache—usually when a table is being
accessed using a full table scan or fast full index scan.
FIGURE 9.7 Sessions: Waiting And Working section of the Performance screen
4367.book Page 438 Monday, October 4, 2004 2:19 PM
Proactive Database Maintenance
439
FIGURE 9.8 Detailed user I/O information
For a complete listing and description of all database wait events, see Appen-
dix C: Oracle Wait Events of Oracle Database Reference 10g Release 1 (10.1),
Part No. B10755-01.
The Performance screen, shown in Figure 9.9, also contains a Performance Overview section
near the bottom that summarizes, in pie graphs, the top SQL and top session wait events iden-
tified by ADDM.
Clicking the links in the boxes next to either of these graphs displays details about that item.
For example, clicking the link for the SQL statement that experienced the most wait time
(35 percent on the graph) shows the output in Figure 9.10.
The output in Figure 9.10 shows that ADDM identified the SQL statement SELECT
count(*) FROM SALES_HISTORY as experiencing the most waits during processing. Click-
ing the link at the bottom of this same screen allows you to view the execution plan for this
statement.
FIGURE 9.9 The Performance Overview section of the Performance screen
4367.book Page 439 Monday, October 4, 2004 2:19 PM
440
Chapter 9

Proactive Database Maintenance and Performance Monitoring
FIGURE 9.10 Drilling down into the Top Waiting SQL
If the execution plan for this query shows that a full table scan of the 900,000+ row SALES_
HISTORY table is occurring, then you can see how it might experience I/O waits while retrieving
its rows. To view the tuning recommendations that ADDM has generated for this statement,

You can also manually perform an ADDM analysis without the use of EM Data-
base Control by using the addmrpt.sql script located in $ORACLE_HOME/rdbms/
admin on Unix systems and %ORACLE_HOME%\rdbms\admin on Windows systems.
See Chapter 6 of Oracle Database Performance Tuning Guide 10g Release 1
(10.1), Part Number B10752-01, for details on how to use this script.
The results of this analysis is displayed at the bottom of the ADDM screen that is displayed
when the analysis is complete. Figure 9.14 shows an example of the ADDM results for the time
interval we chose.
Notice that these findings are similar in nature to the ones displayed by the EM Findings
link shown earlier in Figure 9.4. The difference between the two ADDM results is that those
in Figure 9.4 are for the last three AWR collection periods as they existed when that page was
viewed, whereas the results in Figure 9.14 are for our manually specified time frame. By man-
ually specifying the ADDM analysis period in this way, you can “go back in time” and review
previous spikes in performance that may have been missed with real-time monitoring like that
shown on the EM Findings link.
Although using EM Database Control to view ADDM results is by far the simplest way to
review ADDM recommendations, you can also query the ADDM data dictionary views directly
as well. Some of these data dictionary views are discussed in the following section.
Using Data Dictionary Views to View ADDM Analysis
You can use more than 20 data dictionary views to examine the results of ADDM’s activities.
Four commonly used ADDM views that store the recommendation information we saw in the
EM Database Control pages are described in Table 9.2.
FIGURE 9.14 The results of a manually specified ADDM analysis
4367.book Page 442 Monday, October 4, 2004 2:19 PM