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