ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过hint push_subq优化sql

通过hint push_subq优化sql

原创 Linux操作系统 作者:myownstars 时间:2011-01-24 16:00:17 0 删除 编辑
通过AWR报告捕获了一条cost比较高的sql,考虑对其优化.
该sql大致结构如下:
select rownum
        t.*,
        pm.id
        pm.in_price
        c.justin_cat__name,
        pm.justin_pro_id
   from justin_pro t
   left join justin_cat c
     on c.id = t.justin_cat_id
   join justin_pro_mer pm
     on pm.justin_pro_id = t.id
    and pm.id = (select id
                   from justin_pro_mer p_m
                  where p_m.justin_pro_id = t.id
                    and p_m.CAN_SALE = :a
                    and rownum = :b)
  where t.is_deleted = :c
    and rownum <= :d
    and t.justin_pro_type <> :e
    and t.is_hot = :f;
   
sql最主要的性能指标是consistent gets,而通过explain plan for是得不出的,选用set autotrace traceonly来查看,sql使用了绑定变量。
首先查出其绑定变量的值
SQL> select s.CHILD_NUMBER,s.NAME,s.POSITION,s.DATATYPE_STRING,s.VALUE_STRING from v$sql_bind_capture s where s.SQL_ID='4kz559dt818vm';

CHILD_NUMBER NAME                   POSITION DATATYPE_S VALUE_STRI
------------ -------------------- ---------- ---------- ----------
           1 :SYS_B_0                      1 NUMBER     1
           1 :SYS_B_1                      2 NUMBER     1
           1 :SYS_B_2                      3 NUMBER     0
           1 :SYS_B_3                      4 NUMBER     9
           1 :SYS_B_4                      5 NUMBER     2
           1 :SYS_B_5                      6 NUMBER     1
           0 :SYS_B_0                      1 NUMBER     1
           0 :SYS_B_1                      2 NUMBER     1
           0 :SYS_B_2                      3 NUMBER     0
           0 :SYS_B_3                      4 NUMBER     9
           0 :SYS_B_4                      5 NUMBER     2
           0 :SYS_B_5                      6 NUMBER     1

12 rows selected
然后给绑定变量赋值
SQL> var a number;
SQL> var b number;
SQL> var c number;
SQL> var d number;
SQL> var e number;
SQL> var f number;
SQL> exec :a:= 1;

PL/SQL procedure successfully completed.

SQL> exec :b:= 1;

PL/SQL procedure successfully completed.

SQL> exec :c:=0;

PL/SQL procedure successfully completed.

SQL> exec :d:=9;

PL/SQL procedure successfully completed.

SQL> exec :e:= 2;

PL/SQL procedure successfully completed.

SQL> exec :f:= 1;

PL/SQL procedure successfully completed.

调用set autotrace,获取其执行计划以及consistent gets值
SQL> set autotrace traceonly;
SQL> select rownum
        t.*,
        pm.id
        pm.in_price
        c.justin_cat__name,
        pm.justin_pro_id
   from justin_pro t
   left join justin_cat c
     on c.id = t.justin_cat_id
   join justin_pro_mer pm
     on pm.justin_pro_id = t.id
    and pm.id = (select id
                   from justin_pro_mer p_m
                  where p_m.justin_pro_id = t.id
                    and p_m.CAN_SALE = :a
                    and rownum = :b)
  where t.is_deleted = :c
    and rownum <= :d
    and t.justin_pro_type <> :e
    and t.is_hot = :f;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2173875363

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |   643 |       |   163K  (1)| 00:32:42 |
|*  1 |  COUNT STOPKEY                  |                  |       |       |       |            |          |
|*  2 |   FILTER                        |                  |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER        |                  | 37206 |    22M|       |  7029   (2)| 00:01:25 |
|   4 |     TABLE ACCESS FULL           | justin_cat       |  3073 |   192K|       |    16   (0)| 00:00:01 |
|*  5 |     HASH JOIN                   |                  | 37206 |    20M|  8800K|  7012   (2)| 00:01:25 |
|*  6 |      TABLE ACCESS FULL          | justin_pro       | 17771 |  8590K|       |  3542   (2)| 00:00:43 |
|   7 |      TABLE ACCESS FULL          | justin_pro_mer   |   262K|    21M|       |  1842   (2)| 00:00:23 |
|   8 |    COUNT                        |                  |       |       |       |            |          |
|*  9 |     FILTER                      |                  |       |       |       |            |          |
|* 10 |      TABLE ACCESS BY INDEX ROWID| justin_pro_mer   |     1 |    15 |       |     5   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN          | IDX_PRO_ID       |     2 |       |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=TO_NUMBER(D))
   
2 - filter("PM"."ID"= (SELECT "ID" FROM "justin_pro_mer" "P_M" WHERE ROWNUM=TO_NUMBER(B) AND
              "P_M"."justin_pro_ID"=:B1 AND "P_M"."CAN_SALE"=TO_NUMBER(A)))

   3 - access("C"."ID"(+)="T"."justin_cat_ID")
   5 - access("PM"."justin_pro_ID"="T"."ID")
   6 - filter("T"."IS_DELETED"=TO_NUMBER(C) AND "T"."IS_HOT"=TO_NUMBER(F) AND
              "T"."justin_pro_TYPE"<>TO_NUMBER(E))
   9 - filter(ROWNUM=TO_NUMBER(B))
  10 - filter("P_M"."CAN_SALE"=TO_NUMBER(A))
  11 - access("P_M"."justin_pro_ID"=:B1)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      29382  consistent gets
          6  physical reads
          0  redo size
      91945  bytes sent via SQL*Net to client
      54056  bytes received via SQL*Net from client
         38  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed

根据执行计划可以看出,对表justin_pro_mer做了两次访问,其中一次还是全表扫描,而这两次的访问却没有任何的关联,即子查询没有展开。
子查询部分的代价很小,可以考虑将其展开处理,采用hint push_subq。

select /*+ push_subq(@tmp) */ rownum
        t.*,
        pm.id
        pm.in_price
        c.justin_cat__name,
        pm.justin_pro_id
   from justin_pro t
   left join justin_cat c
     on c.id = t.justin_cat_id
   join justin_pro_mer pm
     on pm.justin_pro_id = t.id
    and pm.id = (select /*+QB_Name(tmp)*/ id
                   from justin_pro_mer p_m
                  where p_m.justin_pro_id = t.id
                    and p_m.CAN_SALE = :a
                    and rownum = :b)
  where t.is_deleted = :c
    and rownum <= :d
    and t.justin_pro_type <> :e
    and t.is_hot = :f;


Execution Plan
----------------------------------------------------------
Plan hash value: 17688155

------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  | 17771 |    10M| 21355   (1)| 00:04:17 |
|*  1 |  COUNT STOPKEY                    |                  |       |       |            |          |
|   2 |   NESTED LOOPS                    |                  | 17771 |    10M| 21350   (1)| 00:04:17 |
|*  3 |    HASH JOIN RIGHT OUTER          |                  | 17771 |  9701K|  3559   (2)| 00:00:43 |
|   4 |     TABLE ACCESS FULL             | justin_cat       |  3073 |   192K|    16   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL             | justin_pro       | 17771 |  8590K|  3542   (2)| 00:00:43 |
|*  6 |    TABLE ACCESS BY INDEX ROWID    | justin_pro_mer   |     1 |    84 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN             | PK4              |     1 |       |     0   (0)| 00:00:01 |
|   8 |      COUNT                        |                  |       |       |            |          |
|*  9 |       FILTER                      |                  |       |       |            |          |
|* 10 |        TABLE ACCESS BY INDEX ROWID| justin_pro_mer   |     1 |    15 |     5   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN          | IDX_PRO_ID       |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=TO_NUMBER(D))
   3 - access("C"."ID"(+)="T"."justin_cat_ID")
   5 - filter("T"."IS_DELETED"=TO_NUMBER(C) AND "T"."IS_HOT"=TO_NUMBER(F) AND
              "T"."justin_pro_TYPE"<>TO_NUMBER(E))
   6 - filter("PM"."justin_pro_ID"="T"."ID")
   
7 - access("PM"."ID"= (SELECT /*+ PUSH_SUBQ QB_NAME ("TMP") */ "ID" FROM
              "justin_pro_mer" "P_M" WHERE ROWNUM=TO_NUMBER(B) AND "P_M"."justin_pro_ID"=:B1 AND
              "P_M"."CAN_SALE"=TO_NUMBER(A)))

   9 - filter(ROWNUM=TO_NUMBER(B))
  10 - filter("P_M"."CAN_SALE"=TO_NUMBER(A))
  11 - access("P_M"."justin_pro_ID"=:B1)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        702  consistent gets
         30  physical reads
          0  redo size
      81501  bytes sent via SQL*Net to client
      45079  bytes received via SQL*Net from client
         41  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

可以看到consistent gets由原来的2万多下降了不到1000。
与push_subq相对的hint是no_unnest,即让子查询不展开,在处理多表连接并带有子查询的sql中,合理的使用这两个hint会有非常好的效果,

[ 本帖最后由 myownstars 于 2011-1-24 16:05 编辑 ]

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

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

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112518