Db_keep_cache_size control range test, dbkeepcachesize

Source: Internet
Author: User

Db_keep_cache_size control range test, dbkeepcachesize

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.



What is the DB_KEEP_CACHE_SIZE parameter in Oracle?

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 ).

Control Test Scope

Company A's internal control performed 100 times in the previous year, so the scope of the control test must be aimed at these 100 controls. If it was 200 times, the scope should be expanded accordingly. The higher the frequency, the wider the scope, the more naturally. the bigger the case, the more case can be understood.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.