生成Oracle AWR（Automatic Workload Repository）报告的详细步骤

---

Oracle AWR报告简介

Oracle AWR报告（Automatic Workload Repository）是Oracle数据库性能诊断及优化的重要工具之一。AWR报告包含了关于数据库性能的诊断和调优信息，包括系统资源利用率、SQL语句执行情况、等待事件及其消耗，以及其他诊断信息。

生成 Oracle AWR（Automatic Workload Repository）报告的详细步骤

生成 Oracle AWR（Automatic Workload Repository）报告的详细步骤如下：

1、连接到 Oracle 数据库。可以使用 SQL*Plus 或者其他支持 Oracle 连接的工具。

比如用PL/SQL连接到数据库。

2、确认你有足够的权限来生成 AWR 报告。通常需要具有 SYSDBA 或 SYSOPER 角色的权限。

3、查看AWR功能是否已启用

执行以下语句来确认 AWR 功能是否已启用：

SELECT value FROM v$parameter WHERE name = 'statistics_level';

如果 value 的值是 TYPICAL 或 ALL，则 AWR 功能已启用。如果 value 的值是 BASIC，则需要修改为 TYPICAL 或 ALL。

4、创建 AWR 快照

4.1、执行以下语句来创建 AWR 快照：

命令行执行下面的语句：

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

或者SQL窗口执行下面的SQL：

CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

这将在数据库中创建一个 AWR 快照，用于生成报告。

4.2、查看所有snapshot涉及的表和视图

SELECT DISTINCT 'select ''' || owner || '.' || object_name ||
                ''' as object_name, count(*) as sl from ' || owner || '.' ||
                object_name || ' union all'
               ,owner
               ,object_name
               ,object_type
  FROM dba_objects
 WHERE object_name LIKE '%SNAPSHOT%'
   AND object_type IN ('VIEW'
                      ,'TABLE')
 ORDER BY 1;

把第一列的结果复制出来，另外开启一个SQL窗口，把数据最后一个union all去掉，查询如下：

select 'DBSNMP.MGMT_SNAPSHOT' as object_name, count(*) as sl from DBSNMP.MGMT_SNAPSHOT union all
select 'DBSNMP.MGMT_SNAPSHOT_SQL' as object_name, count(*) as sl from DBSNMP.MGMT_SNAPSHOT_SQL union all
select 'MDSYS.SDO_DATUMS_OLD_SNAPSHOT' as object_name, count(*) as sl from MDSYS.SDO_DATUMS_OLD_SNAPSHOT union all
select 'MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT' as object_name, count(*) as sl from MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT union all
select 'MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT' as object_name, count(*) as sl from MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT union all
select 'SYS.ALL_REGISTERED_SNAPSHOTS' as object_name, count(*) as sl from SYS.ALL_REGISTERED_SNAPSHOTS union all
select 'SYS.ALL_SNAPSHOTS' as object_name, count(*) as sl from SYS.ALL_SNAPSHOTS union all
select 'SYS.ALL_SNAPSHOT_LOGS' as object_name, count(*) as sl from SYS.ALL_SNAPSHOT_LOGS union all
select 'SYS.DBA_HIST_ASH_SNAPSHOT' as object_name, count(*) as sl from SYS.DBA_HIST_ASH_SNAPSHOT union all
select 'SYS.DBA_HIST_SNAPSHOT' as object_name, count(*) as sl from SYS.DBA_HIST_SNAPSHOT union all
select 'SYS.DBA_REGISTERED_SNAPSHOTS' as object_name, count(*) as sl from SYS.DBA_REGISTERED_SNAPSHOTS union all
select 'SYS.DBA_REGISTERED_SNAPSHOT_GROUPS' as object_name, count(*) as sl from SYS.DBA_REGISTERED_SNAPSHOT_GROUPS union all
select 'SYS.DBA_SNAPSHOTS' as object_name, count(*) as sl from SYS.DBA_SNAPSHOTS union all
select 'SYS.DBA_SNAPSHOT_LOGS' as object_name, count(*) as sl from SYS.DBA_SNAPSHOT_LOGS union all
select 'SYS.GV_$ASM_ACFSSNAPSHOTS' as object_name, count(*) as sl from SYS.GV_$ASM_ACFSSNAPSHOTS union all
select 'SYS.USER_REGISTERED_SNAPSHOTS' as object_name, count(*) as sl from SYS.USER_REGISTERED_SNAPSHOTS union all
select 'SYS.USER_SNAPSHOTS' as object_name, count(*) as sl from SYS.USER_SNAPSHOTS union all
select 'SYS.USER_SNAPSHOT_LOGS' as object_name, count(*) as sl from SYS.USER_SNAPSHOT_LOGS union all
select 'SYS.V_$ASM_ACFSSNAPSHOTS' as object_name, count(*) as sl from SYS.V_$ASM_ACFSSNAPSHOTS union all
select 'SYS.WRM$_SNAPSHOT' as object_name, count(*) as sl from SYS.WRM$_SNAPSHOT union all
select 'SYS.WRM$_SNAPSHOT_DETAILS' as object_name, count(*) as sl from SYS.WRM$_SNAPSHOT_DETAILS union all
select 'SYSMAN.MGMT$ECM_CURRENT_SNAPSHOTS' as object_name, count(*) as sl from SYSMAN.MGMT$ECM_CURRENT_SNAPSHOTS union all
select 'SYSMAN.MGMT$ECM_VISIBLE_SNAPSHOTS' as object_name, count(*) as sl from SYSMAN.MGMT$ECM_VISIBLE_SNAPSHOTS union all
select 'SYSMAN.MGMT_COMP_SNAPSHOT_TO_STEP_MAP' as object_name, count(*) as sl from SYSMAN.MGMT_COMP_SNAPSHOT_TO_STEP_MAP union all
select 'SYSMAN.MGMT_ECM_CSA_SNAPSHOT_INFO' as object_name, count(*) as sl from SYSMAN.MGMT_ECM_CSA_SNAPSHOT_INFO union all
select 'SYSMAN.MGMT_ECM_GEN_SNAPSHOT' as object_name, count(*) as sl from SYSMAN.MGMT_ECM_GEN_SNAPSHOT union all
select 'SYSMAN.MGMT_ECM_SNAPSHOT' as object_name, count(*) as sl from SYSMAN.MGMT_ECM_SNAPSHOT union all
select 'SYSMAN.MGMT_ECM_SNAPSHOT_MD_COLUMNS' as object_name, count(*) as sl from SYSMAN.MGMT_ECM_SNAPSHOT_MD_COLUMNS union all
select 'SYSMAN.MGMT_ECM_SNAPSHOT_MD_TABLES' as object_name, count(*) as sl from SYSMAN.MGMT_ECM_SNAPSHOT_MD_TABLES union all
select 'SYSMAN.MGMT_ECM_SNAPSHOT_METADATA' as object_name, count(*) as sl from SYSMAN.MGMT_ECM_SNAPSHOT_METADATA union all
select 'SYSMAN.MGMT_SNAPSHOT_METRIC_MAP' as object_name, count(*) as sl from SYSMAN.MGMT_SNAPSHOT_METRIC_MAP
;

查询结果：

OBJECT_NAME	SL
DBSNMP.MGMT_SNAPSHOT	0
DBSNMP.MGMT_SNAPSHOT_SQL	0
MDSYS.SDO_DATUMS_OLD_SNAPSHOT	118
MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT	47
MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT	42
SYS.ALL_REGISTERED_SNAPSHOTS	1
SYS.ALL_SNAPSHOTS	1
SYS.ALL_SNAPSHOT_LOGS	0
SYS.DBA_HIST_ASH_SNAPSHOT	88
SYS.DBA_HIST_SNAPSHOT	88
SYS.DBA_REGISTERED_SNAPSHOTS	1
SYS.DBA_REGISTERED_SNAPSHOT_GROUPS	0
SYS.DBA_SNAPSHOTS	1
SYS.DBA_SNAPSHOT_LOGS	0
SYS.GV_$ASM_ACFSSNAPSHOTS	0
SYS.USER_REGISTERED_SNAPSHOTS	0
SYS.USER_SNAPSHOTS	0
SYS.USER_SNAPSHOT_LOGS	0
SYS.V_$ASM_ACFSSNAPSHOTS	0
SYS.WRM$_SNAPSHOT	88
SYS.WRM$_SNAPSHOT_DETAILS	6424
SYSMAN.MGMT$ECM_CURRENT_SNAPSHOTS	0
SYSMAN.MGMT$ECM_VISIBLE_SNAPSHOTS	0
SYSMAN.MGMT_COMP_SNAPSHOT_TO_STEP_MAP	0
SYSMAN.MGMT_ECM_CSA_SNAPSHOT_INFO	0
SYSMAN.MGMT_ECM_GEN_SNAPSHOT	0
SYSMAN.MGMT_ECM_SNAPSHOT	0
SYSMAN.MGMT_ECM_SNAPSHOT_MD_COLUMNS	839
SYSMAN.MGMT_ECM_SNAPSHOT_MD_TABLES	122
SYSMAN.MGMT_ECM_SNAPSHOT_METADATA	53
SYSMAN.MGMT_SNAPSHOT_METRIC_MAP	221

可以看到，SYS.WRM$_SNAPSHOT_DETAILS 这个视图数量比较大，尝试查一下该视图的数据，可以发现它把snap_id和table_id进行了关联。

4.3、查看生成的awr快照视图详情

执行SQL：

SELECT o.object_name
      ,s.snap_id
      ,s.instance_number
      ,s.table_id
      ,to_char(s.begin_time
              ,'yyyymmdd hh24:mi:ss') AS begin_time
      ,to_char(s.end_time
              ,'yyyymmdd hh24:mi:ss') AS end_time
  FROM sys.wrm$_snapshot_details s
  LEFT JOIN dba_objects o
    ON s.table_id = o.object_id
 ORDER BY 2 DESC;

最新的 snap_id（1012）就是刚生成的快照。

5、等待一段时间，以便收集足够的性能数据。通常建议等待至少 1 小时，以便收集足够的样本数据。

6、使用以下语句生成 AWR 报告：

以管理员权限在命令行窗口，执行下面的句子：

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

这将执行 AWR 报告生成脚本，并提示你输入开始和结束时间范围以生成报告。

7、按照提示输入开始和结束时间范围，并选择报告格式（文本或 HTML）。

8、等待一段时间，直到报告生成完成。

9、查看生成的 AWR 报告。报告将显示数据库的性能统计信息、等待事件、SQL 语句、I/O 统计等详细信息，以帮助你分析和调优数据库性能。

请注意，以上步骤仅描述了常规的 AWR 报告生成步骤。在特定的环境中，可能还需要进行其他配置和设置，例如设置 AWR 快照的保留时间、修改快照的频率等。在实际操作中，请参考相关的 Oracle 文档和手册以获取更详细的信息。