自动数据库性能诊断: 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');
