第一步：登入sql/plus 執行命令（無先後順序）
set time on; (說明：打開時間顯示）
set autotrace traceonly; (說明：打開自動分析統計，不顯示SQL語句的運行結果)。
第二步：輸入你要查看的sql 執行
第三步：查看結果
2 用戶、庫表說明 2.1 用戶1用戶名：XUQIU@ORG
密碼：
2.2 用戶2用戶名：需求分析定制網站@ Oracle
密碼：
2.3 用戶3用戶名：PADISTEST@ORAG
密碼：
2.3 庫表說明用戶1、用戶2所對應的庫具有相同的庫表結構，且每個對應的表中具有相同的數據;用戶3所對應的庫與之前相關。
二、測試過程 1 登錄SQL/PLUS、開啟執行計劃並創建DBLINK 1.1 SQL/PLUS記錄Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 1月 18 14:45:04 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
請輸入用戶名: xuqiu@org
輸入口令:
連接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> set time on;
14:45:35 SQL> set autotrace traceonly;
14:45:46 SQL> create database link mopishv0link connect to "需求分析定制網站" identifIEd by "XUQIU" using'ORAC
LE';
數據庫鏈接已創建。
14:47:52 SQL> select * from WORK_INFO@mopishv0link;
已選擇1161行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
1 0 TABLE Access (FULL) OF 'WORK_INFO' OracleDB
.US.ORAC
LE.COM
統計信息
----------------------------------------------------------
0 recursive calls
1 db block gets
0 consistent gets
0 physical reads
212 redo size
172850 bytes sent via SQL*Net to clIEnt
1219 bytes received via SQL*Net from clIEnt
158 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1161 rows processed
15:35:37 SQL> create database link mopishv1link connect to "PADISTEST" identifIEd by "apple" using'ORAG';
數據庫鏈接已創建。
16:44:09 SQL> select * from ACTIVITY@mopishv1link;
已選擇452行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
1 0 TABLE Access (FULL) OF 'ACTIVITY' ORAGS7.U
S.Oracle
.COM
2 有條件查詢本地庫表 2.1 SQL/PLUS記錄14:48:18 SQL> select work_id from WORK_RULE_INFO where RULE_LEV='操作級';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE Access (FULL) OF 'WORK_RULE_INFO'
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to clIEnt
372 bytes received via SQL*Net from clIEnt
4 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1 rows processed
14:49:43 SQL>
2.2 記錄分析由TABLE Access (FULL) OF 'WORK_RULE_INFO'我們可以看到在對本地查詢的時候雖然有條件限制，但也沒有做過多的優化而是直接訪問整個WORK_RULE_INFO表。
3 無條件查詢遠程庫表 3.1 SQL/PLUS記錄14:49:43 SQL> select work_id from WORK_RULE_INFO@mopishv0link;
已選擇1161行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
1 0 TABLE Access (FULL) OF 'WORK_RULE_INFO' OracleDB
.US.ORAC
LE.COM
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
18183 bytes sent via SQL*Net to clIEnt
1219 bytes received via SQL*Net from clIEnt
158 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1161 rows processed
14:53:57 SQL>
3.2 記錄分析與3.1比較可以發現，訪問遠程庫表時，會在操作後加入遠程數據庫的全局數據庫名(Global Database Name)表示操作發生在遠程數據庫服務上。
4 不同庫中的不同表的表間查詢 4.1 SQL/PLUS記錄115:02:39 SQL> select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFO@mopishv0link where WORK_INFO.
WORK_ID=WORK_RULE_INFO.WORK_ID and RULE_LEV='操作級';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 REMOTE* MOPISHV0
LINK.US.
Oracle.C
OM
3 1 TABLE Access (BY INDEX ROWID) OF 'WORK_RULE_INFO'
4 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE)
2 SERIAL_FROM_REMOTE SELECT "WORK_ID" FROM "WORK_INFO" "WORK_INFO
"
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3118 consistent gets
0 physical reads
0 redo size
390 bytes sent via SQL*Net to clIEnt
372 bytes received via SQL*Net from clIEnt
4 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1 rows processed
4.2 SQL/PLUS記錄215:09:21 SQL> select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFO@mopishv0link where RULE_LEV='
操作級' and WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID;
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 REMOTE* MOPISHV0
LINK.US.
Oracle.C
OM
3 1 TABLE Access (BY INDEX ROWID) OF 'WORK_RULE_INFO'
4 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE)
2 SERIAL_FROM_REMOTE SELECT "WORK_ID" FROM "WORK_INFO" "WORK_INFO
"
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3118 consistent gets
0 physical reads
0 redo size
390 bytes sent via SQL*Net to clIEnt
372 bytes received via SQL*Net from clIEnt
4 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1 rows processed
4.2 SQL/PLUS記錄315:10:12 SQL> select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFO@mopishv0link where RULE_LEV='
操作級' and WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and RULE_LEV='操作級';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 REMOTE* MOPISHV0
LINK.US.
Oracle.C
OM
3 1 TABLE Access (BY INDEX ROWID) OF 'WORK_RULE_INFO'
4 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE)
2 SERIAL_FROM_REMOTE SELECT "WORK_ID" FROM "WORK_INFO" "WORK_INFO
"
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3118 consistent gets
0 physical reads
0 redo size
390 bytes sent via SQL*Net to clIEnt
372 bytes received via SQL*Net from clIEnt
4 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:24:25 SQL>
4.4 記錄分析 1通過對比4.1、4.2與4.3我們可以發現條件的顛倒與重復都對執行計劃沒有影響，這說明在聯結之前Oracle會對查詢進行優化。這點符合分布式數據庫系統原理中所描述的查詢語句分解的特點。
4.5 SQL/PLUS記錄415:24:25 SQL> select WORK_INFO.WORK_NAME from WORK_RULE_INFO,WORK_INFO@mopishv0link where WORK_INFO.WORK_ID=WO
RK_RULE_INFO.WORK_ID;
已選擇1161行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 REMOTE* MOPISHV0
LINK.US.
Oracle.C
OM
3 1 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE)
2 SERIAL_FROM_REMOTE SELECT "WORK_ID","WORK_NAME" FROM "WORK_INFO
" "WORK_INFO"
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1240 consistent gets
0 physical reads
0 redo size
32377 bytes sent via SQL*Net to clIEnt
1219 bytes received via SQL*Net from clIEnt
158 SQL*Net roundtrips to/from clIEnt
0 sorts (memory)
0 sorts (disk)
1161 rows processed
15:35:37 SQL>
4.6 記錄分析 2通過對比4.1、4.2、4.3與4.5可以發現，Oracle只從遠程庫表中查詢參與聯結的元組，而不是查詢所有列。這與分布式數據庫系統原理中所描述的半聯結相符。
