Db_keep_cache_size control range test

In the new questions of the ocm examination, you need to create a table for storing keep, but there are some differences regarding whether the parameter should be modified. Some people say that asmm will automatically allocate memory to keep, you do not need to set this parameter. See the document and asktom, which is also a cloud fog cover. Let's test everything: SQLcolCOMPONENTfora30SQLselectCOMPONEN.

In the new questions of the ocm examination, you need to create a table for storing keep, but there are some differences regarding whether the parameter should be modified. Some people say that asmm will automatically allocate memory to keep, you do not need to set this parameter. See the document and asktom. They are also in the fog of the cloud. Let's test everything: SQL col COMPONENT for a30SQL select COMPONEN.

In the new questions of the ocm examination, you need to create a table for storing keep, but there are some differences regarding whether the parameter should be modified. Some people say that asmm will automatically allocate memory to keep, you do not need to set this parameter.

Reading the document and asktom are also cloud fog covers. Let's test everything:

SQL> col COMPONENT for a30SQL> select COMPONENT, CURRENT_SIZE/1024/1024 | 'mb from V _ $ SGA_DYNAMIC_COMPONENTS where COMPONENT in ('default buffer cache ', 'Keep buffer cache'); component mb -------------------------------- -------------------------------------------- DEFAULT buffer cache 352 mb keep buffer cache 0 mb 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 from user_segments where SEGMENT_NAME = 't1 '; SEGMENT_NA MB ---------- ------------------------------------------ T1 3 mb SQL> alter table t1 storage (buffer_pool keep); Table altered. -- create a 3 m keep table SQL> set autot onSQL> select count (*) from t1; COUNT (*) ---------- 27392 Execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 bytes | 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 -- performs a full table scan without physical reads, indicating that the data is read from the previous insert, 240 recursive Statistics failed 240 recursive CILS 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 bytes | 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 -- the second full table scan has no recursion, indicating that the data has been stored in the memory, statistics defaults 0 recursive CILS 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 352 mb keep buffer cache 0 MB -- view the memory, the neatly placed data is not in the keep memory, in default, SQL> alter system set db_keep_cache_size = 12 m; 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 340 mb keep buffer cache 12 MB -- open up keep memory SQL> conn hr/hrConnected. SQL> set autot onSQL> select count (*) from t1; COUNT (*) ---------- 27392 Execution Plan ------------------------------------------------------ Plan hash value: 3724264953 bytes | 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 | notice Note ------dynamic sampling used for this statement -- perform a full table scan again. Although the optimizer has found data in the memory default, the keep memory is opened, the table is keep, but no data is found in the keep. Therefore, physical read is forced here. When the keep parameter is 0, the table is keep, but the data is still in default, the keep size is not automatically allocated by asmm. Statistics defaults 0 recursive call0 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 bytes | 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 | notice Note ------dynamic sampling used for this statement Statistics limit 0 recursive CILS 0 db block gets 310 consistent gets 0 physical reads 0 redo size 413 bytes sent via SQL * Net to client 385 bytes provisioned ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> spool off

If keep is used, open this parameter. recycle is the same.