Configuration

CONTROL_MANAGEMENT_PACK_ACCESS

"Set CONTROL_MANAGEMENT_PACK_ACCESS to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables many Oracle Database features, including ADDM, and is strongly discouraged."(3)

STATISTICS

"ADDM is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable the automatic database diagnostic feature of ADDM. The default setting is TYPICAL. Setting the STATISTICS_LEVEL parameter to BASIC disables many Oracle Database features, including ADDM, and is not recommended."(2)

DBIO_EXPECTED

"The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk"(1)

EXECUTE DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);

To see the current setting use...

COLUMN advisor_name FORMAT a30

COLUMN parameter_value FORMAT a30

COLUMN parameter_name FORMAT a30

SELECT advisor_name, parameter_name, parameter_value, is_default

  FROM dba_advisor_def_parameters

 WHERE advisor_name='ADDM'

   AND parameter_name='DBIO_EXPECTED';

Bibliography & References