db_keep_cache_size參數的控制範圍測試，dbkeepcachesize

db_keep_cache_size參數的控制範圍測試，dbkeepcachesize

ocm考試新題中，需要建立keep儲存的表，但在該參數是否應該修改上，有一些分歧，有人說asmm會自動給keep分配記憶體的，該參數就不用設定了。

看文檔和asktom，也是雲山霧罩，說什麼的都有，還是來實際的測試吧：

SQL> col COMPONENT for a30SQL> 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/hrConnected.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 a10SQL> 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 onSQL> 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 offSQL> conn / as sysdbaConnected.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/hrConnected.SQL> set autot onSQL> 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同理

Oracle裡面的DB_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).
 
控制測試範圍

A公司某項內部控制在上年度執行了100次 那麼控制測試的範圍肯定是針對這100次控制 如果是200次 那麼範圍相應擴大 理解了吧 頻率越高 範圍自然也越大 其實隨便舉個例就能理解到位