转 DBLINK 无统计信息导致SQL变慢

今天重庆ORACLE社区有位哥们提问，为啥索引重建(alter index rebuil)之后，SQL变慢了，以前15秒就可以完成，现在要2分多种，于是问他要了执行计划

view plainprint?

1. SQL>?set?autotrace?traceonly??
2. SQL>?SELECT?SEQ_PAY_CUSTOMEROPER.Nextval,u.ID,'admin',1,t.LAST_LOGIN_TIME,t.LOGIN_TIMES,t.LOGIN_IP??
3. ??2???????FROM?EFB_USER_MOVE@WODBLINK?t,T_PAY_USERINFO?u??
4. ??3???????WHERE?t.ID?=?u.ID?AND?u.ID?>?3500000?AND?u.ID<400000??
5. ?AND?t.ID?>?3500000?AND?t.ID?<=?4000000??
6. ??4??/??
7. ??
8. 123832?rows?selected.??
9. ??
10. ??
11. Execution?Plan??
12. ----------------------??
13. Plan?hash?value:?4225832519??
14. ??
15. -----------------------------------------------------------------------??
16. |?Id??|?Operation??????????|?Name?????????????????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|?Inst???|IN-OUT|??
17. -----------------------------------------------------------------------??
18. |???0?|?SELECT?STATEMENT???|??????????????????????|?????1?|????77?|?????5???(0)|?00:00:01?|????????|??????|??
19. |???1?|??SEQUENCE??????????|?SEQ_PAY_CUSTOMEROPER?|???????|???????|????????????|??????????|????????|??????|??
20. |???2?|???NESTED?LOOPS?????|??????????????????????|?????1?|????77?|?????5???(0)|?00:00:01?|????????|??????|??
21. |*??3?|????INDEX?RANGE?SCAN|?PK_T_PAY_USERINFO????|?????1?|?????6?|?????3???(0)|?00:00:01?|????????|??????|??
22. |???4?|????REMOTE??????????|?EFB_USER_MOVE????????|?????1?|????71?|?????2???(0)|?00:00:01?|?WODBL~?|?R->S?|??
23. -----------------------------------------------------------------------??
24. ??
25. Predicate?Information?(identified?by?operation?id):??
26. ---------------------------------------------------??
27. ??
28. ???3?-?access("U"."ID">3500000?AND?"U"."ID"<=4000000)??
29. ??
30. Remote?SQL?Information?(identified?by?operation?id):??
31. ----------------------------------------------------??
32. ??
33. ???4?-?SELECT?"ID","LAST_LOGIN_TIME","LOGIN_TIMES","LOGIN_IP"?FROM?"EFB_USER_MOVE"?"T"?WHERE??
34. ???????"ID"<=4000000?AND?"ID">3500000?AND?"ID"=:1?(accessing?'WODBLINK'?)??
35. ??
36. ??
37. ??
38. Statistics??
39. ----------------------??
40. ??????86691??recursive?calls??
41. ??????18753??db?block?gets??
42. ??????14781??consistent?gets??
43. ??????????0??physical?reads??
44. ????4035364??redo?size??
45. ????5163224??bytes?sent?via?SQL*Net?to?client??
46. ??????91297??bytes?received?via?SQL*Net?from?client??
47. ???????8257??SQL*Net?roundtrips?to/from?client??
48. ??????????0??sorts?(memory)??
49. ??????????0??sorts?(disk)??
50. ?????123832??rows?processed??

这个SQL太简单了，可以说是我见过的SQL中最简单的，执行计划也非常简单。

这个SQL要返回123832条记录，然后执行计划走的是nested loops，问题显而易见了，原因是表EFB_USER_MOVE是通过DBLINK过来的，本地无法得知表EFB_USER_MOVE的统计信息(也就是无法知道它有多少行)，所以CBO默认给它设置为1行，但是返回了123832条记录，所以给这个SQL加了个HINT

view plainprint?

1. SELECT?/*+?full(u)*/?SEQ_PAY_CUSTOMEROPER.Nextval,??
2. ???????u.ID,??
3. ???????'admin',??
4. ???????1,??
5. ???????t.LAST_LOGIN_TIME,??
6. ???????t.LOGIN_TIMES,??
7. ???????t.LOGIN_IP??
8. ??FROM?EFB_USER_MOVE@WODBLINK?t,?T_PAY_USERINFO?u??
9. ?WHERE?t.ID?=?u.ID??
10. ???AND?u.ID?>?3500000??
11. ???AND?u.ID?<?400000??
12. ???AND?t.ID?>?3500000??
13. ???AND?t.ID?<=?4000000;??

这样SQL就能几秒跑完了，这里的sequence还值得注意，因为要返回123832条记录，如果sequence上的cache很小，也必然导致SQL慢，建议设置cache到1000

总结：遇到SQL语句中要引用DBLINK，需要特别留意，通常这样的SQL需要DBA添加HINT，其实这只是DBLINK中一个需要注意的地方，还有地方就是 有时候需要添加

????????????driving_site 这个HINT来优化，具体就不多说了。