CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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);
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
 
Related Topics
DBMS_ADVISOR
DBMS_SQLTUNE
DBMS_XPLAN
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [222 users online]    © 2010 psoug.org