FIRST_ROWS優化模式訪問遠端表可能導致錯誤結果（二）
在Oracle的9204上碰到了這個問題，FIRST_ROWS優化模式下，通過資料庫鏈訪問遠端表，且SQL包括IN子查詢語句時，優化器可能給出錯誤的執行計劃，從而導致結果集出現重複記錄。
這一篇繼續研究問題和版本的關係，以及相應的解決方法。
FIRST_ROWS優化模式訪問遠端表可能導致錯誤結果（一）：http://yangtingkun.itpub.net/post/468/460444
上一篇討論了問題和哪些因素有關，下面看看這個問題是否和版本有關。由於上面一篇文章中所有的測試都是在9.2.0.4版本上進行的，下面分別將遠端庫和本地庫更改為10.2，檢查錯誤是否依然存在：
SQL> CONN YANGTK/YANGTK@YTK102
已連線。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS
2 WHERE WNER = 'SYS'
3 AND OBJECT_TYPE IN ('TABLE', 'SEQUENCE', 'SYNONYM');
表已建立。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_TAB_PRIVS;
表已建立。
SQL> CREATE INDEX IND_T1_GRANTEE ON T1(GRANTEE);
索引已建立。
SQL> CREATE INDEX IND_T1_NAME ON T1(TABLE_NAME);
索引已建立。
SQL> CREATE UNIQUE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已建立。
SQL> CONN YANGTK/YANGTK@YTK92
已連線。
SQL> CREATE DATABASE LINK YTK102 USING 'YTK102';
資料庫連結已建立。
SQL> SET AUTOT ON EXP
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM T@YTK102
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK102
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK102 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
46535 AW$AWCREATE
46541 AW$AWCREATE10G
46529 AW$AWMD
46553 AW$AWREPORT
46547 AW$AWXML
46523 AW$EXPRESS
7103 IMPDP_STATS
7102 KU$NOEXP_TAB
4312 ODCI_SECOBJ$
4313 ODCI_WARNINGS$
46619 OLAPI_HISTORY
46618 OLAPI_HISTORY_SEQ
46623 OLAPI_IFACE_OBJECT_HISTORY
46625 OLAPI_IFACE_OP_HISTORY
46629 OLAPI_MEMORY_HEAP_HISTORY
46627 OLAPI_MEMORY_OP_HISTORY
46621 OLAPI_SESSION_HISTORY
46560 OLAPTABLEVELS
46563 OLAPTABLEVELTUPLES
48705 OLAP_OLEDB_FUNCTIONS_PVT
48668 OLAP_OLEDB_KEYWORDS
48671 OLAP_OLEDB_MDPROPS
48669 OLAP_OLEDB_MDPROPVALS
8706 PLAN_TABLE$
3989 PSTUBTBL
5287 SCHEDULER$_JOBSUFFIX_S
4161 WRI$_ADV_ASA_RECO_DATA
已選擇27行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 FILTER
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 REMOTE* YTK102.YTK_THINKPAD
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 REMOTE* YTK102.YTK_THINKPAD
8 1 TABLE ACCESS (FULL) OF 'T2'
4 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1"
7 SERIAL_FROM_REMOTE SELECT /*+ */ DISTINCT "A1"."TABLE_NAME" FRO
M "T1" "A1" WHERE EXISTS (SELECT 0
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@YTK102
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK102
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK102 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3989 PSTUBTBL
3989 PSTUBTBL
4161 WRI$_ADV_ASA_RECO_DATA
4161 WRI$_ADV_ASA_RECO_DATA
4161 WRI$_ADV_ASA_RECO_DATA
4312 ODCI_SECOBJ$
4312 ODCI_SECOBJ$
.
.
.
46629 OLAPI_MEMORY_HEAP_HISTORY
46629 OLAPI_MEMORY_HEAP_HISTORY
46629 OLAPI_MEMORY_HEAP_HISTORY
48668 OLAP_OLEDB_KEYWORDS
48669 OLAP_OLEDB_MDPROPVALS
48671 OLAP_OLEDB_MDPROPS
48705 OLAP_OLEDB_FUNCTIONS_PVT
已選擇105行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=2930 Card=
77 Bytes=7392)
1 0 FILTER
2 1 NESTED LOOPS (Cost=928 Card=77 Bytes=7392)
3 2 VIEW OF 'VW_NSO_1' (Cost=49 Card=874 Bytes=14858)
4 3 REMOTE* YTK102.YTK_THINKPAD
5 2 REMOTE* (Cost=1 Card=1 Bytes=79) YTK102.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26
Card=511 Bytes=8687)
4 SERIAL_FROM_REMOTE SELECT "A1"."TABLE_NAME" FROM "T1" "A1" WHER
E EXISTS (SELECT 0 FROM "T1" "A2" W
5 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1" WHERE "OBJECT_NAME"=:1
如果遠端資料庫是10g，那麼問題仍然存在，其實這也可以理解，因為執行計劃是本地產生的，所以遠端資料庫的版本並不重要。
下面將SQL在10g上執行，訪問遠端9I資料庫：
SQL> CONN YANGTK/YANGTK@YTK102
已連線。
SQL> CREATE DATABASE LINK YTK92 USING 'YTK92';
資料庫連結已建立。
SQL> SET AUTOT ON EXP
SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SYNONYMS;
表已建立。
SQL> CREATE INDEX IND_T2_NAME ON T2(SYNONYM_NAME);
索引已建立。
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM T@YTK92
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK92
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK92 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
29559 AW$AWCREATE
29567 AW$AWMD
29527 AW$EXPRESS
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
29573 OLAPTABLEVELS
29576 OLAPTABLEVELTUPLES
3200 PSTUBTBL
已選擇8行。
執行計劃
----------------------------------------------------------
Plan hash value: 4134023224
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113 | 10848 | 79 (3)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | HASH JOIN | | 113 | 10848 | 23 (14)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 113 | 1921 | 19 (11)| 00:00:01 | | |
| 4 | REMOTE | | | | | | YTK92 | R->S |
| 5 | REMOTE | T | 899 | 71021 | 3 (0)| 00:00:01 | YTK92 | R->S |
|* 6 | INDEX RANGE SCAN| IND_T2_NAME | 210 | 3570 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T2" "T2" WHERE "SYNONYM_NAME"=:B1))
2 - access("OBJECT_NAME"="$nso_col_1")
6 - access("SYNONYM_NAME"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS1563' INTO PLAN_TABLE@! FOR SELECT DISTINCT
"A1"."TABLE_NAME" FROM "T1" "A2","T1" "A1" WHERE "A1"."GRANTEE"="A2"."GRANTEE" AND
"A2"."TABLE_NAME"='HELP' (accessing 'YTK92.YTK_THINKPAD' )
5 - SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "SYS_ALIAS_1" (accessing
'YTK92.YTK_THINKPAD' )
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@YTK92
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK92
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK92 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
29559 AW$AWCREATE
29567 AW$AWMD
29527 AW$EXPRESS
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
29573 OLAPTABLEVELS
29576 OLAPTABLEVELTUPLES
3200 PSTUBTBL
已選擇8行。
執行計劃
----------------------------------------------------------
Plan hash value: 4134023224
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113 | 10848 | 79 (3)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | HASH JOIN | | 113 | 10848 | 23 (14)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 113 | 1921 | 19 (11)| 00:00:01 | | |
| 4 | REMOTE | | | | | | YTK92 | R->S |
| 5 | REMOTE | T | 899 | 71021 | 3 (0)| 00:00:01 | YTK92 | R->S |
|* 6 | INDEX RANGE SCAN| IND_T2_NAME | 210 | 3570 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T2" "T2" WHERE "SYNONYM_NAME"=:B1))
2 - access("OBJECT_NAME"="$nso_col_1")
6 - access("SYNONYM_NAME"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS1563' INTO PLAN_TABLE@! FOR SELECT DISTINCT
"A1"."TABLE_NAME" FROM "T1" "A2","T1" "A1" WHERE "A1"."GRANTEE"="A2"."GRANTEE" AND
"A2"."TABLE_NAME"='HELP' (accessing 'YTK92.YTK_THINKPAD' )
5 - SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "SYS_ALIAS_1" (accessing
'YTK92.YTK_THINKPAD' )
Note
-----
- dynamic sampling used for this statement
在10g中，Oracle改變了策略，無論是ALL_ROWS還是FIRST_ROWS都使用了HASH JOIN作為連線方式，這種方式尤其適合通過資料庫鏈的訪問遠端物件，從這裡也可以看到Oracle 10g在處理遠端物件方面所進行的優化。
這裡沒有使用SEMI-JOIN方式，但是也得到了正確的結果，這是由於Oracle在訪問遠端物件的時候在語句中新增了DISTINCT，從而去除了重複記錄，由此可見，這個bug在10.2上已經被解決了。
順便提一下METALINK的搜尋結果，這次在METALINK上倒不是找不到相關的bug，而是找到了太多類似的情況，不過每種情況Oracle並沒有進行詳細的分析和說明，以至於根據現象無法斷定目前的bug到底屬於其中的哪一個，比較類似的bug包括：3092670、2469769、3273161和3041166等。沒有辦法根據metalink上提供的資訊來確定解決方法，好在這些bug基本上都在10.1版本被fixed，基本上可以確定這個bug不會影響10g的版本。
至於解決問題的方法，其實上來在確定問題時已經相應的介紹了很多種了，只要破壞下面的任意一種情況，bug就不會出現：9I、FIRST_ROWS、DB_LINK訪問遠端物件，包含IN子查詢語句。
除了這些方法外，新增HINT也是一種解決問題的方法，比如新增HASH_SJ，MERGE_SJ，NL_SJ等：
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1 T
2 WHERE OBJECT_NAME IN
3 (
4 SELECT /*+ HASH_SJ */ TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3200 PSTUBTBL
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=873 Card=3
3 Bytes=3168)
1 0 FILTER
2 1 HASH JOIN (SEMI) (Cost=15 Card=33 Bytes=3168)
3 2 REMOTE* (Cost=2 Card=33 Bytes=2607) TEST1.YTK_THINKPAD
4 2 VIEW OF 'VW_NSO_1' (Cost=12 Card=462 Bytes=7854)
5 4 REMOTE* TEST1.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26
Card=511 Bytes=8687)
3 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1"
5 SERIAL_FROM_REMOTE SELECT /*+ HASH_SJ */ "A1"."TABLE_NAME" FROM
"T1" "A1" WHERE EXISTS (SELECT 0 F
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1 T
2 WHERE OBJECT_NAME IN
3 (
4 SELECT /*+ NL_SJ */ TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
3200 PSTUBTBL
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=1256 Card=
33 Bytes=3168)
1 0 FILTER
2 1 NESTED LOOPS (SEMI) (Cost=398 Card=33 Bytes=3168)
3 2 REMOTE* (Cost=2 Card=33 Bytes=2607) TEST1.YTK_THINKPAD
4 2 VIEW OF 'VW_NSO_1' (Cost=12 Card=462 Bytes=7854)
5 4 REMOTE* TEST1.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26
Card=511 Bytes=8687)
3 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1"
5 SERIAL_FROM_REMOTE SELECT /*+ NL_SJ */ "A1"."TABLE_NAME" FROM "
T1" "A1" WHERE EXISTS (SELECT 0 FRO
除此之外，新增DRIVING_SITE對於當前的bug也是一個不錯的解決方法：
SQL> SELECT /*+ FIRST_ROWS DRIVING_SITE(T) */ OBJECT_ID, OBJECT_NAME FROM T@TEST1 T
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
3200 PSTUBTBL
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) ptimizer=HINT: FIRST_ROWS (Cost=2
80 Card=5 Bytes=480)
1 0 NESTED LOOPS (Cost=280 Card=5 Bytes=480)
2 1 VIEW OF 'VW_NSO_1' (Cost=186 Card=92 Bytes=1564) TEST1.YANGTINGKUN
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 TEST1.YANGTINGKUN
Bytes=34)
5 4 NESTED LOOPS (Cost=186 Card=92 Bytes=6256)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Ca TEST1.YANGTINGKUN
rd=92 Bytes=3128)
7 6 INDEX (RANGE SCAN) OF 'IND_T1_NAME' (NON-UNIQU TEST1.YANGTINGKUN
E) (Cost=1 Card=37)
8 5 INDEX (RANGE SCAN) OF 'IND_T1_GRANTEE' (NON-UNIQ TEST1.YANGTINGKUN
UE) (Cost=1 Card=1)
9 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Byte TEST1.YANGTINGKUN
s=79)
10 9 INDEX (UNIQUE SCAN) OF 'IND_T_NAME' (UNIQUE) TEST1.YANGTINGKUN
11 10 REMOTE* (Cost=26 Card=511 Bytes=8687) !
11 SERIAL_FROM_REMOTE SELECT "SYNONYM_NAME" FROM "T2" "A2" WHERE L
NNVL("SYNONYM_NAME"<>:1)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-251883/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- FIRST_ROWS優化模式訪問遠端表可能導致錯誤結果（一）優化模式
- CHAR型別函式索引導致結果錯誤型別函式索引
- 錯誤資料導致優化器不識別（高階優化手法用盡，結果盡然是這樣）優化
- 解決防火牆限制遠端連線MySQL（導致錯誤10060可能之一）防火牆MySql
- DB2訪問遠端資料庫通訊錯誤DB2資料庫
- MySQL:MySQL客戶端快取結果導致OOMMySql客戶端快取OOM
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 階梯訪問表優化優化
- Redis遠端訪問Redis
- Tableau視覺化結果的優化小技巧（二）視覺化優化
- mysql 遠端連結錯誤 ERROR 1130MySqlError
- 如何解決url傳參導致錯誤問題
- 錯誤思維導向致IT專案問題多
- TeamViewer遠端訪問View
- Linux sudo 漏洞可能導致未經授權的特權訪問Linux
- [ORACLE BUG]查詢結果錯誤--臨時表並行Oracle並行
- 【優化】ALL_ROWS模式和FIRST_ROWS模式的適用場景優化模式
- 多餘索引導致explain錯誤索引AI
- 移動端API介面優化的術和結果API優化
- rabbitmq 新增遠端訪問功能MQ
- 遠端訪問及控制——ssh
- Linux SSH遠端訪問Linux
- PostgreSQL 9.6 配置遠端訪問SQL
- 核心表AUTOTRACE結果出錯
- iptables導致無法遠端連線oracleOracle
- linux遠端訪問問題薦Linux
- 用SecureCRTPortable連結遠端伺服器用sz下載檔案錯誤問題Securecrt伺服器
- pycharm 遠端除錯之二PyCharm除錯
- GoldenGate MSSQL Oracle的主鍵問題導致的錯誤GoSQLOracle
- OGG 表結構變化導致同步異常
- impdp時parallel=4導致的錯誤Parallel
- 操作複雜物件結構——訪問者模式（二）物件模式
- 遠端客戶端 訪問 ASM 例項客戶端ASM
- mysql如何允許遠端訪問MySql
- jupyter notebook 遠端訪問
- mysql賬戶新增遠端訪問MySql
- NAS如何實現遠端訪問
- 遠端訪問jupyter notebook