DBLINK 无统计信息导致SQL变慢_综合

今天重庆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来优化，具体就不多说了。