SQL> col name format a30

SQL> col value format a30

SQL> conn scott/scott

SQL> create tabletest_default(col number(3)) storage(buffer_pool default);

SQL> create tabletest_keep(col number(3)) storage(buffer_pool keep);

SQL> create table test_recycle(colnumber(3)) storage(buffer_pool recycle);

SQL> insert intotest_default values(1);

SQL> insert into test_keepvalues(1);

SQL> commit;

SQL> set autotrace on statistics

SQL> select * fromtest_default;

统计信息

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

407 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * fromtest_keep;

统计信息

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

407 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> show sga

Total System Global Area 528482304 bytes

Fixed Size 1249944 bytes

Variable Size 150998376 bytes

Database Buffers 369098752 bytes

Redo Buffers 7135232 bytes

SQL> select369098752/1024/1024 from dual; --计算DatabaseBuffers的大小为352M

在上面的例子中，建立了两张表。 Test_default指定默认的default池，test_keep指定了keep池。分别插入了一条数据，然后打开自动跟踪，对这两张表进行查询，由于刚刚执行了 INSERT语句，这两条数据都存放在各自的缓冲区中，因此查询的物理读（physical reads）为 0，接着查看 buffer cache的值，发现大小为352M，SGA为 504M。

下面构造一个较大的批操作，插入的数据大于 504M，将 default区域覆盖掉。

SQL> create tabletest_eat_memory (col1 varchar2(4000), col2 varchar2(4000), col3 varchar2(4000),col4 varchar2(4000), col5 varchar2(4000), col6 varchar2(4000), col7varchar2(4000), col8 varchar2(4000), col9 varchar2(4000), col10 varchar2(4000))storage(buffer_pool default);

SQL> insert intotest_eat_memory select rpad('1',4000,'1'), rpad('2',4000,'2'), rpad('3',4000,'3'),rpad('4',4000,'4'),rpad ('5',4000,'5'), rpad('6',4000,'6'), rpad('7',4000,'7'),rpad('8',4000,'8'), rpad('9',4000,'9'), rpad('0',4000,'0') from all_objectswhere rownum<=15000; --插入15000行数据

统计信息

10410 recursive calls

564195 db block gets

108584 consistent gets

620 physical reads

637527688 redo size --大约插入了638M数据

678 bytes sent via SQL*Net to client

803 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

91 sorts (memory)

0 sorts (disk)

15000 rows processed

SQL> commit;

执行完批操作后，对两张表再次查询。

SQL> select * fromtest_default;

统计信息

70 recursive calls

0 db block gets

13 consistent gets

12 physical reads

0 redo size

407 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * fromtest_keep;

统计信息

70 recursive calls

0 db block gets

13 consistent gets

0 physical reads

0 redo size

407 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

rows processed

SQL> show sga

Total System Global Area 528482304 bytes

Fixed Size 1249944 bytes

Variable Size 150998376 bytes

Database Buffers 369098752 bytes --default区域没有变化

Redo Buffers 7135232 bytes

结果很明显。由于 keep和 default池彼此独立，对于 test_keep的查询的物理读仍然为0，而对 test_default的查询则包含了12个物理读。

上面的例子可以看出，使用 keep池可以保证那些指定 keep池的表不受其他表的影响。可以查询v$bh视图来查找到经常被使用的表，根据表的使用频繁度来确定是否指定 keep池。