郭一军的ChinaUnix博客

http://www.jianfengedu.com

  • 博客访问: 518108
  • 博文数量: 248
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 443
  • 用 户 组: 普通用户
  • 注册时间: 2013-04-27 20:07
  • 认证徽章:
个人简介

郭一军,网名guoyJoe,尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com

文章分类
文章存档

2014年(246)

2013年(2)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

一组LRU链表包括LRU主链,LRU辅助链,LRUW主链,LRUW辅助链,称为一个WorkSet(工作组)如下图:

sys@ZMDB> select CNUM_SET,CNUM_REPL,ANUM_REPL,CNUM_WRITE,ANUM_WRITE from x$kcbwds where CNUM_SET>0;

 

  CNUM_SET  CNUM_REPL  ANUM_REPL CNUM_WRITE ANUM_WRITE

---------- ---------- ---------- ---------- ----------

     15221      15221       3796          0          0

     15221      15221       3783          0          0

 

 CNUM_SET:工作组总的buffer总数量

 CNUM_REPL:工作组中LRUbuffer总数量(主LRU+LRU

 ANUM_REPL:工作组中辅LRUBUFFER的数量

 

通过隐含参数查到BUFFER的总的个数是30442,正好与上面的CNUM_SET=15221+15221

sys@ZMDB> @?/rdbms/admin/show_para

Enter value for p: _db_block_buffers

old  12:     AND upper(i.ksppinm) LIKE upper('%&p%')

new  12:     AND upper(i.ksppinm) LIKE upper('%_db_block_buffers%')

 

P_NAME                                   P_DESCRIPTION                                      P_VALUE                        ISDEFAULT ISMODIFIED ISADJ

---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----

_db_block_buffers                        Number of database blocks cached in memory: hidden 30442                          TRUE      FALSE        FALSE

                                          Parameter

 

我们用以下语句查下数据库中buffer所在LRU的状态

sys@ZMDB> select lru_flag,count(*) from x$bh group by lru_flag;

 

  LRU_FLAG   COUNT(*)

---------- ----------

         6        208

         2         10

         4       7122

         8      15199

0                         7646

 

我们对LRU_FLAG=62480等做出解释,举个例子,对于6是什么含义呢?

首先要在x$bh中找到lru_flag=6的任意的一个BUFFER

sys@ZMDB> select LRU_FLAG,LOWER(BA)from x$bh where lru_flag=6 and rownum=1;

 

  LRU_FLAG LOWER(BA)

---------- ----------------

         6 0000000081dae000

DUMP buffer_cacheBH信息,如下命令:

sys@ZMDB> alter session set events'immediate trace name buffers level 1';

 

Session altered.

ys@ZMDB> col value for a85

sys@ZMDB> select * from v$diag_info where name='Default Trace File';

 

   INST_ID NAME                                               VALUE

---------- -------------------------------------------------- -------------------------------------------------------------------------------------

1                         Default Trace File                                 /u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13235.trc

 

通过BA=81dae000搜索trace文件,

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13235.trc

得到如下内容:

BH (0x81fe7e38) file#: 1 rdba: 0x0040ace1 (1/44257) class: 1 ba: 0x81dae000

  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25

  dbwrid: 0 obj: 421 objn: 423 tsn: 0 afn: 1 hint: f

  hash: [0x9ef9d710,0x853f8da8] lru: [0x81fe7df0,0x81fe8050]

  lru-flags: moved_to_tail on_auxiliary_list

  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]

  st: CR md: NULL fpin: 'kdswh06: kdscgr' tch: 1

  cr: [scn: 0x0.80350f4d],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.80350f4d],[sfl: 0x0],[lc: 0x0.8034c532]

  flags: block_written_once redo_since_read

 

LRU_FLAG=6的意思是lru-flags: moved_to_tail on_auxiliary_list,就是向LRU的辅助链表的尾部移动,这有可能是SMONLRU的主链表上的非脏块、TCH<=1并且状态是非PINBUFFER被挂接到LRU辅助链表的尾部。

根据以上的方法同理可以解释出LRU_FLAG的含义:

LRU_FLAG

0==>LRU-主链冷端的头部,这个比较特殊他在DUMP没有显示LRU_FLAG

 

2==>LRU-主链冷端的尾部,lru-flags: moved_to_tail

 

4==>LRU-辅助链,lru-flags:on_auxiliary_list

 

6==>LRU-辅助链的尾部,lru-flags: moved_to_tail on_auxiliary_list

 

8==>LUR-主链热端,lru-flags: hot_buffer

 

 

当发生物理读时,Oracle会从LRU辅助链表找空闲的BUFFER,然后把LRU辅助的链上的BUFFER挂接到LRU主链的冷端头,实验如下:

首先要保证有LRU辅助链上的BUFFER,即有LRU_FLAG=6LRU_FLAG=4,如果数据库刚刚启来,可能没有LRU_FLAG=6LRU_FLAG=4,那需要做大量的物理读操作,才会有LRU_FLAG=6LRU_FLAG=4

sys@ZMDB> alter system flush buffer_cache;

 

System altered.

 

sys@ZMDB> select lru_flag,count(*) from x$bh group by lru_flag;

 

  LRU_FLAG   COUNT(*)

---------- ----------

         6        208

         4      30009

         0          2

第一次DUMP整个BUFFER CACHE:

sys@ZMDB> alter session set events'immediate trace name buffers level 1';

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trc

 

发生物理读

gyj@ZMDB> conn gyj/gyj

Connected.

gyj@ZMDB> set autot on;

gyj@ZMDB> select id,name, dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj_t1 where id=1;

 

        ID NAME                                FILE#     BLOCK#

---------- ------------------------------ ---------- ----------

         1 gyj1                                    7        139

 

Execution Plan

----------------------------------------------------------

Plan hash value: 59758809

 

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |     1 |    14 |    68   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| GYJ_T1 |     1 |    14 |    68   (0)| 00:00:01 |

----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ID"=1)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          1  db block gets

        254  consistent gets

        248  physical reads

          0  redo size

        733  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

sys@ZMDB> select LRU_FLAG,lower(BA),TCH from x$bh where file#=7 and dbablk=139;

 

  LRU_FLAG LOWER(BA)               TCH

---------- ---------------- ----------

         0 000000007d1b2000          1

         4 0000000078558000          0

         4 0000000085f68000          0

 

物理读完成后,再次dump整个buffer cache,

sys@ZMDB> alter session set events'immediate trace name buffers level 1';

 

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trc

 

BA=7d1b2000,搜索第一次DUMPtrace文件

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trc

 

BH (0x7d3e8098) file#: 3 rdba: 0x00c0586b (3/22635) class: 34 ba: 0x7d1b2000

  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25

  dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f

  hash: [0x9efa7570,0x9efa7570] lru: [0x7f7f5d30,0x7d3e8050]

  lru-flags: on_auxiliary_list

  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]

  st: FREE md: NULL fpin: 'ktuwh03: ktugnb' tch: 0 lfb: 33

  flags:

 

BA=7d1b2000,搜索第二次DUMPtrace文件

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trc

 

BH (0x7d3e8098) file#: 7 rdba: 0x01c0008b (7/139) class: 1 ba: 0x7d1b2000

  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25

  dbwrid: 0 obj: 22919 objn: 19567 tsn: 7 afn: 7 hint: f

  hash: [0x787e4bd8,0x9e4cda50] lru: [0x7f7f5d30,0x7d3e8050]

  ckptq: [NULL] fileq: [NULL] objq: [0x9a88e518,0x7d3e8078] objaq: [0x9a88e508,0x7d3e8088]

  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1

  flags: only_sequential_access

  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

 

从上面的两个trace可以得出结论ba: 0x7d1b2000

lru-flags: on_auxiliary_list(LRU_FLAG=4)LRU-主链冷端的头部,这个比较特殊在DUMP没有显示LRU_FLAG(LRU_FLAG=0)

 

观察LRU TCH>=2时冷端移到热端

 

1BUFFER手动设为100M

 

 ALTER SYSTEM SET memory_max_target=0 scope=spfile;

  ALTER SYSTEM SET memory_target=0;

 alter system set sga_target=0;

 

 

create table gyj1_t80 (id int,name char(2000));

 

create table gyj2_t80 (id int,name char(2000));

 

begin

  for i in 1 .. 30000

  loop

    insert into gyj1_t80 values(i,'gyj'||i);

 commit;

 end loop;

end;

/

 

SQL> SQL> select bytes/1024/1024||'M' from dba_segments where segment_name='GYJ1_T80' and owner='GYJ';

 

BYTES/1024/1024||'M'

-----------------------------------------

80M

 

 

begin

  for i in 1 .. 30000

  loop

    insert into gyj2_t80 values(i,'gyj'||i);

 commit;

 end loop;

end;

/

 

 

create index idx_gyj1_t80m on gyj1_t80(id);

 

create index idx_gyj2_t80m on gyj2_t80(id);

 

SQL> show user;

USER is "GYJ"

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

 

第一次dump

SQL> alter session set events'immediate trace name buffers level 1';

 

Session altered.

 

 

SQL> select * from v$diag_info where name='Default Trace File';

 

   INST_ID NAME

---------- --------------------

VALUE

--------------------------------------------------------------------------------

         1 Default Trace File

/u01/app/oracle/diag/rdbms/jfdb/jfdb/trace/jfdb_ora_7210.trc

 

 

发生一个物理读走索引

set autot on

select id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;

 

 

SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;

 

        ID NAME                      FILE#     BLOCK#

---------- -------------------- ---------- ----------

         1 gyj1                          5        581

 

 

select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

SQL> select LRU_FLAG,lower(BA),TCH, decode(state,0,'free',1,'xcur',2,'scur'

  2   ,3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,

  3  'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashf

  4  ree',  17, 'flashcur', 18, 'flashna')  from x$bh where file#=5 and dbablk=581;

 

  LRU_FLAG LOWER(BA)               TCH DECODE(STA

---------- ---------------- ---------- ----------

         0 000000009fca8000          1 xcur

 

 

SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

 

  LRU_FLAG LOWER(BA)               TCH

---------- ---------------- ----------

         0 000000009fca8000          5

 

 

 

 

SQL> set autot traceonly;

SQL>  select /*+ index(G) */ count(name) from gyj1_t80 G where id<=8000;

 

 

SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

 

  LRU_FLAG LOWER(BA)               TCH

---------- ---------------- ----------

         0 000000009fca8000          6

 

再次发生物理读,此时LRU_FLAG=0变为8,同时TCH=8重置为0

SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

 

  LRU_FLAG LOWER(BA)               TCH

---------- ---------------- ----------

         0 000000009fca8000          8

 

SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

 

  LRU_FLAG LOWER(BA)               TCH

---------- ---------------- ----------

         8 000000009fca8000          0

 

 

BH (0x9ffe02a8) file#: 5 rdba: 0x01400245 (5/581) class: 1 ba: 0x9fca8000

  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 15,19

  dbwrid: 0 obj: 13537 objn: 13537 tsn: 5 afn: 5 hint: f

  hash: [0xb6a86de0,0xb6a86de0] lru: [0x9ffe0260,0x9ffe9a60]

  lru-flags: hot_buffer

  ckptq: [NULL] fileq: [NULL] objq: [0x9ffe0618,0x9ffe0028] objaq: [0x9ffe0628,0x9ffe0038]

  st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 0

  flags:

  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

 

TCH=0时,再发生大量物理读,地址为9fca8000BUFFER就被重用了,彻底从BUFFER消失

 

SQL>  select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

 

  LRU_FLAG LOWER(BA)               TCH

---------- ---------------- ----------

         8 000000009fca8000          0

 

SQL>  select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;

 

no rows selected

通过实验,我们更清楚地了解到物理读LRU的基本流程,可以进一步理解物理读内部的LRU算法。



阅读(3473) | 评论(1) | 转发(2) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册