oracle addm报错,Oracle 性能报告 ADDM

自动数据库性能诊断: ADDM(Automatic Database Diagnostic Monitor)

这种技术是基于AWR技术的，原理就是比较两个AWR快照。

1、获取到两次AWR快照的ID:

SELECT SNAP_ID, TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME, TO_CHAR(STARTUP_TIME,'yyyy-mm-dd hh24:mi:ss') STARTUP_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 1;

会得到类似如下的列表：

SNAP_ID BEGIN_INTERVAL_TIME STARTUP_TIME

---------- ------------------- -------------------

5 2012-11-29 22:27:35 2012-11-29 22:16:28

6 2012-11-30 20:22:57 2012-11-29 22:16:28

7 2012-11-30 21:00:59 2012-11-29 22:16:28

8 2012-12-01 17:22:40 2012-11-29 22:16:28

9 2012-12-01 18:39:16 2012-12-01 18:39:16

10 2012-12-01 19:00:22 2012-12-01 18:39:16

11 2012-12-01 20:00:25 2012-12-01 18:39:16

12 2012-12-08 19:21:19 2012-12-08 19:21:19

13 2012-12-08 19:32:34 2012-12-08 19:21:19

14 2012-12-08 21:00:40 2012-12-08 19:21:19

15 2012-12-09 16:10:05 2012-12-08 19:21:19

2、运行报告生成工具

@?/rdbms/admin/addmrpt

输入 begin_snap 的值: 7

输入 end_snap 的值: 14

输入 report_name 的值:

Report written to addmrpt_1_1199_1200.txt

查看addmrpt_1_1199_1200.txt文件内容即可。

配合其使用的还有一个

SQL Tuning Advisor:

1、创建优化任务并执行:

DECLARE

my_task_name VARCHAR2(30);

my_sqltext CLOB;

BEGIN

my_sqltext := 'select a.table_name, b.object_id from bigtab b, smalltab a';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => 'SCOTT',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'sql_tuning_task_1',

description => '');

dbms_sqltune.Execute_tuning_task (task_name => 'sql_tuning_task_1');

END;

/

2、查看优化建议结果

set long 10000

set linesize 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task_1') FROM DUAL;

删除优化任务:

EXEC DBMS_SQLTUNE.DROP_TUNNING_TASK('sql_tuning_task_1');