40 积分	0 好友	3 主题

发消息

[升级] v$librarycache.namespace中的TABLE和INDEX含义

1^#

发表于 2013-3-7 12:09:33 | 查看: 5181| 回复: 6

在namespace中包含的TABLE/PROCEDURE和INDEX到底是什么呢？
如果是table和index的信息不是应该保存在dictionary cache中吗?
我在网上查了很多的资料都没有很详细的解释它，只有一篇文章说到

library cache也存放了很多的数据库对象的信息，包括表、索引等。有关这些数据库对象的信息都是从dictionary cache中获得的。如果用户对library cache中的对象信息进行了修改，比如为表添加了一个列等，则这些修改会返回到dictionary cache中。

如果如这篇文章所说的，那么将dictionary cache中的表，索引信息copy到library cache又有什么意义呢？

望各位高手指点一二，谢谢。

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2013-3-7 20:09:55

是一个有意义的问题，我们用演示回答你的问题

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2013-3-7 20:25:43

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 3月 7 07:10:46 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create table abc(t1 int);

Table created.

SQL> create index pk_abc on abc(t1);

Index created.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump library_cache 8;
Statement processed.

SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_30974.trc

关注一个index PK_ABC

  1* select KGLNAOBJ,KGLHDNSD, kglhdadr address ,decode(kglhdpar, kglhdadr, 'Parent','Child') type from x$kglob where KGLHDNSD='INDEX' and KGLNAOBJ='PK_ABC'
SQL> /

KGLNAOBJ          KGLHDNSD                                                       ADDRESS       TYPE
-------------------- ---------------------------------------------------------------- ---------------- ------
PK_ABC             INDEX                                                          0000000099727458 Parent

这个PK_ABC只有一个heap 即heap 0 KGLH0^59eb6f6

HEAP0包含哪些信息？

Library Object structure 自身：

type
General Status Flags
Valid/Invalid Status

Library Object Tables:
Dependency Table  ==>依赖表
Child Table    ==>这个对索引是没有的
Translation Table
Authorization Table
Access Table

      Block:  #='0' name=KGLH0^59eb6f6 pins=0 Change=NONE

Bucket: #=46838 Mutex=0xa07f1d00(0, 2766, 0, 6)
  LibraryHandle:  Address=0x9adde2d8 Hash=59eb6f6 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName:  Name=SYS.PK_ABC
   FullHashValue=cb749cf45c9aad5a9b62dece059eb6f6 Namespace=INDEX(04) Type=INDEX(01) Identifier=17504 OwnerIdn=0
Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
Counters:  BrokenCount=2 RevocablePointer=2 KeepDependency=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
Concurrency:  DependencyMutex=0x9adde388(0, 0, 0, 0) Mutex=0x9adde408(17, 18, 0, 6)
Flags=TIM/[00002801]
WaitersLists:
   Lock=0x9adde368[0x9adde368,0x9adde368]
   Pin=0x9adde348[0x9adde348,0x9adde348]
   LoadLock=0x9adde3c0[0x9adde3c0,0x9adde3c0]
Timestamp:  Current=03-07-2013 07:17:30
HandleReference:  Address=0x9adde4a0 Handle=(nil) Flags=[00]
LockInstance:  id='LEcb749cf45c9aad5a' GlobalEnqueue=(nil) ReleaseCount=0
PinInstance:  id='NEcb749cf45c9aad5a' GlobalEnqueue=(nil)
LibraryObject:  Address=0x7d502b80 HeapMask=0000-0001-0001-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
   DataBlocks:
      Block:  #='0' name=KGLH0^59eb6f6 pins=0 Change=NONE
      Heap=0x9adde220 Pointer=0x7d502c20 Extent=0x7d502b00 Flags=I/-/-/A/-/-
      FreedLocation=0 Alloc=0.257812 Size=4.000000 LoadTime=7046633160

回复只看该作者道具举报