| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsaddm.sql |
| First Available |
11.1.0.6 |
| Dependencies |
| DBA_ADDM_FDG_BREAKDOWN |
DBMS_MANAGEMENT_PACKS |
| DBA_ADDM_FINDINGS |
DBMS_SYS_ERROR |
| DBA_ADDM_INSTANCES |
DBMS_UTILITY |
| DBA_ADDM_SYSTEM_DIRECTIVES |
DBMS_WORKLOAD_REPLAY |
| DBA_ADDM_TASKS |
DUAL |
| DBA_ADDM_TASK_DIRECTIVES |
PRVT_ADVISOR |
| DBA_ADVISOR_DIR_DEFINITIONS |
PRVT_HDM |
| DBA_ADVISOR_FINDING_NAMES |
USER_ADVISOR_TASKS |
| DBA_ADVISOR_TASKS |
V$DATABASE |
| DBMS_ADVISOR |
|
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
|
| |
| 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 v$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 v$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); |
SELECT dbid FROM v$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_TASK3';
insts VARCHAR2(50) := '1,2,3';
BEGIN
dbms_addm.analyze_partial(tname, insts, 2387, 2406);
dbms_output.put_line(tname);
END;
/ |
| |
| 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); |
| TBD |
| |
| DELETE_SEGMENT_DIRECTIVE |
| Delete an ADDM directive |
dbms_addm.delete_segment_directive(task_name IN VARCHAR2, dir_name IN VARCHAR2); |
| TBD |
| |
| DELETE_SQL_DIRECTIVE |
| Delete an ADDM directive |
dbms_addm.delete_sql_directive(task_name IN VARCHAR2, dir_name IN VARCHAR2); |
| TBD |
| |
GET_ASH_QUERY (11.2.0.1)  |
| Retrieves the SQL text of an ASH query for a finding |
dbms_addm.get_ash_query(task_name IN VARCHAR2, finding_id IN NUMBER)
RETURN VARCHAR2; |
-- first run the ANALYZE_DB demo above over a range that has "real" work
SELECT finding_id, finding_name, message
FROM dba_advisor_findings
WHERE task_name = 'UW_TASK1';
SELECT dbms_addm.get_ash_query('UW_TASK1', 1) FROM dual;
col task_name format a25
col message format a50
SELECT task_name, finding_id, finding_name, message
FROM dba_advisor_findings;
SELECT dbms_addm.get_ash_query('ADDM:2495529197_1_25', 1) FROM dual;
SELECT dbms_addm.get_ash_query('ADDM:2495529197_1_3', 1) FROM dual; |
| |
| 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 NUMBER); |
| 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 |
