| 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); |
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_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,
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 |
