Oracle里面的DB_KEEP_CACHE_SIZE参数是什么意思

发布网友发布时间：2022-05-02 04:27

共3个回答

懂视网时间：2022-05-02 08:48

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

假设考keep。该參数，还是打开了吧。recycle同理

db_keep_cache_size參数的控制范围測试

标签：default cte time 文档 call amp count 应该 ons

热心网友时间：2022-05-02 05:56

b_keep_cache_size :是否将表保存在内存中。
文档内容：
DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter)。
中文意思：
DB_KEEP_CACHE_SIZE指定保持缓冲池的大小。保持缓冲的缓冲池的大小是主要的块大小(定义的块大小DB_BLOCK_SIZE初始化参数)。

热心网友时间：2022-05-02 07:14

DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).