Oracle通过DBLINK执行SQL后本地SQL不同
Oracle通过DBLINK执行SQL后本地SQL不同
打个比方简单的描述下，在数据库A执行如下SQL：
insert into t@zkm select * from t;
SQL往远程表t@zkm插入数据。
本地表T和远程表T的表结构均如下：
Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) NAME VARCHAR2(20)
在执行SQL的会话开启10046之后，执行该SQL查看产生的trc文件会发现，
===================== PARSING IN CURSOR #140737295024160 len=35 dep=1 uid=0 oct=3 lid=0 tim=1650869461681374 hv=2039156212 ad='9ad00a0d0' sqlid='dtpfkv1wsq3gn' SELECT /*+ FULL(P) +*/ * FROM "T" P END OF STMT PARSE #140737295024160:c=1412,e=2353,p=0,cr=6,cu=0,mis=1,r=0,dep=1,og=4,plh=1601196873,tim=1650869461681373 WAIT #140737295024160: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461681455 WAIT #140737295708896: nam='SQL*Net message from dblink' ela= 318 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461681810 WAIT #140737295024160: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461682451 WAIT #140737295708896: nam='SQL*Net message from dblink' ela= 567 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461683058 CLOSE #140737295024160:c=5,e=5,dep=1,type=0,tim=1650869461683099 ===================== PARSING IN CURSOR #140737295024160 len=35 dep=1 uid=0 oct=3 lid=0 tim=1650869461683214 hv=2039156212 ad='9ad00a0d0' sqlid='dtpfkv1wsq3gn' SELECT /*+ FULL(P) +*/ * FROM "T" P END OF STMT PARSE #140737295024160:c=83,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1601196873,tim=1650869461683214 WAIT #140737295024160: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461683291 WAIT #140737295708896: nam='SQL*Net message from dblink' ela= 256 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461683576 WAIT #140737295024160: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461683687 WAIT #140737295708896: nam='SQL*Net message from dblink' ela= 1504 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461685216 CLOSE #140737295024160:c=0,e=5,dep=1,type=0,tim=1650869461685267
其中，SELECT /*+ FULL(P) +*/ * FROM "T" P这条SQL出现两次，并且只有解析阶段没有执行阶段，也就是这条SQL并不会被执行。
trc文件还有如下内容，
===================== PARSING IN CURSOR #140737295024160 len=32 dep=1 uid=0 oct=3 lid=0 tim=1650869461685856 hv=1599888760 ad='9ad009640' sqlid='cyrkwv5gpsrbs' SELECT "ID","NAME" FROM "T" "A2" END OF STMT PARSE #140737295024160:c=246,e=556,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1601196873,tim=1650869461685855 EXEC #140737295024160:c=24,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1601196873,tim=1650869461685944 WAIT #140737295024160: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461685974 FETCH #140737295024160:c=64,e=64,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=1601196873,tim=1650869461686065 WAIT #140737295708896: nam='SQL*Net message from dblink' ela= 265 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461686365 WAIT #140737295024160: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1650869461686416 WAIT #140737295024160: nam='SQL*Net more data to dblink' ela= 26 driver id=1413697536 #bytes=8145 p3=0 obj#=-1 tim=1650869461686843 WAIT #140737295024160: nam='SQL*Net more data to dblink' ela= 20 driver id=1413697536 #bytes=8145 p3=0 obj#=-1 tim=1650869461687326 WAIT #140737295024160: nam='SQL*Net more data to dblink' ela= 18 driver id=1413697536 #bytes=8145 p3=0 obj#=-1 tim=1650869461687802 ......
其中，SELECT "ID","NAME" FROM "T" "A2"是真正被执行的SQL，EXEC和FETCH表示被开始执行了。
