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 的值: <enter使用默认文件名> 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值大大下降。