library cache latch 只有一个么？

Yong Huang · 发表于 2007-11-25 02:15

原帖由 fxyj2008 于 2007-11-22 16:00 发表
在每个body 中存放的是执行计划，还有什么？？ //这边不清楚？？？

还有在 sql header 中存放的data  里面的数据到底是什么？？？

如何根据一条sql映射到相应的hash_bucket ,其实就是如何找到 LIBRARY_OBJECT_HANDLE  ？？？
我这边有一个现象，我两条相同的sql 映射到不同的 hash_bucket 上了

如下：
BUCKET 40886 total object count=1
BUCKET 41078:
  LIBRARY OBJECT HANDLE: handle=671ef69c
  name=select * from t
  hash=45b47d9c timestamp=11-22-2007 11:04:49
...
BUCKET 116770:
  LIBRARY OBJECT HANDLE: handle=6740d778
  name=select * from t
  hash=f3f74e27 timestamp=11-22-2007 09:56:51

The cursor body (a.k.a heap 6) also has other minor things such as optimizer_mode, last load time, execution count, sort count, which latch it's managed by, and on and on. I find this out by first looking at select * from v$sql where hash_value=[some value], DDL on the table used in the SQL to remove heap 6, looking at v$sql again. All those columns that did not have values 0 (or 'NONE') but now have values 0 or null must be in cursor body. [Note: this is true in 9i only; in 10g, DDL will get rid of the entire cursor including heap 0]

Cursor head (heap 0) is basically the SQL text and hash value. I think it also has first load time.

I don't know how to explain your two identical SQLs having different hash values (45b47d9c and f3f74e27). Can you make absolutely sure the two SQLs are exactly the same, no extra spaces for instance?

Yong Huang

[ 本帖最后由 Yong Huang 于 2007-11-25 03:21 编辑 ]

fxyj2008 · 发表于 2007-11-25 16:36

多谢 Yong Huang 的回复

Each child latch has a number. Oracle uses the SQL hash value to calculate the latch number. So each SQL will have a latch (i.e. it's not possible for a SQL to be not managed by any latch). But it's not that each SQL has its own unique latch; there wouldn't be that many. I don't know how to describe it in data structure.

我这边 select distict child_latch  from v$sql  的时候，只有一个值 1 ，所以当时我误解为是一个latch ，如果按您上面所说的，相应的数据结构就可以描述为一个数组
#DEFINE MAX_CHILD_LATCH N;

uchar  child_latch[MAX_CHILD_LATCH];

当需要给一个sql上 latch 的时候，先对sql 做映射，比如用某个函数映射下来结果是 i
那么就
testandset child_latch; 就可以

The cursor body (a.k.a heap 6) also has other minor things such as optimizer_mode, last load time, execution count, sort count, which latch it's managed by, and on and on. I find this out by first looking at select * from v$sql where hash_value=[some value], DDL on the table used in the SQL to remove heap 6, looking at v$sql again. All those columns that did not have values 0 (or 'NONE') but now have values 0 or null must be in cursor body. [Note: this is true in 9i only; in 10g, DDL will get rid of the entire cursor including heap 0]

我这边观察下来的结果是当发生DDL 的时候那么sql head 是不会变的，sqlbody 中的数据会被清理掉，我做的一个试验：
有三个用户 sys ,currsettlement , test_user ,分别有表t ,做select *from t

BUCKET 116524 total object count=1
BUCKET 116770:
  LIBRARY OBJECT HANDLE: handle=672fa8c8
  name=select * from t
  hash=f3f74e27 timestamp=11-25-2007 14:43:24
  namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/[12010000]
  kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1
  lwt=672FA8E0[672FA8E0,672FA8E0] ltm=672FA8E8[672FA8E8,672FA8E8]
  pwt=672FA8F8[672FA8F8,672FA8F8] ptm=672FA950[672FA950,672FA950]
  ref=672FA8D0[672FA8D0, 672FA8D0] lnd=672FA95C[672FA95C,672FA95C]
LIBRARY OBJECT: object=672fa650
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
      0 672fa80c  672fa56c 672fa34c
      1 672fa80c  672fa5ac 6729fdd8
      2 672fa80c  672fa5ec 672900bc
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 672fa858 672fa6d8 I/P/A    0 NONE    0.93    1.09
HEAP DUMP OF DATA BLOCK 0:

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

LIBRARY OBJECT HANDLE: handle=672fa34c
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=1
  lwt=672FA364[672FA364,672FA364] ltm=672FA36C[672FA36C,672FA36C]
  pwt=672FA37C[672FA37C,672FA37C] ptm=672FA3D4[672FA3D4,672FA3D4]
  ref=672FA354[672FA56C, 672FA56C] lnd=672FA3E0[672FA3E0,672FA3E0]
LIBRARY OBJECT: object=672fa16c
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
            0 672fa290  672f8310 672fdcb4    14 DEP[01]
ACCESSES: count=1 size=16
dependency# types
----------- -----
            0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
672fdcb4 672fdcb4
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 672fa2dc 672f9570 I/-/A    0 NONE    1.09    1.49
      6 672fa208 672f8550 I/-/A    0 NONE    2.13    2.16

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

LIBRARY OBJECT HANDLE: handle=6729fdd8
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=1
  lwt=6729FDF0[6729FDF0,6729FDF0] ltm=6729FDF8[6729FDF8,6729FDF8]
  pwt=6729FE08[6729FE08,6729FE08] ptm=6729FE60[6729FE60,6729FE60]
  ref=6729FDE0[672FA5AC, 672FA5AC] lnd=6729FE6C[6729FE6C,6729FE6C]
LIBRARY OBJECT: object=6729fb60
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
            0 6729fd1c  6729fcc0 672a00b0    14 DEP[01]
ACCESSES: count=1 size=16
dependency# types
----------- -----
            0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
672a00b0 672a00b0
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 6729fd68 6729f920 I/P/A    0 NONE    1.16    1.64
      6 6729fbfc 6729f748 I/-/A    0 NONE    2.42    2.50


  LIBRARY OBJECT HANDLE: handle=672900bc
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=1
  lwt=672900D4[672900D4,672900D4] ltm=672900DC[672900DC,672900DC]
  pwt=672900EC[672900EC,672900EC] ptm=67290144[67290144,67290144]
  ref=672900C4[672FA5EC, 672FA5EC] lnd=67290150[67290150,67290150]
LIBRARY OBJECT: object=6728fe44
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
            0 67290000  6728ff40 67290394    14 DEP[01]
ACCESSES: count=1 size=16
dependency# types
----------- -----
            0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
67290394 67290394
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 6729004c 6728fc04 I/P/A    0 NONE    1.10    1.64
      6 6728fee0 6728ebe4 I/-/A    0 NONE    2.42    2.46



BUCKET 47666:
  LIBRARY OBJECT HANDLE: handle=672fdcb4
  name=SYS.T
  hash=553a8f95 timestamp=08-07-2007 15:34:01
  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0301-0301 lock=0 pin=0 latch#=1
  lwt=672FDCCC[672FDCCC,672FDCCC] ltm=672FDCD4[672FDCD4,672FDCD4]
  pwt=672FDCE4[672FDCE4,672FDCE4] ptm=672FDD3C[672FDD3C,672FDD3C]
  ref=672FDCBC[672FDCBC, 672FDCBC] lnd=672FDD48[672DB74C,67775FF8]
LIBRARY OBJECT: object=672fda3c
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 672fdc44 672fdac4 I/-/A    0 NONE    0.68    1.09
      8 672fd4c4 672fce80 I/-/A    0 NONE    0.46    0.50
      9 672fd528 672fd080 I/-/A    0 NONE    0.17    0.21
HEAP DUMP OF DATA BLOCK 0:

BUCKET 86152:
  LIBRARY OBJECT HANDLE: handle=672a00b0
  name=CURRSETTLEMENT.T
  hash=33bf36a9 timestamp=10-15-2007 20:54:25
  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0501-0501 lock=0 pin=0 latch#=1
  lwt=672A00C8[672A00C8,672A00C8] ltm=672A00D0[672A00D0,672A00D0]
  pwt=672A00E0[672A00E0,672A00E0] ptm=672A0138[672A0138,672A0138]
  ref=672A00B8[672A00B8, 672A00B8] lnd=672A0144[67290428,6729A6D4]
LIBRARY OBJECT: object=6729e118
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 6729e320 6729e1a0 I/-/A    0 NONE    0.68    1.09
      8 6729bf7c 6729bb94 I/-/A    0 NONE    0.57    0.61
   10 6729bfe0 6729be00 I/-/A    0 NONE    0.12    0.15
HEAP DUMP OF DATA BLOCK 0:

BUCKET 98798:
  LIBRARY OBJECT HANDLE: handle=67290394
  name=TEST_USER.T
  hash=105379c5 timestamp=03-31-2007 21:15:55
  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0701-0701 lock=0 pin=0 latch#=1
  lwt=672903AC[672903AC,672903AC] ltm=672903B4[672903B4,672903B4]
  pwt=672903C4[672903C4,672903C4] ptm=6729041C[6729041C,6729041C]
  ref=6729039C[6729039C, 6729039C] lnd=67290428[679442E4,672A0144]
LIBRARY OBJECT: object=6728e954
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 6728eb5c 6728e9dc I/-/A    0 NONE    0.77    1.09
      8 6728e714 6728def4 I/-/A    0 NONE    0.46    0.50
      9 6728e7dc 6728e190 I/-/A    0 NONE    0.23    0.26
   10 6728e778 6728e0f4 I/-/A    0 NONE    0.12    0.15
HEAP DUMP OF DATA BLOCK 0:

然后我对cursettlement 下的表t 做了DDL 操作，结果如下：
BUCKET 116770:
  LIBRARY OBJECT HANDLE: handle=672fa8c8
  name=select * from t
  hash=f3f74e27 timestamp=11-25-2007 14:43:24
  namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/[12010000]
  kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1
  lwt=672FA8E0[672FA8E0,672FA8E0] ltm=672FA8E8[672FA8E8,672FA8E8]
  pwt=672FA8F8[672FA8F8,672FA8F8] ptm=672FA950[672FA950,672FA950]
  ref=672FA8D0[672FA8D0, 672FA8D0] lnd=672FA95C[672FA95C,672FA95C]
LIBRARY OBJECT: object=672fa650
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
      0 672fa80c  672fa56c 672fa34c
      1 672fa80c  672fa5ac 6729fdd8
      2 672fa80c  672fa5ec 672900bc
DATA BLOCKS:
data#    heap  pointer status pins change alloc(K)  size(K)
----- -------- -------- ------ ---- ------    -------- --------
      0 672fa858 672fa6d8 I/P/A    0 NONE    0.93    1.09
HEAP DUMP OF DATA BLOCK 0:

可以看到 header 中并没有变化，再看  handle ＝ 6729fdd8 的记录，这条记录就是处理currsettlement用户下表t 的handle

  LIBRARY OBJECT HANDLE: handle=6729fdd8
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0000-0000 lock=0 pin=0 latch#=1
  lwt=6729FDF0[6729FDF0,6729FDF0] ltm=6729FDF8[6729FDF8,6729FDF8]
  pwt=6729FE08[6729FE08,6729FE08] ptm=6729FE60[6729FE60,6729FE60]
  ref=6729FDE0[672FA5AC, 672FA5AC] lnd=6729FE6C[6729FE6C,6729FE6C]

可以看到 ,body 中的数据被清除了，而且该sql所依赖的对象 currsettlement.t 也被清除了

在currsettlement用户下重新执行 select * from t
，发现原来的header条目被重用了,
select t.CHILD_ADDRESS,t.CHILD_LATCH,t.CHILD_NUMBER,t.ADDRESS,
         t.FIRST_LOAD_TIME,t.HASH_VALUE,t.INVALIDATIONS,t.LAST_LOAD_TIME,
         t.KEPT_VERSIONS,t.LOADED_VERSIONS,t.LOADS,t.OBJECT_STATUS,t.SQL_TEXT
from v$sql t
where t.SQL_TEXT  like 'select * from t%'

CHILD_AD CHILD_LATCH CHILD_NUMBER ADDRESS  FIRST_LOAD HASH_VALUE INVALIDATIONS LAST_LOAD_ KEPT_VERSIONS LOADED_VERSIONS    LOADS OBJECT_STA  SQL_TEXT

672FA34C          1          0 672FA8C8 2007-11-25 4093070887          0 2007-11-25          0             1       1 VALID
                                       /14:43:24                         /14:43:24                                                             select * from t

6729FDD8          1          1 672FA8C8 2007-11-25 4093070887          1 2007-11-25          0             1       2 VALID
                                       /14:43:24                         /15:45:39                                                             select * from t

672900BC          1          2 672FA8C8 2007-11-25 4093070887          0 2007-11-25          0             1       1 VALID
                                       /14:43:24                         /14:49:12                                                             select * from t

6729FDD8    的LOADS ＝2

I don't know how to explain your two identical SQLs having different hash values (45b47d9c and f3f74e27). Can you make absolutely sure the two SQLs are exactly the same, no extra spaces for instance?

我后来又做了几次试验，原先的情况没有重现，可能我原先观察的有问题？

[ 本帖最后由 fxyj2008 于 2007-11-25 16:45 编辑 ]

fxyj2008 · 发表于 2007-11-25 17:14

晕，怎么越编辑越丑阿，还有字符被过滤掉了 testandset child_latch 应该是testandset child_latch[ i ]

Conceptually, I equate a hash bucket with a chain because they have a one-to-one relationship. Is that correct? I didn't go into details as you did. Perhaps when you go that deep, you do need to make a distinction between the two concepts.

这是两个不同的数据结构， hash buckets 是一个结构数组 ,chain  类似于链表,

－－－－－－－－－
|  hash bucket |－－－－>
|       1          |
－－－－－－－－－
|  hash bucket |－－－－>－－－－－－－－－－>－－－－－－
|       2          |             | chain |                |  chain    |
－－－－－－－－－       －－－－－          －－－－－－
|  hash bucket |－－－－>
|       3          |
－－－－－－－－－
|  hash bucket |－－－－>
|       4          |
－－－－－－－－－
|  hash bucket |－－－－> －－－－－－
|       5          |             | chain    |
－－－－－－－－－       －－－－－－－
|  hash bucket |－－－－>
|       6          |
－－－－－－－－－

比如说我现在有一条sql ，select * from t

表t 的数据是在  file #7 block 23 开始的，那么oracle 怎么知道这个块已经在内存中呢
这就需要对DBA(data block address) 做hash 映射，比如说映射出来的hashvalue是2
，那么找到这个块就很容易，先根据 hash buckets 的下标来找
,其实就是数组的访问 hash_buckets[2],然后再搜索整个链表，因为不同的dba 可能映射到同一个hash bucket 上

Yong Huang · 发表于 2007-11-25 22:44

原帖由 fxyj2008 于 2007-11-25 16:36 发表
> [Note: this is true in 9i only; in 10g, DDL will get rid of the entire cursor including heap 0]

我这边观察下来的结果是当发生DDL 的时候那么sql head 是不会变的，sqlbody 中的数据会被清理掉

Your test is completely understandable. Did you do it in 9i? How about 10g?

(Make sure you turn off those stupid smilies when you post a message if some of your text has smilies inserted in the middle.)

Yong Huang