1、手动创建Snapshots

exec dbms_workload_repository.create_snapshot();  
--或者 
BEGIN  
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();  
END; 

2、手动删除Snapshots

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 6770,high_snap_id => 6774,dbid => 4059638244);  
OR  
BEGIN  
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 6770,  
                                               high_snap_id => 6774,  
                                               dbid => 4059638244);  
END;  

这种方法删除是通过delete的会产生大量redo，建议直接删除相关分区

3、修改快照设置

------查看快照策略  
set linesize 1000 pagesize 500  
col SNAP_INTERVAL for a20  
col RETENTION for a30  
select * from dba_hist_wr_control;  
  
------生成修改快照策略的语句  
set linesize 1000 pagesize 5000  
col cmd for a200  
SELECT 'exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 16*24*60,INTERVAL => 30,dbid => ' || DBID ||  
       ' );' cmd  
  FROM dba_hist_wr_control;  
  
------修改快照策略  
BEGIN  
   DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 18*24*60,  
                                                     INTERVAL => 30,  
                                                     dbid => 1328382724);  
 END;  
 /  

4、AWR生成常用脚本

--生成单实例 AWR 报告：  
@$ORACLE_HOME/rdbms/admin/awrrpt.sql  
  
--生成 Oracle RAC AWR 报告：  
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql  
  
--生成 RAC 环境中特定数据库实例的 AWR 报告:  
@$ORACLE_HOME/rdbms/admin/awrrpti.sql  
  
--生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法：  
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql  
  
--生成 SQL 语句的 AWR 报告：  
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql  
  
--生成特定数据库实例上某个 SQL 语句的 AWR 报告：  
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql  
  
--生成单实例 AWR 时段对比报告  
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql  
  
--生成 Oracle RAC AWR 时段对比报告  
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql  
  
--生成特定数据库实例的 AWR 时段对比报告  
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql  
  
--生成 Oracle RAC 环境下特定（多个）数据库实例的 AWR 时段对比报告  
@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql  

5、快速收集AWR/ASH/ADDM脚本

-------快速收集AWR  
declare      
begin_time_date date := to_date('20141130 23:00:00', 'yyyymmdd hh24:mi:ss') ;      
end_time_date   date := to_date('20141203 18:00:00', 'yyyymmdd hh24:mi:ss') ;      
      
      
begin      
  dbms_output.put_line('set linesize 2000');      
  dbms_output.put_line('set pagesize 50000');      
  dbms_output.put_line('set head off');      
  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    
               and t.dbid=3628304518    
             order by t.snap_id, t.instance_number)      
        
   loop      
        
    dbms_output.put_line('spool awrrpt_' || r.instance_number || '_' ||      
                         to_char(r.begin_interval_time+1/24,'mmdd_hh24mi') || '_' ||to_char(r.end_interval_time+1/24,'mmdd_hh24mi') || '.html');      
    dbms_output.put_line('select *       
from table      
(dbms_workload_repository.awr_report_html(' ||      
                         r.dbid || ',' || r.instance_number || ',' ||      
                         r.snap_id || ',' || (r.snap_id + 1) || ',0));');      
    dbms_output.put_line('spool off');      
  end loop;      
end;      
  
  
  
  
  
---------快速收集ASH  
declare    
  begin_time_date  date := to_date('20141130 23:00:00',    
                                   'yyyymmdd hh24:mi:ss');    
  end_time_date    date := to_date('20141203 13:00:00',    
                                   'yyyymmdd hh24:mi:ss');    
  interval_minutes number := 60;    
begin    
  dbms_output.put_line('set linesize 2000');    
  dbms_output.put_line('set pagesize 50000');    
  dbms_output.put_line('set head off');    
  for r in (select db.DBID,    
                   ins.INSTANCE_NUMBER,    
                   begin_time_date + interval_minutes * (lv - 1) / 1440 begin_time_date,    
                   begin_time_date + interval_minutes * lv / 1440,    
                   'yyyymmdd hh24:mi:ss' end_time_date,    
                   to_char(begin_time_date + 60 * (lv - 1) / 1440,    
                           'yyyymmddhh24mi') begin_time,    
                   to_char(begin_time_date + 60 * lv / 1440,    
                           'yyyymmddhh24miss') end_time    
              from (select /*+ no_merge*/    
                     level lv    
                      from dual    
                    connect by level <= (end_time_date - begin_time_date) * 60 * 24 /    
                               interval_minutes),    
                   gv$instance ins,    
                   v$database db) loop    
    dbms_output.put_line('spool ashrpt_' || to_char(r.instance_number) || '_' ||    
                         r.begin_time || '-' || r.end_time || '.html');    
      
    dbms_output.put_line('select output from table(dbms_workload_repository.ash_report_html( ' ||    
                         r.dbid || ' , ' || r.instance_number || ' , ' ||    
                         'to_date(' || '''' || r.begin_time ||    
                         ''', ''yyyymmddhh24miss''' || ')' || ' , ' ||    
                         'to_date(' || '''' || r.end_time ||    
                         ''', ''yyyymmddhh24miss''' || ')));');    
      
    dbms_output.put_line('spool off ');    
  end loop;    
end;   
  
  
  
  
---------------快速收集ADDM  
declare  
begin_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;  

6、常见问题

6.1、AWR快照数据和ASH数据未正常产生，一般是由于Oracle mman和mmnl进程异常导致的，可以尝试下列方法解决

1)、重启一下mmon的刷新   
alter system set "_swrf_mmon_flush"=false;   
alter system set "_swrf_mmon_flush"=true;   
  
2)、或者，找到mmon进程杀掉，让数据库自动重启一个新的mmon进程，或者重启下实例。   
ps -ef|grep mmon   
kill -9 xxxxxx   

MMON负责执行与AWR相关的任务。包括收集数据库统计信息，收集AWR快照，启动各种自动维护作业JOB，生成超过阀值告警信息。
MMNL负责执行与ASH相关的任务。

6.2、生成awr报告时报错，抛出ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小

ERROR:  
ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919  
ORA-06512: at line 1  

解决方法：

1、截断sql_text  
update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);  
commit;  
2、补丁13527323  
 Download Patch 13527323  
3、 Use following workaround:  
sqlplus / as sysdba  
@$ORACLE_HOME/rdbms/admin/prvsawr.plb  
@$ORACLE_HOME/rdbms/admin/prvtawr.plb  

6.3、SYSAUX表空间中WRH$_ACTIVE_SESSION_HISTORY占用大量空间

SELECT owner,  
  segment_name,  
  partition_name,  
  segment_type,  
  bytes/1024/1024/1024 Size_GB  
FROM dba_segments  
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';  

--修改参数手动出发AWR表的新分区生成  
alter session set "_swrf_test_action" = 72; 

--通过下列脚本获取AWR分区对应的快照ID  
set serveroutput on   
declare   
CURSOR cur_part IS   
SELECT partition_name from dba_tab_partitions   
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';   
  
query1 varchar2(200);   
query2 varchar2(200);   
  
TYPE partrec IS RECORD (snapid number, dbid number);   
TYPE partlist IS TABLE OF partrec;   
  
Outlist partlist;   
begin   
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');   
dbms_output.put_line('--------------------------- ------- ----------');   
  
for part in cur_part loop   
query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';   
execute immediate query1 bulk collect into OutList;   
  
if OutList.count > 0 then   
for i in OutList.first..OutList.last loop   
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);   
end loop;   
end if;   
  
query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';   
execute immediate query2 bulk collect into OutList;   
  
if OutList.count > 0 then   
for i in OutList.first..OutList.last loop   
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);   
dbms_output.put_line('---');   
end loop;   
end if;   
  
end loop;   
end;   
/  

通过DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE包删除快照信息，但是会产生大量的redo

直接删除相关分区更为便捷和迅速恢复业务

alter table WRH$_ACTIVE_SESSION_HISTORY drop partition WRH$_ACTIVE_692952975_0;  

参考文档：

AWR Diagnostic Collection Script (Doc ID 733655.1)

Getting ORA-06502 and ORA-06512 Errors When Running AWR Report in HTML Format (Doc ID 2231743.1)

How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)