当前位置:Gxlcms > mysql > db_keep_cache_size参数的控制范围测试

db_keep_cache_size参数的控制范围测试

时间:2021-07-01 10:21:17 帮助过:5人阅读

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
如果考keep,该参数,还是打开了吧,recycle同理

人气教程排行