使用leading(,)优化sql执行计划

今天在数据库中捕获到一条sql，其cost很小但是逻辑读又极高，sql大致如下
select count(*)
  from JUSTIN_REFER u_f
  left join JUSTIN u
    on u_f.refer_id = u.id
where u.ip =:a;
一般来说对于cost极小但是buffer gets又很高的语句，大多是由统计信息不准确造成的，
但是这两个表都是7月份收集的，且两个表的数据改动都是很平稳的，不应该是统计信息出错。

SQL> select table_name,last_analyzed from user_tables where table_name in('JUSTIN_REFER','JUSTIN');

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------
JUSTIN                       2011-7-27 上午
JUSTIN_REFER                 2011-7-2 上午

查看一下绑定变量的历史记录
SQL> select value_string,count(*) from dba_hist_sqlbind where sql_id ='dsc850x8y7can' group by value_string;

VALUE_STRING                                                                       COUNT(*)
-------------------------------------------------------------------------------- ----------
180.a.b.c                                                                           1
222.a.b.c                                                                           401
58.a.b.c                                                                            1
60.a.b.c                                                                            1
218.a.b.c                                                                           1
可以看到大部分时候都是选择222.a.b.c作为绑定值，采用autotrace查看一下其执行计划
SQL> set autotrace traceonly
SQL> set linesize 300 pagesize 300
SQL> variable a varchar2(200);
SQL> exec :a := '222.a.b.c';

PL/SQL procedure successfully completed.

SQL> select count(*)
  2    from JUSTIN_REFER u_f
  3   join JUSTIN u
  4      on u_f.refer_id = u.id
  5   where u.ip = :a;

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     1 |    19 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                            |     1 |    19 |            |          |
|   2 |   NESTED LOOPS                |                            |    11 |   209 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| JUSTIN                     |     2 |    26 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_JUSTIN_IP              |     2 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX_JUSTIN_R_refer_id      |     5 |    30 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
   4 - access("U"."IP"=:A)
   5 - access("U_F"."refer_id"="U"."ID")
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
    2183130  consistent gets
         35  physical reads
      12572  redo size
        517  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
1        rows processed

目前JUSTIN表已经很大了，有1000万条记录，而当ip值为222.a.b.c时，访问JUSTIN的代价很大
SQL> select id from JUSTIN where ip = :a;                  

971191 rows selected.
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     2 |    26 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| JUSTIN          |     2 |    26 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_JUSTIN_IP   |     2 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("IP"=:A)
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
     350669  consistent gets
          0  physical reads
          0  redo size
   18780090  bytes sent via SQL*Net to client
     712693  bytes received via SQL*Net from client
      64748  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     971191  rows processed
我们可以看到，上面这个单表查询语句返回971191行，逻辑读就有35万多，无怪乎整个sql的逻辑读如此之大；
再来看一下表JUSTIN_REFER的数据分布
SQL> select count(*), count(distinct refer_id) from JUSTIN_REFER;

  COUNT(*) COUNT(DISTINCTrefer_id)
---------- --------------------------
    418756                      89276
该表总共也就40万条记录，且refer_id的选择性极佳。
现在我们可以尝试通过加hint来优化该语句，把JUSTIN_REFER作为驱动表，将其过滤后的数据再与JUSTIN表关联；
SQL> select /*+ leading(u_f,u) */ count(*)
  2    from JUSTIN_REFER u_f
  3    left join JUSTIN u
  4      on u_f.refer_id = u.id
  5   where u.ip =:a;

  COUNT(*)
----------
    172625


Execution Plan
----------------------------------------------------------
Plan hash value: 3468976922

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     1 |    19 |       |   782   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE               |                            |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN                   |                            |    11 |   209 |  7048K|   782   (2)| 00:00:10 |
|   3 |    INDEX FAST FULL SCAN       | IDX_JUSTIN_R_refer_id      |   400K|  2348K|       |   427   (2)| 00:00:06 |
|   4 |    TABLE ACCESS BY INDEX ROWID| JUSTIN                     |     2 |    26 |       |     5   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | IDX_JUSTIN_IP              |     2 |       |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - access("U_F"."refer_id"="U"."ID")
   5 - access("U"."IP"=:A)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     238142  consistent gets
          0  physical reads
         80  redo size
        517  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
逻辑读原来的2183130下降为238142，代价下降为原来的1/10;
但此时访问justin表还是选择了ip字段上的索引，再添加hint强制其选择id主键
SQL> select /*+ leading(u_f,u) index(u,pk_justin) */ count(*)
  2    from JUSTIN_REFER u_f
  3    left join JUSTIN u
  4      on u_f.refer_id = u.id
  5   where u.ip = :a;

Execution Plan
----------------------------------------------------------
Plan hash value: 3610563294

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     1 |    19 |   403K  (1)| 01:20:45 |
|   1 |  SORT AGGREGATE               |                            |     1 |    19 |            |          |
|   2 |   NESTED LOOPS                |                            |    11 |   209 |   403K  (1)| 01:20:45 |
|   3 |    INDEX FAST FULL SCAN       | IDX_JUSTIN_R_refer_id      |   400K|  2348K|   427   (2)| 00:00:06 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| JUSTIN                     |     1 |    13 |     2   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_JUSTIN                  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   4 - filter("U"."IP"=:A)
   5 - access("U_F"."refer_id"="U"."ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1277827  consistent gets
          6  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

此时的逻辑读又升高到1277827，几次尝试过后，最终只添加了/*+ leading(u_f,u) */，最后的sql为
select /*+ leading(u_f,u) */ count(*)
  from JUSTIN_REFER u_f
  left join JUSTIN u
    on u_f.refer_id = u.id
where u.ip =:a;

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

转载于:http://blog.itpub.net/15480802/viewspace-703270/