00016.Oracle等待事件中SQL调优

SQL性能调试过程

--1、查看等待的事件；

selectt.SID,t.EVENT,t.p1,t.P1TEXT  fromv$session_wait t;

--2、从等待事件中，选定待调优事件的sid,查询出等待处理的sql

select a.SQL_TEXT

  from v$sqltext a

 where a.HASH_VALUE =

       (select b.SQL_HASH_VALUE from v$sessionb where b.SID = '&sid')         

--3、获取具体的SQL；    

       select b.SAGID,

              b.equipmenttypeid,

              b.ftpIP,

              b.ftpPort,

              b.account,

              b.password

         from equipmentinfo a, SAGCONFIG b

        where a.equipmentid = b.sagid

          and a.extenddevicetype like '%null%'

--4、执行SQL，获取其执行计划详情；

SQL> setautotrace on

SQL> selectb.SAGID,

  2                b.equipmenttypeid,

  3                b.ftpIP,

  4                b.ftpPort,

  5                b.account,

  6                b.password

  7          from equipmentinfo a, SAGCONFIG b

  8         where a.equipmentid = b.sagid

  9           and a.extenddevicetype like '%null%';

 

no rows selected

 

 

Execution Plan

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

   0     SELECT STATEMENT Optimizer=CHOOSE

   1   0   NESTED LOOPS

   2   1     TABLE ACCESS (FULL) OF'SAGCONFIG'

   3   1     TABLE ACCESS (BY INDEXROWID) OF 'EQUIPMENTINFO'

   4   3       INDEX (UNIQUE SCAN) OF'PK_EQUIPMENTINFO' (UNIQUE)

 

Statistics

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

          0 recursive calls

          0 db block gets

         42 consistent gets

          0 physical reads

          0 redo size

        503 bytes sent via SQL*Net to client

        372 bytes received via SQL*Net from client

          1 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          0 rows processed

 

SQL> setautotrace off

--5、调优

  判断原因：根据计划，分析得到： 2    1     TABLE ACCESS (FULL) OF 'SAGCONFIG' 中，采用了

  全表扫描；

  方案：1、在SAGCONFIG表中(字段sagid)添加索引；