1. 采集快照
begin
dbms_workload_repository.create_snapshot('TYPICAL');
end;
/
2. 创建任务
DECLARE
task_name VARCHAR2(30) := 'task_name';
task_desc VARCHAR2(30) := 'ADDM demo';
task_id NUMBER;
BEGIN
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 17128);
dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 17129);
dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 2);
dbms_advisor.set_task_parameter(task_name, 'DB_ID', 228233439);
dbms_advisor.execute_task(task_name);
END
这里是RAC环境的节点2,DBID可以通过select dbid from v$database;查询得到。
3.删除任务
declare
task_name VARCHAR2(30) := 'task_name';
begin
dbms_advisor.delete_task(task_name);
end;
/
4 查询优化结果,结果可能会很多,可以通过spool的方式到某个文件
具体查询的语句如下
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
COLUMN get_clob FORMAT a80
SELECT dbms_advisor.get_task_report('task_name', 'TEXT', 'ALL') FROM DUAL;
5 整个执行的脚本
spool on
spool addm.txt
declare
task_name VARCHAR2(30) := 'DEMO';
begin
dbms_advisor.delete_task(task_name);
end;
/
DECLARE
task_name VARCHAR2(30) := 'DEMO';
task_desc VARCHAR2(30) := 'ADDM DEMO';
task_id NUMBER;
BEGIN
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 17128);
dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 17129);
dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 2);
dbms_advisor.set_task_parameter(task_name, 'DB_ID', 228233439);
dbms_advisor.execute_task(task_name);
END;
/
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
COLUMN get_clob FORMAT a80
SELECT dbms_advisor.get_task_report('DEMO', 'TEXT', 'ALL') FROM DUAL;
spool off