ITPub博客

首页 > 数据库 > Oracle > 一次sql改写优化案例

一次sql改写优化案例

原创 Oracle 作者:DBA_每日记 时间:2019-12-02 09:36:24 0 删除 编辑

问题背景:

客户反馈有条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调优一次拨开迷雾的过程,抓主要矛盾放手次要矛盾


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69950231/viewspace-2666415/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2019-10-09

  • 博文量
    110
  • 访问量
    47773