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等价改写、升级硬件设备、业务代码重构等方式进行优化。
