50-Oracle awr报告生成-实操

一、AWR技术原理与核心架构

 二、版本演进与功能增强

三、各版本操作指南与脚本

​基础配置管理

-- 查看当前配置
SELECT snap_interval, retention 
FROM dba_hist_wr_control;-- 调整快照间隔(20分钟)和保留期(30天)
BEGINdbms_workload_repository.modify_snapshot_settings(interval => 20, retention => 30 * 24 * 60);
END;
/

生成AWR报告通用步骤​

@awrrpt.sql        -- 单实例
@awrgrpt.sql       -- RAC全局报告
@awrrpti.sql       -- 指定实例报告(RAC)

运维脚本手动版​

​1. 手动创建快照（准备测试前后）​

-- 压测前
EXEC dbms_workload_repository.create_snapshot;-- 压测后
EXEC dbms_workload_repository.create_snapshot;

 ​2. 基线管理（性能对比基准）​

-- 创建基线
BEGINdbms_workload_repository.create_baseline(start_snap_id => 100,end_snap_id => 110,baseline_name => 'PEAK_LOAD_BASELINE');
END;
/-- 删除基线
EXEC dbms_workload_repository.drop_baseline('PEAK_LOAD_BASELINE');

​3. Shell自动化脚本（Linux环境）​ 

#!/bin/bash
ORACLE_SID=ORCLCDB --注意自己的ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/19.3/dbhome_1
REPORT_FILE="awr_$(date +%Y%m%d).html"sqlplus -s / as sysdba <<EOF
set pages 0 lin 200 feed off
spool $REPORT_FILE
SELECT dbms_workload_repository.awr_report_html((SELECT dbid FROM v$database),(SELECT instance_number FROM v$instance),(SELECT MAX(snap_id)-1 FROM dba_hist_snapshot),(SELECT MAX(snap_id) FROM dba_hist_snapshot)
) FROM dual;
spool off
EOF
echo "Report generated: $REPORT_FILE" 

4. 单节点和RAC环境实操，输入步骤一样，使用的sql脚本不同。 

[root@OL97 ~]# su - oracle
[oracle@OL97 ~]$ sqlplus / as sysdbaSQL*Plus: Release 23.0.0.0.0 - Production on Thu Jun 19 21:19:57 2025
Version 23.8.0.25.04Copyright (c) 1982, 2025, Oracle.  All rights reserved.Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04SYS@FREE> @?/rdbms/admin/awrrpt.sqlSpecify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active reportEnter value for report_type:html
Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------1475650002     FREE                        1 FREE           CDB$ROOTInstances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
* 1475650002     1      FREE         FREE         OL97Using 1475650002 for database Id
Using          1 for instance numberSpecify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.Enter value for num_days:1179  19 Jun 2025 10:29     1180  19 Jun 2025 10:59     1181  19 Jun 2025 11:29     1182  19 Jun 2025 11:59     1183  19 Jun 2025 12:29     1184  19 Jun 2025 12:59     1185  19 Jun 2025 13:29     1186  19 Jun 2025 13:59     1187  19 Jun 2025 14:29     1188  19 Jun 2025 14:59     1189  19 Jun 2025 15:29     1190  19 Jun 2025 15:59     1191  19 Jun 2025 16:29     1192  19 Jun 2025 16:59     1193  19 Jun 2025 17:29     1194  19 Jun 2025 17:59     1195  19 Jun 2025 18:29     1196  19 Jun 2025 18:59     1197  19 Jun 2025 19:29     1198  19 Jun 2025 19:59     1199  19 Jun 2025 20:29     1200  19 Jun 2025 20:59     1Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:185
Begin Snapshot Id specified: 185Enter value for end_snap: 192
End   Snapshot Id specified: 192Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_185_192.html.  To use this name,
press <return> to continue, otherwise enter an alternative.Enter value for report_name:23ai-free-20250619~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Additional Information----------------------Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.</pre>
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
<a class="awr" name="99991"></a>
<h3 class="awr">Report Footnotes</h3>
<ul>
<li class="awr"> This section contains footnotes used by this report</li>
</ul>
<table border="0" class="tdiff" summary="Report Footnotes"><tr></tr>
</table>
<br /><a class="awr" name="1">[1]. The elapsed time statistic actually shows the measured database time.</a><br />
<br />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
End of Report
</body></html>
Report written to 23ai-free-20250619.html
SYS@FREE>

四、性能分析关键

五、最佳实践与避坑

--
SELECT space_usage_kbytes/1024 MB_used 
FROM v$sysaux_occupants 
WHERE occupant_name='SM/AWR';
--
SELECT space_usage_kbytes/1024 MB_used2  FROM v$sysaux_occupants3  WHERE occupant_name='SM/AWR';MB_USED
----------472.25SYS@FREE>

• 快照策略优化 

• 报告关键指标缺失​