|
原帖由 sundog315 于 2010-9-17 11:37 发表
1.select count(*) from test_t1;
test_t1的内容位于LRU的冷端
2.select count(*) from test_t2;
还可以放下,比test_t1靠近热端
3.循环select count(*) from test_t1;
结果不变
4.select count(*) from test_t3;
从冷端查找,刷掉test_t1
所以,这个就是为什么觉得是先进先出的原因。其实并不是先进先出。
的确,如你说得可以解释我那个试验...
那么我这里修改一下试验
1.select count(*) from test_t1;
test_t1的内容位于LRU的冷端
2.select count(*) from test_t2;
还可以放下,比test_t1靠近热端
3. declare
a number;
i number;
begin
i:=0;
for i in 1..50 loop
select /*+ use_nl(test_t1,txx) */ count(object_type) into a from mydb.test_t1,mydb.txx where test_t1.object_id=txx.object_id;
end loop;
end;
/
这样我应该可以让TEST_T1上的block移动到热端了吧?
如下:
SQL> startup force
ORACLE instance started.
Total System Global Area 126950956 bytes
Fixed Size 454188 bytes
Variable Size 96468992 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
2 4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
3 7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
4 bh.object_name,bh.blocks
5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
6 o.name object_name,count(*) BLOCKS
7 from obj$ o, x$bh x where o.dataobj# = x.obj
8 and x.state !=0 and o.owner# !=0
9 group by set_ds,o.name) bh
10 where ds.set_id >= pd.bp_lo_sid
11 and ds.set_id <= pd.bp_hi_sid
12 and pd.bp_size != 0
13 and ds.addr=bh.set_ds;
SUBCACHE OBJECT_NAME BLOCKS
------------ ---------------------------------------- ----------
DEFAULT AQ$_QUEUES 2
DEFAULT REPCAT$_REPPROP 1
Elapsed: 00:00:00.00
SQL> select count(*) from mydb.test_t1;
COUNT(*)
----------
19068
Elapsed: 00:00:00.01
SQL> select count(*) from mydb.test_t2;
COUNT(*)
----------
19068
Elapsed: 00:00:00.00
SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
2 4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
3 7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
4 bh.object_name,bh.blocks
5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
6 o.name object_name,count(*) BLOCKS
7 from obj$ o, x$bh x where o.dataobj# = x.obj
8 and x.state !=0 and o.owner# !=0
9 group by set_ds,o.name) bh
10 where ds.set_id >= pd.bp_lo_sid
11 and ds.set_id <= pd.bp_hi_sid
12 and pd.bp_size != 0
13 and ds.addr=bh.set_ds;
SUBCACHE OBJECT_NAME BLOCKS
------------ ---------------------------------------- ----------
KEEP TEST_T1 245
KEEP TEST_T2 234
DEFAULT AQ$_QUEUES 2
DEFAULT REPCAT$_REPPROP 1
Elapsed: 00:00:00.00
SQL> declare
2 a number;
3 i number;
4 begin
5 i:=0;
6 for i in 1..500 loop
7 select /*+ use_nl(test_t1,txx) */ count(object_type) into a from mydb.test_t1,mydb.txx where test_t1.object_id=txx.object_id;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:21.09
SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
2 4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
3 7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
4 bh.object_name,bh.blocks
5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
6 o.name object_name,count(*) BLOCKS
7 from obj$ o, x$bh x where o.dataobj# = x.obj
8 and x.state !=0 and o.owner# !=0
9 group by set_ds,o.name) bh
10 where ds.set_id >= pd.bp_lo_sid
11 and ds.set_id <= pd.bp_hi_sid
12 and pd.bp_size != 0
13 and ds.addr=bh.set_ds;
SUBCACHE OBJECT_NAME BLOCKS
------------ ---------------------------------------- ----------
KEEP TEST_T1 245
KEEP TEST_T2 234
DEFAULT TXX 11
DEFAULT T1_IND 41
DEFAULT AQ$_QUEUES 2
DEFAULT REPCAT$_REPPROP 1
6 rows selected.
Elapsed: 00:00:00.00
SQL> select count(*) from mydb.test_t3;
COUNT(*)
----------
19068
Elapsed: 00:00:00.00
SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
2 4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
3 7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
4 bh.object_name,bh.blocks
5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
6 o.name object_name,count(*) BLOCKS
7 from obj$ o, x$bh x where o.dataobj# = x.obj
8 and x.state !=0 and o.owner# !=0
9 group by set_ds,o.name) bh
10 where ds.set_id >= pd.bp_lo_sid
11 and ds.set_id <= pd.bp_hi_sid
12 and pd.bp_size != 0
13 and ds.addr=bh.set_ds;
SUBCACHE OBJECT_NAME BLOCKS
------------ ---------------------------------------- ----------
KEEP TEST_T1 233
KEEP TEST_T2 33
KEEP TEST_T3 234
DEFAULT TXX 11
DEFAULT T1_IND 41
DEFAULT AQ$_QUEUES 2
DEFAULT REPCAT$_REPPROP 1
7 rows selected.
Elapsed: 00:00:00.00
SQL>
可以看到TEST_T2被挤出了KEEP POOL.
可见sundog315说得是对的,keep pool 一样是有LRU链表的 |
|