Recommendations to Improve Manageability

This section describes best practices for improving Oracle Database manageability:

■ Use Automatic Performance Tuning Features

■ Use a Server Parameter File

■ Use Automatic Undo Management

■ Use Locally Managed Tablespaces

■ Use Automatic Segment Space Management

■ Use Temporary Tablespaces and Specify a Default Temporary Tablespace

■ Use Resumable Space Allocation

■ Use Database Resource Manager

2.2.3.1 Use Automatic Performance Tuning Features

Effective data collection and analysis is essential for identifying and correcting performance problems. Oracle provides a number of tools that allow a performance engineer to gather information regarding database performance.

The Oracle Database automatic performance tuning features include:

■ Automatic Workload Repository (AWR)

■ Automatic Database Diagnostic Monitor (ADDM)

■ SQL Tuning Advisor

■ SQL Access Advisor

When using AWR, consider the following best practices:

■ Set the AWR automatic snapshot interval to 10-20 minutes to capture performance peaks during stress testing or to diagnose performance issues.

■ Under usual workloads a 60-minute interval is sufficient

2.2.3.2 Use a Server Parameter File

The server parameter file (SPFILE) enables a single, central parameter file to hold all database initialization parameters associated with all instances of a database. This provides a simple, persistent, and robust environment for managing database parameters. An SPFILE is required when using Data Guard Broker.

See Also:

■ Oracle Database Administrator's Guide for information on managing initialization parameters with an SPFILE

■ Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for information on initialization parameters with Real Application Clusters

■ Oracle Data Guard Broker for information on other prerequisites for using Oracle Data Guard Broker

■ Appendix A, "Database SPFILE and Oracle Net Configuration File Samples"

2.2.3.3 Use Automatic Undo Management

With automatic undo management, the Oracle Database server effectively and efficiently manages undo space, leading to lower administrative complexity and cost.

When Oracle Database internally manages undo segments, undo block and consistent read contention are eliminated because the size and number of undo segments are automatically adjusted to meet the current workload requirement.

To use automatic undo management, set the following initialization parameters:

■ UNDO_MANAGEMENT

This parameter should be set to AUTO.

■ UNDO_RETENTION

This parameter specifies the desired time in seconds to retain undo data. It must be the same on all instances.

■ UNDO_TABLESPACE

This parameter should specify a unique undo tablespace for each instance.

Advanced object recovery features, such as Flashback Query, Flashback Version Query, Flashback Transaction Query, and Flashback Table, require automatic undo

management. By default, Oracle Database automatically tunes undo retention by collecting database use statistics and estimating undo capacity needs. You can affect this automatic tuning by setting the UNDO_RETENTION initialization parameter. It is only necessary to set this initialization parameter in the following cases:

■ The undo tablespace has the AUTOEXTEND option enabled.

■ You want to have undo retention for LOBs.

■ You want a retention guarantee.

With the retention guarantee option, the undo guarantee is preserved even if there is need for DML activity (DDL statements are still allowed). If the tablespace is

configured with less space than the transaction throughput requires, then the following four things will occur in this sequence:

1. If you have an autoextensible file, then it will automatically grow to accommodate the retained undo data.

2. A warning alert is issued at 85 percent full.

3. A critical alert is issued at 97 percent full.

4. Transactions receive an out-of-space error.

Note: By default, undo data can be overwritten by ongoing transactions, even if the UNDO_RETENTION setting specifies that the undo data should be maintained. To guarantee that unexpired undo data is not overwritten, retention guarantee must be enabled for the undo tablespace.

See Also: Oracle Database Administrator's Guide for more information about the UNDO_RETENTION setting and the size of the undo

tablespace

2.2.3.4 Use Locally Managed Tablespaces

Locally managed tablespaces perform better than dictionary-managed tablespaces, are easier to manage, and eliminate space fragmentation concerns. Locally managed tablespaces use bitmaps stored in the datafile headers and, unlike dictionary managed tablespaces, do not contend for centrally managed resources for space allocations and de-allocations.

2.2.3.5 Use Automatic Segment Space Management

Automatic segment space management simplifies space administration tasks, thus reducing the chance of human error. An added benefit is the elimination of

performance tuning related to space management. It facilitates management of free space within objects such as tables or indexes, improves space utilization, and provides significantly better performance and scalability with simplified

administration. The automatic segment space management feature is enabled by default for all new tablespaces created using default attributes.

2.2.3.6 Use Temporary Tablespaces and Specify a Default Temporary Tablespace

Temporary tablespaces improve the concurrency of multiple sort operations, reduce sort operation overhead, and avoid data dictionary space management operations altogether. This is a more efficient way of handling temporary segments, from the perspective of both system resource usage and database performance.

A default temporary tablespace should be specified for the entire database to prevent accidental exclusion of the temporary tablespace clause. This can be done at database creation time by using the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement, or after database creation by the ALTER DATABASE statement.

Using the default temporary tablespace ensures that all disk sorting occurs in a temporary tablespace and that other tablespaces are not mistakenly used for sorting.

2.2.3.7 Use Resumable Space Allocation

Resumable space allocation provides a way to suspend and later resume database operations if there are space allocation failures. The affected operation is suspended instead of the database returning an error. No processes need to be restarted. When the space problem is resolved, the suspended operation is automatically resumed.

To use resumable space allocation, set the RESUMABLE_TIMEOUT initialization parameter to the number of seconds of the retry time. You must also at the session level issue the ALTER SESSION ENABLE RESUMABLE statement.

2.2.3.8 Use Database Resource Manager

The Database Resource Manager gives database administrators more control over resource management decisions, so that resource allocation can be aligned with the business objectives of an enterprise. The Database Resource Manager provides the

See Also: Oracle Database Administrator's Guide for more information on locally managed tablespaces

See Also: Oracle Database Administrator's Guide for more information on segment space management

See Also: Oracle Database Administrator's Guide for more information on managing tablespaces

See Also: Oracle Database Administrator's Guide for more information on managing resumable space allocation

ability to prioritize work within the Oracle Database server. Availability of the database encompasses both its functionality and performance. If the database is available but users are not getting the level of performance they need, then availability and service level objectives are not being met. Application performance, to a large extent, is affected by how resources are distributed among the applications that access the database. The main goal of the Database Resource Manager is to give the Oracle Database server more control over resource management decisions, thus

circumventing problems resulting from inefficient operating system management and operating system resource managers. The Database Resource Manager is enabled by default.