• 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表示被开始执行了。

  • 相关阅读:
    ubuntu下eclipse连接mysql
    关于oracle 11g导出数据时 报 ORA 1455错误的处理
    SQL语句改动表名和字段名
    C++组合通信
    退出应用工具类
    ListView间隔设置颜色
    Android闪光灯操作
    Android设置对话框去除黑边
    android设置组件透明度
    git在windows命令行下使用
  • 原文地址:https://www.cnblogs.com/PiscesCanon/p/16190543.html
Copyright © 2020-2023  润新知