观察Oracle数据库性能，oracle自带的awr功能为我们提供了一个近乎完美的解决方案，通过awr特性我们可以随时从数据库提取awr报告。通过报告可以了解一个系统的整个运行情况，生成的报告包括多个部分。
如何获取awr报告？- 通过awrrpt.sql脚本执行
- 通过调用ORALCE的包dbms_workload_repository.awr_report_html/dbms_workload_repository.awr_report_text
获取awr报告通常是采用html形式；打开页面比较友好；也方便查看。
通过方法1 awrrpt.sql脚本执行需要我们提供一些交互信息。操作也比较简单。本文通过shell脚本中来实现自动产生指定时段的awr报告。采用方法2。
1. 产生awr report 的sql脚本 autoawr.sql
SET ECHO OFF; SET VERI OFF; SET FEEDBACK OFF; SET TERMOUT ON; SET HEADING OFF; VARIABLE dbid NUMBER; VARIABLE inst_num NUMBER; VARIABLE bid NUMBER; VARIABLE eid NUMBER; BEGIN SELECT MIN (snap_id) INTO :bid FROM dba_hist_snapshot WHERE TO_CHAR (end_interval_time, 'yyyymmdd') = TO_CHAR (SYSDATE-1, 'yyyymmdd'); SELECT MAX (snap_id) INTO :eid FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time,'yyyymmdd') = TO_CHAR (SYSDATE-1, 'yyyymmdd'); SELECT dbid INTO :dbid FROM v$database; SELECT instance_number INTO :inst_num FROM v$instance; END; / set pagesize 0;set linesize 121;COLUMN report_name NEW_VALUE report_name NOPRINT;SELECT instance_name || '_awrrpt_' || instance_number || '_' || b.timestamp || '.' || 'html' report_name FROM v$instance a, (SELECT TO_CHAR (begin_interval_time, 'yyyymmdd') timestamp FROM dba_hist_snapshot WHERE snap_id = :eid) b; SET TERMOUT OFF; SPOOL $AWR_DIR/&report_name; SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.awr_report_html(:dbid, :inst_num, :bid, :eid)); SPOOL OFF; SET TERMOUT ON; CLEAR COLUMNS SQL; TTITLE OFF; BTITLE OFF; REPFOOTER OFF; UNDEFINE report_name
2. 产生awr report 的shell脚本autoawr.sh
#!/bin/bashif [ -f ~/.bash_profile ]; then source ~/.bash_profile fi export AWR_CMD=/home/oracle/awrexport AWR_DIR=/home/oracle/awr/report RETENTION=31 # ---------------------------------------------- # Generate awr report # ---------------------------------------------- $ORACLE_HOME/bin/sqlplus / as sysdba<