db_keep_cache_size参数的控制范围测试

ocm考试新题中，需要创建keep存储的表，但在该参数是否应该修改上，有一些分歧，有人说asmm会自动给keep分配内存的，该参数就不用设置了。 看文档和asktom，也是云山雾罩，说什么的都有，还是来实际的测试吧： SQL col COMPONENT for a30SQL select COMPONEN

ocm考试新题中，需要创建keep存储的表，但在该参数是否应该修改上，有一些分歧，有人说asmm会自动给keep分配内存的，该参数就不用设置了。

看文档和asktom，也是云山雾罩，说什么的都有，还是来实际的测试吧：

SQL> col COMPONENT for a30
SQL> select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');

COMPONENT MB 
------------------------------ ------------------------------------------ 
DEFAULT buffer cache 352MB 
KEEP buffer cache 0MB 

SQL> conn hr/hr
Connected.
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from employees;

Table created.

SQL> insert into t1 select * from t1;

107 rows created.

SQL> /

214 rows created.

SQL> /

428 rows created.

SQL> /

856 rows created.

SQL> /

1712 rows created.

SQL> /

3424 rows created.

SQL> /

6848 rows created.

SQL> /

13696 rows created.

SQL> commit;

Commit complete.

SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME,BYTES/1024/1024||'mb' MB from user_segments where SEGMENT_NAME='T1';

SEGMENT_NA MB 
---------- ------------------------------------------ 
T1 3mb 

SQL> alter table t1 storage( buffer_pool keep);

Table altered.

--造了一张3m的keep表
SQL> set autot on
SQL> select count(*) from t1;

 COUNT(*) 
---------- 
 27392 


Execution Plan
---------------------------------------------------------- 
Plan hash value: 3724264953 
 
------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 85 (0)| 00:00:02 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T1 | 26589 | 85 (0)| 00:00:02 | 
------------------------------------------------------------------- 
 
Note 
----- 
 - dynamic sampling used for this statement 

--做全表扫描，没有物理读，说明是从之前的插入语句读取的数据，并做了240次递归
Statistics
---------------------------------------------------------- 
 240 recursive calls 
 1 db block gets 
 421 consistent gets 
 0 physical reads 
 176 redo size 
 413 bytes sent via SQL*Net to client 
 385 bytes received via SQL*Net from client 
 2 SQL*Net roundtrips to/from client 
 4 sorts (memory) 
 0 sorts (disk) 
 1 rows processed 

SQL> select count(*) from t1;

 COUNT(*) 
---------- 
 27392 


Execution Plan
---------------------------------------------------------- 
Plan hash value: 3724264953 
 
------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 85 (0)| 00:00:02 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T1 | 26589 | 85 (0)| 00:00:02 | 
------------------------------------------------------------------- 
 
Note 
----- 
 - dynamic sampling used for this statement 

--第二次全表扫描已经没有递归了，说明数据已经存入内存，并整齐摆放了
Statistics
---------------------------------------------------------- 
 0 recursive calls 
 0 db block gets 
 310 consistent gets 
 0 physical reads 
 0 redo size 
 413 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> set autot off
SQL> conn / as sysdba
Connected.
SQL> select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS
 2 where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');

COMPONENT MB 
------------------------------ ------------------------------------------ 
DEFAULT buffer cache 352MB 
KEEP buffer cache 0MB 
--查看内存，整齐摆放的数据并未在keep内存里，说明在default里
SQL> alter system set db_keep_cache_size=12m;

System altered.

SQL> select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS
 2 where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');

COMPONENT MB 
------------------------------ ------------------------------------------ 
DEFAULT buffer cache 340MB 
KEEP buffer cache 12MB 
--开辟keep内存
SQL> conn hr/hr
Connected.
SQL> set autot on
SQL> select count(*) from t1;

 COUNT(*) 
---------- 
 27392 


Execution Plan
---------------------------------------------------------- 
Plan hash value: 3724264953 
 
------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 85 (0)| 00:00:02 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T1 | 26589 | 85 (0)| 00:00:02 | 
------------------------------------------------------------------- 
 
Note 
----- 
 - dynamic sampling used for this statement 

--重新做全表扫描，优化器虽然已经发现内存default里有数据，但是keep内存开辟了，表又是keep的，但keep里没找到数据
--所以在此强制做了物理读
--说明keep参数为0的时候，表虽然是keep的，但数据还是在default里的，keep的大小并未被asmm自动分配
Statistics
---------------------------------------------------------- 
 0 recursive calls 
 0 db block gets 
 310 consistent gets 
 307 physical reads 
 0 redo size 
 413 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 count(*) from t1;

 COUNT(*) 
---------- 
 27392 


Execution Plan
---------------------------------------------------------- 
Plan hash value: 3724264953 
 
------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 85 (0)| 00:00:02 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T1 | 26589 | 85 (0)| 00:00:02 | 
------------------------------------------------------------------- 
 
Note 
----- 
 - dynamic sampling used for this statement 


Statistics
---------------------------------------------------------- 
 0 recursive calls 
 0 db block gets 
 310 consistent gets 
 0 physical reads 
 0 redo size 
 413 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> spool off