unt(*) 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,搜索第一次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)
观察LRU TCH>=2时冷端移到热端
1、BUFFER手动设为100M
ALTER SYSTEM SET memory_max_target=0 scope=spfile;
ALTER SYSTEM SET memory_target=0;
alter