Oracle Database and Instance Level Trace

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

In Oracle 10.2 includes new procedures to enable and disable trace at database and/or instance level for a given Client Identifier, Service Name, MODULE and ACTION.

To enable trace in the whole database

DBMS_MONITOR.DATABASE_TRACE_ENABLE

To enable trace in the instance level

DBMS_MONITOR.DATABASE_TRACE_ENABLE
(INSTANCE_NAME=>'RAC1')

This procedure disables SQL trace for the whole database or a specific instance

DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name IN VARCHAR2 DEFAULT NULL)

For information about tracing at service level, refer to the section "Enhancements in Managing Multitier Environments".

Using Automatic Undo Retention Tuning

Oracle recommends using Automatic Undo Management (AUM) feature. However, be aware that the Manual undo management is the default.

AUM is controlled by the following parameters:

o UNDO_MANAGEMENT : AUTO|MANUAL
o UNDO_TABLESPACE
o UNDO_RETENTION : default is 900 seconds

The Undo Advisor

This OEM utility provides you undo related functions like:

o undo tablespace size recommendations
o undo retention period recommendations

Using the Retention Guarantee Option

This feature guarantees that Oracle will never overwrite any undo data that is within the undo retention period. This new feature is disabled by default. You can enable the guarantee feature at database creation time, at the undo tablespace creation time, or by using the alter tablespace command.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE

Automatically Tuned Multiblock Reads

The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the number of blocks prefetched into the buffer cache during scan operations, such as full table scan and index fast full scan.

Oracle Database 10g Release 2 automatically selects the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.

This is the default behavior in Oracle Database 10g Release 2, if you do not set any value for DB_FILE_MULTIBLOCK_READ_COUNT parameter, or you explicitly set it to 0. If you explicitly set a value, then that value is used, and is consistent with the previous behavior.