1次connect by的优化

最新推荐文章于 2022-06-30 22:23:28 发布
最新推荐文章于 2022-06-30 22:23:28 发布
阅读量464 收藏
点赞数
昨天一条sql造成了大量的CBC latch等待，CPU超过了90%，sql每次执行消耗了6441854逻辑读，耗时130s，只返回3行，基本全部消耗在CPU上，sql文本以及执行计划如下：

sql文本

SELECT /*+qb_name(a)*/ t.id
FROM venustech.venus_acc_organizationview t start WITH t.id IN (
        SELECT /*+qb_name(b)*/ t5.ID
        FROM venustech.venus_auth_masterauth t7,
            venustech.venus_acc_slave t4,
            venustech.venus_acc_organizationview t5,
            venustech.venus_acc_master t6
        WHERE 1 = 1
            AND t5.ID = t6.orgof
            AND t6.STATUS != 9
            AND t7.slaveid = t4.id
            AND t7.id = t6.id
            AND t4.STATUS != 9
            AND t4.NAME = 'xiayuping'
        ) connect BY prior t.parentorgid = t.id
ORDER BY to_number(t.id) DESC;


执行计划

Plan hash value: 785436050
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                              |  2358 |   156K|    16  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                       |                              |  2358 |   156K|    16  (25)| 00:00:01 |
|*  2 |   CONNECT BY WITH FILTERING          |                              |       |       |            |          |
|*  3 |    FILTER                            |                              |       |       |            |          |
|   4 |     COUNT                            |                              |       |       |            |          |
|   5 |      VIEW                            | VENUS_ACC_ORGANIZATIONVIEW   |  2358 |   156K|    14  (15)| 00:00:01 |
|   6 |       SORT UNIQUE                    |                              |  2358 |   172K|    14  (36)| 00:00:01 |
|   7 |        UNION-ALL                     |                              |       |       |            |          |
|   8 |         TABLE ACCESS FULL            | VENUS_ACC_ORGANIZATION       |  2317 |   169K|     9   (0)| 00:00:01 |
|   9 |         TABLE ACCESS FULL            | VENUS_ACC_ORGANIZATIONEXT    |    41 |  2542 |     3   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                     |                              |     1 |    65 |   537   (2)| 00:00:07 |
|  11 |      NESTED LOOPS                    |                              |     1 |    46 |   535   (2)| 00:00:07 |
|  12 |       NESTED LOOPS                   |                              |     1 |    32 |   530   (2)| 00:00:07 |
|* 13 |        TABLE ACCESS FULL             | VENUS_ACC_MASTER             |    60 |  1320 |   530   (2)| 00:00:07 |
|  14 |        VIEW                          | VENUS_ACC_ORGANIZATIONVIEW   |     1 |    10 |     0   (0)| 00:00:01 |
|  15 |         SORT UNIQUE                  |                              |       |       |            |          |
|  16 |          UNION-ALL PARTITION         |                              |       |       |            |          |
|  17 |           TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATION       |     1 |    75 |     3   (0)| 00:00:01 |
|* 18 |            INDEX RANGE SCAN          | PK_VENUS_ACC_ORGANIZATION    |     1 |       |     2   (0)| 00:00:01 |
|  19 |           TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATIONEXT    |     1 |    62 |     1   (0)| 00:00:01 |
|* 20 |            INDEX UNIQUE SCAN         | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |       |     0   (0)| 00:00:01 |
|  21 |       TABLE ACCESS BY INDEX ROWID    | VENUS_AUTH_MASTERAUTH        |     4 |    56 |     5   (0)| 00:00:01 |
|* 22 |        INDEX RANGE SCAN              | VENUS_AUTH_MASTERAUTH_INDEX  |     4 |       |     2   (0)| 00:00:01 |
|* 23 |      TABLE ACCESS BY INDEX ROWID     | VENUS_ACC_SLAVE              |     1 |    19 |     2   (0)| 00:00:01 |
|* 24 |       INDEX UNIQUE SCAN              | PK_VENUS_ACC_SLAVE           |     1 |       |     1   (0)| 00:00:01 |
|* 25 |    HASH JOIN                         |                              |       |       |            |          |
|  26 |     CONNECT BY PUMP                  |                              |       |       |            |          |
|  27 |     COUNT                            |                              |       |       |            |          |
|  28 |      VIEW                            | VENUS_ACC_ORGANIZATIONVIEW   |  2358 |   156K|    14  (15)| 00:00:01 |
|  29 |       SORT UNIQUE                    |                              |  2358 |   172K|    14  (36)| 00:00:01 |
|  30 |        UNION-ALL                     |                              |       |       |            |          |
|  31 |         TABLE ACCESS FULL            | VENUS_ACC_ORGANIZATION       |  2317 |   169K|     9   (0)| 00:00:01 |
|  32 |         TABLE ACCESS FULL            | VENUS_ACC_ORGANIZATIONEXT    |    41 |  2542 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - A
   5 - SET$2        / T@A
   8 - SEL$4        / VENUS_ACC_ORGANIZATION@SEL$4
   9 - SEL$5        / VENUS_ACC_ORGANIZATIONEXT@SEL$5
  10 - B
  13 - B            / T6@B
  14 - SET$6071500D / T5@B
  15 - SET$6071500D
  17 - SEL$81C74289 / VENUS_ACC_ORGANIZATION@SEL$8
  18 - SEL$81C74289 / VENUS_ACC_ORGANIZATION@SEL$8
  19 - SEL$193D1E52 / VENUS_ACC_ORGANIZATIONEXT@SEL$9
  20 - SEL$193D1E52 / VENUS_ACC_ORGANIZATIONEXT@SEL$9
  21 - B            / T7@B
  22 - B            / T7@B
  23 - B            / T4@B
  24 - B            / T4@B
  28 - SET$2        / T@A
  29 - SET$2
  31 - SEL$4        / VENUS_ACC_ORGANIZATION@SEL$4
  32 - SEL$5        / VENUS_ACC_ORGANIZATIONEXT@SEL$5
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1FB8A168" "VENUS_ACC_ORGANIZATION"@"SEL$6" ("VENUS_ACC_ORGANIZATION"."ID"
              "VENUS_ACC_ORGANIZATION"."AUTOID"))
      INDEX_RS_ASC(@"SEL$37248FBF" "VENUS_ACC_ORGANIZATIONEXT"@"SEL$7" ("VENUS_ACC_ORGANIZATIONEXT"."ID"))
      INDEX_RS_ASC(@"SEL$661FCD0D" "VENUS_ACC_ORGANIZATION"@"SEL$2" ("VENUS_ACC_ORGANIZATION"."ID"
              "VENUS_ACC_ORGANIZATION"."AUTOID"))
      INDEX_RS_ASC(@"SEL$A8E2213E" "VENUS_ACC_ORGANIZATIONEXT"@"SEL$3" ("VENUS_ACC_ORGANIZATIONEXT"."ID"))
      USE_NL(@"SEL$85C38F10" "T5"@"B")
      USE_HASH(@"SEL$85C38F10" "T6"@"B")
      USE_HASH(@"SEL$85C38F10" "T7"@"B")
      LEADING(@"SEL$85C38F10" "T4"@"B" "T7"@"B" "T6"@"B" "T5"@"B")
      NO_ACCESS(@"SEL$85C38F10" "T5"@"B")
      FULL(@"SEL$85C38F10" "T6"@"B")
      FULL(@"SEL$85C38F10" "T7"@"B")
      FULL(@"SEL$85C38F10" "T4"@"B")
      USE_NL(@"SEL$AE3814F3" "T"@"SEL$1")
      LEADING(@"SEL$AE3814F3" "VW_NSO_1"@"SEL$AE3814F3" "T"@"SEL$1")
      NO_ACCESS(@"SEL$AE3814F3" "T"@"SEL$1")
      NO_ACCESS(@"SEL$AE3814F3" "VW_NSO_1"@"SEL$AE3814F3")
      INDEX_RS_ASC(@"SEL$81C74289" "VENUS_ACC_ORGANIZATION"@"SEL$8" ("VENUS_ACC_ORGANIZATION"."ID"
              "VENUS_ACC_ORGANIZATION"."AUTOID"))
      INDEX_RS_ASC(@"SEL$193D1E52" "VENUS_ACC_ORGANIZATIONEXT"@"SEL$9" ("VENUS_ACC_ORGANIZATIONEXT"."ID"))
      USE_NL(@"B" "T4"@"B")
      USE_NL(@"B" "T7"@"B")
      USE_NL(@"B" "T5"@"B")
      LEADING(@"B" "T6"@"B" "T5"@"B" "T7"@"B" "T4"@"B")
      INDEX_RS_ASC(@"B" "T4"@"B" ("VENUS_ACC_SLAVE"."ID"))
      INDEX_RS_ASC(@"B" "T7"@"B" ("VENUS_AUTH_MASTERAUTH"."ID"))
      NO_ACCESS(@"B" "T5"@"B")
      FULL(@"B" "T6"@"B")
      FULL(@"SEL$4" "VENUS_ACC_ORGANIZATION"@"SEL$4")
      FULL(@"SEL$5" "VENUS_ACC_ORGANIZATIONEXT"@"SEL$5")
      NO_ACCESS(@"A" "T"@"A")
      OUTLINE(@"A")
      OUTLINE(@"SET$4")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$1")
      UNNEST(@"B")
      OUTLINE(@"SEL$AE3814F3")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"B")
      OUTLINE(@"SEL$85C38F10")
      OUTLINE(@"SET$3")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$6")
      OUTLINE_LEAF(@"A")
      OUTLINE_LEAF(@"SET$2")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"B")
      OLD_PUSH_PRED(@"B" "T5"@"B")
      OUTLINE_LEAF(@"SET$6071500D")
      OUTLINE_LEAF(@"SEL$193D1E52")
      OUTLINE_LEAF(@"SEL$81C74289")
      UNNEST(@"B")
      OUTLINE_LEAF(@"SEL$AE3814F3")
      OLD_PUSH_PRED(@"SEL$AE3814F3" "T"@"SEL$1")
      OUTLINE_LEAF(@"SET$AD7CC163")
      OUTLINE_LEAF(@"SEL$A8E2213E")
      OUTLINE_LEAF(@"SEL$661FCD0D")
      OUTLINE_LEAF(@"SEL$85C38F10")
      OLD_PUSH_PRED(@"SEL$85C38F10" "T5"@"B")
      OUTLINE_LEAF(@"SET$61667F90")
      OUTLINE_LEAF(@"SEL$37248FBF")
      OUTLINE_LEAF(@"SEL$1FB8A168")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."ID"=PRIOR "T"."PARENTORGID")
   3 - filter( EXISTS (SELECT /*+ NO_EXPAND UNNEST QB_NAME ("B") */ 0 FROM "VENUSTECH"."VENUS_ACC_MASTER"
              "T6", ( (SELECT /*+ NO_STAR_TRANSFORMATION INDEX ("VENUS_ACC_ORGANIZATION") */ "ID" "ID","PARENTORGID"
              "PARENTORGID","AUTOID" "AUTOID","NAME" "NAME","STATUS" "STATUS",'VENUS_ACC_ORGANIZATION' "TNAME" FROM
              VENUSTECH."VENUS_ACC_ORGANIZATION" "VENUS_ACC_ORGANIZATION" WHERE "ID"=:B1 AND "ID"="T6"."ORGOF")UNION
              (SELECT /*+ NO_STAR_TRANSFORMATION INDEX ("VENUS_ACC_ORGANIZATIONEXT") */ "ID" "ID","PARENTORGID"
              "PARENTORGID","ID" "AUTOID","NAME" "NAME","STATUS" "STATUS",'VENUS_ACC_ORGANIZATIONEXT' "TNAME" FROM
              VENUSTECH."VENUS_ACC_ORGANIZATIONEXT" "VENUS_ACC_ORGANIZATIONEXT" WHERE "ID"=:B2 AND "ID"="T6"."ORGOF"))
              "T5","VENUSTECH"."VENUS_ACC_SLAVE" "T4","VENUSTECH"."VENUS_AUTH_MASTERAUTH" "T7" WHERE
              "T7"."ID"=TO_NUMBER("T6"."ID") AND "T7"."SLAVEID"="T4"."ID" AND "T4"."NAME"='xiayuping' AND "T4"."STATUS"<>9
              AND "T5"."ID"=:B3 AND "T5"."ID"="T6"."ORGOF" AND "T6"."ORGOF"=:B4 AND "T6"."STATUS"<>9))
  13 - filter("T6"."ORGOF"=:B1 AND "T6"."STATUS"<>9)
  18 - access("ID"=:B1)
       filter("ID"="T6"."ORGOF")
  20 - access("ID"=:B1)
       filter("ID"="T6"."ORGOF")
  22 - access("T7"."ID"=TO_NUMBER("T6"."ID"))
  23 - filter("T4"."NAME"='xiayuping' AND "T4"."STATUS"<>9)
  24 - access("T7"."SLAVEID"="T4"."ID")
  25 - access("T"."ID"=PRIOR "T"."PARENTORGID")


执行计划首先出现了filter，子查询没有展开，但是奇怪的是outline部分UNNEST(@"B")明确指出了已经对查询块B做了子查询展开，这个稍后做个10053看看。
这里的性能问题关键出在filter上，venus_acc_organizationview为一个union all的视图，首先对该视图进行扫描，扫描出多少distinct id，就执行子查询多少次。 


sys@GZ4ADB>select count(distinct id) from venustech.venus_acc_organizationview;
COUNT(DISTINCTID)
-----------------
             2374

再看看子查询的执行计划：
|  10 |     NESTED LOOPS                     |                              |     1 |    65 |   537   (2)| 00:00:07 |
|  11 |      NESTED LOOPS                    |                              |     1 |    46 |   535   (2)| 00:00:07 |
|  12 |       NESTED LOOPS                   |                              |     1 |    32 |   530   (2)| 00:00:07 |
|* 13 |        TABLE ACCESS FULL             | VENUS_ACC_MASTER             |    60 |  1320 |   530   (2)| 00:00:07 |
|  14 |        VIEW                          | VENUS_ACC_ORGANIZATIONVIEW   |     1 |    10 |     0   (0)| 00:00:01 |
|  15 |         SORT UNIQUE                  |                              |       |       |            |          |
|  16 |          UNION-ALL PARTITION         |                              |       |       |            |          |
|  17 |           TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATION       |     1 |    75 |     3   (0)| 00:00:01 |
|* 18 |            INDEX RANGE SCAN          | PK_VENUS_ACC_ORGANIZATION    |     1 |       |     2   (0)| 00:00:01 |
|  19 |           TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATIONEXT    |     1 |    62 |     1   (0)| 00:00:01 |
|* 20 |            INDEX UNIQUE SCAN         | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |       |     0   (0)| 00:00:01 |
|  21 |       TABLE ACCESS BY INDEX ROWID    | VENUS_AUTH_MASTERAUTH        |     4 |    56 |     5   (0)| 00:00:01 |
|* 22 |        INDEX RANGE SCAN              | VENUS_AUTH_MASTERAUTH_INDEX  |     4 |       |     2   (0)| 00:00:01 |
|* 23 |      TABLE ACCESS BY INDEX ROWID     | VENUS_ACC_SLAVE              |     1 |    19 |     2   (0)| 00:00:01 |
|* 24 |       INDEX UNIQUE SCAN              | PK_VENUS_ACC_SLAVE           |     1 |       |     1   (0)| 00:00:01 |


迎面而来的就是一个全表扫描，需要扫描VENUS_ACC_MASTER表2374次就够惨的了，还不算后续的操作，VENUS_ACC_MASTER有19M，虽然不大但是扫描2374次还是有点老火。

优化

不妨看看子查询内返回多少记录
sys@GZ4ADB>SELECT count(*) FROM venustech.venus_auth_masterauth t7,
  2                     venustech.venus_acc_slave t4,
  3                     venustech.venus_acc_organizationview t5,
  4                     venustech.venus_acc_master t6
  5             WHERE 1 = 1
  6                     AND t5.ID = t6.orgof
  7                     AND t6.STATUS != 9
  8                     AND t7.slaveid = t4.id
  9                     AND t7.id = t6.id
 10                     AND t4.STATUS != 9
 11                     AND t4.NAME = 'xiayuping';


  COUNT(*)
----------
         2


只有2条记录，执行计划为：
Execution Plan
----------------------------------------------------------
Plan hash value: 4080383923
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |    10 |  1040 |  3003   (2)| 00:00:37 |
|   1 |  NESTED LOOPS                   |                              |    10 |  1040 |  3003   (2)| 00:00:37 |
|*  2 |   HASH JOIN                     |                              |     2 |   128 |  2991   (2)| 00:00:36 |
|*  3 |    HASH JOIN                    |                              |     2 |    66 |  2460   (2)| 00:00:30 |
|*  4 |     TABLE ACCESS FULL           | VENUS_ACC_SLAVE              |     2 |    38 |  2233   (2)| 00:00:27 |
|   5 |     TABLE ACCESS FULL           | VENUS_AUTH_MASTERAUTH        |   146K|  2003K|   225   (3)| 00:00:03 |
|*  6 |    TABLE ACCESS FULL            | VENUS_ACC_MASTER             | 36742 |  1112K|   530   (2)| 00:00:07 |
|   7 |   VIEW                          | VENUS_ACC_ORGANIZATIONVIEW   |     1 |    40 |     6  (34)| 00:00:01 |
|   8 |    SORT UNIQUE                  |                              |     2 |   137 |     6  (50)| 00:00:01 |
|   9 |     UNION ALL PUSHED PREDICATE  |                              |       |       |            |          |
|  10 |      TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATION       |     1 |    75 |     3   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN          | PK_VENUS_ACC_ORGANIZATION    |     1 |       |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATIONEXT    |     1 |    62 |     1   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN         | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T7"."ID"=TO_NUMBER("T6"."ID"))
   3 - access("T7"."SLAVEID"="T4"."ID")
   4 - filter("T4"."NAME"='xiayuping' AND "T4"."STATUS"<>9)
   6 - filter("T6"."STATUS"<>9)
  11 - access("ID"="T6"."ORGOF")
  13 - access("ID"="T6"."ORGOF")


通过统计信息发现VENUS_ACC_SLAVE的NAME字段选择性非常好，但是没有索引，驱动结果集为2，且VENUS_ACC_MASTER的ID是主键但是并没有走NL，原因是隐式转换
 2 - access("T7"."ID"=TO_NUMBER("T6"."ID"))，发现这些问题后，首先做了以下改动。
后续就是优化connect by由于之前一篇关于connect by的blog提到了connect by with filter的connect by pump的原理，这里就不说了，看了下VENUS_ACC_ORGANIZATION和VENUS_ACC_ORGANIZATIONEXT的ID列都是主键。


优化措施如下：
create index venustech.idx_venus_acc_slave_name on venustech.venus_acc_slave(name);
create index venustech.idx_VENUS_AUTH_slaveid_id on venustech.VENUS_AUTH_MASTERAUTH(slaveid,id);
sql改写为：
with aa as(
        SELECT /*+ materialize */t5.ID 
        FROM venustech.venus_auth_masterauth t7,
            venustech.venus_acc_slave t4,
            venustech.venus_acc_organizationview t5,
            venustech.venus_acc_master t6
        WHERE 1 = 1
            AND t5.ID = t6.orgof
            AND t6.STATUS != 9
            AND t7.slaveid = t4.id
            AND to_char(t7.id) = t6.id
            AND t4.STATUS != 9
            AND t4.NAME = 'xiayuping')
SELECT t.id
  FROM venustech.VENUS_ACC_ORGANIZATIONEXT t 
 start WITH t.id IN (select id from aa)
connect BY prior t.parentorgid = t.id
ORDER BY to_number(t.id) DESC;

执行计划和统计信息为：
Execution Plan
----------------------------------------------------------
Plan hash value: 3290992204
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |  2358 |   156K|    26  (16)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION        |                              |       |       |            |          |
|   2 |   LOAD AS SELECT                  | VENUS_ACC_ORGANIZATIONVIEW   |       |       |            |          |
|   3 |    NESTED LOOPS                   |                              |     3 |   195 |    10   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                              |     2 |   110 |    10   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                              |     2 |    66 |     8   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID | VENUS_ACC_SLAVE              |     2 |    38 |     6   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN           | IDX_VENUS_ACC_SLAVE_NAME     |     2 |       |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN            | IDX_VENUS_AUTH_SLAVEID_ID    |     1 |    14 |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID  | VENUS_ACC_MASTER             |     1 |    22 |     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN           | PK_VENUS_ACC_MASTER          |     1 |       |     0   (0)| 00:00:01 |
|  11 |     VIEW                          | VENUS_ACC_ORGANIZATIONVIEW   |     1 |    10 |     0   (0)| 00:00:01 |
|  12 |      SORT UNIQUE                  |                              |       |       |            |          |
|  13 |       UNION-ALL PARTITION         |                              |       |       |            |          |
|  14 |        TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATION       |     1 |    75 |     3   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN          | PK_VENUS_ACC_ORGANIZATION    |     1 |       |     2   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATIONEXT    |     1 |    62 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN         | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |       |     0   (0)| 00:00:01 |
|  18 |   SORT ORDER BY                   |                              |  2358 |   156K|    16  (25)| 00:00:01 |
|* 19 |    CONNECT BY WITH FILTERING      |                              |       |       |            |          |
|* 20 |     FILTER                        |                              |       |       |            |          |
|  21 |      COUNT                        |                              |       |       |            |          |
|  22 |       VIEW                        | VENUS_ACC_ORGANIZATIONVIEW   |  2358 |   156K|    14  (15)| 00:00:01 |
|  23 |        SORT UNIQUE                |                              |  2358 |   172K|    14  (36)| 00:00:01 |
|  24 |         UNION-ALL                 |                              |       |       |            |          |
|  25 |          TABLE ACCESS FULL        | VENUS_ACC_ORGANIZATION       |  2317 |   169K|     9   (0)| 00:00:01 |
|  26 |          TABLE ACCESS FULL        | VENUS_ACC_ORGANIZATIONEXT    |    41 |  2542 |     3   (0)| 00:00:01 |
|* 27 |      VIEW                         |                              |     3 |   102 |     2   (0)| 00:00:01 |
|  28 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D6735_A5EF84F8  |     3 |    30 |     2   (0)| 00:00:01 |
|* 29 |     HASH JOIN                     |                              |       |       |            |          |
|  30 |      CONNECT BY PUMP              |                              |       |       |            |          |
|  31 |      COUNT                        |                              |       |       |            |          |
|  32 |       VIEW                        | VENUS_ACC_ORGANIZATIONVIEW   |  2358 |   156K|    14  (15)| 00:00:01 |
|  33 |        SORT UNIQUE                |                              |  2358 |   172K|    14  (36)| 00:00:01 |
|  34 |         UNION-ALL                 |                              |       |       |            |          |
|  35 |          TABLE ACCESS FULL        | VENUS_ACC_ORGANIZATION       |  2317 |   169K|     9   (0)| 00:00:01 |
|  36 |          TABLE ACCESS FULL        | VENUS_ACC_ORGANIZATIONEXT    |    41 |  2542 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("T4"."STATUS"<>9)
   7 - access("T4"."NAME"='xiayuping')
   8 - access("T7"."SLAVEID"="T4"."ID")
   9 - filter("T6"."STATUS"<>9)
  10 - access("T6"."ID"=TO_CHAR("T7"."ID"))
  15 - access("ID"="T6"."ORGOF")
  17 - access("ID"="T6"."ORGOF")
  19 - access("T"."ID"=PRIOR "T"."PARENTORGID")
  20 - filter( EXISTS (SELECT 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "ID" FROM
              "SYS"."SYS_TEMP_0FD9D6735_A5EF84F8" "T1") "AA" WHERE "ID"=:B1))
  27 - filter("ID"=:B1)
  29 - access("T"."ID"=PRIOR "T"."PARENTORGID")
Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
       7328  consistent gets
          1  physical reads
        648  redo size
        573  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          3  rows processed

逻辑读降低为7328，但是子查询还是没有展开，因为这个不是关键，因为我们已经固化了临时结果集。
关键是VENUS_ACC_ORGANIZATION和VENUS_ACC_ORGANIZATIONEXT的主键ID居然没有被选择，尝试了各种办法都无法将谓词推入到视图中，环境是10.2.0.4，肯定和union有关，没有深究，最后把视图拆开。

视图定义：
CREATE OR REPLACE FORCE VIEW "VENUSTECH"."VENUS_ACC_ORGANIZATIONVIEW" ("ID", "PARENTORGID", "AUTOID", "NAME", "STATUS", "TNAME") ASm
SELECT ID,PARENTORGID,AUTOID,NAME,STATUS,'VENUS_ACC_ORGANIZATION' AS TNAME FROM VENUS_ACC_ORGANIZATION
UNION
SELECT ID,PARENTORGID,ID AS AUTOID,NAME,STATUS,'VENUS_ACC_ORGANIZATIONEXT' AS TNAME FROM VENUS_ACC_ORGANIZATIONEXT

sql再次改写:
with aa as(
        SELECT /*+ materialize */t5.ID 
        FROM venustech.venus_auth_masterauth t7,
            venustech.venus_acc_slave t4,
            venustech.venus_acc_organizationview t5,
            venustech.venus_acc_master t6
        WHERE 1 = 1
            AND t5.ID = t6.orgof
            AND t6.STATUS != 9
            AND t7.slaveid = t4.id
            AND to_char(t7.id) = t6.id
            AND t4.STATUS != 9
            AND t4.NAME = 'xiayuping')
SELECT t.id
  FROM venustech.VENUS_ACC_ORGANIZATION t 
 start WITH t.id IN (select id from aa)
connect BY prior t.parentorgid = t.id
union
SELECT t.id
  FROM venustech.VENUS_ACC_ORGANIZATIONEXT t 
 start WITH t.id IN (select id from aa)
connect BY prior t.parentorgid = t.id;



执行计划以及统计信息为：
Plan hash value: 1633223951
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |     2 |    27 |     6  (50)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION        |                              |       |       |            |          |
|   2 |   LOAD AS SELECT                  |                              |       |       |            |          |
|   3 |    NESTED LOOPS                   |                              |     3 |   195 |    10   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                              |     2 |   110 |    10   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                              |     2 |    66 |     8   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID | VENUS_ACC_SLAVE              |     2 |    38 |     6   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN           | IDX_VENUS_ACC_SLAVE_NAME     |     2 |       |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN            | IDX_VENUS_AUTH_SLAVEID_ID    |     1 |    14 |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID  | VENUS_ACC_MASTER             |     1 |    22 |     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN           | PK_VENUS_ACC_MASTER          |     1 |       |     0   (0)| 00:00:01 |
|  11 |     VIEW                          | VENUS_ACC_ORGANIZATIONVIEW   |     1 |    10 |     0   (0)| 00:00:01 |
|  12 |      SORT UNIQUE                  |                              |       |       |            |          |
|  13 |       UNION-ALL PARTITION         |                              |       |       |            |          |
|  14 |        TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATION       |     1 |    75 |     3   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN          | PK_VENUS_ACC_ORGANIZATION    |     1 |       |     2   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID| VENUS_ACC_ORGANIZATIONEXT    |     1 |    62 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN         | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |       |     0   (0)| 00:00:01 |
|  18 |   SORT UNIQUE                     |                              |     2 |    27 |     6  (50)| 00:00:01 |
|  19 |    UNION-ALL                      |                              |       |       |            |          |
|* 20 |     CONNECT BY WITH FILTERING     |                              |       |       |            |          |
|  21 |      TABLE ACCESS BY INDEX ROWID  | VENUS_ACC_ORGANIZATION       |       |       |            |          |
|  22 |       NESTED LOOPS                |                              |     3 |   129 |     5   (0)| 00:00:01 |
|  23 |        VIEW                       |                              |     3 |   102 |     2   (0)| 00:00:01 |
|  24 |         TABLE ACCESS FULL         | SYS_TEMP_0FD9D6734_A5EF84F8  |     3 |    30 |     2   (0)| 00:00:01 |
|* 25 |        INDEX RANGE SCAN           | PK_VENUS_ACC_ORGANIZATION    |     1 |     9 |     1   (0)| 00:00:01 |
|  26 |      NESTED LOOPS                 |                              |       |       |            |          |
|  27 |       CONNECT BY PUMP             |                              |       |       |            |          |
|  28 |       TABLE ACCESS BY INDEX ROWID | VENUS_ACC_ORGANIZATION       |     1 |    14 |     3   (0)| 00:00:01 |
|* 29 |        INDEX RANGE SCAN           | PK_VENUS_ACC_ORGANIZATION    |     1 |       |     2   (0)| 00:00:01 |
|* 30 |     CONNECT BY WITH FILTERING     |                              |       |       |            |          |
|  31 |      TABLE ACCESS BY INDEX ROWID  | VENUS_ACC_ORGANIZATIONEXT    |       |       |            |          |
|  32 |       NESTED LOOPS                |                              |     3 |   132 |     2   (0)| 00:00:01 |
|  33 |        VIEW                       |                              |     3 |   102 |     2   (0)| 00:00:01 |
|  34 |         TABLE ACCESS FULL         | SYS_TEMP_0FD9D6734_A5EF84F8  |     3 |    30 |     2   (0)| 00:00:01 |
|* 35 |        INDEX UNIQUE SCAN          | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |    10 |     0   (0)| 00:00:01 |
|  36 |      NESTED LOOPS                 |                              |       |       |            |          |
|  37 |       CONNECT BY PUMP             |                              |       |       |            |          |
|  38 |       TABLE ACCESS BY INDEX ROWID | VENUS_ACC_ORGANIZATIONEXT    |     1 |    13 |     1   (0)| 00:00:01 |
|* 39 |        INDEX UNIQUE SCAN          | PK_VENUS_ACC_ORGANIZATIONEXT |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("T4"."STATUS"<>9)
   7 - access("T4"."NAME"='xiayuping')
   8 - access("T7"."SLAVEID"="T4"."ID")
   9 - filter("T6"."STATUS"<>9)
  10 - access("T6"."ID"=TO_CHAR("T7"."ID"))
  15 - access("ID"="T6"."ORGOF")
  17 - access("ID"="T6"."ORGOF")
  20 - access("T"."ID"=PRIOR "T"."PARENTORGID")
  25 - access("T"."ID"="ID")
  29 - access("T"."ID"=PRIOR "T"."PARENTORGID")
  30 - access("T"."ID"=PRIOR "T"."PARENTORGID")
  35 - access("T"."ID"="ID")
  39 - access("T"."ID"=PRIOR "T"."PARENTORGID")
Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
         50  consistent gets
          1  physical reads
        692  redo size
        573  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          3  rows processed

逻辑读降低到了50，完美。 这个sql有点优化强迫症了。

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

转载于:http://blog.itpub.net/31347199/viewspace-2123081/

cr13324309138
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL实现类似于connect_by_isleaf的功能MySQL方法或存储过程
09-09
在MySQL中，面对类似Oracle的`connect_by_isleaf`功能的需求，即查找并修改指定ID的所有子级记录，可以通过自连接、存储过程或者递归函数等方法实现。这里我们主要探讨两种MySQL的实现方式：一种是使用用户定义的...
关于递归效率低的解决方法___SQL递归
hnbcjzj的专栏
01-07 6686
<br />有时在程序代码中要用到递归，其他递归一个比较费时的，其实有时候我们不需要用代码同样也可以解决这类问题，比如说一棵树，要查询一棵树，如果代码用递归去写太慢了，如果这棵树比较大，我们就不得不频繁地跟数据库打交道，可不可以在给出父节点后把所有子孙节点都查出来呢，你可以用下面这个sql来解决问题<br />select *  from tree t <br />connect by prior id = parent_id start with paren_id = '1200';这就是查父节点是120
oracle 中 connect by prior 递归算法 -- 理解
weixin_43761722的博客
06-30 568
oracle 中 connect by prior 递归算法
Oracle 有表连接的connect by 的优化
DATA
11-07 1541
说明 有1个有表连接，还有connect by 的SQL，整了好几天才优化成功，感觉像游戏中打死了只大boss一样。现将这个过程整理一下。 优化前：   SELECT r.OUT_VER_BEGIN_IDdataID   FROM DMS_DATA_RELA r, DMS_OBJ o  WHERE r.DELETE_FLAG = '0'    AND r.RELA_TYPE_CO
oracle connect by优化小探
u011165335的博客
09-23 4433
前一篇介绍了connect by的用法 https://blog.csdn.net/u011165335/article/details/82822224 这里在看看如何优化: 环境跟上一篇的一样:11g -- 表结构 drop table menu; create table menu( mid varchar2(64) not null, parent_id varchar2(64) ...
oracle递归函数
热门推荐
pengguojun117的专栏
06-26 1万+
oracle start with connect by 用法 oracle中 connect by prior 递归算法  Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是：  select ... from tablename start with 条件1  connect by 条件2
connect by的使用
07-27
在Oracle数据库中，`CONNECT BY` 是一个非常重要的SQL语法，用于构建层次查询，它能够帮助我们处理具有层级关系的数据，比如组织结构、产品分类、树形菜单等。`CONNECT BY` 查询允许从一个表中抽取数据，并按照指定...
Lora-32-Connect-by-Wifi
04-11
总之，"Lora-32-Connect-by-Wifi"是一个结合了LoRa无线通信和Wi-Fi连接的项目，涉及到嵌入式系统、C语言编程、无线通信协议等多个领域的知识。实现这个项目需要理解Wi-Fi连接、LoRa通信机制以及如何在微控制器上编写...
mysql优化配置参数
09-11
8. `sort_buffer`: 排序缓冲区大小，用于排序操作，如ORDER BY或GROUP BY。 9. `record_buffer`: 记录缓冲区大小，用于在扫描表时临时存储行。 10. `thread_cache`: 线程缓存大小，用于存储已创建但暂时未使用的...
oracle ---递归查询（start with --connect by prior ）
qq_24896653的博客
01-06 504
递归查询要处理的问题：树形结构数据，存储在一张表中，然后通过start with --connect by prior-来进行遍历 示例基础: create table a_test ( p_id  varchar2(10),    id    varchar2(10)); insert into a_test values ( '', '1' ); insert into a
子查询中使用了递归CONNECT BY巨慢的解决方案
zwxiaole的专栏
12-09 2173
有一段sql执行速度很慢 要50s 经查询是由于在sql的子查询中使用了递归查询导致 sql如下 select (SELECT to_char(temp.rwwcsx, 'yyyy-mm-dd') from (SELECT * FROM fxyd_rwdy_mx mx order by mx.rwdy_sj desc...
CONNECT BY PRIOR语句优化案例
congan6145125的博客
12-10 894
原语句文本 SELECT * FROM (SELECT DISTINCT A.AUTHID,A.AUTHCODE,A.SUPERCODE,A.AUTHNAME,A.MENUFLAG,A.DESCRI...
mysql更改执行计划_11g中 connect by 语句执行计划改变
weixin_28702613的博客
01-28 295
从10.2.0.3升级到11.2.0.4的朋友，如果细心会发现，以下sql在11.2.0.4中执行效率变低(该sql主要是获取连接用户获取权限信息)select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and priv...
oracle中connect by语句的优化
weixin_34416649的博客
08-01 658
很多应用中都会有类似组织机构的表，组织机构的表又通常是典型的层次结构（没有循环节点）。于是通过组织控制数据权限的时候，许多人都喜欢通过connect by获得组织信息，然后再过滤目标数据。 在有些情况下，这样写并没有什么问题，但有些情况下，这个就是一个大问题。 归根结底，这是connect by特性导致的，oracle无法知道connect by之后到底返回多少数据，所以有可能采取一些你所不期...
Oracle 树查询 性能优化纪实（start with, connect by）
cpda53349199的博客
07-14 831
在项目中做权限控制时，需要用组织阶层来控制能够访问的数据， 比如A组织的人可以看到其下属组织的人员数据，或者只有A组织是B组织上级的时候才有看B组织人员数据的权利。 根据需求需要构筑DB的表结构，如下（ORG_RANK...
Oracle 层级查询 connect by prior再理解
Richie's 残酷舞台
06-30 2182
Oracle 层级查询 connect by prior
Connect reset by peer
最新发布
01-17
"Connection reset by peer"是一个常见的网络错误，它表示在网络连接的一端关闭了连接，而另一端仍然在尝试发送数据。这种错误通常发生在以下几种情况下： 1. 服务器的并发连接数超过了其承载量，服务器会将其中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值