[20261109]关于sql语句对象的idn.txt

--//别人问的问题，转储library_cache,发现许多sql语句对象的idn=0.对方提示在测试环境也可以重现。
--//第1时间想到的这些sql语句根本无法执行，这在许多生产系统很常见，如果sql没有使用绑定变量，在共享池就是一大堆类似sql语句。

1.环境：
SYS@book> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> select count(*) from deptxxx;
select count(*) from deptxxx
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
--//执行多次。

3.转储library_cache：

SYS@book>  oradebug setmypid
Statement processed.

SYS@book> oradebug dump library_cache 4;
Statement processed.

SYS@book> @ t
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3118.trc

--//打开跟踪文件检索deptxxx
Bucket: #=60047 Mutex=0x753006c0(519691042816, 12, 0, 6)
  LibraryHandle:  Address=0x66775950 Hash=6896ea8f LockMode=N PinMode=0 LoadLockMode=0 Status=VALD Subpool=1
    ObjectName:  Name=select count(*) from deptxxx
      FullHashValue=2a80660e44510a13b97494666896ea8f Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=1754720911 OwnerIdn=109
    Statistics:  InvalidationCount=3 ExecutionCount=0 LoadCount=4 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x66775a00(0, 1, 0, 0) Mutex=0x66775aa8(121, 47, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0x667759e0[0x667759e0,0x667759e0]
      Pin=0x667759c0[0x667759c0,0x667759c0]
      LoadLock=0x66775a38[0x66775a38,0x66775a38]
    Timestamp:  Current=11-09-2026 17:08:57
    HandleReference:  Address=0x66775b38 Handle=(nil) Flags=[00]
    LibraryObject:  Address=0x667209f0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] Flags3=[0000] PublicFlags=[0000]
      ChildTable:  size='16'
        Child:  id='0' Table=0x66721858 Reference=0x667213d8 Handle=0x666e6970
    NamespaceDump:
      Parent Cursor:  sql_id=bkx4nctn9dung parent=0x66720ad0 maxchild=1 plk=y ppn=n prsfcnt=3 obscnt=0
--//Identifier=1754720911

SYS@book> @ sharepool/shp4 bkx4nctn9dung -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16       N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- --------- ---------- ------------- ---------- ----------
child handle address   00000000666E6970 0000000066775950 select count(*) from deptxxx                      0          0          3 00               00                        0          0       3293      3293      3293 1754720911 bkx4nctn9dung      60047          0
parent handle address  0000000066775950 0000000066775950 select count(*) from deptxxx                      1          0          3 0000000066775790 00                     4064          0          0      4064      4064 1754720911 bkx4nctn9dung      60047      65535
--//不是该问题引起的.

4.继续分析:

$ grep "^      FullHashValue" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3118.trc >| bb3.txt

$ egrep -o "Namespace=.+\([0-9]+\) Type|Identifier=[0-9]+ OwnerIdn" bb3.txt | sed -n 's/Type//p;s/OwnerIdn//p' | paste -d"|" - - | sort | uniq -c| sort -nr | head -14
   1638 Namespace=SQL AREA(00) |Identifier=0
   1475 Namespace=SQL AREA BUILD(82) |Identifier=0
    330 Namespace=TABLE/PROCEDURE(01) |Identifier=0
     71 Namespace=OPTIMIZER DIRECTIVE OWNER(104) |Identifier=4294967295
     50 Namespace=TABLE/PROCEDURE(01) |Identifier=4294967295
      6 Namespace=PDBOPER(125) |Identifier=0
      6 Namespace=OPTIMIZER FINDING(103) |Identifier=0
      5 Namespace=OPTIMIZER DIRECTIVE OWNER(104) |Identifier=0
      5 Namespace=BODY(02) |Identifier=0
      4 Namespace=SUBSCRIPTION(28) |Identifier=0
      4 Namespace=PUB SUB INTERNAL INFORMATION(18) |Identifier=0
      3 Namespace=TABLE/PROCEDURE(01) |Identifier=84
      3 Namespace=TABLE/PROCEDURE(01) |Identifier=833
      3 Namespace=TABLE/PROCEDURE(01) |Identifier=83
--//确实存在许多 Namespace=SQL AREA(00) |Identifier=0 的情况:
--//$ perl -ne 'print "$1 $2\n" if /(Namespace=[^(]*\(\d+\)).*?(Identifier=\d+)/' bb3.txt


$ egrep -o "Namespace=.+\([0-9]+\) Type|Identifier=[0-9]+ OwnerIdn" bb3.txt | sed -n 's/Type//p;s/OwnerIdn//p' | paste -d"|" - - | sort | uniq -c| sort -nr | grep -v "Namespace=SQL AREA(00) |Identifier=0" |wc
   6094   23434  356537
--//非0的更多。      

--//顺便拿一个idn=0的观察:

Bucket: #=39 Mutex=0x76f78ec8(519691042816, 42, 0, 6)
  LibraryHandle:  Address=0x6d0e30a0 Hash=3eb80027 LockMode=0 PinMode=0 LoadLockMode=0 Status=0 Subpool=1
    ObjectName:  Name=SELECT /*+ ordered index(u) index(o) index(po)
            OPT_PARAM('_parallel_syspls_obey_force' 'false') */ MOD(PO.SPARE2, 256), PO.PARTTYPE FROM SYS.USER$ U, SYS.OBJ$ O, SYS.PARTOBJ$ PO WHERE U.NAME = :B3 AND O.NAME = :B2 AND O.NAMESPACE = :B1 AND U.USER# = O.OWNER# AND O.OBJ# = PO.OBJ#
      FullHashValue=34765072305afb9d3bc745e43eb80027 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=0 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=0 TotalLockCount=0 TotalPinCount=0
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x6d0e3150(0, 4, 0, 0) Mutex=0x6d0e31f8(121, 42, 0, 6)
    Flags=RON/PIN/PN0/DBN/[10010040] Flags2=[0000]
    WaitersLists:
      Lock=0x6d0e3130[0x6d0e3130,0x6d0e3130]
      Pin=0x6d0e3110[0x6d0e3110,0x6d0e3110]
      LoadLock=0x6d0e3188[0x6d0e3188,0x6d0e3188]
    Timestamp:
    HandleReference:  Address=0x6d0e3390 Handle=(nil) Flags=[00]
    NamespaceDump:
      Parent Cursor:  sql_id=3rju5whzbh017 parent=(nil) maxchild=0 plk=n ppn=n

SYS@book> @ sharepool/shp4 3rju5whzbh017 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16       N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- --------- ---------- ------------- ---------- ----------
parent handle address  000000006D0E30A0 000000006D0E30A0 SELECT /*+ ordered i                              0          0          0 00               00                        0          0          0         0         0 1052246055 3rju5whzbh017         39      65535
--//这类sql语句没有子光标,父游标的堆0也被清楚了,仅仅保留父游标句柄。而且sql语句显示不全,脚本仅仅截取40个字符,实际仅仅显
--//示20个字符.
--//oradebug dump library_cache 4;,出现Namespace=SQL AREA(00) ,Identifier=0属于正常现象.

--//330 Namespace=TABLE/PROCEDURE(01) |Identifier=0 出现估计也是类似的情况.正常表的idn=object_id.