逻辑读低，性能低的一次优化

背景：维护反映客户现场的一个页面打开的速度非常慢，把该功能执行的sql语句输出到日志文件导回本地。

 

现象：

 

• 在本地执行非常快，查看执行计划，有五张表是全表扫描。
• 在客户现场查看执行，有七张表是全表扫描，逻辑读是9000多，执行时间为5秒左右，下面是客户现场的执行计划。

------------------------------------------------------------------------------
| Id  | Operation                           | A-Rows |   A-Time   | Buffers | 
----------------------------------------------------------------------------- 
|   1 |  SORT GROUP BY                      |      1 |00:00:05.05 |    9154 | 
|*  2 |   FILTER                            |  10060 |00:00:05.17 |    9154 | 
|*  3 |    HASH JOIN                        |  60710 |00:00:05.11 |    9072 | 
|*  4 |     FILTER                          |  60710 |00:00:05.15 |    5558 | 
|*  5 |      HASH JOIN OUTER                |  61672 |00:00:05.03 |    5558 | 
|*  6 |       HASH JOIN                     |  61672 |00:00:06.92 |    3166 | 
|   7 |        MERGE JOIN CARTESIAN         |   4368 |00:00:00.03 |      18 | 
|   8 |         MERGE JOIN CARTESIAN        |    728 |00:00:00.01 |      16 | 
|   9 |          TABLE ACCESS BY INDEX ROWID|     28 |00:00:00.01 |       3 | 
|* 10 |           INDEX RANGE SCAN          |     28 |00:00:00.01 |       2 | 
|  11 |          BUFFER SORT                |    728 |00:00:00.01 |      13 | 
|  12 |           VIEW                      |     26 |00:00:00.01 |      13 | 
|  13 |            UNION-ALL                |     26 |00:00:00.01 |      13 | 
|* 14 |             TABLE ACCESS FULL       |      0 |00:00:00.01 |       3 | 
|* 15 |             FILTER                  |     26 |00:00:00.01 |      10 | 
|  16 |              TABLE ACCESS FULL      |     26 |00:00:00.01 |       7 | 
|* 17 |              TABLE ACCESS FULL      |      0 |00:00:00.01 |       3 | 
|  18 |         BUFFER SORT                 |   4368 |00:00:00.01 |       2 | 
|* 19 |          TABLE ACCESS BY INDEX ROWID|      6 |00:00:00.01 |       2 | 
|* 20 |           INDEX RANGE SCAN          |      9 |00:00:00.01 |       1 | 
|* 21 |        TABLE ACCESS FULL            |  55658 |00:00:00.11 |    3148 | 
|  22 |       TABLE ACCESS FULL             |    183K|00:00:00.18 |    2392 | 
|  23 |     TABLE ACCESS FULL               |    144K|00:00:00.14 |    3514 | 
|* 24 |    TABLE ACCESS FULL                |     17 |00:00:00.01 |      82 | 
------------------------------------------------------------------------------

 

 解决办法：

 

• 通过加hits，建议数据库用索引来访问数据（注意：只是建议，数据库有权拒绝）。
• 执行时间由5秒降到了0.5秒，这不是重点，让人惊讶的是逻辑读由9000上升到了15000，为什么会这样？逻辑读增加了1.5倍，执行时间却降到了原来的1/10,这是以前没遇到过的现象。

 

下面是加了hits之后的执行计划

 

------------------------------------------------------------------------------
| Id  | Operation                            | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------
|   1 |  SORT GROUP BY                       |      1 |00:00:00.53 |   14946 |
|*  2 |   FILTER                             |  10060 |00:00:01.03 |   14946 |
|   3 |    MERGE JOIN CARTESIAN              |  60710 |00:00:03.04 |   14864 |
|*  4 |     FILTER                           |   2335 |00:00:01.44 |   14851 |
|   5 |      NESTED LOOPS OUTER              |   2372 |00:00:00.29 |   14851 |
|   6 |       NESTED LOOPS                   |   2372 |00:00:00.21 |    7899 |
|*  7 |        HASH JOIN                     |   2372 |00:00:00.16 |    3153 |
|   8 |         MERGE JOIN CARTESIAN         |    168 |00:00:00.01 |       5 |
|   9 |          TABLE ACCESS BY INDEX ROWID |     28 |00:00:00.01 |       3 |
|* 10 |           INDEX RANGE SCAN           |     28 |00:00:00.01 |       2 |
|  11 |          BUFFER SORT                 |    168 |00:00:00.01 |       2 |
|* 12 |           TABLE ACCESS BY INDEX ROWID|      6 |00:00:00.01 |       2 |
|* 13 |            INDEX RANGE SCAN          |      9 |00:00:00.01 |       1 |
|* 14 |         TABLE ACCESS FULL            |  55897 |00:00:00.11 |    3148 |
|  15 |        TABLE ACCESS BY INDEX ROWID   |   2372 |00:00:00.04 |    4746 |
|* 16 |         INDEX UNIQUE SCAN            |   2372 |00:00:00.02 |    2374 |
|  17 |       TABLE ACCESS BY INDEX ROWID    |   2036 |00:00:00.06 |    6952 |
|* 18 |        INDEX RANGE SCAN              |   2036 |00:00:00.02 |    4748 |
|  19 |     BUFFER SORT                      |  60710 |00:00:00.07 |      13 |
|  20 |      VIEW                            |     26 |00:00:00.01 |      13 |
|  21 |       UNION-ALL                      |     26 |00:00:00.01 |      13 |
|* 22 |        TABLE ACCESS FULL             |      0 |00:00:00.01 |       3 |
|* 23 |        FILTER                        |     26 |00:00:00.01 |      10 |
|  24 |         TABLE ACCESS FULL            |     26 |00:00:00.01 |       7 |
|* 25 |         TABLE ACCESS FULL            |      0 |00:00:00.01 |       3 |
|* 26 |    TABLE ACCESS FULL                 |     17 |00:00:00.01 |      82 |
------------------------------------------------------------------------------

 

 

通过观察这俩个执行计划，发现未加hits的时候，HASH JOIN 后的行数是61672行，加了hits之后，HASH JOIN 后的行数是2372行，而且HASH JOIN 的时间也大大缩短，由此推断，

虽然没加hits之前，逻辑读少，但是产生的中间数据太多，消耗内存和cpu，而加了hits之后，虽然逻辑读多了1.5倍，但产生的中间数据却少了很多，既节省内存，又节省cpu。这也给我提供了一个新的优化sql思路，并不像一些博文说的，优化的终极目标就是降低逻辑读，除此之外，还要关注sql执行的中间过程。