一组LRU链表包括LRU主链,LRU辅助链,LRUW主链,LRUW辅助链,称为一个WorkSet(工作组)如下图:
sys@ZMDB selectCNUM_SET,CNUM_REPL,ANUM_REPL,CNUM_WRITE,ANUM_WRITE from x$kcbwds whereCNUM_SET
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:工作组中LRU的buffer总数量(主LRU+辅LRU)
ANUM_REPL:工作组中辅LRU总BUFFER的数量
通过隐含参数查到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) LIKEupper('% p%')
new 12: AND upper(i.ksppinm) LIKEupper('%_db_block_buffers%')
P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIEDISADJ
_db_block_buffers Number of database blocks cached inmemory: 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=6,2,4,8,0等做出解释,举个例子,对于6是什么含义呢?
首先要在x$bh中找到lru_flag=6的任意的一个BUFFER
sys@ZMDB select LRU_FLAG,LOWER(BA)from x$bh where lru_flag=6 andrownum=1;
LRU_FLAG LOWER(BA)
6 0000000081dae000
DUMP buffer_cache中BH信息,如下命令:
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 TraceFile';
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: 1hint: 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的辅助链表的尾部移动,这有可能是SMON从LRU的主链表上的非脏块、TCH =1并且状态是非PIN的BUFFER被挂接到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=6或LRU_FLAG=4,如果数据库刚刚启来,可能没有LRU_FLAG=6、LRU_FLAG=4,那需要做大量的物理读操作,才会有LRU_FLAG=6或LRU_FLAG=4
sys@ZMDB alter system flush buffer_cache;
System altered.
sys@ZMDB selectlru_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 bufferslevel 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 139Execution 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)
Statistics1 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 selectLRU_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,搜索第一次DUMP的trace文件
/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,搜索第二次DUMP的trace文件
/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)
观察LRUTCH =2时冷端移到热端
1、BUFFER手动设为100M
ALTER SYSTEM SETmemory_max_target=0 scope=spfile;
ALTER SYSTEM SET memory_target=0;
alter system set sga_target=0;
create table gyj1_t80 (idint,name char(2000));
create table gyj2_t80 (idint,name char(2000));
begin
for i in 1 .. 30000
loop
insert into gyj1_t80 values(i,'gyj'||i);
commit;
end loop;
end;
/
SQL SQL selectbytes/1024/1024||'M' from dba_segments where segment_name='GYJ1_T80' andowner='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 ongyj1_t80(id);
create index idx_gyj2_t80m ongyj2_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 level1';
Session altered.
SQL select * fromv$diag_info where name='Default Trace File';
INST_ID NAME
VALUE1 Default Trace File
/u01/app/oracle/diag/rdbms/jfdb/jfdb/trace/jfdb_ora_7210.trc
发生一个物理读走索引
set autot on
selectid,name,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;
SQL selectid,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),TCHfrom 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 anddbablk=581;
LRU_FLAG LOWER(BA) TCH DECODE(STA
0 000000009fca8000 1 xcur
SQL selectLRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;
LRU_FLAG LOWER(BA) TCH
0 000000009fca8000 5
本文转自网络,原文链接:https://developer.aliyun.com/article/787650
本站部分内容转载于网络,版权归原作者所有,转载之目的在于传播更多优秀技术内容,如有侵权请联系QQ/微信:153890879删除,谢谢!
我们要的不是数据,而是数据告诉我们的事实。大多数人面临这样一个挑战:我们认...
线程?Thread?/?进程?Process 进程 一个正在运行的程序进程间内存不共享 通过进程...
我国21世纪的时候就已经进入了电子信息时代,几乎人人都在网上冲浪,了解世界各...
作者:冯江涛 Beats 是轻量级(资源高效,无依赖性,小型)和开放源代码日志发送...
清楚你的优先级 代码的写法有很多种:有的运行起来很快,有的只会占用少量内存,...
近日,据业内消息,资深 域名 投资人戴跃通过经纪人中介拿下了efang.com。 通过 ...
现在由于有海量的数据需要处理,很多企业已开始打造自己的服务器和数据中心,而...
1. 熟悉轻量应用服务器基础知识 什么是轻量应用服务器 Lighthouse? 轻量应用服...
域名 过期多少天不能赎回?一般来说,域名到期之后有一定时间的续费期,续费期结...
今年5月,在发布 Fastly Developer Hub 时 Fastly 客户解决方案高级副总裁亚当登...