oracle数据库新手,对cache buffer的几点疑惑,请各位高手指点!

sqysl

1、10G是可以动态调整内存的；
2、在以往的帖子里已回答；

Yong Huang

原帖由 viadeazhu 于 2009-6-19 06:53 发表

我的结论是：addr这一列是查询x$bh的这一个session的PGA session heap的某个地址。
...
详细的内容，可以参考我昨天的回帖：
http://www.itpub.net/thread-996640-2-5.html

Message #11 of http://www.itpub.net/thread-996640-2-5.html is an excellent posting! Thank you!

Can you post the relevant part of the trace file indicating it's PGA session heap?

I wonder from where zhouwf0726 got "Hex address of the Buffer Header" (see #3 of http://www.itpub.net/viewthread.php?tid=996640).

Yong Huang

Yong Huang

原帖由 sqysl 于 2009-6-17 07:22 发表
2、nxt_hash,prv_hash分别指向hash chain中前一个和后一个buffer，nxt_repl,prv_repl 分别指向LRU中前一个和后一个buffer，这些在导出块中存在的；

That's correct! "repl" means replacement list, a.k.a (also known as) LRU. Ref: Steve Adams, http://www.ixora.com.au/q+a/0103/07160329.htm:
"The main replacement list is what is commonly called the LRU list."
Also see http://www.ixora.com.au/tips/mystery.doc

Yong Huang

viadeazhu

原帖由 Yong Huang 于 2009-6-21 03:27 发表


Message #11 of http://www.itpub.net/thread-996640-2-5.html is an excellent posting! Thank you!

Can you post the relevant part of the trace file indicating it's PGA session heap?

I wonder from where zhouwf0726 got "Hex address of the Buffer Header" (see #3 of http://www.itpub.net/viewthread.php?tid=996640).

Yong Huang

hi HY老大，那天来上海和你合影了：）可能你不知道谁是我：）
SQL> select ADDR,INDX,HLADDR,FLAG,TS#,FILE#,DBABLK,CLASS,BA,state from sys.x$bh where OBJ=25182;

ADDR                   INDX HLADDR                 FLAG        TS#      FILE#     DBABLK      CLASS BA                    STATE
---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
FFFFFFFF7B849758       3847 00000004192428F8   35659776          5         17       4809          8 00000003D4282000          1
FFFFFFFF7B849758      10516 0000000419392920   35659776          5         17       4811          4 00000003D3096000          1
FFFFFFFF7B849758      21322 00000003DA2E9508   35659776          5         17       4810          9 00000003CEB10000          1
FFFFFFFF7B849758      27831 00000003DA439530     524288          5         17       4812          1 00000003D2AA2000          1

SQL> select a.spid
  2  from v$process a,
  3   v$session b
where a.ADDR = b.PADDR
and b.sid     = (select sid from v$mystat where rownum<2);  4    5  

SPID
------------------------------------
24811

SQL> oradebug setospid 24811
Oracle pid: 27, Unix process pid: 24811, image: oracle@xxx(TNS V1-V3)
SQL> oradebug dump heapdump 1
Statement processed.

截取部分pga trace文件：
******************************************************
******************************************************
HEAP DUMP heap name="top uga heap"  desc=1066a87e8
extent sz=0xffc0 alt=200 het=32767 rec=0 flg=2 opc=3
parent=0 owner=0 nex=0 xsz=0x1fff8
EXTENT 0 addr=ffffffff7b840008
  Chunk ffffffff7b840018 sz=       80    free      "               "
  Chunk ffffffff7b840068 sz=    65432    freeable  "session heap   "  ds=ffffffff7b826ac8EXTENT 1 addr=ffffffff7b830008
  Chunk ffffffff7b830018 sz=       80    free      "               "
  Chunk ffffffff7b830068 sz=    65432    recreate  "session heap   "  latch=0
     ds ffffffff7b826ac8 sz=   130864 ct=        2
        ffffffff7b840068 sz=    65432
Total heap size    =   131024


至于为什么大家认为是"Hex address of the Buffer Header"，我想应该是看到了这篇对x$bh的column解释吧：
http://www.o r a c l e fans.cn/forum/showtopic.jsp?rootid=801
(ps:去掉“oracle”之间的空格，不然他说我是违规广告。。。)

[ 本帖最后由 viadeazhu 于 2009-6-21 13:10 编辑 ]

sqysl

YONG HUANG终于回来了，欢迎！
我想这个问题，仅仅靠试验搞清楚的难度比较大，如果有一些比较权威的资料，先搞清楚这个列的定义和意义，然后再通过实验验证会更好些，这个问题的资料可真是很难找。
现在可以确定的是，这个地址和SESSION有关。

[ 本帖最后由 sqysl 于 2009-6-21 13:30 编辑 ]

Yong Huang

原帖由 viadeazhu 于 2009-6-20 23:08 发表
hi HY老大，那天来上海和你合影了：）可能你不知道谁是我：）
...
HEAP DUMP heap name="top uga heap"  desc=1066a87e8
...
  Chunk ffffffff7b840068 sz=    65432    freeable  "session heap   "  ds=ffffffff7b826ac8EXTENT 1 addr=ffffffff7b830008
...
至于为什么大家认为是"Hex address of the Buffer Header"，我想应该是看到了这篇对x$bh的column解释吧：
http://www.o r a c l e fans.cn/forum/showtopic.jsp?rootid=801
(ps:去掉“oracle”之间的空格，不然他说我是违规广告。。。)

I got the picture Jack sent me. Let me know who is you in it. It's a shame I didn't arrange more time to meet with my friends, old or new.

Since the heap dump has the subheap descriptor (ds ffffffff7b826ac8), maybe you can drill down to get more info by dumping that, something like
alter session set events 'immediate trace name heapdump_addr level 2 addr 0xffffffff7b826ac8'.

The quoted article on 违规广告.cn seems to be relevant to 8i only. I happen to have an 8.1.7.0 database around. I checked the addr column for buffers of a given table. The addr values do NOT change, even after I checkpoint. (On higher versions, 9.2.0.8 and up, checkpoint or flushing buffer cache will change addr.) Since an 8i database is hard to find, let me post my findings:

SQL> select ADDR,INDX,HLADDR,FLAG,TS#,FILE#,DBABLK,CLASS,BA,state from sys.x$bh where OBJ = 285756;

ADDR           INDX HLADDR         FLAG        TS#      FILE#     DBABLK      CLASS BA            STATE
-------- ---------- -------- ---------- ---------- ---------- ---------- ---------- -------- ----------
4054E190      26497 406A76C4   35659776         26         21          9          4 55614000          1
4056F668      27500 406A7940   35659776         26         21         10          1 55DEA000          1

Level 1 buffers dump trace:

BH #26497 (0x4054e190) file#: 21 rdba: 0x05400009 (21/9) class 4 ba: 0x55614000
  set: 2, dbwrid: 0
  hash: [406a792c,406a792c], lru: [40613f50,4049ddb8]
  LRU flags:
  ckptq: [NULL] fileq: [NULL]
  st: XCURRENT, md: NULL, rsop: 0x0, tch: 2
  flags: gotten_in_current_mode block_written_once redo_since_read
  L:[0x0.0.0] H:[0x0.0.0] R:[0x0.0.0]

BH #27500 (0x4056f668) file#: 21 rdba: 0x0540000a (21/10) class 1 ba: 0x55dea000
  set: 2, dbwrid: 0
  hash: [406a7ba8,406a7ba8], lru: [405286a0,4049aa30]
  LRU flags:
  ckptq: [NULL] fileq: [NULL]
  st: XCURRENT, md: NULL, rsop: 0x0, tch: 1
  flags: gotten_in_current_mode block_written_once redo_since_read
  L:[0x0.0.0] H:[0x0.0.0] R:[0x0.0.0]

Note the x$bh.indx value matches the number after # in the trace, and addr value matches that in the parentheses. From 9.2.0.8 on, it's no longer the case.

Yong Huang

viadeazhu

hehe，I am beside weifeng

言归正传，貌似分析不下去了。
SQL> select ADDR,INDX,HLADDR,FLAG,TS#,FILE#,DBABLK,CLASS,BA,state from sys.x$bh where OBJ=25182;

ADDR                   INDX HLADDR                 FLAG        TS#      FILE#     DBABLK      CLASS BA                    STATE
---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
FFFFFFFF7B849758       3817 00000004192428F8   35659776          5         17       4809          8 00000003D4282000          1
FFFFFFFF7B849758      10492 0000000419392920   35659776          5         17       4811          4 00000003D3096000          1
FFFFFFFF7B849758      21309 00000003DA2E9508   35659776          5         17       4810          9 00000003CEB10000          1
FFFFFFFF7B849758      27823 00000003DA439530     524288          5         17       4812          1 00000003D2AA2000          1

SQL> select a.spid
  2  from v$process a,
  3  v$session b
where a.ADDR = b.PADDR
and b.sid     = (select sid from v$mystat where rownum<2);    4    5  

SPID
------------------------------------
16893

SQL>  oradebug setospid 16893
Statement processed.
SQL> oradebug dump heapdump 1
Statement processed.

> grep session pgaheap.trc
  Chunk ffffffff7b840068 sz=    65432    freeable  "session heap   "  ds=ffffffff7b826ac8
  Chunk ffffffff7b830068 sz=    65432    recreate  "session heap   "  latch=0

ALTER SESSION SET EVENTS \'immediate trace name heapdump_addr level 1, addr 0xffffffff7b826ac8\';
（level 2没什么实质信息，于是作了level 1summary dump）
HEAP DUMP heap name="session heap"  desc=ffffffff7b826ac8
extent sz=0xff80 alt=32767 het=32767 rec=0 flg=2 opc=2
parent=1066a87e8 owner=3da111008 nex=0 xsz=0xff80
EXTENT 0 addr=ffffffff7b840080
  Chunk ffffffff7b840090 sz=    28768    perm      "perm           "  alo=1432
  Chunk ffffffff7b8470f0 sz=    13248    free      "               "
  Chunk ffffffff7b84a4b0 sz=     4144    freeable  "kxsFrame4kPage "
  Chunk ffffffff7b84b4e0 sz=     4144    freeable  "kxsFrame4kPage "
  Chunk ffffffff7b84c510 sz=     4144    freeable  "kxsFrame4kPage "
  Chunk ffffffff7b84d540 sz=      184    free      "               "


他在free的chunk里。。。

[ 本帖最后由 viadeazhu 于 2009-6-22 21:14 编辑 ]