快速收集ADDM性能报告的脚本

来源：互联网发布：美工岗位招聘要求编辑：程序博客网时间：2024/04/30 03:59
declarebegin_time_date date := to_date('20141109 15:00:00', 'yyyymmdd hh24:mi:ss') ;end_time_date   date := to_date('20141109 19:00:00', 'yyyymmdd hh24:mi:ss') ;begin  dbms_output.put_line('set linesize 100');  dbms_output.put_line('set pagesize 50000');  dbms_output.put_line('set head off');  dbms_output.put_line('serveroutput ON SIZE UNLIMITED ');  dbms_output.put_line('spool addm.txt');  for r in (select *              from sys.WRM$_SNAPSHOT t             where t.begin_interval_time >=begin_time_date                                 and t.begin_interval_time<= end_time_date              order by t.snap_id, t.instance_number)     loop    dbms_output.put_line('declare     id number ;    name varchar2(100) ;    v_dec varchar2(200) :='||'''addm_'||to_char(r.snap_id)||'_'||to_char(r.snap_id+1)||''';');    dbms_output.put_line( 'begin ') ;    dbms_output.put_line( 'dbms_advisor.create_task(''ADDM'',id,name,v_dec,null);');    dbms_output.put_line( 'dbms_advisor.set_task_parameter(name, '||'''START_SNAPSHOT'','||r.snap_id||');') ;    dbms_output.put_line( 'dbms_advisor.set_task_parameter(name, ' ||'''END_SNAPSHOT'','||to_char(r.snap_id+1)||');') ;    dbms_output.put_line( 'dbms_advisor.set_task_parameter(name, '||'''INSTANCE'','||r.instance_number||');') ;    dbms_output.put_line( 'dbms_advisor.set_task_parameter(name, '||'''DB_ID'','||r.dbid||');') ;    dbms_output.put_line( 'dbms_advisor.execute_task(name); ') ;    dbms_output.put_line('dbms_output.put_line('||'''#spool addm_' || r.instance_number || '_' ||                         r.snap_id || '_' || (r.snap_id + 1) || '.txt'');');    dbms_output.put_line('dbms_output.put_line('||'''#select dbms_advisor.get_task_report('''''''||'||name||'''''','||'''''TEXT'''',''''TYPICAL'''') from dual ;'');');                                                                                    dbms_output.put_line('dbms_output.put_line(''#spool off'');');    dbms_output.put_line('end ;     /');  end loop;  dbms_output.put_line('spool off');end;--clearselect 'exec dbms_advisor.delete_task('''||x.task_name||''');'from dba_advisor_tasks x
0 0