addm

方法1
 
@?/rdbms/admin/addmrpt.sql


--第一步：创建测试用的表
drop table t cascade constraints purge;
create table t AS SELECT * FROM dba_objects ;


--第二步:快照
exec dbms_workload_repository.create_snapshot(); 


--第三步：模拟进行
DECLARE
    v_var number;
BEGIN
    FOR n IN 1..10000
    LOOP
        select count(*) into v_var from t;
    END LOOP;
END;
/




---第四步：再次快照
exec dbms_workload_repository.create_snapshot(); 




--第五步：创建一个优化诊断任务并执行
--(1)先获取到两次快照的ID:
select snap_id from (SELECT * FROM dba_hist_snapshot ORDER BY snap_id desc) where rownum <=2;


--(2)创建优化任务，并执行：
DECLARE
    task_name VARCHAR2(30) := 'ADDM_02';
    task_desc VARCHAR2(30) := 'ADDM Feature Test';
    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', 2033);
    dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 2034);
    dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
    dbms_advisor.set_task_parameter(task_name, 'DB_ID', 977587123);
    dbms_advisor.execute_task(task_name);
END;
/


--第六步：查看优化建议结果
--通知函数dbms_advisor.get_task_report可以得到优化建议结果。
set pagesize 0
set linesize 121
spool d:\addm_rpt.html
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
COLUMN get_clob FORMAT a80
SELECT dbms_advisor.get_task_report('ADDM_02', 'TEXT', 'ALL') FROM DUAL;
spool off