Oracle 10g - Automatic Database Management

Using the Automatic Database Diagnostic Monitor (ADDM)

The Automatic Workload Repository (AWR) is a statistics collection facility that collects new performance statistics in the form of a snapshot on an hourly basis and saves the snapshots for seven days into SYSAUX before purging them.

The Automatic Database Diagnostic Monitor (ADDM) is a new diagnosis tool that runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and saves recommendations for improving performance in SYSAUX.

You can then go to the OEM Database Control to view the results, or even view them from a SQL*Plus session with the help of an Oracle-supplied SQL script.

Goal of the ADDM

ADD aims at reducing a key database metric called DB time, which stands for the cumulative amount of time (in milliseconds) spent on actual database calls (at the user level);i.e. both the wait time and processing time (CPU time).

Problems That the ADDM Diagnoses

Configuration issues
Improper application usage
Expensive SQL statements
I/O performance issues
Locking issues
Excessive parsing
CPU bottlenecks
Undersized memory allocation
Connection management issues, such as excessive logon/logoff statistics

The New Time Model

V$SYS_TIME_MODEL

This view shows time in terms of the number of microseconds the database has spent on a specific operation.

V$SESS_TIME_MODEL

displays the same information in the session-level.

Automatic Management of the ADDM

The Manageability Monitor Process (MMON) process schedules the automatic running of the ADDM.

Configuring the ADDM

You only need to make sure that the initialization parameters STATISTICS_LEVEL is set to TYPICAL or ALL, in order for the AWR to gather its cache of performance statistics.

Determining Optimal I/O Performance

Oracle assumes the value of the parameter (not intialization parameter) DBIO_EXPECTED is 10 milliseconds.

SELECT PARAMETER_VALUE

FROM DBA_ADVISOR_DEF_PARAMETERS

WHERE ADVISOR_NAME='ADDM'

AND PARAMETER_NAME='DBIO_EXPECTED'

If your hardware is significantly different, you can set the parameter value one time for all subsequent ADDM executions:

DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM','DBIO_EXPECTED', 8000);

Running the ADDM

MMON schedules the ADDM to run every time the AWR collects its most recent snapshot.

To view the ADDM’s findings:

Use the OEM Database Control
Run the Oracle-provided script addmrpt.sql

The ADDM Analysis

ADDM analysis finding consists of the following four components:

The definition of the problem itself
The root cause of the performance problem
Recommendation(s) to fix the problem
The rationale for the proposed recommendations

Viewing Detailed ADDM Reports

Click the View Report button on the ADDM main page in the Database Control.

Using the DBMS_ADVISOR Package to Manage the ADDM

The DBMS_ADVISOR package is part of the Server Manageability Suite of advisors, which is a set of rule-based expert systems that identify and resolve performance problems of several database components.

Note: The DBMS_ADVISOR package requires the ADVISOR privilege.

CREATE_TASK	to create a new advisor task.
SET_DEFAULT_TASK	helps you modify default values of parameters within a task.
DELETE_TASK	deletes a specific task from the repository.
EXECUTE_TASK	executes a specific task.
GET_TASK_REPORT	displays the most recent ADDM report.
SET_DEFAULT_TASK_PARAMETER	modifies a default task parameter.

Syntaxes:

DBMS_ADVISOR.GET_TASK_REPORT (

task_name ,

type , -- TEXT, XML, HTML

level, -- TYPICAL, ALL, BASIC

section, owner_name) RETURN CLOB

Examples:

CREATE OR REPLACE FUNCTION run_addm(start_time IN DATE, end_time IN DATE )

RETURN VARCHAR2

begin_snap NUMBER;

end_snap NUMBER;

tid NUMBER; -- Task ID

tname VARCHAR2(30); -- Task Name

tdesc VARCHAR2(256); -- Task Description

BEGIN

-- Find the snapshot IDs corresponding to the -- given input parameters.

SELECT max(snap_id)INTO begin_snap

FROM DBA_HIST_SNAPSHOT

WHERE trunc(end_interval_time, 'MI') <=

start_time;

SELECT min(snap_id) INTO end_snap

FROM DBA_HIST_SNAPSHOT

WHERE end_interval_time >= end_time;

-- set Task Name (tname) to NULL and let

-- create_task return a unique name for

-- the task.

tname := '';

tdesc := 'run_addm( ' || begin_snap || ', ' || end_snap || ' )';

-- Create a task, set task parameters and

-- execute it

DBMS_ADVISOR.CREATE_TASK( 'ADDM', tid, tname, tdesc );

DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'START_SNAPSHOT', begin_snap );

DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'END_SNAPSHOT' , end_snap );

DBMS_ADVISOR.EXECUTE_TASK( tname );

RETURN tname;

END;

SET PAGESIZE 0 LONG 1000000 LONGCHUNKSIZE 1000

COLUMN get_clob FORMAT a80

-- execute run_addm() with 7pm and 9pm as

-- input

VARIABLE task_name VARCHAR2(30);

BEGIN

:task_name := run_addm( TO_DATE('19:00:00

(10/20)', 'HH24:MI:SS (MM/DD)'),

TO_DATE('21:00:00 (10/20)', 'HH24:MI:SS

(MM/DD)') );

END;

-- execute GET_TASK_REPORT to get the textual

-- ADDM report.

SELECT

DBMS_ADVISOR.GET_TASK_REPORT(:task_name)

FROM DBA_ADVISOR_TASKS t

WHERE t.task_name = :task_name

AND t.owner = SYS_CONTEXT( 'userenv', 'session_user' );

ADDM-Related Dictionary Views

DBA_ADVISOR_RECOMMENDATIONS

DBA_ADVISOR_FINDINGS

DBA_ADVISOR_RATIONALE

Using Automatic Shared Memory Management (ASMM)

With Automatic Shared Memory Management, Oracle will use internal views and statistics to decide on the best way to allocate memory among the SGA components. The new process MMAN constantly monitors the workload of the database and adjusts the size of the individual memory components accordingly.

Note: In Oracle Database 10g, the database enables the Automatic PGA Memory Management feature by default. However, if you set the PGA_AGGREGATE_TARGET parameter to 0 or the WORKAREA_SIZE_POLICY parameter to MANUAL, Oracle doesn’t use Automatic PGA Memory Management.

Manual Shared Memory Management

As in previous version, you use the following parameters to set SGA component sizes:

DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL, JAVA_POOL_SIZE, LOG_BUFFER and STREAMS_POOL_SIZE.

In Oracle Database 10g, the value of the SHARED_POOL_SIZE parameter includes the internal overhead allocations for metadata such as the various data structures for sessions and processes.

You must, therefore, make sure to increase the size of the SHARED_POOL_SIZE parameter when you are upgrading to Oracle Database 10g. You can find the appropriate value by using the following query:

select sum(BYTES)/1024/1024 from V$SGASTAT

where POOL = 'shared pool'

Automatic Memory Management

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE) in Release 2

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management.

The following pools are not affected by Automatic Shared Memory Management:

Log buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Streams pool (in Release 1 only)
Fixed SGA and other internal allocations
The new Oracle Storage Management (OSM) buffer cache, which is meant for the optional ASM instance

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

Note: If you dynamically set SGA_TARGET to zero, the size of the four auto-tuned shared memory components will remain at their present levels.

Note: The SGA_MAX_SIZE parameter sets an upper bound on the value of the SGA_TARGET parameter.

Note: In order to use Automatic Shared Memory Management, you should make sure that the initialization parameter STATISTICS_LEVEL is set to TYPICAL or ALL.

You can use the V$SGA_DYNAMIC_COMPONENTS view to see the values assigned to the auto-tuned components. Whereas the V$PARAMETER will display the value you set to the auto-tuned SGA parameter, not the value assigned by the ASMM.

When you restart the instance, by using SPFILE Oracle will start with the values the auto-tuned memory parameters had before you shut down the instance.

COLUMN COMPONENT FORMAT A30

SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB

FROM V$SGA_DYNAMIC_COMPONENTS

WHERE CURRENT_SIZE <>0

Using Automatic Optimizer Statistics Collection

All you need to do to make sure the automatic statistics collection process works is to ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

Oracle will use the DBMS_STATS package to collect optimizer statistics on an automatic basis.

Changes on DBMS_STATS

Oracle Database 10g introduces new values for the GRANULARITY and DEGREE arguments of the GATHER_*_STATS procedures to simplify the determination of the calculated statistics. Unless you are an experienced user, you should use the new default values:

GRANULARITY
AUTO (default): The procedure determines the granularity based on the partitioning type. It collects the global-, partition-, and subpartition-level statistics if the subpartitioning method is LIST. Otherwise, it collects only the global- and partition-level statistics.
GLOBAL AND PARTITION: Gathers the global- and partition-level statistics. No subpartition-level statistics are gathered even if it is a composite partitioned object.
DEGREE
AUTO_DEGREE: This value enables the Oracle server to decide the degree of parallelism automatically. It is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on the number of CPUs and initialization parameters) according to the size of the object.

Using the Scheduler to Run DBMS_GATHER_STATS_JOB

Oracle automatically creates a database job called GATHER_STATS_JOB at database creation time.

select JOB_NAME

from DBA_SCHEDULER_JOBS

where JOB_NAME like 'GATHER_STATS%'

Oracle automatically schedules the GATHER_STATS_JOB job to run when the maintenance window opens.

The GATHER_STATS_JOB job calls the procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to gather the optimizer statistics.

The job collects statistics only for objects with missing statistics and objects with stale statistics.

If you want to stop the automatic gathering of statistics:

DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB')

Using the Database Control to Manage the GATHER_STATS_JOB Schedule

click the Administration tab.
Scheduler Group -> Windows Link
Click the Edit button. You’ll then be able to edit the weeknight or the weekend window timings.

Table Monitoring

You cannot use the ALTER_DATABASE_TAB_MONITORING and ALTER_SCHEMA_TAB_MONITORING procedures of the DBMS_STATS package to turn table monitoring on and off at the database and schema level, respectively, because these subprograms are deprecated in Oracle Database 10g. Oracle 10g automatically performs these functions, if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

Manual Collection of Optimizer Statistics

Oracle 10g allows you to gather Optimizer statistics manually using the DBMS_STATS.

Handling Volatile Tables by Locking Statistics

You can lock statistics of specific objects so that current object statistics will be used by the optimizer regardless of data changes on the locked objects.

Use the following procedures in DBMS_STATS

LOCK_TABLE_STATISTICS
UNLOCK_TABLE_STATISTICS
LOCK_SCHEMA_STATISTICS
UNLOCK_SCHEMA_STATISTICS

Example:

DBMS_STATS.LOCK_TABLE_STATS('scott','test')

Overriding Statistics Locking

You may want Oracle to override any existing statistics locks. You can do so by setting the FORCE argument with several procedures to TRUE in the DBMS_STATS package. The default is FALSE.

Restoring Historical Optimizer Statistics

Fortunately, Oracle lets you automatically save all old statistics whenever your refresh the statistics.

You can restore statistics by using the appropriate RESTORE_*_STATS procedures.

The view DBA_OPTSTAT_OPERATIONS contains a history of all optimizer statistics collections.

DBA_TAB_STATS_HISTORY

This view contains a record of all changes made to table statistics. By default, the DBA_TAB_STATS_HISTORY view saves the statistics history for 31 days. However, by using the ALTER_STATS_HISTORY_RETENTION procedure of the DBMS_STATS package, you can change the default value of the statistics history retention interval.

Rule-Based Optimizer Obsolescence

RBO still exists in Oracle Database 10g but is an unsupported feature. No code changes have been made to RBO, and no bug fixes are provided.

Database and Instance Level Trace

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:

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

The Undo Advisor

This OEM utility provides you undo related functions like:

undo tablespace size recommendations
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.