SQL优化案例10（ORACLE SQL语句逻辑读高优化案例）

川川找我优化SQL，逻辑读达到398,000，安排一下。

SQL和执行计划：

SELECT t1.*, t3.bed_number, t3.patient_name, t4.name
  FROM odw_checkrecipe_result t1
  left join lenovo_his.ip_patient_regis t3
    on t3.ip_number = t1.visit_id
  left join hd_advice t4
    on t4.advice_id = t1.order_id
 WHERE NOT EXISTS
 (select *
          from odw_checkrecipe_result t2
         WHERE t1.DETAIL_ID = t2.DETAIL_ID
           AND t1.AUDIT_TIME < t2.AUDIT_TIME)
   and t1.detail_id in (select advice_main_id
                          from hd_advice
                         where create_id = '70013'
                           and status = 1
                           and advice_type in (11, 12, 13)
                         group by advice_main_id);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9mmh1q6bxznxj, child number 0
-------------------------------------
SELECT t1.*, t3.bed_number, t3.patient_name, t4.name   FROM
odw_checkrecipe_result t1   left join lenovo_his.ip_patient_regis t3
 on t3.ip_number = t1.visit_id   left join hd_advice t4     on
t4.advice_id = t1.order_id  WHERE NOT EXISTS  (select *           from
odw_checkrecipe_result t2          WHERE t1.DETAIL_ID = t2.DETAIL_ID
        AND t1.AUDIT_TIME < t2.AUDIT_TIME)    and t1.detail_id in
(select advice_main_id                           from hd_advice
                 where create_id = '70013'
and status = 1                            and advice_type in (11, 12,
13)                          group by advice_main_id)

Plan hash value: 3849450906

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |      1 |        |      0 |00:00:00.85 |     398K|       |       |          |
|   1 |  HASH GROUP BY                  |                          |      1 |     93 |      0 |00:00:00.85 |     398K|   760K|   760K|          |
|   2 |   NESTED LOOPS OUTER            |                          |      1 |     93 |      0 |00:00:00.85 |     398K|       |       |          |
|   3 |    NESTED LOOPS OUTER           |                          |      1 |     93 |      0 |00:00:00.85 |     398K|       |       |          |
|   4 |     NESTED LOOPS                |                          |      1 |     93 |      0 |00:00:00.85 |     398K|       |       |          |
|*  5 |      HASH JOIN RIGHT ANTI       |                          |      1 |   1372 |    135K|00:00:00.22 |    3268 |  9702K|  3788K| 8770K (0)|
|   6 |       TABLE ACCESS FULL         | ODW_CHECKRECIPE_RESULT   |      1 |    137K|    141K|00:00:00.02 |    1634 |       |       |          |
|   7 |       TABLE ACCESS FULL         | ODW_CHECKRECIPE_RESULT   |      1 |    137K|    141K|00:00:00.05 |    1634 |       |       |          |
|*  8 |      TABLE ACCESS BY INDEX ROWID| HD_ADVICE                |    135K|      1 |      0 |00:00:00.61 |     394K|       |       |          |
|*  9 |       INDEX RANGE SCAN          | IDX_HD_ADVICE_MAIN_ID    |    135K|      1 |    180K|00:00:00.31 |     260K|       |       |          |
|  10 |     TABLE ACCESS BY INDEX ROWID | IP_PATIENT_REGIS         |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 11 |      INDEX RANGE SCAN           | IDX_IP_PATIENT_REGIS_NUM |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  12 |    TABLE ACCESS BY INDEX ROWID  | HD_ADVICE                |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 13 |     INDEX UNIQUE SCAN           | HD_ADVICE_PK             |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."DETAIL_ID"="T2"."DETAIL_ID")
       filter("T1"."AUDIT_TIME"<"T2"."AUDIT_TIME")
   8 - filter(("STATUS"=1 AND "CREATE_ID"='70013' AND (TO_NUMBER("ADVICE_TYPE")=11 OR TO_NUMBER("ADVICE_TYPE")=12 OR
              TO_NUMBER("ADVICE_TYPE")=13)))
   9 - access("ADVICE_MAIN_ID"=TO_NUMBER("T1"."DETAIL_ID"))
  11 - access("T3"."IP_NUMBER"="T1"."VISIT_ID")
  13 - access("T4"."ADVICE_ID"=TO_NUMBER("T1"."ORDER_ID"))


45 rows selected.

这条SQL比较简单，我其实就喵了一眼执行计划就看到问题，hd_advice表有700W行数据，通过谓词信息了解到 第9行 access("ADVICE_MAIN_ID"=TO_NUMBER("T1"."DETAIL_ID")) t1 表和 hd_advice 表关联访问是用到索引的，

但是执行到第8行 filter(("STATUS"=1 AND "CREATE_ID"='70013' AND (TO_NUMBER("ADVICE_TYPE")=11 OR TO_NUMBER("ADVICE_TYPE")=12 OR TO_NUMBER("ADVICE_TYPE")=13))) 这段条件过滤时候又进行了回表扫描，

说明现有的索引不合适这个谓词过滤条件，索引块获取不到相关的记录，需要再次回表扫描，产生大量的逻辑读。

创建联合索引进行优化规避回表扫描：

create index idx_hd_advice_1_2_3_4 on hd_advice(create_id,status,advice_type,advice_main_id)；

创建完索引后逻辑读应该就能降下来了，川川也提供了验证，buffer 降到3，这条SQL到此已经完成优化。

最后总结：

   ORACLE调优慢SQL的步骤如下：

　　　　1、先尝试创建索引或者修改数据库参数看看能否达到调优目的。

　　　　2、上面手段无效果尝试通过HINT干预执行计划进行调优，如果有效果使用sqlprofile绑定新的执行计划。

　　　　3、如果上述手段均无办法调优SQL，只能通过SQL等价改写、升级硬件设备、业务代码重构等方式进行优化。