Control range of db_keep_cache_size parameters test

OCM the new question of the exam. There is a need to create a keep stored table, but there are some differences as to whether the parameter should be changed. Some people say that ASMM will take the initiative to allocate memory to the keep, the number of parameters will not be set.

See documents and Asktom. Also enveloping, say what all have, or come to the actual test it:

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& Gt ALTER TABLE T1 storage (Buffer_pool keep);  Table altered.--built a 3m keep table sql> set Autot onsql> Select COUNT (*) from T1;                                                                           COUNT (*)---------- 27392 execut                                                                                                                                 Ion 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 --Do a full table scan, no physical read, indicating that the data was read from the previous INSERT statement and made 240 recursive statistics--------------------------------------                                                           --------------------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 SQ                                                               L*net roundtrips To/from Client 4 sorts (memory) 0 Sorts (disk) 1 rows P  rocessed 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-the second full table scan is no longer recursive, indicating that the data has been stored in memory and neatly placed statistics-                                                              ---------------------------------------------------------0 Recursive calls                                                              0 db Block gets                                                               310 consistent gets 0 physical reads 0 Redo Size 4                                        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 sy Sdbaconnected.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--view memory. The neatly placed data is not in the keep memory, indicating in 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 CAC He 12MB--open Keep memory sql> conn hr/hrconnected.sql> set Autot o  Nsql> Select COUNT (*) from T1;                                                                           COUNT (*)---------- 27392 execut                                                                                                                                 Ion 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 --Do a full table scan again, the optimizer although has found the memory default in the data, but keep memory opened up, the table is keep, but keep found no data--so this forced to do a physical reading--explain keep When the number is 0, the table is keep, but the data is still in the default,                  The size of the keep was not 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 thi                                S 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/f              ROM Client                              0 sorts (memory) 0 sorts (di                                                     SK) 1 rows processed Sql> Spool Off

Hypothesis test Keep. The number, or open it. Recycle

Control range of db_keep_cache_size parameters test