Database and Instance Level Trace
Oracle Tips by Burleson Consulting
is an excerpt from "Oracle 10g New Features for Administrators" by
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
To enable trace in the instance level
This procedure disables SQL trace for the whole
database or a specific instance
DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name IN VARCHAR2
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
AUM is controlled by the following parameters:
o UNDO_MANAGEMENT : AUTO|MANUAL
o UNDO_RETENTION : default is 900 seconds
The Undo Advisor
This OEM utility provides you undo related functions
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.
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.
If you like Oracle tuning, see the
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.