通过案例学调优之--Oracle ADDM

通过案例学调优之--OracleADDM应用环境：操作系统：RedHatEL55Oracle：Oracle10gR2一、ADDM简介在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比

通过案例学调优之--Oracle ADDM

应用环境：

操作系统： RedHat EL55

Oracle：   Oracle 10gR2

一、ADDM简介  
        在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快的定位性能问题。但这些工具都只给出一些统计数据，然后再由DBA们根据自己的经验进行优化。
        那能不能由机器自动在统计数据的基础上给出优化建议呢？Oracle10g中就推出了新的优化诊断工具：数据库自动诊断监视工具（Automatic Database Diagnostic Monitor ADDM）和SQL优化建议工具（SQL Tuning Advisor STA）。这两个工具的结合使用，能使DBA节省大量优化时间，也大大减少了系统宕机的危险。简单点说，ADDM就是收集相关的统计数据到自动工作量知识库(Automatic Workload Repository AWR)中，而STA则根据这些数据，给出优化建议。例如，一个系统资源紧张，出现了明显的性能问题，由以往的办法，做个一个statspack快照，等30分钟，再做一次。查看报告，发现’ db file scattered read’事件在top 5 events里面。根据经验，这个事件一般可能是因为缺少索引、统计分析信息不够新、热表都放在一个数据文件上导致IO争用等原因引起的。根据这些经验，我们需要逐个来定位排除，比如查看语句的查询计划、查看user_tables的last_analysed子段，检查热块等等步骤来最后定位出原因，并给出优化建议。但是，有了STA以后，它就可以根据ADDM采集到的数据直接给出优化建议，甚至给出优化后的语句（抢了DBA的饭碗喽）。
        ADDM能发现定位的问题包括：
        操作系统内存页入页出问题
        由于Oracle负载和非Oracle负载导致的CPU瓶颈问题
        导致不同资源负载的Top SQL语句和对象——CPU消耗、IO带宽占用、潜在IO问题、RAC内部通讯繁忙 
        按照PLSQL和JAVA执行时间排的Top SQL语句. 
        过多地连接 (login/logoff). 
        过多硬解析问题——由于shared pool过小、书写问题、绑定大小不适应、解析失败原因引起的。 
        过多软解析问题
        索引查询过多导致资源争用. 
        由于用户锁导致的过多的等待时间 (通过包dbms_lock加的锁)
        由于DML锁导致的过多等待时间(例如锁住表了) 
        由于管道输出导致的过多等待时间(如通过包dbms_pipe.put进行管道输出) 
        由于并发更新同一个记录导致的过多等待时间(行级锁等待) 
        由于ITL不够导致的过多等待时间（大量的事务操作同一个数据块）
        系统中过多的commit和rollback(logfile sync事件).
        由于磁盘带宽太小和其他潜在问题（如由于logfile太小导致过多的checkpoint，MTTR设置问题，过多的undo操作等等）导致的IO性能问题I 
        对于DBWR进程写数据块，磁盘IO吞吐量不足 
        由于归档进程无法跟上redo日至产生的速度，导致系统变慢 
        redo数据文件太小导致的问题
        由于扩展磁盘分配导致的争用
        由于移动一个对象的高水位导致的争用问题 
        内存太小问题——SGA Target, PGA, Buffer Cache, Shared Pool 
        在一个实例或者一个机群环境中存在频繁读写争用的热块 
        在一个实例或者一个机群环境中存在频繁读写争用的热对象 
        RAC环境中内部通讯问题 

        LMS进程无法跟上导致锁请求阻塞 
        在RAC环境中由于阻塞和争用导致的实例倾斜 
        RMAN导致的IO和CPU问题
        Streams和AQ问题
        资源管理等待事件

        有一点要记住：AWR收集的数据时放到内存中（share pool），通过一个新的后台进程MMON定期写到磁盘中。所以10g的share pool要求比以前版本更大，一般推荐比以前大15-20%。另外，还要求系统参数STATISTICS_LEVEL设置为TYPICAL（推荐）或ALL;
ALTER SESSION SET STATISTICS_LEVEL= TYPICAL;

二、案例：

1、采集AWR Snapshot

SQL> begin   2   dbms_workload_repository.create_snapshot('TYPICAL');   3  end;   4  /

2、运行事务

scott用户执行一个资源消耗高的事务：

15:14:47 SCOTT@ prod>begin 15:34:41   2    for i in 1..1000000 loop 15:34:41   3    execute immediate 'insert into scott.t1 values ('||i||')'; 15:34:41   4    end loop; 15:34:41   5    end; 15:34:41   6    /