|
测试在KEEP Pool中,cache的作用~ 发现无论nocache/cache test_t1都无法把全表扫描的test_t1留在keep中~
似乎对于全表扫描这样的方式读入到keep中的block,cache都没有作用,似乎是先入先出的方式使用keep pool~
SQL> -- nocache table test_t1
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 table_name, blocks, buffer_pool, cache
2 from dba_tables
3 where owner = 'MYDB'
4 and table_name in ('TEST_T1', 'TEST_T2', 'TEST_T3');
TABLE_NAME BLOCKS BUFFER_ CACHE
------------------------------ ---------- ------- ----------
TEST_T1 252 KEEP N
TEST_T2 245 KEEP N
TEST_T3 245 KEEP N
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
SQL> select count(*) from mydb.test_t1;
COUNT(*)
----------
19068
SQL> select count(*) from mydb.test_t2;
COUNT(*)
----------
19068
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
SQL> declare
2 a number;
3 i number;
4 begin
5 i:=0;
6 for i in 1..50 loop
7 select count(*) into a from mydb.test_t1;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
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
SQL> select count(*) from mydb.test_t3;
COUNT(*)
----------
19068
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 32
KEEP TEST_T2 234
KEEP TEST_T3 234
DEFAULT AQ$_QUEUES 2
DEFAULT REPCAT$_REPPROP 1
SQL> -- cache table test_t1
SQL> alter table mydb.test_t1 cache;
Table altered.
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 table_name, blocks, buffer_pool, cache
2 from dba_tables
3 where owner = 'MYDB'
4 and table_name in ('TEST_T1', 'TEST_T2', 'TEST_T3');
TABLE_NAME BLOCKS BUFFER_ CACHE
------------------------------ ---------- ------- ----------
TEST_T1 252 KEEP Y
TEST_T2 245 KEEP N
TEST_T3 245 KEEP N
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
SQL> select count(*) from mydb.test_t1;
COUNT(*)
----------
19068
SQL> select count(*) from mydb.test_t2;
COUNT(*)
----------
19068
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
SQL> declare
2 a number;
3 i number;
4 begin
5 i:=0;
6 for i in 1..50 loop
7 select count(*) into a from mydb.test_t1;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
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
SQL> select count(*) from mydb.test_t3;
COUNT(*)
----------
19068
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 32
KEEP TEST_T2 234
KEEP TEST_T3 234
DEFAULT AQ$_QUEUES 2
DEFAULT REPCAT$_REPPROP 1
|
|