Oracle10g ADDM和STA使用

Oracle10g中就推出了新的优化诊断工具：数据库自动诊断监视工具ADDM和SQL优化建议工具STA。这两个工具的结合使用，能使DBA节省大量优化时间，也大大减少了系统宕机的危险。简单点说，ADDM就是收集相关的统计数据到自动工作量知识库(Automatic Workload Repository AWR)中，而STA则根据这些数据，给出优化建议。

一、ADDM(Automatic Database Diagnostic Monitor)

ADDM提供了一个整体的优化方案。基于一段时间内的AWR snapshots可以执行ADDM 分析，它可以帮我们诊断在这段期间内数据库可能存在的瓶颈。

1、这个工具的使用非常简单，它是不需要安装的。但要求系统参数STATISTICS_LEVEL设置为TYPICAL（推荐）或ALL，系统默认为TYPICAL了。
SQL> ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

2、先获取到两次AWR快照的ID
SQL> select snap_id from (SELECT * FROM dba_hist_snapshot ORDER BY snap_id desc) where rownum <= 2;
   SNAP_ID
----------
      1200
      1199

3、然后创建优化任务，并执行
SQL> @?/rdbms/admin/addmrpt.sql
输入 begin_snap 的值: 1199
输入 end_snap 的值: 1200
输入 report_name 的值:
Report written to addmrpt_1_1199_1200.txt
查看addmrpt_1_1199_1200.txt文件内容即可。
此外，如果是RAC环境下，可以执行addmrpti.sql，这脚本的执行，会多出要求输入DB ID和instance ID的要求。

4、诊断结果分析

第一部分包括一些基础信息，分析时间段、DB和instance ID&名字、主机名字、Oracle版本、快照范围、数据库消耗时间、多少个活动会话。
第二部分就是ADDM发现的问题，并给出的相应建议。
第三部分是关于此次优化建议的一些附加信息。
第四部分是对诊断报告中用到的术语的解释：
DATABASE TIME：是ADDM的度量数据。从用户角度看：这是从向数据库请求开始，消耗在用户等待响应上的全部时间（不包括网络响应时间）；从数据库实例角度看：前台进程消耗在等待一种数据库资源（例如，IO读）、CPU运行和等待CPU释放（队列等待）的总共时间。ADDM分析的目标就尽量降低这个数字，也就是减少实例响应时间。
AVERAGE DATABASE LOAD：所有能统计到的有多少用户（也称为“活动会话”）等待实例响应。这是实例负荷的度量指标。平均数据库负荷是由整个分析计算出来的平均负荷。通过“Database Time”除以分析周期时间得到。例如，分析周期时30分钟，而数据库运行消耗时间是90分钟，那就说明平均有3个用户在等待响应。
IMPACT：每一个找到的问题都有“影响”这一项。“影响”是数据库消耗时间用于处理这个问题的时间不分。假定我们所找到的这个问题完全解决，那么数据库消耗时间就会相应减少“影响”时间。
BENEFIT：每一个找到的问题都“受益”这一项。如果所有建议操作得到实施，ADDM分析估计数据库消耗时间能减少“受益”的全部时间。

二、STA(SQL Tuning Advisor)

ADDM得出了诊断结果，并给出了优化建议。通常90%的性能问题都是由于应用引起的，而应用问题肯定离不开问题语句。那么如何优化这些语句呢，以前靠的是DBA的经验，现在就可以使用STA了。使用STA一定要保证优化器是CBO模式下。

1、创建优化任务并执行
SQL> 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 => 'DEMO',
        scope => 'COMPREHENSIVE',
        time_limit => 60,
        task_name => 'TEST_sql_tuning_task',
        description => 'Task to tune a query on a specified PRODUCT');
    dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
END;
/
DBMS_SQLTUNE.CREATE_TUNING_TASK 就是用来创建优化任务的函数。其中，sql_text是需要优化的语句，user_name是该语句通过哪个用户执行，scope是优化范围（limited或comprehensive），time_limit优化过程的时间限制，task_name优化任务名称，description优化任务描述。dbms_sqltune.Execute_tuning_task是执行优化的函数。

2、查看优化建议结果
SQL> set long 10000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_sql_tuning_task') FROM DUAL;
优化建议结果分为三部分：
第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述；然后是建议的具体内容；最后是相关注意事项。
最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。