ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一个sql的优化

一个sql的优化

原创 Linux操作系统 作者:babyyellow 时间:2011-08-01 16:47:37 0 删除 编辑
处理了一起sql优化
[code]
SELECT
  *
FROM
  (
    SELECT
      f.name AS variance_name,
      a.*,
      (a.last_count_order-a.count_order) AS rise_diff ,
      b.name                             AS t_name,
      b.parent_id                        AS t_parent_id,
      b.forum_dir,
      b.forum_id AS t_forum_id,
      b.pub_url  AS t_pub_url,
      b.pub_dir ,
      d.price            AS area_price ,
      NVL(e.min_price,0) AS min_price,
      NVL(e.max_price,0) AS max_price
    FROM
      PDL_PRODUCT a,
      PDL_PRODUCT_TYPE b ,
      (
        SELECT
          *
        FROM
          PP_PRICE
        WHERE
          PRICE BETWEEN 501 AND 1000
        AND AREA_ID = 100
      )
      d,
      eml_price e ,
      pdl_product_variance f
    WHERE
      a.status!         =0
    AND a.review_status<>4
    AND a.order_type    <2
    AND a.concept      IS NULL
    AND a.type_id       =b.id
    AND b.status        =1
    AND b.type          =3
    AND b.parent_id     =20811
    AND a.id            =d.product_id
    AND e.product_id(+) = a.id
    AND a.variance_id   =f.id(+)
    ORDER BY
      a.last_count_order ASC
  )
WHERE
  rownum <= 300;
[/code]对应的执行计划:

Plan hash value: 1304873736

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |   300 |   624K|  6207   (1)| 00:01:15 |
|*  1 |  COUNT STOPKEY                           |                        |       |       |            |          |
|   2 |   VIEW                                   |                        |   613 |  1275K|  6207   (1)| 00:01:15 |
|*  3 |    SORT ORDER BY STOPKEY                 |                        |   613 |   373K|  6207   (1)| 00:01:15 |
|*  4 |     HASH JOIN OUTER                      |                        |   613 |   373K|  6206   (1)| 00:01:15 |
|   5 |      NESTED LOOPS OUTER                  |                        |   613 |   366K|  6148   (1)| 00:01:14 |
|*  6 |       HASH JOIN                          |                        |   613 |   352K|  6132   (1)| 00:01:14 |
|*  7 |        INDEX FAST FULL SCAN              | IDX_PP_PRIC_PRD        | 14923 |   174K|  2060   (3)| 00:00:25 |
|*  8 |        TABLE ACCESS BY INDEX ROWID       | PDL_PRODUCT_BASE       |    44 | 21252 |    33   (0)| 00:00:01 |
|   9 |         NESTED LOOPS                     |                        |  9967 |  5616K|  4071   (1)| 00:00:49 |
|* 10 |          TABLE ACCESS BY INDEX ROWID     | PDL_PRODUCT_TYPE       |   225 | 21150 |    96   (2)| 00:00:02 |
|  11 |           BITMAP CONVERSION TO ROWIDS    |                        |       |       |            |          |
|  12 |            BITMAP AND                    |                        |       |       |            |          |
|  13 |             BITMAP CONVERSION FROM ROWIDS|                        |       |       |            |          |
|* 14 |              INDEX RANGE SCAN            | PDL_PRODUCT_TYPE_I2    |       |       |     1   (0)| 00:00:01 |
|  15 |             BITMAP CONVERSION FROM ROWIDS|                        |       |       |            |          |
|* 16 |              INDEX RANGE SCAN            | PDL_PRODUCT_TYPE_I1    |       |       |    26   (0)| 00:00:01 |
|* 17 |          INDEX RANGE SCAN                | PDL_PRODUCT_TYPE_ID    |    56 |       |     2   (0)| 00:00:01 |
|  18 |       TABLE ACCESS BY INDEX ROWID        | PDL_PRODUCT_VARIANCE   |     1 |    23 |     1   (0)| 00:00:01 |
|* 19 |        INDEX UNIQUE SCAN                 | PDL_PRODUCT_VARIOUS_U1 |     1 |       |     0   (0)| 00:00:01 |
|  20 |      TABLE ACCESS FULL                   | EML_PRICE              | 88782 |  1040K|    57   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

可以看到cost 花了6207  是一个比较差的计划
可以看到大部分的性能都消耗在 hash join 上

修改后的执行计划:
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |     6 | 12786 |   167   (1)| 00:00:03 |
|*  1 |  COUNT STOPKEY                          |                        |       |       |            |          |
|   2 |   VIEW                                  |                        |     6 | 12786 |   167   (1)| 00:00:03 |
|*  3 |    SORT ORDER BY STOPKEY                |                        |     6 |  3678 |   167   (1)| 00:00:03 |
|   4 |     NESTED LOOPS OUTER                  |                        |     6 |  3678 |   166   (0)| 00:00:02 |
|   5 |      NESTED LOOPS OUTER                 |                        |     6 |  3606 |   164   (0)| 00:00:02 |
|   6 |       NESTED LOOPS                      |                        |     6 |  3468 |   163   (0)| 00:00:02 |
|   7 |        NESTED LOOPS                     |                        |    94 | 53204 |    46   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID     | PDL_PRODUCT_TYPE       |     2 |   166 |    11   (0)| 00:00:01 |
|   9 |          BITMAP CONVERSION TO ROWIDS    |                        |       |       |            |          |
|  10 |           BITMAP AND                    |                        |       |       |            |          |
|  11 |            BITMAP CONVERSION FROM ROWIDS|                        |       |       |            |          |
|* 12 |             INDEX RANGE SCAN            | PDL_PRODUCT_TYPE_I2    |    26 |       |     1   (0)| 00:00:01 |
|  13 |            BITMAP CONVERSION FROM ROWIDS|                        |       |       |            |          |
|* 14 |             INDEX RANGE SCAN            | PDL_PRODUCT_TYPE_I1    |    26 |       |     8   (0)| 00:00:01 |
|* 15 |         TABLE ACCESS BY INDEX ROWID     | PDL_PRODUCT_BASE       |    44 | 21252 |    33   (0)| 00:00:01 |
|* 16 |          INDEX RANGE SCAN               | PDL_PRODUCT_TYPE_ID    |    56 |       |     2   (0)| 00:00:01 |
|* 17 |        TABLE ACCESS BY INDEX ROWID      | PP_PRICE               |     1 |    12 |     2   (0)| 00:00:01 |
|* 18 |         INDEX UNIQUE SCAN               | PP_PRICE_PK            |     1 |       |     1   (0)| 00:00:01 |
|  19 |       TABLE ACCESS BY INDEX ROWID       | PDL_PRODUCT_VARIANCE   |     1 |    23 |     1   (0)| 00:00:01 |
|* 20 |        INDEX UNIQUE SCAN                | PDL_PRODUCT_VARIOUS_U1 |     1 |       |     0   (0)| 00:00:01 |
|  21 |      TABLE ACCESS BY INDEX ROWID        | EML_PRICE              |     1 |    12 |     1   (0)| 00:00:01 |
|* 22 |       INDEX UNIQUE SCAN                 | PK_EML_PRICE           |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

执行cost 从6200 降到了167

这里看到计划从hash join 变为了neted loop

这里还有一个点,当然针对目前这个sql 可能意义不太大,但是针对产品库意义就很大了

那就是sql 代码里大量的常量没有适用绑定变量 ,导致目前sql版本很多。

对于表关联,如果量表的数据量都比较大,hash join 不是一个好方案, 应该尽量安排走neted loop
而如果其中一个表的数据量比较小,hash join 是优于neted loop


这里的计划里,都出现了bitmap  convertion   to rowid
这个计划是oracle 希望尽可能的利用索引,少回表的一个策略,一般是两个条件都存在索引,这种情况先,我们可以考虑建立组合索引。
bitmap convertion 一般认为不是很好的计划,(这个我不能确定对错,自己决定吧)

bitmap 这个可以考虑 参数 _b_tree_bitmap_plans=false

下面列出加了组合索引的执行计划:


---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                |     6 | 12786 |   169   (1)| 00:00:03 |
|*  1 |  COUNT STOPKEY                     |                                |       |       |            |          |
|   2 |   VIEW                             |                                |     6 | 12786 |   169   (1)| 00:00:03 |
|*  3 |    SORT ORDER BY STOPKEY           |                                |     6 |  3678 |   169   (1)| 00:00:03 |
|   4 |     NESTED LOOPS OUTER             |                                |     6 |  3678 |   168   (0)| 00:00:03 |
|   5 |      NESTED LOOPS OUTER            |                                |     6 |  3606 |   166   (0)| 00:00:02 |
|   6 |       NESTED LOOPS                 |                                |     6 |  3468 |   165   (0)| 00:00:02 |
|   7 |        NESTED LOOPS                |                                |    94 | 53204 |    48   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_TYPE               |     2 |   166 |    13   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_PDL_PRD_TYP_STAT_PARID_TYP |    18 |       |     1   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_BASE               |    44 | 21252 |    33   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | PDL_PRODUCT_TYPE_ID            |    56 |       |     2   (0)| 00:00:01 |
|* 12 |        TABLE ACCESS BY INDEX ROWID | PP_PRICE                       |     1 |    12 |     2   (0)| 00:00:01 |
|* 13 |         INDEX UNIQUE SCAN          | PP_PRICE_PK                    |     1 |       |     1   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID  | PDL_PRODUCT_VARIANCE           |     1 |    23 |     1   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN           | PDL_PRODUCT_VARIOUS_U1         |     1 |       |     0   (0)| 00:00:01 |
|  16 |      TABLE ACCESS BY INDEX ROWID   | EML_PRICE                      |     1 |    12 |     1   (0)| 00:00:01 |
|* 17 |       INDEX UNIQUE SCAN            | PK_EML_PRICE                   |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------







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

请登录后发表评论 登录
全部评论
oracle MySQL Postgresql 专职数据库dba。 系统架构师。 mysql 官方认知dba 。 15年专职dba 经验。

注册时间:2010-12-02

  • 博文量
    289
  • 访问量
    1560931