db_keep_cache_size參数的控制范围測试

2021年09月15日 阅读数:1
这篇文章主要向大家介绍db_keep_cache_size參数的控制范围測试,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

ocm考试新题中。需要建立keep存储的表,但在该參数是否应该改动上,有一些分歧。有人说asmm会本身主动给keep分配内存的,该參数就不用设置了。sql

看文档和asktom。也是云山雾罩,说什么的都有,仍是来实际的測试吧:ide

 

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同理