Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

5.2 Oracle Database 10g Self-Tuning and Management

A major focus in Oracle Database 10g
is the introduction of new
self-tuning and management features in the database. Necessary for
grid computing, these new features also make managing more familiar
database environments much easier. Out of the box, the Oracle
installation process builds an Intelligent Infrastructure. The
infrastructure includes a workload repository
(Automatic
Workload Repository, or AWR) that is automatically populated with
database statistical information gathered via
"instrumentation" in the database
code at very low overhead. Active session history is typically
gathered every second, and the AWR is populated by default every 30
seconds. The Intelligent Infrastructure includes automatic setup of
maintenance tasks that a DBA would normally schedule (such as ANALYZE
to build statistics needed for the cost-based optimizer),
server-generated alerts that occur just-in-time (not through a
polling mechanism, as was present in previous EM deployment), and an
advisory infrastructure.

The new self-tuning and management features that DBAs and others can
leverage include:

Automatic Database Diagnostic Manager (ADDM)

ADDM automatically tracks changes in database performance and makes
recommendations on how to improve performance. Steps needed to
resolve system utilization problems are typically reduced from
watching events, exploring V$ tables, and identifying related SQL to
simply reviewing the recommendations and accepting them. ADDM
leverages data gathered in the workload repository. ADDM can be
accessed through Advisor Central in EM.

Automatic SQL Tuning Optimizer

Rather than reviewing sessions with "bad
SQL" by examining parameters such as system
utilization and wait events, then observing Top Sessions in
Enterprise Manager to identify such sessions and reviewing an explain
plan, you can now simply review the ADDM recommendations, follow a
link to SQL Tuning, and accept the recommendations. The SQL Tuning
Optimizer performs a more extensive optimization investigation, and
stores the results of this process in a Profile for automatic use by
Oracle in the future. These Profiles can be used with packaged
applications, as well as with your own custom code, and are used by
the optimizer in subsequent resubmission of the SQL statements.

Automatic Shared Memory Advisor

The Memory Advisor is an expert system that eliminates manual
adjustment of the buffer pool and shared pool sizes previously
required to eliminate out-of-memory errors.

Segment Advisor

Use of the Segment Advisor eliminates the need to
identify fragmented objects and then to use Enterprise Manager to
reorganize the objects. The Segment Advisor advises which objects to
shrink and allows you to simply accept the recommendations.

Single Command Flashback Recovery

The FLASHBACK DATABASE statement enables
you to quickly revert the database to a state it was in at a past
moment in time. Oracle Database 10g uses a log file containing data
block images for changed blocks and other information. You can simply
recover the table with a single FLASHBACK TABLE command. For more
information on all of the new FLASHBACK commands, see Chapter 10.

Automated Undo Management (AUM) Tuning

AUM, the database feature that automatically allocates undo
(rollback) space among active sessions, first appeared in
Oracle9i. Oracle Database 10g
AUM Tuning automatically determines optimal undo retention
time based on system activity, and maximizes usage of the undo
tablespace.

Automated Storage Management (ASM)

ASM provides a filesystem and volume manager in the database,
enabling the automated striping of files and automating mirroring of
database extents. DBAs simply define a pool of storage or disk group
and manage the disk group through EM. Workloads can be dynamically
rebalanced without taking the database down, so the DBA can add to
the pool of storage while the database is available. ASM includes
support of datafiles, log files, control files, archive logs, and
RMAN and backup sets, and can be applied to a single system, or a RAC
or grid computing environment.