从dtrace日志中贴出部分日志: 0 51768 kcbgtcr:entry i=643 PID::entry:==pid1555:oracle:kcbgtcr:entry fffffd7ffac27cd8 0 2fe 0 23dede0 fffffd7fffdfa790 0 51769 sskgslcas:entry i=644 PID::entry:==pid1555:oracle:sskgslcas:entry 3a4282a90 0 1 0 0 3a4c8b1a0 0 60716 ktrexf:entry i=645 PID::entry:==pid1555:oracle:ktrexf:entry fffffd7fffdfa790 d7fcd60 0 15398551572 1fe8 23dc9f0 0 64186 kcbzgs:entry i=646 PID::entry:==pid1555:oracle:kcbzgs:entry 1 d7fcd60 0 0 395fe35c8 23dc9f0 0 53941 kssadf_numa_intl:entry i=647 PID::entry:==pid1555:oracle:kssadf_numa_intl:entry 23 3a41458b0 3a4874d70 0 0 13 0 51769 sskgslcas:entry i=648 PID::entry:==pid1555:oracle:sskgslcas:entry 395fe3610 0 1 0 395fe35c8 395fe35c8 -------如果判断这个就是buffer pin呢?????
0 128723 sskgsldecr:entry i=649 PID::entry:==pid1555:oracle:sskgsldecr:entry 3a4282a90 1 1 0 1 2000000000000000
为了能更好的解释,我再次用dtrace跟踪 一把,先查一下本会话下的进程号:
SQL> select spid from v$session s,v$process b where s.paddr=b.addr and s.sid in(select sid from v$mystat where rownum=1); SPID ------------------------ 1555 进程号为1555 SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj.t1¡¡where rownum=1; ROWID FILE# BLOCK# ID NAME ------------------ ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- AAASP9AAGAAAACHAAA 6 135 1 gyj1 SQL> select hladdr from x$bh where file#=6 and dbablk=135; HLADDR ---------------- 00000003A4282A90 SQL> select id,name from gyj.t1 where rowid='AAASP9AAGAAAACHAAA'; ID NAME ---------- ---------------------------------------------------------------------------------------------------- 1 gyj1 接着用dtrace跟踪这个进程: bash-3.2# ./dtrace.d -x switchrate=1hz -b 32m 1555 > dtrace111.log dtrace: script './dtrace.d' matched 160196 probes 然后会到回话号1555中执行 SQL> select id,name from gyj.t1 where rowid='AAASP9AAGAAAACHAAA'; ID NAME ---------- ---------------------------------------------------------------------------------------------------- 1 gyj1 从dtrace日志中贴出的部分日志: 0 51768 kcbgtcr:entry i=643 PID::entry:==pid1555:oracle:kcbgtcr:entry fffffd7ffac27cd8 0 2fe 0 23dede0 fffffd7fffdfa790 0 51769 sskgslcas:entry i=644 PID::entry:==pid1555:oracle:sskgslcas:entry 3a4282a90 0 1 0 0 3a4c8b1a0 0 60716 ktrexf:entry i=645 PID::entry:==pid1555:oracle:ktrexf:entry fffffd7fffdfa790 d7fcd60 0 15398551572 1fe8 23dc9f0 0 64186 kcbzgs:entry i=646 PID::entry:==pid1555:oracle:kcbzgs:entry 1 d7fcd60 0 0 395fe35c8 23dc9f0 0 53941 kssadf_numa_intl:entry i=647 PID::entry:==pid1555:oracle:kssadf_numa_intl:entry 23 3a41458b0 3a4874d70 0 0 13 0 51769 sskgslcas:entry i=648 PID::entry:==pid1555:oracle:sskgslcas:entry 395fe3610 0 1 0 395fe35c8 395fe35c8 -------如果判断这个就是buffer pin呢????? 0 128723 sskgsldecr:entry i=649 PID::entry:==pid1555:oracle:sskgsldecr:entry 3a4282a90 1 1 0 1 2000000000000000 dump出buffer header日志,找到6号文件135号块的bh SQL> alter session set events 'immediate trace name buffers level 1'; 直接dump6号文件135号块也可以,11g可以直接看块上的BH: SQL> alter system dump datafile 6 block 135;---红色部分就是6号文件135号块的bh的信息 BH (0x395fe35c8) file#: 6 rdba: 0x01800087 (6/135) class: 1 ba: 0x395d34000 set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 245,28 dbwrid: 0 obj: 74749 objn: 74749 tsn: 7 afn: 6 hint: f hash: [0x3a42842b0,0x3a42842b0] lru: [0x395fe37e0,0x395fe3580] ckptq: [NULL] fileq: [NULL] objq: [0x395fe3808,0x3947e6b18] st: XCURRENT md: NULL tch: 38 flags: block_written_once redo_since_read LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] cr pin refcnt: 0 sh pin refcnt: 0 0 51769 sskgslcas:entry i=648 PID::entry:==pid1555:oracle:sskgslcas:entry 395fe3610 0 1 0 395fe35c8 395fe35c8 BH (0x395fe35c8) ---这是6号文件135号块的BH地址 比较0x395fe35c8和0x395fe3610的地址偏移量相差72个字节,一个BH大小约200字节,即buffer pin位于BH的第72个字节 用oradebug查一下buffer pin的值 SQL> oradebug peek 0x395fe3610 4 [395FE3610, 395FE3614) = 00000001 --获得buffer pin SQL> oradebug peek 0x395fe3610 4 [395FE3610, 395FE3614) = 00000000 --释放buffer pin 可以配合mdb设断点来观察buffer pin的值 -bash-3.2# mdb -p 1555 Loading modules: [ ld.so.1 libc.so.1 ] > kcbgtcr:b --开始逻辑读 > :c mdb: stop at kcbgtcr mdb: target stopped at: kcbgtcr: pushq %rbp > sskgsldecr:b --获得Buffer Pin马上释放CBC Latch > :c mdb: stop at sskgsldecr mdb: target stopped at: sskgsldecr: lock subq %rsi,(%rdi) > ::delete all > kcbrls:b --释放Buffer Pin > :c mdb: stop at kcbrls mdb: target stopped at: kcbrls: pushq %rbp > sskgsldecr:b --释放CBC Latch > :c mdb: stop at sskgsldecr mdb: target stopped at: sskgsldecr: lock subq %rsi,(%rdi) > ::quit
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoeQQ: 252803295
Email: oracledba_cn@hotmail.com
Blog:
ITPUB:
OCM:
_____________________________________________________________ 加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!答案在：
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036