今天在数据库中捕获到一条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博客

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

最新文章