http://blog.csdn.net/robinson1988/article/details/6925422
今天重庆ORACLE社区有位哥们提问,为啥索引重建(alter index rebuil)之后,SQL变慢了,以前15秒就可以完成,现在要2分多种,于是问他要了执行计划
- SQL> set autotrace traceonly
- SQL> SELECT SEQ_PAY_CUSTOMEROPER.Nextval,u.ID,'admin',1,t.LAST_LOGIN_TIME,t.LOGIN_TIMES,t.LOGIN_IP
- 2 FROM EFB_USER_MOVE@WODBLINK t,T_PAY_USERINFO u
- 3 WHERE t.ID = u.ID AND u.ID > 3500000 AND u.ID<400000
- AND t.ID > 3500000 AND t.ID <= 4000000
- 4 /
- 123832 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 4225832519
- -----------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
- -----------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 77 | 5 (0)| 00:00:01 | | |
- | 1 | SEQUENCE | SEQ_PAY_CUSTOMEROPER | | | | | | |
- | 2 | NESTED LOOPS | | 1 | 77 | 5 (0)| 00:00:01 | | |
- |* 3 | INDEX RANGE SCAN| PK_T_PAY_USERINFO | 1 | 6 | 3 (0)| 00:00:01 | | |
- | 4 | REMOTE | EFB_USER_MOVE | 1 | 71 | 2 (0)| 00:00:01 | WODBL~ | R->S |
- -----------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("U"."ID">3500000 AND "U"."ID"<=4000000)
- Remote SQL Information (identified by operation id):
- ----------------------------------------------------
- 4 - SELECT "ID","LAST_LOGIN_TIME","LOGIN_TIMES","LOGIN_IP" FROM "EFB_USER_MOVE" "T" WHERE
- "ID"<=4000000 AND "ID">3500000 AND "ID"=:1 (accessing 'WODBLINK' )
- Statistics
- ----------------------------------------------------------
- 86691 recursive calls
- 18753 db block gets
- 14781 consistent gets
- 0 physical reads
- 4035364 redo size
- 5163224 bytes sent via SQL*Net to client
- 91297 bytes received via SQL*Net from client
- 8257 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 123832 rows processed
这个SQL太简单了,可以说是我见过的SQL中最简单的,执行计划也非常简单。
这个SQL要返回123832条记录,然后执行计划走的是nested loops,问题显而易见了,原因是表EFB_USER_MOVE是通过DBLINK过来的,本地无法得知表EFB_USER_MOVE的统计信息(也就是无法知道它有多少行),所以CBO默认给它设置为1行,但是返回了123832条记录,所以给这个SQL加了个HINT
- SELECT /*+ full(u)*/ SEQ_PAY_CUSTOMEROPER.Nextval,
- u.ID,
- 'admin',
- 1,
- t.LAST_LOGIN_TIME,
- t.LOGIN_TIMES,
- t.LOGIN_IP
- FROM EFB_USER_MOVE@WODBLINK t, T_PAY_USERINFO u
- WHERE t.ID = u.ID
- AND u.ID > 3500000
- AND u.ID < 400000
- AND t.ID > 3500000
- AND t.ID <= 4000000;
这样SQL就能几秒跑完了,这里的sequence还值得注意,因为要返回123832条记录,如果sequence上的cache很小,也必然导致SQL慢,建议设置cache到1000
总结:遇到SQL语句中要引用DBLINK,需要特别留意,通常这样的SQL需要DBA添加HINT,其实这只是DBLINK中一个需要注意的地方,还有地方就是 有时候需要添加
driving_site 这个HINT来优化,具体就不多说了。