ANBOB™

在MES平台看到一个提问，应用程序总时会自动产生类似”SELECT /*+ FULL(P) +*/ * FROM XXXXX P “这类SQL，确认不是应用代码中调用，看到FULL hint对于SQL调优人员可能会捶开发人员的冲动 ，同样对于SQL审核或SPA、 数据库国产迁移性能分析等需求抓到这类SQL可能就白白浪费感情。这SQL是数据库自动产生的吗？是！它是DBLINK调用的。

SQL ordered by Parse Calls

上面是该环境的AWR，下面测试是否是dblink产生，env  oracle 19c

创建dblink
note:这里是虚拟机创建一个到本数据库的回路DBLINK

SQL> @cc pdb1
ALTER SESSION SET container = pdb1;

SQL> create database link dl_lo connect to anbob identified by xxxxx  using 'cdb1pdb1';
Database link created.

SQL> select sysdate from dual@dl_lo;
SYSDATE
-------------------
2023-06-09 22:52:10

确认DBLINK远程会话

SQL> select sid,username,machine,program from v$session where username is not null;

       SID USERNAME   MACHINE    PROGRAM
---------- ---------- ---------- ------------------------------------------------
        12 SYS        oel7db1    oracle@oel7db1 (OFSD)
        33 SYS        oel7db1    sqlplus@oel7db1 (TNS V1-V3)
        34 SYS        oel7db1    sqlplus@oel7db1 (TNS V1-V3)
        68 ANBOB      oel7db1    oracle@oel7db1 (TNS V1-V3)  《《《《《《《《《

note:
如果会话较多，使用我之前BLOG Script: Who’s using a database link?(找出谁在使用dblink)

启用SQL trace

SQL> @usid 68

USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID           HASH_VALUE   LASTCALL STATUS   SADDR                                    PADDR            TADDR            LOGON_TIM
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ --------------- ----------- ---------- -------- ------                        ---------- ---------------- ---------------- ---------
ANBOB                    '68,51112'        4056002 oracle           oel7db1            (TNS V1-V3)          7516               47 7380                     2bvcw86f03kx9    2617363369         71 INACTIVE 000000                        00781281E0 0000000078D0AA88 000000007524CFF0 09-JUN-23

SQL> oradebug setorapid 47
Oracle pid: 47, Unix process pid: 7516, image: oracle@oel7db1
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_7516.trc

note:
给DBLINK 远程会话启用sql trace, 跟踪是否它产生的SQL。

查询DBLINK调用

SQL> select count(*) from t1@dl_lo;
  COUNT(*)
----------
    611120

note:
在dblink的调用端执行查询，这里查询的是远程库的T1表。

分析SQL trace file中的SQL

[oracle@oel7db1 ~]$ awk '/PARSING/,/END OF STMT/' /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_7516.trc |egrep -v '^END|PARSING IN'|cat -n
     1  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9, nvl(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
     2  select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread,ts.im_imcu_count,ts.im_block_count,ts.im_sys_incarnation,ts.im_stat_update_time,ts.scanrate,nvl(t.acdrflags, 0),nvl(t.acdrtsobj#, 0),t.acdrdefaulttime, nvl(t.acdrrowtsintcol#, 0) from tab$ t,tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
     3  select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
     4  select audit$ from tab$ where obj# = :1
     5  select policy#, action# from aud_object_opt$ where object# = :1 and type = 2
     6  select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1),nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(intcols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
     7  select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0), case when (type# in (1,8,9,96,112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol#
     8  select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
     9  select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0), case when (type# in (1,8,9,96,112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol#
    10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)), max(bitand(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16), max(bitand(nvl(option$,0),64) /64), max(bitand(nvl(option$,0), 128) /128) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
    11  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)),max(bitand(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16), max(bitand(nvl(option$,0),64) /64), max(bitand(nvl(option$,0), 128) /128) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
    12  select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc
    13  select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc
    14  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
    15  select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc
    16  select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc
    17  select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc
    18  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtype$ where obj# = :1 order by intcol# asc
    19  select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
    20  select binaryDefVal, lengthb(binaryDefVal), guard_id from ecol$ where tabobj# = :1 and colnum = :2
    21  select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296), 256) defhscflags, mod(trunc(spare2/1099511627776), 4096) defimcflags, mod(trunc(spare2/4503599627370496), 8) defimcpl, mod(spare3, 256) interval_dty, rowid, defmaxsize, mod(trunc(spare3/256), 256) subptn_interval_dty, mod(trunc(spare3/65536), 256) defccflags, mod(trunc(spare3/16777216), 256) defimcflags2, mod(trunc(spare3/4294967296), 256) defhscflags2 from partobj$ where obj# = :1
    22  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3,refact from cdef$ where robj#=:1
    23  select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
    24  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln, minimum_enc, maximum_enc from hist_head$ where obj#=:1 and intcol#=:2
    25  select /* QOSD */ /*+ index(do) */ dir_cnt from opt_directive_own$ do where dir_own# = :1
    26  SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("P") FULL("P") NO_PARALLEL_INDEX("P") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "ANBOB"."T1" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "P") SAMPLESUB
    27  select /* QOSD */ /*+ index(eh)*/ objn, sub_id, fixed_cost, text, col_list, flags, ctime from exp_head$ eh where exp_id = :1
    28  SELECT /*+ FULL(P) +*/ * FROM "T1" P
    29  SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("P") FULL("P") NO_PARALLEL_INDEX("P") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "ANBOB"."T1" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "P") SAMPLESUB
    30  SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("A1") FULL("A1") NO_PARALLEL_INDEX("A1") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "ANBOB"."T1" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "A1") SAMPLESUB
    31  SELECT COUNT(*) FROM "T1" "A1"

Note:
能看到SQL中确实调用了SELECT /*+ FULL(P) +*/ * FROM “T1” P ，还有一些和表相关的动态采样(未收统计信息原因).

格式化SQL TRACE

[oracle@oel7db1 ~]$ tkprof /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_7516.trc a.out
TKPROF: Release 19.0.0.0.0 - Development on Fri Jun 9 22:57:29 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

[oracle@oel7db1 ~]$  vi a.out

SQL ID: 1qffndqtbsbhy Plan Hash: 0

SELECT /*+ FULL(P) +*/ *
FROM
 "T1" P


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          1          2          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.01       0.02          1          2          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106

Note:
sql文本虽然使用的是select 全表扫描，但此SQL仅有parse，并没有真正的execute，也没有fetch任何一条数据。

小结：
SELECT /*+ FULL(P) +*/ * FROM XXXXX P 这种SQL是ORACLE的内部机制在dblink的目标数据库自动触发的解析。注意仅是“解析” 实际不会执行也不会fetch任何数据，所以AWR中也确认只有parse calles,executions全为0，
目的应该远程传递给本地节点进行SQL语义检查， 不是bug.

打赏

微信扫一扫，打赏作者吧～