可以使用如下的脚本来定制addm，能够根据快照生成指定的addm报告。
脚本的内容如下：
相比于定制awr,ash要略微有一些难度。对于addm目前只提供了文本格式的内容，所以格式的处理上没什么特别的地方，不用考虑html格式的兼容性。
TASK_NAME=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID set pagesize 0 feedback off verify off heading on echo off
select 'ADDM_TASK_'||i.instance_name||'_'||$1||'_'||$2 from v\\$database d,
v\\$instance i;
exit;
END`
if [ -z "$TASK_NAME" ]; then
echo "no addm task exists, please check again"
exit 0
else
echo '*******************************************'
echo " $TASK_NAME "
echo '*******************************************'
fi
sqlplus -silent $DB_CONN_STR@$SH_DB_SID declare
task_name varchar2(200);
begin
task_name:='$TASK_NAME';
dbms_output.put_line(task_name);
dbms_advisor.create_task('ADDM',task_name);
dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', $1);
dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', $2);
--dbms_advisor.set_task_parameter(task_name, 'INSTANCE', :inst_num);
--dbms_advisor.set_task_parameter(task_name, 'DB_ID', :dbid);
dbms_advisor.execute_task(task_name);
end;
/
prompt
prompt Generating the ADDM report for this analysis ...
prompt
prompt
set long 1000000 pagesize 0 longchunksize 1000
column get_clob format a80
select dbms_advisor.get_task_report('$TASK_NAME', 'TEXT', 'TYPICAL')
from sys.dual;
END
addm相比于awr,ash需要在数据库中创建一个task，使用dbms_advisor包来实现。
如果在备份库只读的情况下执行addm，就会报错。
begin
*
ERROR at line 1:
ORA-16000: database open for read-only access
ORA-06512: at "SYS.PRVT_ADVISOR", line 4869
ORA-06512: at "SYS.DBMS_ADVISOR", line 103
ORA-06512: at line 12
可以通过一个实例来说明。
在生产中我们查看系统负载，有个时间段负载很高。110分钟的时间内，负载高达5862%
23973 ** 18-DEC-14 01.00.10.807 AM 23984 ** 18-DEC-14 02.50.16.550 AM 110.096 1412.37 1282%
23979 ** 18-DEC-14 02.00.13.664 AM 23990 ** 18-DEC-14 03.50.19.853 AM 110.103 1362.54 1237%
23985 ** 18-DEC-14 03.00.17.345 AM 23996 ** 18-DEC-14 04.50.23.456 AM 110.102 1310.3 1190%
23991 ** 18-DEC-14 04.00.20.532 AM 24002 ** 18-DEC-14 05.50.27.041 AM 110.108 1392.96 1265%
23997 ** 18-DEC-14 05.00.24.104 AM 24008 ** 18-DEC-14 06.50.32.136 AM 110.134 1629.76 1479%
24003 ** 18-DEC-14 06.00.27.751 AM 24014 ** 18-DEC-14 07.50.36.151 AM 110.140 3341.5 3033%
24009 ** 18-DEC-14 07.00.33.002 AM 24020 ** 18-DEC-14 08.50.39.371 AM 110.106 2766.72 2512%
24015 ** 18-DEC-14 08.00.36.792 AM 24026 ** 18-DEC-14 09.50.42.965 AM 110.103 2470.97 2244%
24021 ** 18-DEC-14 09.00.40.041 AM 24032 ** 18-DEC-14 10.50.47.631 AM 110.127 5018.62 4557%
24027 ** 18-DEC-14 10.00.43.922 AM 24038 ** 18-DEC-14 11.50.51.813 AM 110.132 6456.99 5862%
24033 ** 18-DEC-14 11.00.48.581 AM 24044 ** 18-DEC-14 12.50.55.064 PM 110.108 5888.03 5347%
24039 ** 18-DEC-14 12.00.52.341 PM 24050 ** 18-DEC-14 01.50.59.248 PM 110.115 5476.78 4973%
24045 ** 18-DEC-14 01.00.55.730 PM 24056 ** 18-DEC-14 02.50.02.623 PM 109.115 4371.67 4006%
24051 ** 18-DEC-14 02.01.00.065 PM 24062 ** 18-DEC-14 03.50.05.943 PM 109.098 3828.68 3509%
23922 ** 17-DEC-14 04.30.43.677 PM 24064 ** 18-DEC-14 04.10.07.337 PM 1419.394 40205.2 2832%
23922 ** 17-DEC-14 04.30.43.677 PM 24064 ** 18-DEC-14 04.10.07.337 PM 1419.394 40205.2 2832%
我们来查看一下在这个时间段对应的快照情况
> ksh showsnap.sh 20141218 10 11
Current Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NUM INST_NAME
---------- --------- ---------- ----------------
3100077577 CUST01 1 CUST01
DB_NAME SNAP_ID SNAPDAT LVL
--------- ---------- ----------------- ----------
CUST01 24021 18 Dec 2014 09:00 1
24022 18 Dec 2014 09:10 1
24023 18 Dec 2014 09:20 1
24024 18 Dec 2014 09:30 1
24025 18 Dec 2014 09:40 1
24026 18 Dec 2014 09:50 1
24027 18 Dec 2014 10:00 1
24028 18 Dec 2014 10:10 1
24029 18 Dec 2014 10:20 1
24030 18 Dec 2014 10:30 1
24031 18 Dec 2014 10:40 1
24032 18 Dec 2014 10:50 1
24033 18 Dec 2014 11:00 1
24034 18 Dec 2014 11:10 1
24035 18 Dec 2014 11:20 1
24036 18 Dec 2014 11:30 1
24037 18 Dec 2014 11:40 1
24038 18 Dec 2014 11:50 1
24039 18 Dec 2014 12:00 1
24040 18 Dec 2014 12:10 1
24041 18 Dec 2014 12:20 1
24042 18 Dec 2014 12:30 1
24043 18 Dec 2014 12:40 1
24044 18 Dec 2014 12:50 1
ksh genaddmrpt.sh 24030 24031
*******************************************
ADDM_TASK_XXXXX_24030_24031
*******************************************
PL/SQL procedure successfully completed.
Generating the ADDM report for this analysis ...
ADDM Report for Task 'ADDM_TASK_XXXXX_24030_24031'
----------------------------------------------------
Analysis Period
---------------
AWR snapshot range from 24030 to 24031.
Time period starts at Dec 2014 10:30 AM
Time period ends at Dec 2014 10:40 AM
Analysis Target
---------------
Database 'XXXXX' with DB ID 3100077577 .
Database version 11.2.0.2.0.
Analysis was requested for all instances, but ADDM analyzed instance xxxx,
numbered 1 and hosted at xxxxx.
See the "Additional Information" section for more information on the requested
instances.
。。。。