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

-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
|* 5 | INDEX RANGE SCAN | IDX_JUSTIN_R_refer_id | 5 | 30 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
SQL> select count(*), count(distinct refer_id) from JUSTIN_REFER;
COUNT(*) COUNT(DISTINCTrefer_id)
---------- --------------------------
418756 89276
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
where u.ip =:a;
一般来说对于cost极小但是buffer gets又很高的语句，大多是由统计信息不准确造成的，
但是这两个表都是7月份收集的，且两个表的数据改动都是很平稳的，不应该是统计信息出错。
SQL> select table_name,last_analyzed from user_tables where table_name in('JUSTIN_REFER','JUSTIN');
0 sorts (memory)
0 sorts (disk)
971191 rows processed
我们可以看到，上面这个单表查询语句返回971191行，逻辑读就有35万多，无怪乎整个sql的逻辑读如此之大；
再来看一下表JUSTIN_REFER的数据分布
0 db block gets
2183130 consistent gets
35 physical reads
12572 redo size
517 bytes sent via SQL*Net to client
该表总共也就40万条记录，且refer_id的选择性极佳。
现在我们可以尝试通过加hint来优化该语句，把JUSTIN_REFER作为驱动表，将其过滤后的数据再与JUSTIN表关联；
SQL> select /*+ leading(u_f,u) */ count(*)
2 from JUSTIN_REFER u_f
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
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;
| 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 |
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';
2 - access("IP"=:A)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
350669 consistent gets
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
查看一下绑定变量的历史记录
SQL> select value_string,count(*) from dba_hist_sqlbind where sql_id ='dsc850x8y7can' group by value_string;
VALUE_STRING COUNT(*)
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 - access("U"."IP"=:A)
5 - access("U_F"."refer_id"="U"."ID")
Statistics
----------------------------------------------------------
7 recursive calls
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ----------
180.a.b.c 1
使用leading(,)优化sql执行计划
个人ቤተ መጻሕፍቲ ባይዱ类：优化案例
今天在数据库中捕获到一条sql，其cost很小但是逻辑读又极高，sql大致如下
select count(*)
from JUSTIN_REFER u_f
left join JUSTIN u
on u_f.refer_id = u.id
| 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 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | NESTED LOOPS | | 11 | 209 | 9 (0)| 00:00:01 |
222.a.b.c 401
58.a.b.c 1
1 rows processed
目前JUSTIN表已经很大了，有1000万条记录，而当ip值为222.a.b.c时，访问JUSTIN的代价很大
SQL> select id from JUSTIN where ip = :a;
971191 rows selected.
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 9 (0)| 00:00:01 |
TABLE_NAME LAST_ANALYZED
------------------------------ -------------
JUSTIN 2011-7-27 上午
JUSTIN_REFER 2011-7-2 上午