Oracle AWR

--AWR全称是Automatic Workload Repository。
--AWR默认60分钟采集一次统计数据，保存一周，然后删除。统计数据保存在数据库中。
--为了正确收集统计数据，STATISTICS_LEVEL设置为TYPICAL(默认)或ALL。
--11g使用名为GATHER_STATS_JOB的调度作业收集AWR统计信息。创建Oracle数据库时，就会自动创建并激活这项作业。
--AWR由许多表组成，这些表属于SYS模式，通常保存在SYSAUX表空间。
--所有AWR表名都以标识符“WR”开始：元数据(WRM$)、历史/可变数据(WRH$、WRR$和WRI$)和和顾问(advisor)功能相关的AWR表(WRI$)。
--可以对AWR仓库进行查询的DBA视图，以DBA_HIST开头。

--取awr报告
--1.生成单实例 AWR 报告：
@?/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
--2.生成 Oracle RAC AWR 报告：
@?/rdbms/admin/awrgrpt.sql
--3.生成 RAC 环境中特定数据库实例的 AWR 报告：
@?/rdbms/admin/awrrpti.sql
--4.生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法：
@?/rdbms/admin/awrgrpti.sql
--5.生成 SQL 语句的 AWR 报告：
@?/rdbms/admin/awrsqrpt.sql
--6.生成特定数据库实例上（生成 AWR 时段对比报告）某个 SQL 语句的 AWR 报告：
@?/rdbms/admin/awrsqrpi.sql
--7.生成单实例 AWR 时段对比报告
@?/rdbms/admin/awrddrpt.sql
--8.生成 Oracle RAC AWR 时段对比报告
@?/rdbms/admin/awrgdrpt.sql
--9.生成特定数据库实例的 AWR 时段对比报告
@?/rdbms/admin/awrddrpi.sql
--10.生成 Oracle RAC 环境下特定（多个）数据库实例的 AWR 时段对比报告
@?/rdbms/admin/awrgdrpi.sql

--AWR信息提取：
--使用awrextr脚本将AWR性能数据导出，可以用以留错或者异地分析
@?/rdbms/admin/awrextr.sql

--AWR信息加载：
--通过awrload，可以将导出的AWR性能数据导入到其他数据库中，便于集中和分析
@?/rdbms/admin/awrload.sql

--将AWR数据导出并迁移到其它数据库以便于以后分析
exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);
--迁移AWR数据文件到其他数据库
exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');
--把AWR数据转移到SYS模式中
exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

--查询awr保存策略
COL DBID FOR 999999999999
COL SNAP_INTERVAL FORMAT A20
COL RETENTION FORMAT A20
COL TOPNSQL FOR A10
SELECT * FROM DBA_HIST_WR_CONTROL;

--调整AWR产生snapshot的频率和保留策略,如将收集间隔时间改为30分钟一次,并且保留7天时间(单位都是分钟)
exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>7*24*60);
--Or(10分钟取一次)
BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
 interval  =>  10,
 retention =>  7*24*60);
 END;
 /

--关闭AWR，把interval设为0则关闭自动捕捉快照
exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

--查看所有快照
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
--Or
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY 1 DESC;
--Or
SELECT SNAP_ID, DBID,INSTANCE_NUMBER,STARTUP_TIME,
       BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,
       FLUSH_ELAPSED,SNAP_LEVEL,ERROR_COUNT
FROM WRM$_SNAPSHOT
WHERE STATUS = 0;
--查看快照历史
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY;

--手工创建一个快照
exec dbms_workload_repository.create_snapshot();

--查询确认
COL BASELINE_NAME FOR A32;
SELECT DBID,BASELINE_NAME,START_SNAP_ID,END_SNAP_ID FROM DBA_HIST_BASELINE; 
--创建基线 
exec dbms_workload_repository.create_baseline(59806,59809,'20160726_07_11');
--创建baseline，保存这些数据用于将来分析和比较
exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');
--查询确认
SELECT DBID,BASELINE_NAME,START_SNAP_ID,END_SNAP_ID FROM DBA_HIST_BASELINE;

--修改基线名称
BEGIN
  DBMS_WORKLOAD_REPOSITORY.rename_baseline(
    old_baseline_name => 'old_name',
    new_baseline_name => 'new_name');
END;
/

--删除baseline
exec dbms_workload_repository.drop_baseline('20110112',true);
exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'apply_interest_1', cascade => FALSE);

--删除快照
--查询dbid及保留周期
select dbid, retention fromdba_hist_wr_control;
--根据dbid查询快照id号
select snap_id from dba_hist_snapshot;
select min(snap_id), max(snap_id) from dba_hist_snapshot where dbid = 879543530;

--dbms_workload_repository
--可以删除本地和其他数据库的快照，可以选择不同的快照来进行删除
--不指定DBID，默认当前实例DBID
exec dbms_workload_repository.drop_snapshot_range(61263,61274);
--指定DBID
exec dbms_workload_repository.drop_snapshot_range(7417,7429,3990839260);

--dbms_swrf_internal(对导入的快照可删除)
--只能对其他数据库的快照来进行操作，会把整个快照unregister掉
exec dbms_swrf_internal.unregister_database(877621333);

--Stackpack(/AWR)的执行需要三个条件：
--1、connect / create session权限
--2、select any dictionary权限
--3、execute on any dbms_workload_repository权限

 

--err
--AWR报告时出现了如下报错：ORA-06502: PL/SQL: numeric or value error: character string buffer too small
--根据上面的分析已经基本清楚，导致AWR报告生成失败的原因就是系统中存在一个特别长的sql
--WRH$_SQLTEXT基表的结构如下：

SQL>desc WRH$_SQLTEXT
Name                     Null              Type
----------------------- -------- ----------------------------
SNAP_ID                                    NUMBER
DBID                     NOT NULL    NUMBER
SQL_ID                 NOT NULL    VARCHAR2(13)
SQL_TEXT                                 CLOB
COMMAND_TYPE                     NUMBER
REF_COUNT                             NUMBER

--保存sql文本的字段为CLOB字段，所以可以采用下面的方法对WRH$_SQLTEXT.SQL_TEXT字段进行截断：
update WRH$_SQLTEXT set sql_text = dbms_lob.substr(sql_text,3000);
commit;
--执行完上述操作后,成功生成了AWR报告,但是,过长的sql是被截断的
--如果要获得完整的sql,可以根据AWR报告中的sqlid到系统中进行查找
--or-导出格式用TXT，即可导出