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 fp