通过如下命令来转储buffer cache的内容,从而清晰地看到它的数据结构
SQL> alter session set events 'immediate trace name buffers level 10'; Session altered. SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from sys.v$mystat m, 6 sys.v$session s,sys.v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from sys.v$thread t,sys.v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from sys.v$parameter 12 where name = 'user_dump_dest') d; TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_3294.trc
不同的level转储的内容详细程度不同,此命令的可用级别主要有1-10级,其中和级别
的含义如下:
level 1:仅包含buffer headers信息
level 2:包含buffer headers和buffer概要信息转储
level 3:包含buufer headers和完整的buffer内容转储
level 4: level 1+ latch转储+ lru队列
level 5: level 4+ buffer概要信息转储
level 6和level 7:level 4+完整的buffer内容转储
level 8:level 4+显示users/waiters信息
level 9:level 5+显示users/waiters信息
level 10:level 6+显示users/waiters信息
转储仅限于在测试环境中使用,转储的跟踪文件可能非常巨大,为获取完整的跟踪
文件,建议设置初始化参数max_dump_file_size为UNLIMITED
** 2013-12-27 23:18:36.317
*** SERVICE NAME:(SYS$USERS) 2013-12-27 23:18:36.315
*** SESSION ID:(144.29) 2013-12-27 23:18:36.315
Dump of buffer cache at level 10 for tsn=2147483647, rdba=0
(WS) size: 0 wsid: 1 state: 0
(WS_REPL_LIST) main_prev: 29142bbc main_next: 29142bbc aux_prev: 29142bc4 aux_next: 29142bc4curnum: 0 auxnum: 0
cold: 29142bbc hbmax: 0 hbufs: 0
(WS_WRITE_LIST) main_prev: 29142bd8 main_next: 29142bd8 aux_prev: 29142be0 aux_next: 29142be0curnum: 0 auxnum: 0
(WS_XOBJ_LIST) main_prev: 29142bf4 main_next: 29142bf4 aux_prev: 29142bfc aux_next: 29142bfccurnum: 0 auxnum: 0
(WS_XRNG_LIST) main_prev: 29142c10 main_next: 29142c10 aux_prev: 29142c18 aux_next: 29142c18curnum: 0 auxnum: 0
(WS_REQ_LIST) main_prev: 29142c2c main_next: 29142c2c aux_prev: 29142c34 aux_next: 29142c34curnum: 0 auxnum: 0
(WS) fbwanted: 0
(WS) bgotten: 0 sumwrt: 0
(WS) pwbcnt: 0
MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN RPL_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (PREV_DIRECTION)[NULL]
MAIN WRT_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN WRT_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (PREV_DIRECTION)[NULL]
MAIN REQ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN REQ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (PREV_DIRECTION)[NULL]
(WS) size: 0 wsid: 2 state: 0
(WS_REPL_LIST) main_prev: 29142f40 main_next: 29142f40 aux_prev: 29142f48 aux_next: 29142f48curnum: 0 auxnum: 0
cold: 29142f40 hbmax: 0 hbufs: 0
(WS_WRITE_LIST) main_prev: 29142f5c main_next: 29142f5c aux_prev: 29142f64 aux_next: 29142f64curnum: 0 auxnum: 0
(WS_XOBJ_LIST) main_prev: 29142f78 main_next: 29142f78 aux_prev: 29142f80 aux_next: 29142f80curnum: 0 auxnum: 0
(WS_XRNG_LIST) main_prev: 29142f94 main_next: 29142f94 aux_prev: 29142f9c aux_next: 29142f9ccurnum: 0 auxnum: 0
(WS_REQ_LIST) main_prev: 29142fb0 main_next: 29142fb0 aux_prev: 29142fb8 aux_next: 29142fb8curnum: 0 auxnum: 0
(WS) fbwanted: 0
(WS) bgotten: 0 sumwrt: 0
(WS) pwbcnt: 0
MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN RPL_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (PREV_DIRECTION)[NULL]
MAIN WRT_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN WRT_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (PREV_DIRECTION)[NULL]
MAIN REQ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN REQ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (PREV_DIRECTION)[NULL]
(WS) size: 10479 wsid: 3 state: 2
(WS_REPL_LIST) main_prev: 23fedc20 main_next: 247ee1a0 aux_prev: 207f6bd0 aux_next: 23fedcd0curnum: 10479 auxnum: 1105
cold: 207f6b20 hbmax: 5200 hbufs: 2848
(WS_WRITE_LIST) main_prev: 291432e0 main_next: 291432e0 aux_prev: 291432e8 aux_next: 291432e8curnum: 0 auxnum: 0
(WS_XOBJ_LIST) main_prev: 291432fc main_next: 291432fc aux_prev: 29143304 aux_next: 29143304curnum: 0 auxnum: 0
(WS_XRNG_LIST) main_prev: 29143318 main_next: 29143318 aux_prev: 29143320 aux_next: 29143320curnum: 0 auxnum: 0
(WS_REQ_LIST) main_prev: 29143334 main_next: 29143334 aux_prev: 2914333c aux_next: 2914333ccurnum: 0 auxnum: 0
(WS) fbwanted: 0
(WS) bgotten: 11481 sumwrt: 4081
(WS) pwbcnt: 112
MAIN RPL_LST Queue header (NEXT_DIRECTION)[247ee1a0,23fedc20]
0x247ee14c=>0x23feee5c=>0x23feedac=>0x23fee61c=>0x23fee1fc=>0x23fef53c=>0x23fef1cc=>0x23fee77c
0x23fee56c=>0x23fee82c=>0x23fee8dc=>0x23fee98c=>0x23feea3c=>0x23feeaec=>0x23fef06c=>0x243eefbc
0x23fef11c=>0x23fef32c=>0x23fef3dc=>0x23fef48c=>0x23fef5ec=>0x23fef74c=>0x23fef7fc=>0x23fef95c
0x23fefa0c=>0x23fefabc=>0x23fefb6c=>0x23fefc1c=>0x23fefccc=>0x23fefd7c=>0x23fefe2c=>0x23fefedc
0x23feff8c=>0x23ff003c=>0x23ff00ec=>0x23ff019c=>0x23ff024c=>0x23ff03ac=>0x23ff045c=>0x23ff050c
0x23ff05bc=>0x23ff066c=>0x23ff071c=>0x23ff07cc=>0x23ff087c=>0x23ff092c=>0x23ff09dc=>0x23ff0a8c
0x23ff0b3c=>0x23ff0bec=>0x23ff0c9c=>0x23ff0d4c=>0x23ff0dfc=>0x23ff0eac=>0x23ff0f5c=>0x23ff10bc
0x23ff116c=>0x23ff12cc=>0x23ff137c=>0x23ff142c=>0x23ff14dc=>0x23ff158c=>0x23ff163c=>0x23ff16ec