问题背景： 客户反馈有条sql执行时间要30s左右，需要优化，sql如下 （一次改写案例） （涉及相关表以及重要字段隐去） 原sql： 问题背景： 客户反馈有条sql执行时间要30s左右，需要优化，sql如下 （一次改写案例，引以为戒） select r.* from (select my_table.*, rownum as my_rownum from (select tableA.*, rownum as oracle_rownum from (select distinct o.id, o.字段1, ... r.字段1, ... from 表1 o, 表2 r where o.字段1 = r.字段1 and r.字段2 = 0 and o.type = 1 and r.istemplate = 0 and r.status in (6, 7, 8, 9, 10) and o.optstatus in (-1, 0, 1, 2, 7, 8) and (exists (select 1 from 表3 s1 where s1.sharetype = 1 and s1.objid = 2501 and s1.字段1 = r.字段1) or exists (select 1 from 表4 h, 表3 s2 where s2.sharetype = 2 and h.seclevel >= s2.seclevel and s2.objid = h.subcompanyid1 and h.id = 2501 and s2.字段1 = r.字段1) or exists (select 1 from 表4 h, 表3 s3 where s3.sharetype = 3 and h.seclevel >= s3.seclevel and s3.objid = h.departmentid and h.id = 2501 and s3.字段1 = r.字段1) or exists (select 1 from 表5 m, 表3 s4, 表4 h where h.id = m.resourceid and s4.objid = m.roleid and s4.字段1 = r.字段1 and h.seclevel >= s4.seclevel and s4.sharetype = 4 and m.rolelevel >= s4.rolelevel and h.id = 2501) or exists (select 1 from 表4 h, 表3 s5 where s5.sharetype = 5 and s5.字段1 = r.字段1 and h.seclevel >= s5.seclevel and s5.foralluser = 1 and h.id = 2501) or exists (select 1 from 表4 h, 表3 s6 where s6.sharetype = 6 and s6.字段1 = r.字段1 and s6.creater = h.id and h.managerid = 2501) or exists (select 1 from 表4 h, 表3 s7 where s7.sharetype = 7 and s7.字段1 = r.字段1 and s7.creater = h.id and h.departmentid = 610) or exists (select 1 from 表4 h, 表3 s8 where s8.sharetype = 8 and s8.字段1 = r.字段1 and s8.creater = h.id and h.subcompanyid1 = 121) or (r.creater = 2501) or exists (select 1 from 表1 o where o.字段1 = r.字段1 and r.status in (6, 7, 8, 9, 10) and o.userid = 2501) or exists (select 1 from 表2 a inner join 表6 b on a.字段1 = b.字段1 inner join 表7 c on b.id = c.wtlistid where c.userid = '2501' and a.字段1 = o.字段1 )) order by o.id desc nulls last) tableA) my_table where oracle_rownum < 6 and oracle_rownum > 0) r执行计划： Plan hash value: 825717004 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7841 | 9877K| | 1853 (1)| 00:00:23 | | 1 | VIEW | | 7841 | 9877K| | 1853 (1)| 00:00:23 | | 2 | COUNT | | | | | | | |* 3 | VIEW | | 7841 | 9778K| | 1853 (1)| 00:00:23 | | 4 | COUNT | | | | | | | | 5 | VIEW | | 7841 | 9678K| | 1853 (1)| 00:00:23 | | 6 | SORT UNIQUE | | 7841 | 1876K| 2104K| 1435 (1)| 00:00:18 | |* 7 | FILTER | | | | | | | |* 8 | HASH JOIN | | 21206 | 5073K| | 310 (1)| 00:00:04 | |* 9 | TABLE ACCESS FULL | WORKTASK_OPERATOR | 21227 | 621K| | 69 (2)| 00:00:01 | |* 10 | TABLE ACCESS FULL | WORKTASK_REQUESTBASE | 21207 | 4452K| | 241 (1)| 00:00:03 | |* 11 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 39 | | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_REQUESTSHARESET | 1 | | | 1 (0)| 00:00:01 | | 13 | NESTED LOOPS | | 1 | 63 | | 3 (0)| 00:00:01 | | 14 | NESTED LOOPS | | 1 | 63 | | 3 (0)| 00:00:01 | |* 15 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 52 | | 1 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | |* 18 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 11 | | 2 (0)| 00:00:01 | | 19 | NESTED LOOPS | | 1 | 64 | | 2 (0)| 00:00:01 | | 20 | NESTED LOOPS | | 1 | 64 | | 2 (0)| 00:00:01 | |* 21 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 52 | | 1 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | |* 23 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 0 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 12 | | 1 (0)| 00:00:01 | | 25 | NESTED LOOPS | | 1 | 82 | | 5 (0)| 00:00:01 | | 26 | NESTED LOOPS | | 1 | 82 | | 5 (0)| 00:00:01 | | 27 | NESTED LOOPS | | 1 | 75 | | 3 (0)| 00:00:01 | | 28 | TABLE ACCESS BY INDEX ROWID| HRMROLEMEMBERS | 2 | 20 | | 3 (0)| 00:00:01 | |* 29 | INDEX RANGE SCAN | HRMROLEMEMBERS_RESOURCEID_IN | 2 | | | 1 (0)| 00:00:01 | |* 30 | TABLE ACCESS BY INDEX ROWID| REQUESTSHARESET | 1 | 65 | | 0 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | I_REQUESTSHARESET | 1 | | | 0 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | |* 33 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 7 | | 2 (0)| 00:00:01 | | 34 | NESTED LOOPS | | 1 | 59 | | 3 (0)| 00:00:01 | | 35 | NESTED LOOPS | | 1 | 59 | | 3 (0)| 00:00:01 | |* 36 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 52 | | 1 (0)| 00:00:01 | |* 37 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | |* 38 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | |* 39 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 7 | | 2 (0)| 00:00:01 | | 40 | NESTED LOOPS | | 1 | 52 | | 2 (0)| 00:00:01 | | 41 | NESTED LOOPS | | 1 | 52 | | 2 (0)| 00:00:01 | |* 42 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 39 | | 1 (0)| 00:00:01 | |* 43 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | |* 44 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | |* 45 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 13 | | 1 (0)| 00:00:01 | | 46 | NESTED LOOPS | | 1 | 47 | | 2 (0)| 00:00:01 | | 47 | NESTED LOOPS | | 1 | 47 | | 2 (0)| 00:00:01 | |* 48 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 39 | | 1 (0)| 00:00:01 | |* 49 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | |* 50 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | |* 51 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 8 | | 1 (0)| 00:00:01 | |* 52 | FILTER | | | | | | | |* 53 | TABLE ACCESS BY INDEX ROWID | WORKTASK_OPERATOR | 1 | 9 | | 2 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | REQUESTID_WTO_INDEX | 1 | | | 1 (0)| 00:00:01 | |* 55 | HASH JOIN | | 1 | 86 | | 246 (0)| 00:00:03 | | 56 | MERGE JOIN CARTESIAN | | 1 | 46 | | 243 (0)| 00:00:03 | |* 57 | TABLE ACCESS FULL | WORKTASK_REQUESTBASE | 1 | 5 | | 240 (0)| 00:00:03 | | 58 | BUFFER SORT | | 1 | 41 | | 3 (0)| 00:00:01 | |* 59 | TABLE ACCESS FULL | WORKTASK_LIST_LIABLEPERSON | 1 | 41 | | 3 (0)| 00:00:01 | |* 60 | TABLE ACCESS FULL | WORKTASK_LIST | 2 | 80 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------原sql不得不说从各个角度来看都是一条糟糕的sql，生产环境执行时间要50s左右，有问题的地方多了优化只能抓主要问题原sql的结尾有一段sql如下 (select 1 from 表2 a inner join 表6 b on a.字段1 = b.字段1 inner join 表7 c on b.id = c.wtlistid where c.userid = '2501' and a.字段1 = o.字段1 )) order by o.id desc nulls last) tableA) my_table where oracle_rownum < 6 and oracle_rownum > 0) r标红的位置，子查询里有两张表关联， a.字段1很好理解，是表2，那么 o.字段1的o在哪？发现是父表，这里使用了子表和父表的关联条件，所以执行计划里才有大量的nested loop循环 尝试改写为执行在子查询里关联字表字段，改写如下
(select 1
from 表2 a
inner join 表6 b
on a.字段1 = b.字段1
inner join 表7 c
on b.id = c.wtlistid
where c.userid = '2501' inner join 表1 o.requestid on a.requestid --改写部分
-- and a.字段1 = o.字段1 注释
))
order by o.id desc nulls last) tableA) my_table
where oracle_rownum < 6
and oracle_rownum > 0) r 改写后的sql在生产环境执行时间5ms,调优成功！ sql调优一次拨开迷雾的过程，抓主要矛盾放手次要矛盾
