可以通过以下命令进行转储:
sys@ORCL>alter session set events 'immediate trace name buffers level 4';
会话已更改。
sys@ORCL>select value from v$diag_infowhere name like 'Default Trace File';
VALUE
----------------------------------------
d:\app\lenovo\diag\rdbms\orcl\orcl\trace
\orcl_ora_6480.trc
不同的内容详细程度不同,此命令可用级别主要有1-10个级别,其中级别的含义如下:
level1:仅包含buffer header信息
Level2:包含buffer heades和buffer 概要信息转储
Level3:包含buffer headers和完整buffer内容转储
Level4:level1+Latch转储+LRU队列
Level5:Level4+Buffer 概要信息转储
Level6和level7:level4+完整的buffer内容转储
Level8:level4+显示users/waites信息
Level9:level 5+显示users/waiters信息
Level10:level 6+显示users/waiters信息
转储仅限于在测试环境中使用,转储的跟踪文件可能非常巨大,为获取完整的跟踪文件,建议设置初始化参数max_dump_file_size为UNLIMITED
本章节选取环节:
sys@ORCL>show parameter memory_target
NAME TYPE VALUE
----------------------------------------------- ------------------------------
memory_target big integer 3264M
sys@ORCL>show parameter max_dump
NAME TYPE VALUE
----------------------------------------------- ------------------------------
max_dump_file_size string unlimited
sys@ORCL>
从level4级跟踪文件的开头部分可以获得如下信息,这是记录的不同list的prev和next定位信息,其中ws就是指working sets,注意WRID指不同的编号
Dump of buffer cache at level 4 fortsn=2147483647, rdba=0
(WS) size: 493 (0) wsid: 1 state: 2 pool: 1
(WS_REPL_LIST) main_prev: 0x000007FFC7D4A920 main_next:0x000007FFC7D4A920 aux_prev: 0x000007FFACF8A940 aux_next: 0x000007FFACF66100
curnum: 493 auxnum: 493
cold: 7ffc7d4a920 hbmax: 0 hbufs: 0
(WS_WRITE_LIST) main_prev: 0x000007FFC7D4A950 main_next:0x000007FFC7D4A950 aux_prev: 0x000007FFC7D4A960 aux_next: 0x000007FFC7D4A960
curnum: 0 auxnum: 0
(WS_XOBJ_LIST) main_prev: 0x000007FFC7D4A980 main_next:0x000007FFC7D4A980 aux_prev: 0x000007FFC7D4A990 aux_next: 0x000007FFC7D4A990
curnum: 0 auxnum: 0
(WS_XRNG_LIST) main_prev: 0x000007FFC7D4A9B0 main_next:0x000007FFC7D4A9B0 aux_prev: 0x000007FFC7D4A9C0 aux_next: 0x000007FFC7D4A9C0
curnum: 0 auxnum: 0
(WS_REQ_LIST) main_prev: 0x000007FFC7D4A9E0 main_next:0x000007FFC7D4A9E0 aux_prev: 0x000007FFC7D4A9F0 aux_next: 0x000007FFC7D4A9F0
curnum: 0 auxnum: 0
(WS_L2WRT_LIST) main_prev: 0x000007FFC7D4AA10 main_next:0x000007FFC7D4AA10 aux_prev: 0x000007FFC7D4AA20 aux_next: 0x000007FFC7D4AA20
curnum: 0 auxnum: 0
(WS_L2REPL_LIST) main_prev: 0x000007FFC7D4AA40 main_next:0x000007FFC7D4AA40 aux_prev: 0x000007FFC7D4AA50 aux_next: 0x000007FFC7D4AA50
curnum: 0 auxnum: 0
(WS_L2KEEP_LIST) main_prev: 0x000007FFC7D4AA70 main_next:0x000007FFC7D4AA70 aux_prev: 0x000007FFC7D4AA80 aux_next: 0x000007FFC7D4AA80
curnum: 0 auxnum: 0
(WS) fbwanted: 0
(WS) bgotten: 0 sumwrt: 0
(WS) pwbcnt: 0,last: 0
接下来是具体的list链表信息,注意这里存在多条null列表,这是为buffercache不同部分(keep池,Recycle池以及不同block_size大小的内存使用)预分配:
MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN RPL_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[0x000007FFACF66100,0x000007FFACF8A940]
0x000007FFACF66018=>0x000007FFACF66148=>0x000007FFACF66278=>0x000007FFACF663A8=>0x000007FFACF664D8=>0x000007FFACF66608=>0x000007FFACF66738=>0x000007FFACF66868
………
MAIN WRT_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN WRT_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYWRT_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYWRT_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XOBJ_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN XOBJ_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYXOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYXOBJ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XRNG_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN XRNG_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYXRNG_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYXRNG_LST Queue header (PREV_DIRECTION)[NULL]
MAIN REQ_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN REQ_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYREQ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYREQ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN L2W_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN L2W_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYL2W_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYL2W_LST Queue header (PREV_DIRECTION)[NULL]
MAIN L2R_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN L2K_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN L2R_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYL2R_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYL2K_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYL2R_LST Queue header (PREV_DIRECTION)[NULL]
MAIN L2K_LSTQueue header (NEXT_DIRECTION)[NULL]
MAIN L2K_LSTQueue header (PREV_DIRECTION)[NULL]
AUXILIARYL2K_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARYL2K_LST Queue header (PREV_DIRECTION)[NULL]
(WS) size: 493(0) wsid: 2 state: 2 pool: 1
(WS_REPL_LIST) main_prev:0x000007FFC7D4AFF0 main_next: 0x000007FFC7D4AFF0 aux_prev: 0x000007FFACFAF2B0aux_next: 0x000007FFACF8AA70
curnum: 493 auxnum: 493
cold: 7ffc7d4aff0 hbmax: 0 hbufs: 0
(WS_WRITE_LIST) main_prev:0x000007FFC7D4B020 main_next: 0x000007FFC7D4B020 aux_prev: 0x000007FFC7D4B030aux_next: 0x000007FFC7D4B030
curnum: 0 auxnum: 0
(WS_XOBJ_LIST) main_prev:0x000007FFC7D4B050 main_next: 0x000007FFC7D4B050 aux_prev: 0x000007FFC7D4B060aux_next: 0x000007FFC7D4B060
curnum: 0 auxnum: 0
(WS_XRNG_LIST) main_prev:0x000007FFC7D4B080 main_next: 0x000007FFC7D4B080 aux_prev: 0x000007FFC7D4B090aux_next: 0x000007FFC7D4B090
curnum: 0 auxnum: 0
(WS_REQ_LIST) main_prev: 0x000007FFC7D4B0B0main_next: 0x000007FFC7D4B0B0 aux_prev: 0x000007FFC7D4B0C0 aux_next:0x000007FFC7D4B0C0
curnum: 0 auxnum: 0
(WS_L2WRT_LIST) main_prev:0x000007FFC7D4B0E0 main_next: 0x000007FFC7D4B0E0 aux_prev: 0x000007FFC7D4B0F0aux_next: 0x000007FFC7D4B0F0
curnum: 0 auxnum: 0
(WS_L2REPL_LIST) main_prev:0x000007FFC7D4B110 main_next: 0x000007FFC7D4B110 aux_prev: 0x000007FFC7D4B120aux_next: 0x000007FFC7D4B120
curnum: 0 auxnum: 0
(WS_L2KEEP_LIST) main_prev:0x000007FFC7D4B140 main_next: 0x000007FFC7D4B140 aux_prev: 0x000007FFC7D4B150aux_next: 0x000007FFC7D4B150
curnum: 0 auxnum: 0
(WS) fbwanted: 0
(WS) bgotten: 0 sumwrt: 0
(WS) pwbcnt: 0, last: 0
在跟踪文件中,看到的主要是AUXILIARY RPL_LST Queue队列的信息
AUXILIARY RPL_LST Queue的由来:
从oracle 8i开始,lru list 和dirty list又分别增加了辅助list(AUXILIARY List),用于提高管理效率。引入了辅助List之后,当数据库初始化时,buffer首先存放在lru的辅助list上(AUXILIBARY RPL_LST),当被使用后移动到lru主list上,这样当用户进程搜索free buffer 时,就可以从 LRU_AUX List 开始,而dbwr搜索dirty Buffer时,则可以从LRU_MAIN List开始,从而提高了搜索效率和数据库性能