478
Chapter 9

Proactive Database Maintenance and Performance Monitoring
To begin the recompilation process, select the Reorganize option from the Actions drop-
down list, as shown in Figure 9.56.
Click Go to display the second screen of the Reorganize Objects Wizard, which is shown in
Figure 9.57.
Click the Set Attributes or Set Attributes By Type button to modify the index’s attributes—such
as the tablespace that it will be stored in or its storage parameters—before rebuilding. Click Next
to display the third screen of the Reorganize Objects Wizard, partially shown in Figure 9.58.
Using this screen, you can control how the index is rebuilt. For example, you can select the
rebuild method, either offline or online, that is best suited for your environment. Offline rebuilds
are faster but impact application users who need to access the index. Online rebuilds have minimal
impact on users but take longer to complete. You can also specify a “scratch” tablespace where
Oracle stores the intermediate results during the rebuild process. Redirecting this activity to
another tablespace helps minimize potential space issues in the index’s tablespace during the
rebuild. You can also specify whether to gather new optimizer statistics when the index build is
complete. Click Next on this screen to generate an impact report, as shown in Figure 9.59.
FIGURE 9.56 The Indexes screen showing the Reorganize action
FIGURE 9.57 The second Reorganize Objects screen
4367.book Page 478 Monday, October 4, 2004 2:19 PM
Performance Monitoring
479
FIGURE 9.58 The third Reorganize Objects screen
FIGURE 9.59 The Reorganize Objects: Impact Report screen
The output indicates that there is adequate space in the EXAMPLE tablespace for the
unusable JOBS_ID_PK index. Clicking Next displays the job scheduling screen shown in
Figure 9.60.
Like the earlier job-scheduling example in this chapter, you can use this screen to assign a job
description and to specify the start time for the job. Clicking Next submits the job and rebuilds

Manual Collection of Statistics
You can also configure automatic statistics collection for manually created databases using man-
ual techniques. Collecting manual statistics is also useful for tables and indexes whose storage
4367.book Page 480 Monday, October 4, 2004 2:19 PM
Performance Monitoring
481
characteristics change frequently or that need to be analyzed outside the normal analysis window
of 10:00
P.M. and 6:00 A.M. You can collect manual statistics through EM Database Control or
using the built-in DBMS_STATS PL/SQL package.
Manually Gathering Statistics Using EM
You can use the EM Gather Statistics Wizard to manually collect statistics for individual seg-
ments, schemas, or the database as a whole. To start the wizard, click the Maintenance link on
the EM Database Control screen. This wizard walks you through five steps, beginning with the
Introduction screen.
Click Next on the Introduction screen to open Step 2 in the wizard, and select the method
to use when gathering the statistics shown in Figure 9.61.
As you can see, three primary statistics options are available: Compute, Estimate, and Delete.
The Compute option examines the entire table or index when determining the statistics. This
option is the most accurate, but also the most costly in terms of time and resources if used on
large tables and indexes. The Estimate option takes a representative sample of the rows in the
table and then stores those statistics in the data dictionary. The default sample size is 10 percent
of the total table or index rows. You can also manually specify your own sample size if desired.
You can also specify the sample method, telling EM Database Control to sample based on a per-
centage of the overall rows, or blocks, in the table or index. The Delete option removes statistics
for a table or index from the data dictionary.
If you specify a sample size of 50 percent or more, the table or index is analyzed
using the Compute method.
After choosing a collection and sampling method, click Next to display the Object Selection
screen, as shown in Figure 9.62.

screen where its output can be inspected for job success or failure and any associated
runtime messages.
4367.book Page 483 Monday, October 4, 2004 2:19 PM
484
Chapter 9

Proactive Database Maintenance and Performance Monitoring
FIGURE 9.66 The Review screen of the Gather Statistics Wizard
FIGURE 9.67 The Scheduler Jobs summary screen
Manually Gathering Statistics Using DBMS_STATS
The output in Figure 9.66 shows that the EM Gather Statistics Wizard uses the DBMS_STATS PL/SQL
package when it gathers statistics. You can also call the DBMS_STATS PL/SQL package directly from
a SQL*Plus session. Some of the options for the DBMS_STATS package include the following:

Back up old statistics before new statistics are gathered. This feature allows you to restore some
or all of the original statistics if the CBO performs poorly after updated statistics are gathered.

Gather table statistics much faster by performing the analysis in parallel.

Automatically gather statistics on highly volatile tables and bypass gathering statistics on
static tables.
The following example shows how the DBMS_STATS packages can be used to gather statistics
on the PRODUCT_HISTORY table in SH’s schema:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS (‘SH’,’PRODUCT_HISTORY’);
4367.book Page 484 Monday, October 4, 2004 2:19 PM
Performance Monitoring
485
You can use the DBMS_STATS package to analyze tables, indexes, an entire schema, or the whole
database. A sample of some of the procedures available within the DBMS_STATS package are
shown in Table 9.9.

Performance tuning considerations for decision-support systems usually
revolve around response time minimization.
EM Database Control can be used to both monitor and react to sudden changes in perfor-
mance metrics like throughput and response time.
Using EM Database Control to View Performance Metrics
EM Database Control provides a graphical view of throughput, response time, I/O, and other
important performance metrics. To view these metrics, click the All Metrics link at the bottom
of the EM Database Control main screen to display the All Metrics screen, which is partially dis-
played in Figure 9.68.
Click the metric you want to examine to expand the available information. Figure 9.69
shows a partial listing of the expanded list for the Throughput metric.
Click the Database Block Changes (Per Second) link to display details on the number of data-
base blocks that were modified by application users, per second, for any period between the last
24 hours and the last 31 days. Figure 9.70 shows the Database Blocks Changes detail screen.
Telling ADDM about Your Server I/O Capabilities
Both throughput and response time are impacted by disk I/O activity. In order for ADDM to
make meaningful recommendations about the I/O activity on your server, you need to give
ADDM a reference point against which to compare the I/O statistics it has gathered. This refer-
ence point is defined as the “expected I/O” of the server. By default, ADDM uses an expected
I/O rate of 10,000 microseconds (10 milliseconds). This means that ADDM expects that, on aver-
age, your server will need 10 milliseconds to read a single database block from disk.
Using operating system utilities, we performed some I/O tests against our large storage area
network disk array and found that the average time needed to read a single database block was
about 7 milliseconds (7000 microseconds). To give ADDM a more accurate picture of our
expected I/O speeds, we used the DBMS_ADVISOR package to tell ADDM that our disk sub-
system was faster than the default 10 millisecond value:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 7000);
Without this adjustment, the ADDM might have thought that our I/O rates were better than
average (7 milliseconds instead of 10 milliseconds) when in fact they were only average for our
system. The effect of this inaccurate assumption regarding I/O would impact nearly every rec-

and paging activity.
In addition to other metrics, the Sessions: Waiting And Working section of the Performance tab
always shows CPU and I/O activity per session for the previous one-hour period. Figure 9.72
shows the Sessions: Waiting And Working section of the Performance main screen.
The final section of the Performance main screen, Instance Throughput, is shown in
Figure 9.73.
FIGURE 9.70 The database block changes metric detail
FIGURE 9.71 Host performance metrics
4367.book Page 488 Monday, October 4, 2004 2:19 PM
Summary
489
FIGURE 9.72 Session performance metrics
FIGURE 9.73 Instance Throughput performance metrics
This portion of the Performance tab graphically depicts the logons and transactions per
second and the physical reads and redo activity per second. You can also view these metrics
on a per transaction basis instead of per section, by clicking the Per Transaction button below
the graph.
Using EM Database Control to React to Performance Issues
Suppose you notice a drop in database performance within the last 30 minutes. Using the EM
Database Control Performance tab, you can drill down into the detail of any of the perfor-
mance metrics summarized on the tab and identify the source of the problem using techniques
described in the “Using EM Database Control To View ADDM Analysis” section earlier in
this chapter.
Summary
Oracle 10g provides many tools for proactively identifying and fixing potential performance
and management problems in the database. At the core of the monitoring system is the Auto-
matic Workload Repository (AWR), which uses the MMON background process to gather sta-
tistics from the SGA and store them in a collection of tables owned by the user SYSMAN.
4367.book Page 489 Monday, October 4, 2004 2:19 PM
490

Available storage space, excessive wait times, and high I/O activity are all examples of events
that you can monitor using alerts.
In addition to EM Database Control, you can find indicators of database performance in the
database Alert log, user and background trace files, data dictionary views, and dynamic perfor-
mance views. Some data dictionary views do not contain accurate information about the
segments in the database until after statistics are collected on those objects. Therefore, you can
automatically collect segment statistics through the use of EM Database Control jobs.
Invalid and unusable database objects also have a negative impact on performance and man-
ageability. You can monitor and repair invalid and unusable objects using the data dictionary
and the EM Database Control Administration screen.
EM Database Control summarizes several important performance metrics on the EM Data-
base Control main screen. These metrics include performance statistics for the host server, user
sessions, and instance throughput.
4367.book Page 490 Monday, October 4, 2004 2:19 PM
Exam Essentials
491
Exam Essentials
Understand the Automatic Workload Repository. Describe the components of the AWR and
how they are used to collect and store database performance statistics.
Describe the role of Automatic Database Diagnostic Monitor. Know how ADDM uses the
AWR statistics to formulate tuning recommendations using historical and baseline metrics.
Explain how each advisor is used to improve performance. Describe how you can use each of
the EM Database Control advisors shown on the Advisor Central screen to improve database
performance and manageability.
Describe how alerts are used to monitor performance. Show how you can configure the EM
Database Control alert system to alert you via the console or e-mail whenever a monitored event
occurs in the database.
Identify and fix invalid or unusable objects. Understand the techniques you can use to iden-
tify invalid procedures, functions, triggers, and views and how to validate them. Know how to
find unusable indexes and how to fix them.

4. The following graphic shows the SQL statements that are having the greatest impact on overall
DB Time. Which statement has had the greatest impact?
A. 9d87jmt7vo6nb(2.
B. 8acv8js8kr574(24.
C. b6usrq82hwsa3(73.
D. None of the above was highest.
5. Suppose you have used EM Database Control to drill down into ADDM findings and have
found that a single SQL statement is causing the majority of I/O on your system. Which of the
following advisors is best suited to troubleshoot this SQL statement?
A. SQL Tuning Advisor
B. SQL Access Advisor
C. Both A and B
D. Neither A or B
6. Nearly all the advisors submit their analysis activities to the database in the form of a job. When
the analysis job is submitted, which option for job scope adds the least overhead to the system?
A. Limited
B. Restricted
C. Comprehensive
D. Thorough
4367.book Page 493 Monday, October 4, 2004 2:19 PM
494
Chapter 9

Proactive Database Maintenance and Performance Monitoring
7. Using the Top SQL link of the EM Database Control Performance screen produces the output
shown in the following graphic. Approximately which time interval produced the highest activ-
ity for this monitored event?
A. 9:45 to 9:50
B. 10:00 to 10:45
C. 9:55 to 10:10

B. Critical threshold
C. Both A and B
D. Neither A or B
13. Multiple baseline metrics can be gathered and stored in the AWR. Why might you want more
than one metrics baseline?
A. You might want a separate baseline metric for each user.
B. You might want a separate baseline metric for daytime usage versus off-hours usage.
C. You might want a separate baseline metric for each schema.
D. You would never want more than one baseline metric, even though it is possible to gather
and store them.
14. Using EM Database Control, you discover that two application PL/SQL functions and a view are
currently invalid. Which of the following might you use to fix these objects? (Choose two.)
A. Shut down and restart the database.
B. Use EM Database Control to recompile the object.
C. Export the invalid objects, drop them, and then import them.
D. Use the ALTER FUNCTION … COMPILE and ALTER VIEW … COMPILE commands.
15. You have just created a database using scripts that you wrote. Now you notice that the auto-
matic collection of database statistics by the EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_
PROCS procedure is not running. What might be the cause?
A. The PFILE/SPFILE parameter GATHER_STATS=FALSE.
B. Only databases created using Database Configuration Assistant have automatic statistics
collection enabled.
C. The SYSMAN user who owns the AWR is not logged in.
D. The operating system does not support automatic statistics collection.
16. Which of the following is a performance metric that could be defined as “the amount of work
that a system can perform in a given amount of time”?
A. Response time
B. Uptime
C. Throughput
D. Runtime

D. Click the Alert Log Content link in the EM Database Control main screen.
4367.book Page 496 Monday, October 4, 2004 2:19 PM
Answers to Review Questions
497
Answers to Review Questions
1. B. The MMON process gathers statistics from the SGA and stores them in the AWR. The
ADDM process then uses these statistics to compare the current state of the database with base-
line and historical performance metrics before summarizing the results on the EM Database
Control screens.
2. D. Setting STATISTICS_LEVEL = BASIC disables the collection and analysis of AWR statistics.
TYPICAL is the default setting, and ALL gathers information for the execution plan and operating
system timing. OFF is not a valid value for this parameter.
3. B. The I/O caused by user activity is the primary source of user waits because it is listed first in
the graph’s legend. Clicking the User I/O link opens a screen in which you can examine which
SQL statements are contributing the most to the user waits.
4. C. The pie graph shows that the SQL statement that has been assigned the identifier of
b6usrq82hwsa3(73) contributed to 73 percent of the total time spent servicing the top three
SQL statements.
5. C. You can use the SQL Tuning Advisor and SQL Access Advisor together to determine if I/O
can be minimized and overall DB Time reduced to the targeted SQL statement.
6. A. The Limited scope has the least impact on the system. The Comprehensive scope is the most
detailed, but also makes the greatest demands on the system. There are no job scope options
called Restricted or Thorough.
7. D. The shaded area shows that the time interval from approximately 10:00 to 10:05 will be ana-
lyzed for Top SQL statements.
8. D. DBA_ADVISOR_RATIONALE provides the rationale for each ADDM recommendation. The
ADDM findings are stored in DBA_ADVISOR_FINDINGS. The object related to the findings are
shown in DBA_ADVISOR_OBJECTS. The actual ADDM recommendations are found in DBA_
ADVISOR_RECOMMENDATIONS.
9. C. The Memory Advisor can help determine whether the overall size of the SGA is appropriate

the Performance screen or using the DBMS_WORKLOAD_REPOSITORY PL/SQL package.
19. B. The Performance screen of the EM Database Control provides a quick overview of how the
host system, user sessions, and throughput are impacted by the system slowdown. You can also
drill down into any of these three areas to take a look at details about this slowdown.
20. A. Running the SQL Tuning Advisor provides the most information about how the performance
of this SQL statement might be improved. The SQL Access Advisor is run only after the output
from the SQL Tuning Advisor indicates that it will be useful. EM Database Control does not
store detailed information about I/O activity in either its alerts or the Alert log.
4367.book Page 498 Monday, October 4, 2004 2:19 PM

Chapter

10

Implementing
Database Backups

ORACLE DATABASE 10

G

:
ADMINISTRATION I EXAM OBJECTIVES
COVERED IN THIS CHAPTER:


Backup and Recovery Concepts


Describe the basics of database backup, restore and


Describe the different types of database backups.


Back up a control file to trace.


Manage backups.

Exam objectives are subject to change at any time with-
out prior notice and at Oracle’s sole discretion. Please
visit Oracle’s Training and Certification website (

http://
www.oracle.com/education/certification/

) for the
most current exam objectives listing.

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

Oracle Database 10

g

(Oracle 10

g

) makes it easy for you to con-


Minimizing or eliminating loss of committed transactions by using archived redo logs,
standby databases, and Oracle Data Guard

RAC, Streams, Data Guard, and standby databases are beyond the scope of this

book, but are covered in more detail in advanced Oracle courseware.

In this chapter, we will first describe the components that you will use to minimize or elim-
inate data loss in your database while at the same time keeping availability high: checkpoints,
redo log files, archived redo log files, and the Flash Recovery area. Next, we will show you how
to configure your database for recovery, including a discussion of

ARCHIVELOG

mode and other
required initialization parameters. Once your environment is configured, you will need to know
how to actually back it up, using both operating system commands and the RMAN utility.
Finally, we will show you how to automate and manage your backups as well as how to monitor
one of the key components in your backup strategy: the Flash Recovery area. In Chapter 11,
“Implementing Database Recovery,” we will show you how to use the files created and main-
tained during your backups to quickly recover the database in the event of a database failure.
Oracle’s GUI administration tool, the Enterprise Manager (EM) Database Control, makes
backup configuration and performing backups easier than in any previous release of Oracle.
Most, if not all, of the functionality available with the command-line interface is available in
a GUI interface to save time and make backup operations less error prone.

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

Understanding and Configuring Recovery Components

a datafile from a disk or tape backup before you can initiate media recovery.
In addition to regularly scheduled backups (see the section “Automating Backups” near the
end of this chapter), you can configure a number of other features to maximize your database’s
availability and minimize recovery time: multiplexing control files, multiplexing redo log files,
configuring the database in

ARCHIVELOG

mode, and using a Flash Recovery area.

Control Files

The control file is one of the smallest, yet one of the most critical, files in your database. Recov-
ering from the loss of one copy of a control file is relatively straightforward; recovering from the
loss of your only control file or all control files is more of a challenge and requires more
advanced recovery techniques.

Recovering from the loss of a control file is covered in Chapter 11.

In the following sections, we will give you an overview of the control file architecture as well
as show you how to maximize the recoverability of the control file in the section “Multiplexing
Control Files.”

Control File Architecture

The

control file

is a relatively small (in the megabyte range) binary file that contains information



The name, location, and online/offline status information of the datafiles.


The name and location of the redo log files.


Redo log archive information.


Tablespace names.


The current

log sequence number

, which is a unique identifier that is incremented and
recorded when an online redo log file is switched.


The most recent checkpoint information.

Checkpoints are discussed in more detail later in this chapter in the section

“Understanding Checkpoints.”


The beginning and ending of undo segments.

changes. The log writer (LGWR) process updates the control file with the current log sequence
number. CKPT updates the control file with the recent checkpoint information. When the data-
base is in

ARCHIVELOG

mode, the archiver (ARC

n

) processes update the control file with infor-
mation such as the archive log filename and log sequence number.

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