转储buffer cache

可以通过以下命令进行转储：
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开始，从而提高了搜索效率和数据库性能