Oracle DBMS


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ADDM

Version 11.1

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmsaddm.sql

First Available

11.1

Dependencies

DBA_ADDM_FDG_BREAKDOWN	DBA_ADVISOR_TASKS
DBA_ADDM_FINDINGS	DBMS_ADVISOR
DBA_ADDM_INSTANCES	DBMS_MANAGEMENT_PACKS
DBA_ADDM_SYSTEM_DIRECTIVES	DBMS_SYS_ERROR
DBA_ADDM_TASKS	GV$INSTANCE
DBA_ADDM_TASK_DIRECTIVES	GV$PARAMETER
DBA_ADVISOR_DIR_DEFINITIONS	V$DATABASE
DBA_ADVISOR_FINDING_NAMES

Security Model

Execute is granted to PUBLIC

ANALYZE_DB

Create and execute an ADDM task to analyze all instances of a database for a range of snapshots

dbms_addm.analyze_db(
task_name      IN OUT VARCHAR2,
begin_snapshot IN     NUMBER,
end_snapshot   IN     NUMBER,
db_id          IN     NUMBER := NULL);

SELECT dbid
FROM gv$database;

set pagesize 25
set linesize 121
col instart_fmt format a20
break on instart_fmt
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set serveroutput on

DECLARE
tname VARCHAR2(30) := 'UW_TASK1';
BEGIN
dbms_addm.analyze_db(tname, 2437, 2504, 1701481905);
dbms_output.put_line(tname);
END;
/

col execution_name format a15
col finding_name format a30
col parent format 99
col impact format 99999999999
col message format a60
col more_info format a60

SELECT execution_name, finding_name, parent, impact
FROM dba_addm_findings
WHERE task_name = 'UW_TASK1';

SELECT execution_name, finding_name, message
FROM dba_addm_findings
WHERE task_name = 'UW_TASK1';

SELECT execution_name, finding_name, more_info
FROM dba_addm_findings
WHERE task_name = 'UW_TASK1';

ANALYZE_INST

Create and execute an ADDM task to analyze a specific instance of a database for a range of snapshots

dbms_addm.analyze_inst(
task_name       IN OUT VARCHAR2,
begin_snapshot IN     NUMBER,
end_snapshot    IN     NUMBER,
instance_number IN     NUMBER := NULL,
db_id           IN     NUMBER := NULL);

SELECT dbid
FROM gv$database;

SELECT inst_id
FROM gv$instance;

set pagesize 25
set linesize 121
col instart_fmt format a20
break on instart_fmt
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set serveroutput on

DECLARE
tname VARCHAR2(30) := 'UW_TASK2';
BEGIN
dbms_addm.analyze_inst(tname, 2387, 2406, 1, 1701481905);
dbms_output.put_line(tname);
END;
/

desc dba_addm_findings

SELECT task_name
FROM dba_addm_findings;

SELECT task_name
FROM dba_addm_findings
WHERE task_name LIKE 'UW%';

col finding_name format a30
col impact format 99999.99

SELECT execution_name, finding_name, parent, impact, message, more_info
FROM dba_addm_findings
WHERE task_name = 'UW_TASK2';

desc dba_addm_tasks

SELECT DISTINCT task_name, advisor_name
FROM dba_addm_tasks;

col advisor_name format a15
col how_created format a15

SELECT advisor_name, status, how_created, system_task, requested_analysis, actual_analysis
FROM dba_addm_tasks
WHERE task_name = 'UW_TASK2';

ANALYZE_PARTIAL

Create and execute an ADDM task to analyze a partial list of instances for a range of snapshots

dbms_addm.analyze_partial(
task_name        IN OUT VARCHAR2,
instance_numbers IN     VARCHAR2,
begin_snapshot   IN     NUMBER,
end_snapshot     IN     NUMBER,
db_id            IN     NUMBER := NULL);

DELETE

Delete an ADDM task

dbms_addm.delete(task_name IN VARCHAR2);

exec dbms_addm.delete('UW_TASK1');

DELETE_FINDING_DIRECTIVE

Delete an ADDM directive

dbms_addm.delete_finding_directive(
task_name IN VARCHAR2,
dir_name IN VARCHAR2);

SELECT task_name, directive_name, description
FROM dba_addm_task_directives;

DELETE_PARAMETER_DIRECTIVE

Delete an ADDM directive

dbms_addm.delete_parameter_directive(
task_name IN VARCHAR2,
dir_name IN VARCHAR2,
parameter_name IN VARCHAR2);

TBD

DELETE_SEGMENT_DIRECTIVE

Delete an ADDM directive

dbms_addm.delete_segment_directive(
task_name IN VARCHAR2,
dir_name IN VARCHAR2,
object_number IN NUMBER);

TBD

DELETE_SQL_DIRECTIVE

Delete an ADDM directive

dbms_addm.delete_sql_directive(
task_name IN VARCHAR2,
dir_name IN VARCHAR2);

TBD

GET_REPORT

Returns the ADDM report

dbms_addm.get_report(task_name IN VARCHAR2) RETURN CLOB;

set long 1000000
set serveroutput on

DECLARE
rptlob CLOB;
BEGIN
rptlob := dbms_addm.get_report('UW_TASK1');
dbms_output.put_line(rptlob);
END;
/

SELECT * FROM TABLE(dbms_xplan.display_cursor('1tkztbrcvfcdw'));

INSERT_FINDING_DIRECTIVE

Insert a directive filtering a finding, either for a task or system wide.

dbms_addm.insert_finding_directive(
task_name           IN VARCHAR2,
dir_name            IN VARCHAR2,
finding_name        IN VARCHAR2,
min_active_sessions IN NUMBER := 0,
min_perc_impact     IN NUMBER := 0);

TBD

INSERT_PARAMETER_DIRECTIVE

Insert a directive filtering a recommendations to change a system parameter

dbms_addm.insert_parameter_directive(
task_name IN VARCHAR2,
dir_name IN VARCHAR2,
parameter_name IN VARCHAR2);

TBD

INSERT_SEGMENT_DIRECTIVE

Insert a directive filtering recommendations to run the segment advisor

Overload 1

dbms_addm.insert_segment_directive(
task_name       IN VARCHAR2,
dir_name        IN VARCHAR2,
owner_name      IN VARCHAR2,
object_name     IN VARCHAR2 := NULL,
sub_object_name IN VARCHAR2 := NULL);

TBD

Overload 2

dbms_addm.insert_segment_directive(
task_name IN VARCHAR2,
dir_name IN VARCHAR2,
object_number IN VARCHAR2 := NULL);

TBD

INSERT_SQL_DIRECTIVE

Insert a directive filtering SQL recommendations, either for a task or system wide.

dbms_addm.inset_sql_directive(
task_name           IN VARCHAR2,
dir_name            IN VARCHAR2,
sql_id              IN VARCHAR2,
min_active_sessions IN NUMBER := 0,
min_response_time   IN NUMBER := 0);

TBD

Related Topics

DBMS_ADVISOR

DBMS_SQLTUNE

DBMS_XPLAN

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us