ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 加hint改变执行计划访问顺序优化sql

加hint改变执行计划访问顺序优化sql

原创 Linux操作系统 作者:myownstars 时间:2011-01-26 17:38:19 0 删除 编辑
今天又捕获到一个执行代价比较高的sql,下面是对其优化的大致步骤
sql语句如下
SELECT *
  FROM (SELECT row_.*, rownum rn
          FROM (select /*+ leading(eu,sh) */ sh.*,
                       pm.PAYMENT_TYPE,
                       pm.PAYMENT_NAME as ORDER_justin_method_NAME
                  from v_justin sh
                  left join justin_user eu
                    on eu.id = sh.justin_user_ID
                  left join justin_method pm
                    on pm.id = sh.ORDER_justin_method_ID
                 where sh.IS_LEAF = :g
                   and sh.mc_site_id in (:a)
                   and eu.justin_user_NAME like :b
                   and trunc(sh.ORDER_CREATE_TIME) >=
                       trunc(to_date(:c, 'yyyy-mm-dd'))
                   and trunc(sh.ORDER_CREATE_TIME) <=
                       trunc(to_date(:d, 'yyyy-mm-dd'))
                 order by sh.ORDER_CREATE_TIME desc) row_
         WHERE rownum <= :e)

通过set autotrace查看执行计划和consistent gets
SQL> var a number;
SQL> var b varchar2(200);
SQL> var c varchar2(20);
SQL> var d varchar2(20);
SQL> var e number;
SQL> var f number;
SQL> var g number;
SQL> exec :a := 1;

PL/SQL procedure successfully completed.

SQL> exec :b :='%gui_8@126.com%';

PL/SQL procedure successfully completed.

SQL> exec :c := '2010-10-31';

PL/SQL procedure successfully completed.

SQL> exec :d := '2011-01-20';

PL/SQL procedure successfully completed.

SQL> exec :e := 20;

PL/SQL procedure successfully completed.

SQL> exec :f := 0;

PL/SQL procedure successfully completed.

SQL> exec :g := 1;

PL/SQL procedure successfully completed.


查看原语句执行计划,可以看到consisten gets值非常高,达到七位数
--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |    98 |   537K| 12576   (1)| 00:02:31 |
|*  1 |  VIEW                                |                 |    98 |   537K| 12576   (1)| 00:02:31 |
|*  2 |   COUNT STOPKEY                      |                 |       |       |            |          |
|   3 |    VIEW                              |                 |    98 |   535K| 12576   (1)| 00:02:31 |
|*  4 |     SORT ORDER BY STOPKEY            |                 |    98 |   536K| 12576   (1)| 00:02:31 |
|*  5 |      HASH JOIN RIGHT OUTER           |                 |    98 |   536K| 12575   (1)| 00:02:31 |
|   6 |       TABLE ACCESS FULL              | justin_method   |    77 |  1694 |     2   (0)| 00:00:01 |
|   7 |       NESTED LOOPS                   |                 |    98 |   534K| 12573   (1)| 00:02:31 |
|   8 |       VIEW                          | v_justin        |  1952 |    10M|  8665   (1)| 00:01:44 |
|   9 |         UNION-ALL                    |                 |       |       |            |          |
|* 10 |          FILTER                      |                 |       |       |            |          |
|* 11 |           TABLE ACCESS BY INDEX ROWID| justin_a        |   151 | 71725 |   447   (1)| 00:00:06 |
|* 12 |            INDEX RANGE SCAN          | IDX_CREATE_TIME |  1089 |       |    13   (0)| 00:00:01 |
|* 13 |          FILTER                      |                 |       |       |            |          |
|* 14 |           TABLE ACCESS BY INDEX ROWID| justin_b        |  1801 |   851K|  8218   (1)| 00:01:39 |
|* 15 |            INDEX RANGE SCAN          | IDX_CREATE_DATE | 13007 |       |   765   (1)| 00:00:10 |
|* 16 |        TABLE ACCESS BY INDEX ROWID   | justin_user     |     1 |    25 |     2   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN            | PK1             |     1 |       |     1   (0)| 00:00:01
--------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
    2804436  consistent gets
          0  physical reads
        124  redo size
      11145  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

可优化部分主要集中于黑体字区域,其中view v_justin部分是访问视图,暂不考虑优化,剩下的只有justin_user表了,该sql访问了justin_user表里的justin_user_name字段,但是执行计划中并未出现;
通过justin_user_name可以顾虑很大一部分条件,并且以该列为引导列建有复合的unique index,可以改变执行计划的访问顺序,让它用到该索引,且提早执行
考虑添加hint改变访问顺序,
SQL> SELECT *
  2    FROM (SELECT row_.*, rownum rn
  3            FROM (select /*+ leading(eu,sh) */ sh.*,
  4                         pm.PAYMENT_TYPE,
  5                         pm.PAYMENT_NAME as ORDER_justin_method_NAME
  6                    from v_justin sh
  7                    left join justin_user eu
  8                      on eu.id = sh.justin_user_ID
  9                    left join justin_method pm
10                      on pm.id = sh.ORDER_justin_method_ID
11                   where sh.IS_LEAF = :g
12                     and sh.mc_site_id in (:a)
13                     and eu.justin_user_NAME like :b
14                     and trunc(sh.ORDER_CREATE_TIME) >=
15                         trunc(to_date(:c, 'yyyy-mm-dd'))
16                     and trunc(sh.ORDER_CREATE_TIME) <=
17                         trunc(to_date(:d, 'yyyy-mm-dd'))
18                   order by sh.ORDER_CREATE_TIME desc) row_
19           WHERE rownum <= :e)
20   WHERE RN > :f;
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    98 |   537K|       | 54186   (1)| 00:10:51 |
|*  1 |  VIEW                                |                        |    98 |   537K|       | 54186   (1)| 00:10:51 |
|*  2 |   COUNT STOPKEY                      |                        |       |       |       |            |          |
|   3 |    VIEW                              |                        |    98 |   535K|       | 54186   (1)| 00:10:51 |
|*  4 |     SORT ORDER BY STOPKEY            |                        |    98 |   536K|       | 54186   (1)| 00:10:51 |
|*  5 |      HASH JOIN RIGHT OUTER           |                        |    98 |   536K|       | 54185   (1)| 00:10:51 |
|   6 |       TABLE ACCESS FULL              | justin_method          |    77 |  1694 |       |     2   (0)| 00:00:01 |
|*  7 |       HASH JOIN                      |                        |    98 |   534K|  9088K| 54183   (1)| 00:10:51 |
|   8 |        TABLE ACCESS BY INDEX ROWID   | justin_user               |   251K|  6137K|       | 44557   (1)| 00:08:55 |
|*  9 |         INDEX RANGE SCAN             | IDX_justin_user_NAME_UNIQ | 45248 |       |       |   271   (1)| 00:00:04 |
|  10 |        VIEW                          | v_justin               |  1952 |    10M|       |  8665   (1)| 00:01:44 |
|  11 |         UNION-ALL                    |                        |       |       |       |            |          |
|* 12 |          FILTER                      |                        |       |       |       |            |          |
|* 13 |           TABLE ACCESS BY INDEX ROWID| justin_a                     |   151 | 71725 |       |   447   (1)| 00:00:06 |
|* 14 |            INDEX RANGE SCAN          | IDX_CREATE_TIME        |  1089 |       |       |    13   (0)| 00:00:01 |
|* 15 |          FILTER                      |                        |       |       |       |            |          |
|* 16 |           TABLE ACCESS BY INDEX ROWID| justin_b               |  1801 |   851K|       |  8218   (1)| 00:01:39 |
|* 17 |            INDEX RANGE SCAN          | IDX_CREATE_DATE        | 13007 |       |       |   765   (1)| 00:00:10
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     161888  consistent gets
          0  physical reads
          0  redo size
      11145  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
可以看出consistent gets变成了6位数,有所好转,但依然很高; 同时黄体部分,执行计划采用了hash join,并把justin_user当成驱动表;
继续尝试添加hint
SQL> SELECT *
  2    FROM (SELECT row_.*, rownum rn
  3            FROM (select /*+ use_nl(eu,sh) leading(eu,sh) */ sh.*,
  4                         pm.PAYMENT_TYPE,
  5                         pm.PAYMENT_NAME as ORDER_justin_method_NAME
  6                    from v_justin sh
  7                    left join justin_user eu
  8                      on eu.id = sh.justin_user_ID
  9                    left join justin_method pm
10                      on pm.id = sh.ORDER_justin_method_ID
11                   where sh.IS_LEAF = :g
12                     and sh.mc_site_id in (:a)
13                     and eu.justin_user_NAME like :b
14                     and trunc(sh.ORDER_CREATE_TIME) >=
15                         trunc(to_date(:c, 'yyyy-mm-dd'))
16                     and trunc(sh.ORDER_CREATE_TIME) <=
17                         trunc(to_date(:d, 'yyyy-mm-dd'))
18                   order by sh.ORDER_CREATE_TIME desc) row_
19           WHERE rownum <= :e)
20   WHERE RN > :f;
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    98 |   537K|  5077K  (1)| 16:55:36 |
|*  1 |  VIEW                                |                        |    98 |   537K|  5077K  (1)| 16:55:36 |
|*  2 |   COUNT STOPKEY                      |                        |       |       |            |          |
|   3 |    VIEW                              |                        |    98 |   535K|  5077K  (1)| 16:55:36 |
|*  4 |     SORT ORDER BY STOPKEY            |                        |    98 |   535K|  5077K  (1)| 16:55:36 |
|*  5 |      HASH JOIN RIGHT OUTER           |                        |    98 |   535K|  5077K  (1)| 16:55:36 |
|   6 |       TABLE ACCESS FULL              | justin_method          |    77 |  1694 |     2   (0)| 00:00:01 |
|   7 |       NESTED LOOPS                   |                        |    98 |   533K|  5077K  (1)| 16:55:36 |
|   8 |        TABLE ACCESS BY INDEX ROWID   | justin_user               |   251K|  6137K| 44557   (1)| 00:08:55 |
|*  9 |         INDEX RANGE SCAN             | IDX_justin_user_NAME_UNIQ | 45248 |       |   271   (1)| 00:00:04 |
|  10 |        VIEW                          | v_justin               |     1 |  5553 |    20   (0)| 00:00:01 |
|  11 |         UNION ALL PUSHED PREDICATE   |                        |       |       |            |          |
|* 12 |          FILTER                      |                        |       |       |            |          |
|* 13 |           TABLE ACCESS BY INDEX ROWID| justin_a                     |     1 |   475 |     8   (0)| 00:00:01 |
|* 14 |            INDEX RANGE SCAN          | IDX_SO_justin_user_ID     |     5 |       |     3   (0)| 00:00:01 |
|* 15 |          FILTER                      |                        |       |       |            |          |
|* 16 |           TABLE ACCESS BY INDEX ROWID| justin_b               |     1 |   484 |    12   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN          | IDX_SH_justin_user_ID     |     9 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      30074  consistent gets
          0  physical reads
          0  redo size
      11145  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
这次效果就非常明显了,consistent gets变成了5位数,此时对表justin_user走了基于justin_user_name的索引,但是使用的谓词为like且绑定变量有前后都有通配符%%,所以其代价应该比较大
验证一下
SQL> select id from justin_user where justin_user_name like :b;


Execution Plan
----------------------------------------------------------
Plan hash value: 4183727034

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |   251K|  6137K| 44557   (1)| 00:08:55 |
|   1 |  TABLE ACCESS BY INDEX ROWID| justin_user               |   251K|  6137K| 44557   (1)| 00:08:55 |
|*  2 |   INDEX RANGE SCAN          | IDX_justin_user_NAME_UNIQ | 45248 |       |   271   (1)| 00:00:04 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("justin_user_NAME" LIKE :B)
       filter("justin_user_NAME" LIKE :B)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      30053  consistent gets
          0  physical reads
          0  redo size
        580  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
此条sql的Consistent gets就有30053,而优化后的sql只有30074,可见优化后的sql应属于当前最优

[ 本帖最后由 myownstars 于 2011-1-26 18:40 编辑 ]

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

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

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3157626